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

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 fna开发者_运维技巧me = 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)

You might need to use a different FileMaker JDBC driver. I don't think that the JDBC driver shipped by FileMaker supports streaming result sets, which means it loads all the data into memory.

If you'd like, you can try our FileMaker JDBC driver at http://java.net/projects/woof/. It is quite a bit slower than the driver supplied by FileMaker, because it runs over the (slow) XML Web Publishing interface, but it streams the results and thus should not run out of memory.

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.

PreparedStatement is the way to go; no worries there.

I'd have to see the exception that described the crash to know what the root cause is, but my guess is that you're trying to INSERT too many records at once. I'd break it into smaller chunks and commit each chunk as I went as a single transaction. Your transaction log doesn't have to contain every INSERT that way.

Maybe that would fix the problem:

//...
stmt.executeUpdate();
stmt.close();
//...

(release the statement explicitly instead of waiting for the GC to do it for you)