Hibernate batch statements
September 5, 2018Here is a working way to do bulk inserts for hibernate:
First make sure that your entity id generation is not AUTO. I use UUID/GUID generation
@Id
@GeneratedValue(generator = "uuid")
@GenericGenerator(name = "uuid", strategy = "uuid")
@Column(columnDefinition = "CHAR(32)")
private String id;
add the following to your configuration (this is for spring boot)
spring.jpa.properties.hibernate.jdbc.batch_size=400
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true
the following code will batch the statements for a great performance increase:
List<SurfaceTile> tiles = new ArrayList<>();
for (Star star : stars) {
for (Planet planet : star.getPlanets()) {
tiles.addAll(planet.getTiles());
}
}
tiles.removeAll(Collections.singleton(null));
surfaceTileRepository.save(tiles);
you can check that it’s being batched using the following spy driver:
add compile 'p6spy:p6spy:2.1.4' to your build file and set the following for your datasource url and driver
#spring.datasource.url=jdbc:p6spy:mysql://host:3306/db?rewriteBatchedStatements=true
#spring.datasource.driverClassName=com.p6spy.engine.spy.P6SpyDriver
download the properties file in the attachment and put it in your resources directory and you will see the following in the logs
p6spy : 1450114684813|0|batch|connection 7|insert into ...
p6spy : 1450114684814|1|statement|connection 7|insert into ...
p6spy : 1450114684817|2|commit|connection 7||
the 3rd column (batch) means that the statements are getting batched and if the 3rd column reads ‘statement’ that means the query was sent to the server.