CODEWITHSUNDEEP
mysqlhibernatejpaspring-data-jpa
opuser1
opuser1

Reputation: 477

JPA batch inserts on MySQL with identity generation strategy

I'm using MySQL as my backend and i went through the post Hibernate disabled insert batching when using an identity identifier generator to figure out batch insert is not a viable option with GenerationType.IDENTITY

Also figured that GenerationType.TABLE performs poorly based on this article & GenerationType.SEQUENCE is not viable as MySQL doesn't support sequence.

My app heavily uses Spring data JPA and most primary keys are generated via MySQL's AUTO_INCREMENT & mapped with IDENTITY strategy in the entity. I have usecase to bulk insert records (typically 500 records to atmost 10000). I have seen suggestions on using jOOQ and would like to avoid bringing in a whole new framework just for this purpose.

As a work around, I came up with my own custom solution, although not the most elegant. Some preliminary tests revealed it can perform faster compared to JpaRepository.saveAll() (without batching) but i may be overlooking other implications. Would this be better than not having batching at all ?

New interface to expose bulk insert option:

public interface MyCustomRepository {
   void bulkInsert(List<MyEntity> entities);
}

public interface MyRepository extends JpaRepository<MyEntity, Long>, MyCustomRepository {

} 

public class MyCustomRepositoryImpl implements MyCustomRepository {

    @PersistenceContext
    private EntityManager em;
    
    @Transactional
    @Override
    public void bulkInsert(List<MyEntity> entities) {
        List<List<MyEntity>> batchedList = Lists.partition(entities, batchSize);

        batchedList.stream().forEach(list -> {
            StringBuffer builder = new StringBuffer();
            builder.append("INSERT into my_entity (column1, column2, column3) values ");
            
            builder.append(list.stream().map(myEntity -> {
                StringBuilder builder1 = new StringBuilder();
                builder1.append("(");
                builder1.append(myEntity.getColumn1() + ",");
                builder1.append(myEntity.getColumn2() + ",");
                builder1.append(myEntity.getColumn3());
                builder1.append(")");
                return builder1.toString();
            }).collect(Collectors.joining(", ")));
            em.createNativeQuery(builder.toString()).executeUpdate();
        });
    }
}

Upvotes: 2

Views: 1213

Answers (2)

dinakaran karan
dinakaran karan

Reputation: 91

In addition to yours(hibernate), you can use the JDBC template or query DSL-SQL. To see how to implement using query DSL-SQL click here. For the JDBC template click here.

If you need type-safe, easy to code choose query DSL-SQL else choose JDBC template

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81862

The implication of this approach is that your entities are still detached from the persistent context and the created id is not set on the entities.

If that is fine for you, this is a fine approach.

Upvotes: 1

Related Questions