CODEWITHSUNDEEP
javamysqlhsqldb
Bala
Bala

Reputation: 11234

How to copy data from one db to another db using java?

I have a situation where I need to copy data from HSQLDB text tables to MYSQL using java and INSERT into...as SELECT.... Not sure how to go from here.

...
Connection hcon = DriverManager.getConnection(param1); #hsql connection
...
Connection mscon = DriverManager.getConnection(param2); #mysql connection
...
Statement htmt = hcon.createStatement();
Statement mstmt = mscon.createStatement();
...
#How do I reference MySQL here?
ResultSet r = hstmt.executeQuery("insert into mysqlemp as select * from hsqlemp");

I am aware there is a LOAD DATA INFILE... to load text into table and its not an option for me.

Upvotes: 2

Views: 5164

Answers (1)

stevecross
stevecross

Reputation: 5684

You are making the wrong approach. You can not access the MySQL database with the connection that was made to the HSQL database. You could do something like this:

PreparedStatement loadStatement = hcon.prepareStatement("SELECT data FROM table");
PreparedStatement storeStatement = mscon.prepareStatement("INSERT INTO table (data) VALUES (?)");

ResultSet loadedData = loadStatement.executeQuery();

while (loadedData.next()) {
    storeStatement.setString(1, loadedData.getString(1));
    storeStatement.executeUpdate();
}

Upvotes: 1

Related Questions