Hibernate provides configuration properties to turn on batching of DML statements.
The properties are –
After setting the values for these two properties at the session factory level, hibernate batches the prepared statements and reduce the number of database hits.
A bit off topic – Hibernate mostly uses prepared statements to execute queries instead of normal statements to avoid SQL injection.
If your database server is Oracle and Oracle’s JDBC Driver is used for connecting to the database then there are few more steps required before hibernate batching is correctly enabled.
Oracle’s JDBC Driver supports two batching models – standard batching (JDBC 3.0) and oracle specific batching. After setting the above two batching properties, hibernate uses the BatchingBatcher implementation to batch the executed prepared statements. This implementation uses the JDBC 3.0 batching model which has a disadvantage when it comes to Oracle JDBC driver implementation. The Oracle JDBC driver implementation for standard batching does not return correct update count. The return value of preparedStmt.executeBatch() is an int array with all values set to -2 for a successful execution. The update count returned by the driver are important for handling StaleObjectException even if JPA standard is used.
A bit more info on why I mention JPA here – JPA provides a method called merge which should be used when your hibernate object needs to be updated. The merge implementation executes a select query before firing an update if the object is not already present in the hibernate cache. It then validates the hibernate version of the loaded and modified objects to check stale object scenarios. Hence an update query is not fired if the object to be updated is already stale.
Now the reason I mention that the update count is still important even if JPA is used was bulk object updates. When there many objects to be updated in the same transaction there could be a time difference between the execution of the select query and the update query
Back to the main topic of hibernate batching – Oracle JDBC Developer Guide recommends using the Oracle specific batching model implementation when the update counts are important. Using the oracle specific batching model requires our application to provide an implementation to hibernate’s Batcher interface or extending the AbstractBatcher class (which is more convenient) and BatcherFactory. These are needed so that the application can make calls to the oracle specific batching model classes like OraclePreparedStatement.sendBatch() for e.g.
One can find a ready-to-use implementation of these classes here. A few points/differences from this implementation to consider are :
1. Caching the expectations in an array as an instance variable is not needed. Maintaining a counter which is incremented every time in the addBatch() method when an BasicExpectation instance is encountered.
2. There are three types of Expectations in hibernate – None, Basic and Param.
- None is mostly used for queries which effect more than one row (for e.g. deleting an element collection from an entity).
- Basic is used for insert, update and delete queries which update only one row (hence I assume the expected row count is hard coded to 1).
- Param is used for callable statements (stored procedures).
Out of the above three None and Basic are batchable. Since the number of rows which would be effected cannot be determined in a None Expectation an instanceOf check is needed for BasicExpectation as mentioned in point 1.
3. Add a new logger to log any statements instead of using the inherited one from AbstractBatcher. If the latter one is used you would have to turn on hibernate logging to view the log statements.
After providing implementation to these two interfaces/classes, the last thing you need to add is another configuration property to specify a fully qualified class name of the BatcherFactory. The property name is – hibernate.jdbc.factory_class.
Posted on March 19, 2011, in hibernate and tagged hibernate, oracle-jdbc. Bookmark the permalink. Leave a comment.
Leave a comment