Chapter 14. Batch processing

A naive approach to inserting 100 000 rows in the database using Hibernate might look like this:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
for ( int i=0; i<100000; i++ ) {
    Customer customer = new Customer(.....);
    session.save(customer);
}
tx.commit();
session.close();

This would fall over with an OutOfMemoryException somewhere around the 50 000th row. That's because Hibernate caches all the newly inserted Customer instances in the session-level cache.

In this chapter we'll show you how to avoid this problem. First, however, if you are doing batch processing, it is absolutely critical that you enable the use of JDBC batching, if you intend to achieve reasonable performance. Set the JDBC batch size to a reasonable number (say, 10-50):

hibernate.jdbc.batch_size 20

You also might like to do this kind of work in a process where interaction with the second-level cache is completely disabled:

hibernate.cache.use_second_level_cache false

14.1. Batch inserts

When making new objects persistent, you must flush() and then clear() the session regularly, to control the size of the first-level cache.

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
   
for ( int i=0; i<100000; i++ ) {
    Customer customer = new Customer(.....);
    session.save(customer);
    if ( i % 20 == 0 ) { //20, same as the JDBC batch size
        //flush a batch of inserts and release memory:
        session.flush();
        session.clear();
    }
}
   
tx.commit();
session.close();

14.2. Batch updates

For retrieving and updating data the same ideas apply. In addition, you need to use scroll() to take advantage of server-side cursors for queries that return many rows of data.

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
   
ScrollableResults customers = session.getNamedQuery("GetCustomers")
    .setCacheMode(CacheMode.IGNORE)
    .scroll(ScrollMode.FORWARD_ONLY);
int count=0;
while ( customers.next() ) {
    Customer customer = (Customer) customers.get(0);
    customer.updateStuff(...);
    if ( ++count % 20 == 0 ) {
        //flush a batch of updates and release memory:
        session.flush();
        session.clear();
    }
}
   
tx.commit();
session.close();

14.3. Bulk update/delete

As already discussed, automatic and transparent object/relational mapping is concerned with the management of object state. This implies that the object state is available in memory, hence updating or deleting (using SQL UPDATE and DELETE) data directly in the database will not affect in-memory state. However, Hibernate provides methods for bulk SQL-style UPDATE and DELETE statement execution which are performed through the Hibernate Query Language (Chapter 15, HQL: The Hibernate Query Language).

The psuedo-syntax for UPDATE and DELETE statements is: ( UPDATE | DELETE ) FROM? ClassName (WHERE WHERE_CONDITIONS)?. Some points to note:

  • In the from-clause, the FROM keyword is optional

  • There can only be a single class named in the from-clause, and it cannot have an alias.

  • No joins (either implicit or explicit) can be specified in a bulk HQL query. Sub-queries may be used in the where-clause.

  • The where-clause is also optional.

As an example, to execute an HQL UPDATE, use the Query.executeUpdate() method:

Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        String hqlUpdate = "update Customer set name = :newName where name = :oldName";
        int updatedEntities = s.createQuery( hqlUpdate )
                            .setString( "newName", newName )
                            .setString( "oldName", oldName )
                            .executeUpdate();
        tx.commit();
        session.close();

To execute an HQL DELETE, use the same Query.executeUpdate() method (the method is named for those familiar with JDBC's PreparedStatement.executeUpdate()):

Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        String hqlDelete = "delete Customer where name = :oldName";
        int deletedEntities = s.createQuery( hqlDelete )
                            .setString( "oldName", oldName )
                            .executeUpdate();
        tx.commit();
        session.close();

The int value returned by the Query.executeUpdate() method indicate the number of entities effected by the operation. Consider this may or may not correlate to the number of rows effected in the database. An HQL bulk operation might result in multiple actual SQL statements being executed, for joined-subclass, for example. The returned number indicates the number of actual entities affected by the statement. Going back to the example of joined-subclass, a delete against one of the subclasses may actually result in deletes against not just the table to which that subclass is mapped, but also the "root" table and potentially joined-subclass tables further down the inheritence hierarchy.

Note that there are currently a few limitations with the bulk HQL operations which will be addressed in future releases; consult the JIRA roadmap for details.