Skip navigation links

MySQL Forums :: Connector/JDBC and Java :: Transfer information from one database to another database


Advanced Search

Transfer information from one database to another database
Posted by: tanha22 maral ()
Date: April 01, 2009 12:54AM

Hello!
I want to transfer information from one database to another database with some condition.
In java i can connect one database and select and insert to this database.
But if I want do this statement for exemple
Insert into database1.firstTable select * from database2.firstTable where database2.firstTable.date > '2009-02-03' order by database2.firstTable.date;

How can I connect to both database.
I have this code to connect to a singel database.
public class CMySql {
private Connection conn = null;


public int connectDB(dataSourceName,userName,password) {


// Register and Load ODBC JDBC driver Load
// TO DO Here I can load another driver. I do not how I can load another driver then the sun.
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

} catch (ClassNotFoundException e) {
System.out.println("Connection failed : " + "MySQL Library is not found ."+e.getMessage());
return CMySqlDriverHandler.LibraryNotFound;
}

try {

String URL = "jdbc:odbc:"+ dataSourceName ;
System.out.println(dataSourceName);



this.conn = DriverManager.getConnection(URL,userName,password);
conn.setCatalog(dataSourceName);
}catch (SQLException ce) {
System.out.println("Connect failed : " + ce.getMessage() + ", Error code :" + ce.getErrorCode());
conn = null;
return CMySql.DatabaseNotFound;
}
return CMySql.ConnectionSucceed;

}


public void updateThisTable(String sql) {
try {
Statement stmt = this.conn.createStatement();
// Execute the insert statement
stmt.execute(sql);
stmt.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}

}
}
public void insertThisTable(Object[] objectArray,String tableName) {
// to enable transaction pairs.
// After finishing "executeInsert" method, must call "setAutoCommit(true)"
PreparedStatement ps = null;
String str = "INSERT INTO " + tableName +" VALUES (" ;
for(int i = 0; i < objectArray.length; i++){
if(i == objectArray.length-1){
str = str + "?)";
}else{
str = str + "?,";
}
}
//setAutoCommit(false);
//--------------------//
try {
ps = this.conn.prepareStatement(str);
for(int i = 0; i < objectArray.length; i++){
ps.setObject(i+1, objectArray);
}
ps.executeUpdate();

} catch (SQLException e) {
System.out.println("SQLException: " + e.getMessage() );
}
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}



static public void main(String args[]) {
CMySql mysql = new CMySql();



mysql.connectDB("database1","root","root");
//mysql.executeInsert(new Object[]{7,"track",true,"name"}, "OBJECTS");
String sql = "UPDATE OBJECTS SET OBJECT_TYPE ='"+ "Track" +"' " +
",TEMPLATE = " + false +
",NAME = '" + "T17-14@TCC1" +"' " +
"WHERE DBID = "+1;
mysql.updateThisTable(sql);
//mysql.setAutoCommit(true);
mysql.disconnectDB();

}


Thankyou for your response



Edited 1 time(s). Last edit at 04/01/2009 01:05AM by tanha22 maral.

Options: ReplyQuote


Subject Written By Posted
Transfer information from one database to another database tanha22 maral 04/01/2009 12:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.