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

Question

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.

HSQLDB comes with a utility which can copy tables between different databases. If you just need this one time, I suggest use that. — icza, Aug 22 '14 at 10:12
Possible duplicate of [How to copy table from one database to another?](https://stackoverflow.com/questions/22624711/how-to-copy-table-from-one-database-to-another) — Alex R, Sep 17 '18 at 16:34

score 1 · Answer 1 · answered Aug 22 '14 at 10:11

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();
}

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

1 Answers1