Oracle Batch Process for JDBC

Today will talk about the Oracle Batch process for JDBC.

When we talk about batch process, we talk about the way in which you can send transaction in a batch when you are making java database applications, this means that instead of send one transaction at the time you will be sending a batch of 10, 30 or more transactions or statements. From the Oracle documentation we can see that the best batch size is between 10 and 30 transactions or statements by batch.

In JDBC one can define an statement a delete, insert, or update SQL statement.

The Oracle JDBC driver has two implementations for batching SQL statements, the first one is the Standard JDBC batching mechanism and the second one is the proprietary Oracle implementation.

As you saw the first is the standard JDBC way and is used in all the JDBC classes as Statement, PreparedStatement and CallebleStatement. The java.sql.Statement work in a different way than the PreparedStatement and CallebleStatment, because you can add different kind of SQL statement to the java.sql.Statement batch, but for the PreparedStatement and CallebleStatement you only can add the same SQL statement to the batch, before we go to the example is good to remember that all batch process need to be in a transaction, so before start a batch process please set the AutoCommit flag of the JDBC connection to false.

For Example:

In a java.sql.Statement you can have a batch composed of insert and update statements or any statements that you want to add to the batch:

String sql = "insert into demoTable (demoName, demoAge) values ('Demo Name', 20)";

String sql1 = "updatre demoTable set demoName = 'Demo Update' where demoAge = 20";


stmt.addBatch(sql);

stmt.addBatch(sql1);


int results[] = stmt.executeBatch();

But in a PreparedStatement or CallableStatement you only can have the same statement, this means the same insert or update, but not both combined, see the example below:

String sql = "insert into demoTable (demoName, demoAge) values (?, ?)";

PreparedStatemen pstmt = conn.prepareStatement(sql);

conn.setAutoCommit(false);

pstmt.setInt( 1, "Demo Name");
pstmt.setString( 2, 20 );
pstmt.addBatch();

pstmt.setInt( 1, "Demo Name1");
pstmt.setString( 2, 21 );
pstmt.addBatch();

int[] count = stmt.executeBatch();

You only can use the same statement in a PreparedStatement because in precompiled in the DBMS, and you are not able to change it, because the parameters are bind to the sql statement that is compiled into the database. 

Finally for the standard way to make batching you can see that is the more portable, easy, but is not predictable like the proprietary batching implementation that offers Oracle.

The proprietary option offer only batching for PreparedStatement and the batching cannot be done for different kind of SQL statement, one point to note here is that the proprietary batching does not have a mechanism that you can use to make PreparedStatement to depend from others PreparedStatements results.

To make batching with the proprietary option you only need to cast the PreparedStatement returned by the JDBC connection to an OraclePreparedStatement and set the executeBatch property to the size that you want for the batch:

((OraclePreparedStatement)pstmt).setExecuteBatch(30);

When you set the ExecuteBatch property this makes the oracle implementation more predictable, because the JDBC driver knows the batch size and how and when to send the statements to the database, you can set a default for this property using the Oracle JDBC connection and setting the setDefaultExecuteBatch property value.

Drawbacks that this option has are:

1. Portability, depends on Oracle driver and database.
2. The batch execution problem: think in a process in which you have two statements, each statement needs to set the setExecuteBatch property value, but each statement will behave like a separate batch, this means that we have two batch one for the first statement and one for the second statement, if the batch statement of the second statement is executed before the first statement and the second statement depends of the first statement batch, this can cause some data lost and some weird exceptions, like not foreign key present exceptions. 

For conclusions:

• First, we saw that the standard way is more portable, but is not predictable. 
• Second, The oracle proprietary way to make batching has some advantages like predictability but you do not control the statement execution, and how the information is sent and when you send this information to the database. 
• Third, I forgot to say that Oracle does not support batching optimization in the standard way for java.sql.Statement, so if you use batching here you will not see any performance improvements, so this means that the Standard way and the Oracle way just only support batching for PreparedStatements. 
• Finally, I presented the ways for make batching with the Oracle driver, so is your opportunity to decide what is the best and optimal way to make batching when you are connecting to the Oracle database, this does not depend of a blog entry, it depends of the necessity that you have for your business, and architecture. 

If you want more information about this interesting topic please see the next link: http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraperf.htm#JJDBC28752 

Thanks and let your comments.