Data Migration

hi, i got some problem here. The aim of the coding below is to get the column name of name in one table(source) and another column name of score in another table(source1). The selected columns(name and score) will be migrated another table(target) into its respective columns(custName and custScore). Hence this code is only limited to the specified columns and tables.

My problem now is that I need to able to select multiple tables and column and migrate them to the table(target). Pls help me or u can give me a sample of codes that allow the database to read in multiple columns and table. Thank!!!

import java.io.*;
import java.util.*;
import java.sql.*;
import java.io.FileInputStream;
import java.util.Properties;

public class dataMapping
{
    public static void main(String[] args) throws Exception
          {
               try{
                         FileInputStream propFile = new FileInputStream("Data.txt"); // setting up properties object
                  Properties p = new Properties(); //name the Properties object 'p'
                  p.load(propFile);
                             
                              String url = "jdbc:odbc:" + p.getProperty("Dsn");//get propertries of database url
                              Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //initialise driver
                              Connection con = DriverManager.getConnection(url, "", ""); //database connection
                             
                              String sourceCol =  p.getProperty("sourceCol");//get propertries of sourceCol
                              String sourceTable =  p.getProperty("sourceTable");//get propertries of sourceTable
                              String sourceCol1 =  p.getProperty("sourceCol1");//get propertries of sourceCol
                              String sourceTable1 =  p.getProperty("sourceTable1");//get propertries of sourceTable
                             
                              //String SQL = "SELECT s."+sourceCol+",q."+sourceCol1+" FROM "+sourceTable+" s, "+sourceTable1+" q WHERE s.ID = q.ID";

                             //String SQL = "select s.
                              Statement s1 = con.createStatement();
                              ResultSet rs = s1.executeQuery(SQL);          

                              Vector store = new Vector();
                              Vector store1 = new Vector();
                              int i = 0;
                              while(rs.next())
                              {
   
                                   store.add(rs.getString(1));
                                   store1.add(rs.getString(2));
                                   System.out.println(" " + store.get(i));//print all the data    
                                   //System.out.println(" " + store1.get(i));
                                   String TargetCol = p.getProperty("TargetCol");//get propertries of MapCol
                                   String TargetTable =  p.getProperty("TargetTable");//get propertries of MapTable
                                   //int ivalue = Integer.parseInt("TargetCol");
                                             
                                   //String sql2 = " INSERT INTO " + TargetTable + '" VALUES "' + TargetCol + " = '" + store.get(i) + "' Where ID = " + (i + 1);
                                   String sql2 = ("INSERT INTO target (custName,custScore) " + "VALUES ('"+ store.get(i) +"','"+ store1.get(i) +"')");
                                   Statement s2 = con.createStatement();
                                   s2.executeUpdate(sql2);
                             
                                             
                                   i++;
                              }
                             
                              con.close();//close database connection
                     }
                             
               catch(SQLException e)
               {                                                      
                    e.printStackTrace();                                            
               }
               catch(ClassNotFoundException f)
               {
                    f.printStackTrace();
               }
    }
}// close


YvonneYapAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sudhakar_koundinyaCommented:
This is not a big task,

Even your code is also right hough it was not tested by me). But only thing is you have complicated your code.


And also here is the sample code, which is almost similar to your's

                              String url = "jdbc:odbc:" + p.getProperty("Dsn");//get propertries of database url
                              Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //initialise driver
                              Connection con = DriverManager.getConnection(url, "", ""); //database connection
                            String SQL="select ename,dname from emp,dept where emp.deptno=dept.deptno" ;
                                          Statement st=con.createStatement();
                                          Resultset rs=st.executeQuery(SQL);
                                          while(rs.next()
                                          {
                                                Statement st1=con.createStatement();
                                                SQL="insert into emp1(ename,dname) values('"+rs.getString(1)+"','" +rs.getString(2)+"'";
                                                st1.executeUpdate(SQL);
                                                st1.close();
                                          }
                                          rs.close();
                                          st.close();

                           
                             
                              con.close();//close database connection
0
vk33Commented:
Hi!

I would try something like this:

1. Code:

// getting properties...
ArrayList tables = new ArrayList();
String tmp = null;
int i=0;
while ((tmp = p.getProperty("table" + i)) != null) {
   tables.add(tmp);
}

ArrayList sourceCols = new ArrayList();
i = 0;
while ((tmp = p.getProperty("sourcecol" + i)) != null) {
   sourceCols.add(tmp);
}

String destTable = p.getProperty("desttable");
ArrayList destCols = new ArrayList();
i=0;
while ((tmp = p.getProperty("destcol" + i)) != null) {
   destCols.add(tmp);
}
String whereClause = p.getProperty("whereClause");
.....
StringBuffer sql = new StringBuffer();
// creating insert clause...
sql.append ("INSERT INTO " + destTable + "(");
Iterator iter = destCols.iterator();
while (iter.hasNext()) {
   String colname = (String)iter.next();
   sql.append (colname);
   if (iter.hasNext())
      sql.append (",");
}

//creating select clause...
sql.append (") SELECT ");
iter = sourceCols.iterator();
i = 0;
while (iter.hasNext()) {
   String colName = (String)iter.next();
   sql.append ("table" + i + "." + colName);
   if (iter.hasNext())
      sq.append (",");
}
sql.append (" FROM ");
iter = tables.iterator();
i = 0;
while (iter.hasNext()) {
   String tablename = (String)iter.next();
   sql.append (tablename + " table" + i);
   if (iter.hasNext())
      sql.append(",");
}
sql.append ("WHERE " + whereClause);

Statement st = con.createStatement();
st.executeUpdate(sql.toString());

2. properties file:
table0 = sometable
table1 = othertable
...
sourcecol0 = somecolumn
sourcecol1 = othercolumn
...
desttable = mynewtable
destcolumn0 = mynewcolumn
destcolumn1 = anothernewcolumn
whereclause = "table0.id=table1.id"

The code will produce the following sql-statement:
INSERT INTO mynewtable (mynewcolumn,anothernewcolumn) SELECT (table0.somecolumn, table1.othercolumn) FROM sometable table0, othertable table1 WHERE table0.id=table1.id

You don't need to fetch your data before insertion, you can do it with a single query above. Tested on PostgreSQL and MySQL (no more DBMS here :))

If you need some more sophisticated logic you'll need to specify more data in properties files.

Good luck!
0
YvonneYapAuthor Commented:
To vk33,
 can u give me yr email address. Cause I would like to send u my file as i not really understand the coding u provide. I hope u will help me to amend for me thanx
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

vk33Commented:
Sorry, we're not allowed to post e-mail address. :(

The main idea of my code is the following:
1. Get data from property files.
2. Build a special sql-statement (insert from select) which will do the job within one statement.
3. Execute it.

What exactly is not clear? Feel free to ask!

Regards!
0
YvonneYapAuthor Commented:
I mean that I do not noe where to include yr code or make change to my coding. As for my property files, I dun understand the part u say
table0 = sometable
table1 = othertable
...
sourcecol0 = somecolumn
sourcecol1 = othercolumn
...
desttable = mynewtable
destcolumn0 = mynewcolumn
destcolumn1 = anothernewcolumn
whereclause = "table0.id=table1.id"

However my proterty file is
Dsn=dm
sourceTable=source1
sourceCol=Name
sourceTable1=Source2
sourceCol1=score
TargetTable=target
TargetCol=custName
TargetCol1=custScore

Hum... can i give u my email instead cause i really need the coding urgently!!
0
vk33Commented:
Well, if I'm not mistaken your task is processing more than two tables thus more than two source columns. In your property file it was:

sourceTable = ...
souceCol = ...

In mine it is:
table0 = ...
table1 = ...
sourcecol0 = ...
sourcecol1 = ...

You can supply as many tables/columns as you need. If you're processing n tables you'll need to put n tablex, sourcecolx and destcolumnx. It's easy!

My code looks through your properties and determines number of tables processed. E.g. if you supplied table0, table1, table2 and table3 it will get them all.

Anyway, your task is just to build SQL-statement. Ok, an example... Suppose you have 3 tables: t0,t1 and t2. You want to store column "name" from t1, column"age" from t2 and column"score" from t3 in a separate table "report" with the same column names. Your property file will look like this:

table0=t0
table1=t1
table2=t2
sourcecol0=name
sourcecol1=age
sourcecol2=score
desttable=report
destcolumn0=name
destcolumn1=age
destcolumn2=score
whereclause="table0.id=table1.id AND table1.another_id=table2.id"

My code will build the following SQL:
INSERT INTO report (name,age,score) SELECT table0.name table1.age ttable2.score FROM t0 table0, t1 table1, t2 table2 WHERE table0.id=table1.id AND table1.anogher_id=table2.id

That's it! If you have more problems - put your code on some public ftp or http and post a link here. It's a common practice here...

Good luck!
0
YvonneYapAuthor Commented:
sorry i think i didnt get yr last comment!!!!!!
0
YvonneYapAuthor Commented:
oops i saw yr comment now.. ok let me try!! thanx
0
YvonneYapAuthor Commented:
But can i ask that if i am not allow to key in the no of table or columns in the property file. I mean what if it depent on the user to key in no of tables or columns...
0
vk33Commented:
Didn't get you... Once again!
0
vk33Commented:
ahh, I think I got it! Do you mean that the information about number of tables and columns is typed in by user runtime?

No problems anyway. You can prompt the user to enter everything:

BufferedReader console = new BufferedReader(new InputStreamReader(System.in));
System.out.print("Number of tables: ");
int n = Integer.parseInt(console.readLine());
ArrayList tables = new ArrayList();
for (int i=0; i<n; i++) {
   System.out.print ("Table " + i + " name: ");
   tables.add(console.readLine());
}
.......

So there's no difference how you get the parameters: from property files or user input...

Regards!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.