SQL: Batching statements with bound variables.

Question

I'm looking to reduce the round-trips in my application to improve performance. I want to use PreparedStatements for their multitude of benefits.

This is an example of what I've come up with.. generalized with details elided..

  Class.forName( "..Driver" );
  Connection connection = DriverManager.getConnection( .. );      
  PreparedStatement statement = connection.prepareStatement( "UPDATE Table SET XmlData = ? WHERE SomeGUID = ?; INSERT INTO SomeTable ( Col1,Col2 ) VALUES ( ?, ? )" );

  try{ 
     statement.setString( 1, "<XmlData></XmlData>" );
     statement.setString( 2, "32ABD5-438B0-.." );
     statement.setString( 3, "ABC" );
     statement.setString( 4, "XYZ" );
     statement.execute();
     connection.commit();
  }
  catch {
     connection.rollback();
  }
  finally {
     statement.close();
     connection.close();
  }

(Again, this is not the actual code, just a simplified example)

In general, I'm trying to execute multiple insert/update statements in a single statement to reduce round-trip traffic. Is this an effective way to do it or is there a more accepted approach?

I could have thousands of statements for a single transaction. I will likely segment the statement into reasonably-sized blocks to prevent timeouts on a single statement's execution.

I will be supporting multiple DB vendors, but will be using ANSI SQL so there shouldn't be an issue. If needed I can leverage the intelligence my DAL. So, this is a non-issue.

Any tips/suggestions?

Answer 1

PreparedStatement supports batch submitting the same statement with different arguments using addBatch and executebatch. I don't think there's a good way of executing multiple parameterised statements in a cross-database platform manner. Traditionally this would be handled by [evil] stored procs.

Answer 2

Here's an example from Oracle's JDBC manual. This is described as the "standard" (non-Oracle-specific) method.

PreparedStatement pstmt = 
          conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");

pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();

pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();
...
int[] updateCounts = pstmt.executeBatch();

Answer 3

By the way, if you;re concerned about peformance, you should consider using a connection pooling package like dbcp that will also support PreparedStatement pooling. c3p0 is also popular but I don't have any experience with it.