Hibernate batch statements

Keywords: guid generation, great performance, spy driver, datasource url, file, batch, batched, column, configuration, code, download. Powered by TextRank.

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.

Metadata

First published on 2018-09-05

Generated on May 5, 2024, 8:48 PM

Index