Dfyjkt

I'm trying to migrate a big db from derby to HSQLDB in a Spring Boot service, like 1.5M regs in few tables of 10 columns. I'm checking with VisualVM; byte and char consume a lot of memory. But the biggest delta in time are in the derby classes.

Sometimes the error is thrown here, but other times thrown in other controllers. I don't want to touch all files to add my catchOutofMemory to restart.

Following is a version of my code, the block comment shows the resume of the process:

run()//thread inside static function.
 while(keepMigrating)
 keepMigrating=Migrate();
 

private static boolean Migrate(JdbcTemplate derby,JdbcTemplate hsql)
 int regs = 100000;
 PreparedStatement statement = null;
 ResultSet rs = null;
 PreparedStatement statementHSQL = null;
 try 
 for (String table : tables) //tables contains all tables to migrate
 //check how many registers left asd asign to cant, if cant is 0 the empty is true.
 PreparedStatement statementUpd;
 while (!empty) 
 if (trys <= 0) throw new Exception("redo");
 //check how many registers left asd asign to cant, if cant is 0 the empty is true and out of bucle and ready to check next table
 /*
 *Next process resume as:
 *fetch data from derby that hasnt been migrated limited by cant
 *create a batch to insert in hsql
 *create a update for derby
 *create a delete in case someting goes wrong
 *excecute insert and update, if someting in batch fail delete the entry in migrate table
 *reduce regs to get out of migrate method at some ponint.
 */
 statement = derby.getDataSource().getConnection().prepareStatement(
 MessageFormat.format(select_all_migrate_false_and_fetch_cant,table));
 statementUpd = new PreparedStatement[cant];
 ArrayList<String> deleteIds = new ArrayList<>();
 StringBuilder columnNames = new StringBuilder();
 StringBuilder updateSQL = new StringBuilder();
 StringBuilder bindVariables = new StringBuilder();
 try 
 ResultSetMetaData meta = rs.getMetaData();
 for (int i = 1; i <= meta.getColumnCount(); i++) 
 if (!meta.getColumnName(i).equals("MIGRATED")) 
 if (i > 1) 
 columnNames.append(", ");
 bindVariables.append(", ");
 
 columnNames.append(meta.getColumnName(i));
 bindVariables.append('?');
 
 
 String sql = "INSERT INTO " + table.substring(4) + " ("
 + columnNames
 + ") VALUES ("
 + bindVariables
 + ")";
 statementHSQL = hsql.getDataSource().getConnection().prepareStatement(sql);
 HashMap<String, Object> data = new HashMap<>();
 int row = 0;
 int lastId = 0;
 String columnName;
 while (rs.next()) 
 for (int i = 1; i <= meta.getColumnCount(); i++) 
 columnName = meta.getColumnName(i);
 Object o = rs.getObject(i);
 statementHSQL.setObject(i, o);
 if (columnName.equals(mainColumn))
 deleteIds.add(String.valueOf(o));
 if (!(meta.getColumnType(i) == 2004)) data.put(columnName, o);
 if (columnName.equals(mainColumn)) id = rs.getObject(i);
 
 int c = 1;
 String update = MessageFormat.format("INSERT INTO 0M (1M, MIGRATED) VALUES(?, TRUE)",
 table.substring(4), mainColumn).replace(""M", "M"");//migrated state is saved in other table
 lastId = Integer.valueOf(String.valueOf(id));
 statementUpd[row] = derby.getDataSource().getConnection().prepareStatement(update);
 statementUpd[row].setObject(1, rs.getObject(mainColumn));
 updateSQL = new StringBuilder();
 statementHSQL.addBatch();
 row += 1;
 
 /*
 * Build delete query in case of inserted values in HSQLDB but not updated in DERBY
 */
 StringBuilder builder = new StringBuilder();
 builder.append("(");
 int count = 1;
 for (String s : deleteIds) 
 if (count > 1) builder.append(", ");
 builder.append("?");
 count++;
 
 builder.append(")");
 String str = builder.toString();
 String queryDelete = "DELETE FROM " + table.substring(4) + " WHERE " + mainColumn + " IN " + str;
 PreparedStatement statementHSQLDel = hsql.getDataSource().getConnection().prepareStatement
 (queryDelete);
 int c = 1;
 for (String s : deleteIds) 
 statementHSQLDel.setObject(c, s);
 c++;
 
 boolean deletes = statementHSQLDel.execute();
 statementHSQLDel.close();
 try 
 DatabaseUtils.close(statementHSQLDel);
 catch (Exception e) 
 catchOutOfMemory(e);
 
 int result = statementHSQL.executeBatch();
 StringBuilder resultS = new StringBuilder();
 int stCounter = 0;
 int stCounterInsert = 0;
 int stCounterUpdate = 0;
 String notarydebug;
 for (int i : result) 
 int upd = 0;
 try 
 if (i == 1) upd = statementUpd[stCounter].executeUpdate();
 catch (Exception e) 
 catchOutOfMemory(e);
 
 stCounterInsert += i;
 stCounterUpdate += upd;
 resultS.append(",").append(String.valueOf(i)).append("-").append(String.valueOf(upd));
 stCounter += 1;
 
 statementHSQL.clearBatch();
 try 
 DatabaseUtils.close(statementHSQL);
 catch (Exception e) 
 catchOutOfMemory(e);
 
 catch (SQLException se) 
 catchOutOfMemory(se);//otherstuff
 catch (Exception e) 
 catchOutOfMemory(e);
 
 try 
 DatabaseUtils.close(rs);
 DatabaseUtils.close(statement);
 catch (Exception e) 
 catchOutOfMemory(e);
 
 regs=regs-cant;
 
 
 catch (Exception e) 
 if (e.getMessage().equals("redo")) return true;//end the loop of regs maximun and get out of method.
 
return false;//end migration succesfully

private static int catchOutOfMemory(Throwable e) 
 if (e == null) return 0;
 if (e instanceof OutOfMemoryError) 
 Application.restartBat();
 return 1;
 else 
 return catchOutOfMemory(e.getCause());
 

edit:
So I change as sugested inthe comment to accept a commit, something like this:

Connection hsqlCon;
PrepareStatement hsqlStm;
hsqlCon = JdbcHSQLDB.getDataSource().getConnection();
hsqlStm = hsqlCon.prepareStatement(sql);
hsqlStm.addBatch();
hsqlStm.execute();
hsqlStm.close();
hsqlCon.close();

but i got the same heap memory consumpsion:

The type of table in HSQLDB is not clear from the supplied code. You must use this statement once for each table, to make sure the table data is stored in the finename.data file:

SET TABLE tableName TYPE CACHED

The reported sequence of batch INSERT is not correct. Use this sequence:

Connection hsqlCon;
PrepareStatement hsqlStm;
hsqlCon = JdbcHSQLDB.getDataSource().getConnection();
hsqlStm = hsqlCon.prepareStatement(sql);

 // repeat this block until all is finished
 // repeat for 1000 rows
 hsqlStm.addBatch();
 
 hsqlStm.executeBatch(); // after every 1000 rows
 

hsqlStm.close();
hsqlCon.close();