RimuDB Logo
Database Access Framework for Java

Batch Updates

Batch Updates were added in JDBC 2.0 and allow multiple statements to be sent to the database as a unit, which can lead to improved performance.

Using Batch Updates

For batch operations, an internal queue is maintained. The queue contains batch statements in the order they are submitted. One batch statement is used for each table. This is necessary in order to ensure that batch statements get executed in the correct order.

This means that if 10 batch inserts are executed for table_a, then another 10 batch inserts are executed for table_b, and then another 10 batch inserts are executed for table_a, then the queue will contain three prepared statements in order of inserts. When commit() is called the three prepared statements will be executed one after another, preserving the order of inserts.

Obviously, if your application was coded differently, it would be more efficient to perform all 20 batch inserts for table_a first, and then the 10 batch inserts for table_b. This would only result in two prepared statements in the queue.

In order to get the best batch performance, applications should attempt to perform all the operations to one table and then all the operations to the next table. The worst performance would occur if the application alternately executed one batch insert to table_a and then one batch insert to table_b. This would result in no gain in performance over regular inserts.

Always Close Sessions

In order to perform batch operations, a single JDBC connection must be used. Normally, a developer does not need to be aware of the underlying connections when using RimuDB. However, when using batch operations, it is important to remember that each Session that is created is holding an open connection to the database. If your process is multi-threaded, ensure you have enough connections in the pool to handle all the checked out connections. Also make sure that Session.close() is called under all circumstances. Not closing sessions in a multi-threaded application will quickly lead to the application failing due to running out of available connections.

Example - Add Using a Batch

Batch updates are implemented in a very similar way to transactions. This example illustrates how records are added in a batch. Note that Session.close() is in a try/finally so that it always gets called.


CompoundDatabase cdb;
Database db = cdb.getDatabase("staging-db");
Session session = db.createSession();

try {
    for (int i=0; i < 50; i++) {

        // Insert a record
        OrderTransactionDO orderTransaction = new OrderTransactionDO(cdb);
        orderTransaction.setOrderNr(5555);
        orderTransaction.setName("some name");
        orderTransaction.setValue("value-"+i);
        session.batchInsert(orderTransaction);
    }

    // Commit the batch
    session.commit();
    
} finally {
    // Always close the session (releasing the connection)
    session.close();
}


Example - Delete Using a Batch

This example illustrates how records are deleted in a batch.


CompoundDatabase cdb;
Database db = cdb.getDatabase("staging-db");
Session session = db.createSession();

try {
    // Get some records
    OrderTransactionFinder orderTransactionFinder = new OrderTransactionFinder(cdb);
    OrderTransactionDO orderTransactions[] = orderTransactionFinder.selectAll();

    // Delete the records
    for (int i=0; i < orderTransactions.length; i++) {

        // Delete a record
        session.batchDelete(orderTransactions[i]);

        // Commit the batch every 50 records
        if (i % 50 == 49) {
            session.commit();
        }
    }

    // Commit the batch
    session.commit();

} finally {
    // Always close the session (releasing the connection)
    session.close();
}