Solved

Data Migration

Posted on 2003-11-02
11
210 Views
Last Modified: 2010-03-31
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


0
Comment
Question by:YvonneYap
  • 5
  • 5
11 Comments
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9669189
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
 
LVL 4

Expert Comment

by:vk33
ID: 9669288
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
 

Author Comment

by:YvonneYap
ID: 9669450
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
 
LVL 4

Expert Comment

by:vk33
ID: 9669670
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
 

Author Comment

by:YvonneYap
ID: 9669897
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:vk33
ID: 9669987
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
 

Author Comment

by:YvonneYap
ID: 9670264
sorry i think i didnt get yr last comment!!!!!!
0
 

Author Comment

by:YvonneYap
ID: 9670283
oops i saw yr comment now.. ok let me try!! thanx
0
 

Author Comment

by:YvonneYap
ID: 9670369
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
 
LVL 4

Expert Comment

by:vk33
ID: 9670383
Didn't get you... Once again!
0
 
LVL 4

Accepted Solution

by:
vk33 earned 250 total points
ID: 9670411
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now