Batch Update with JPA

A customer of mine has a highly scalable system, with high database load. To improve performance, we’ve decided to use Batch update. Alas – the application uses JPA, so how do we do it?

SessionFactoryImplementor sfi = SessionFactoryImplementor)entityManager.unwrap(Session.class).getSessionFactory();
//Retrieve DB connection
connect = sfi.getConnectionProvider().getConnection();
PreparedStatement ps = connect.prepareStatement("INSERT INTO temp_table values(?,?)");
for (Data p : insertCommands) {
	ps.setInt(1, p.getId());
	if (p.isWorking() != null) {
		ps.setInt(2, p.isWorking() ? 1 : 0);
	} else {
		ps.setNull(2, Types.INTEGER);
	}
	ps.addBatch();
}
ps.executeBatch();


Some points to remember:

  1. Do not close the connection!!! When closing the connection, it returns to the connection pool, without commit or rollback. You’ll get some exceptions in your code, but mainly – you’re open to database locks and dead-locks, as the connection keeps all the locks that were done in the batch update. And since other threads will reuse the connection – it will never close.
  2. Do not use Statement.addBatch. Use PreparedStatement.addBatch. When I checked both solutions, the Statement one took anywhere between 30s-60s. The PreparedStatement version took <300ms...

5 thoughts on “Batch Update with JPA

    • Some additional points:

      1. If you’re using MySQL, make sure to use server side prepared statements (which are not the default!). Activate it by using the ?useServerPrepStmts=true on the JDBC URL. See here how.
      2. Oracle has an option of splitting the batch size by using a special OracleConnection extension. Documented here.
  1. I got deprecated error so I used the below method. It is working.

    SessionImplementor si = (SessionImplementor) em.unwrap(Session.class);
    Connection connection = si.getJdbcConnectionAccess().obtainConnection();
    PreparedStatement pstmt = connection.prepareStatement(“…”);

    Thanks for early heads up 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *