Writing a database migrator in Java, Memory Issues (Code Structuring?)

Question

I'm currently attemping to transfer data away from filemaker pro 11 to MySQL using JDBC.

I've dealt with setting up the connection to each, and have queries that work, and insert the data safely into MySQL.

  try {
  results =
    query.executeQuery("SELECT \"field one\", \"field two\" from table");


  Connection con = DriverManager.getConnection("jdbc:mysql://website.com/database","user","password");

 // Iterate through the results and print them to standard output

  while (results.next()) {
    String fname = results.getString("field one");
    String lname = results.getString("field two");
  System.out.println("Found user \"" + fname + " " + lname + "\"");
  stmt = con.prepareStatement("INSERT ignore INTO table (idtable, name) values (?, ?)");

  // some of the data I've been provided with is pretty horrific,
  // so inserting safely is of large concern.

  stmt.setString(1, fname);
  stmt.setString(2, lname);
  stmt.executeUpdate();

  }
}
catch (SQLException e) {
  System.out.println("Error retrieving data from database.");
   e.printStackTrace();
  //System.exit(1);
}

This works okay for smaller tables(~100,000 records in 4 mins), but some of these are very, very big and cause the application to crash :(.

This needs to be able to run at least once to do a full population, but after that I can limit the output to pick up changes made in say the last week.

I previously wrote this in VB.net, and constructed large inserts, but I've made a switch - and I really need that prepare statement, as the current database has all sorts of crazy characters in there.

Thanks, Paul S

 Error: 
 Exception in thread "AWT-EventQueue-0" java.lang.OutOfMemoryError: Java heap space
at java.lang.StringCoding$StringDecoder.decode(StringCoding.java:151)
at java.lang.StringCoding.decode(StringCoding.java:191)
at java.lang.String.<init>(String.java:451)
at java.util.jar.Attributes.read(Attributes.java:401)
at java.util.jar.Manifest.read(Manifest.java:199)
at java.util.jar.Manifest.<init>(Manifest.java:69)
at java.util.jar.JarFile.getManifestFromReference(JarFile.java:182)
at java.util.jar.JarFile.getManifest(JarFile.java:163)
at sun.misc.URLClassPath$JarLoader$2.getManifest(URLClassPath.java:710)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:238)
at java.net.URLClassLoader.access$000(URLClassLoader.java:73)
at java.net.URLClassLoader$1.run(URLClassLoader.java:212)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:205)
at java.lang.ClassLoader.loadClass(ClassLoader.java:321)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:294)
at java.lang.ClassLoader.loadClass(ClassLoader.java:266)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:435)
at com.mysql.jdbc.PreparedStatement.getInstance(PreparedStatement.java:872)
at com.mysql.jdbc.ConnectionImpl.clientPrepareStatement(ConnectionImpl.java:1491)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4250)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4149)
at datasync2.FMProConnection.companyQuoteInsert(FMProConnection.java:686)
at datasync2.DataSync2View.jButton1ActionPerformed(DataSync2View.java:220)
at datasync2.DataSync2View.access$800(DataSync2View.java:22)
at datasync2.DataSync2View$4.actionPerformed(DataSync2View.java:124)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2012)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2335)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:404)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at         javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:253)
at java.awt.Component.processMouseEvent(Component.java:6268)

Answer 1

Do it in batches. You could start with a batch size of 100K and increase it until performance degrades.

• 1) Try to select a limited number of rows from the unprocessed rows of the source table.
• 2) Do a batch insert

:

Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
int[] insertCounts = stmt.executeBatch();

like here http://download.oracle.com/javase/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html

• 3) Also, keep track of which records had been processed successfully. (either update a flag on the source row or process them in a certain order and save the last one)
• 4) Handle errors, Commit changes to db, free up resources (close statements, etc)

loop 1-4 until all records in source table had been processed successfully.