asked on

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

sudhakar_koundinya

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

vk33

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!

YvonneYap

ASKER

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

vk33

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!

YvonneYap

ASKER

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!!

vk33

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!

YvonneYap

ASKER

sorry i think i didnt get yr last comment!!!!!!

YvonneYap

ASKER

oops i saw yr comment now.. ok let me try!! thanx

YvonneYap

ASKER

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...

vk33

Didn't get you... Once again!

ASKER CERTIFIED SOLUTION

vk33

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial