Solved

Data Migration

Posted on 2003-11-02
11
206 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
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…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

707 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

19 Experts available now in Live!

Get 1:1 Help Now