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
– icza, Commented Aug 22, 2014 at 10:12
Possible duplicate of How to copy table from one database to another? — Alex R
– Alex R, Commented Sep 17, 2018 at 16:34

stevecross · Accepted Answer · 2014-08-22 10:11:16Z

1

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

answered Aug 22, 2014 at 10:11

stevecross

5,6948 gold badges51 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related