1

I have a MySQL database and a H2 database. I have a resultSet from a SELECT to MySQL.

Now I want to insert each row of this resultSet into a H2 db table

I've tried this.

// Return records filtered
ResultSet filter = stmt.executeQuery(query);
// Create table filterName in H2
ResultSetMetaData rsMetaData = filter.getMetaData();
query = "CREATE TABLE " + filterName + "(";
ArrayList<String> cols = new ArrayList<String>();

for(int i = 1; i < rsMetaData.getColumnCount(); i++) {
   cols.add(rsMetaData.getColumnName(i));
   query = query + rsMetaData.getColumnLabel(i) + " " + 
   rsMetaData.getColumnTypeName(i) + "(" + rsMetaData.getColumnDisplaySize(i) + ") NOT NULL, ";
}

query = query + ");";
String queryDel = "DROP TABLE IF EXISTS " + filterName + ";";
stmtH2.executeUpdate(queryDel);
stmtH2.executeUpdate(query);
query = "INSERT INTO " + filterName + " VALUES (";

// Insert into H2 table
while(filter.next() ) {

   for(int i = 0; i < cols.size(); i++) {

      if(i != cols.size()-1)
     query = query + filter.getString(i+1) + ", ";
      else
     query = query + filter.getString(i+1) + ")";

   }
}

It is working, but when number of rows increase this is very slow. Is there another way to insert each row into a H2 db table?

jumpy
  • 73
  • 7
  • I think you could try select insert combination mentioned here https://stackoverflow.com/a/25971/2199826 – Naveen Sep 26 '19 at 21:39
  • Do the MySQL server and the H2 database live on the same machine, and is that the machine on which your Java code will be running? – Gord Thompson Sep 27 '19 at 00:46
  • @naveen I can't do that because resultSet is a result of a select on MySQL database – jumpy Sep 27 '19 at 07:41
  • @GordThompson the H2 database is running on the same machine of the java codevis running, but MySQL is running on different machine – jumpy Sep 27 '19 at 07:51

0 Answers0