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:
- 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.
- 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…
Good post. Just remember to watch the amount of data you’re pushing in the loop, because you are batching on the client side.
LikeLike
Some additional points:
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.
Oracle has an option of splitting the batch size by using a special OracleConnection extension. Documented here.
LikeLike
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 🙂
LikeLike
how did you get (SessionImplementor) em
LikeLike
It’s the EntityManager. Usually you just get it injected
LikeLike