Hibernate batch statements

Sep 5, 2018

Here 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

spy.properties

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.