JDBC Batch Processing

Batch processing allows grouping related SQL statements into a batch by calling addBatch(), and submitting them with a single call to executeBatch(). Sending several SQL statements with executeBatch() improves performance by reducing the amount of communication overhead. The clearBatch() method can be called to remove all statements previously added to the batch.

Consider the following class Batch with constructor and method createTable()defining table Record:

 
    public class Batch
    {
        static final int nRecords = 5;
        Connection con;
 
        public Batch(String url) throws ClassNotFoundException, SQLException
        {
            String user = null;
            String password = null;
             
            // Load the driver
            Class.forName("com.mcobject.jdbc.McoDriver");
             
            // Connect to the database
            con = DriverManager.getConnection(url, user, password);
             
            createTable();
 
            insertBatch(nRecords*3);
     
            // Finally close the database
            con.close();
        }
         
        public void createTable() throws SQLException 
        {
            Statement stmt = null;
            try
            {
                con.setAutoCommit(true);
                String sql = "create table Record(idx int primary key, dec_value int, str_value string)";
                stmt = con.createStatement();
                stmt.executeUpdate(sql);
                stmt.close();
            }catch(SQLException e){
                e.printStackTrace();
                throw e;
            }
        }
     

The following method demonstrates the use of addBatch(), clearBatch() and executeBatch():

 
        public void insertBatch(int initial_idx) throws SQLException {
        Statement stmt = null;
        try
        {
            stmt = con.createStatement();
            int next_to_last_idx = initial_idx + nRecords;
            for (int n=0; n < 3; n++)
            {
                for (int i=initial_idx; i<next_to_last_idx; i++)
                {
                    String sql = "insert into Record values(" + i + 
                                "," + (100+i) + ",'Str200" + i + "')";
                    stmt.addBatch(sql);
                }
                if (n == 2) 
                {
                    stmt.clearBatch();
                }
                int[] res = stmt.executeBatch();
                if (n == 2) 
                {
                    if (res.length != 0) 
                    {
                        throw new SQLException("Unexpected : result (" + 
                                res.length + " instead of 0)!");
                    }
                } else {
                    if (res.length != nRecords) 
                    {
                        throw new SQLException("Unexpected : result (" + 
                                res.length + " instead of " + nRecords + ")!");
                    }
                    for (int j = 0; j < res.length; j++)
                    {
                        if (res[j] != 1) 
                        {
                            throw new SQLException("Unexpected : result [" + 
                                    j + "] (" + res[j] + " instead of 1)!");
                        }
                    }
                }
                initial_idx += nRecords;
                next_to_last_idx += nRecords;
            }
            stmt.close();
            con.commit();
             
        }catch(SQLException e){
            e.printStackTrace();
            throw e;
        }
    }
     

Clearly, to execute these methods, a Batch instance must be created in a main function like the following:

 
    public static void main(String[] args) throws Exception
    {
        new Batch("jdbc:extremedb:localhost:5001");
    }
     

Example

In a console window from directory samples/jdbc/JDBCBatch run

 
    ../../../target/bin/xsql -i -c xsql.cfg
     

Then in a second console window run

 
    On Unix/Linux:
                
     
    JDBCBatch.sh
     
    On Windows:
                
     
    JDBCBatch.bat