JDBC Batching using Oracle Driver

There's lots of reasons why using java when developing oracle based applications - especially after Oracle bought sun company. One of the main imporvements in compare to the .NET ODP is that JDBC gives us is the ability to use statement batching at a very simple development cost. No need to be familiar with Array binding or bulk operations.

Let's see how its done...

Oracle Side:

CREATE TABLE TEST (ID NUMBER, STR_VALUE VARCHAR2(10));

Java Side:

import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

...
Connection connection;
OracleDataSource ods = new OracleDataSource();

ods.setDriverType("thin");
ods.setServerName("SERVER");
ods.setDatabaseName("SID");
ods.setPortNumber(1521);
ods.setUser("USER"); ods.setPassword("PASSWORD");
connection=ods.getConnection();

// Set the currect batch size - default value is 1
((OracleConnection)connection).setDefaultExecuteBatch(100);

PreparedStatement ps = connection.prepareStatement ("INSERT INTO test VALUES (?,?)");

ps.setInt (1,1);
ps.setString (2,"Oracle");
ps.executeUpdate(); // Nothing actually happens here

ps.setInt (1,2);
ps.setString (2,"SQL Server");
ps.executeUpdate(); // Nothing actually happens here either - until we get to the batch size

int total_rows = ((OraclePreparedStatement)ps).sendBatch (); // Or we can just tell JDBC we're done using this statement :)

Checking the shared pool will show us just one execution.

I Like Java - it keeps the things simple!