Bulk inserts and updates in Spring JPA use SimpleJpaRepository#saveAll, which loops to the save method, which looks for records based on the entity ID, updates them if they exist, and inserts them if they don’t. It is inefficient to execute 2n statements for n entities.

@Transactional
@Override
public <S extends T> S save(S entity) {

   if (entityInformation.isNew(entity)) {
      em.persist(entity);
      return entity;
   } else {
      returnem.merge(entity); }}Copy the code

Note: When id is a basic type and null, records are inserted directly and only N statements are executed.

In this article, we will explore Spring JPA batch insertion and update optimization with druid monitoring under the premise of primary key increment.

Bulk insert

Using SimpleJpaRepository#saveAll, insert 5K records.

A total of 5000*2 transactions were executed in 543 seconds.

Hibernate supports batch execution itself, through the spring. The jpa. Properties. Hibernate. JDBC. Batch_size specified batch capacity.A total of 5000+5 transactions were executed in 439 s.

Use EntityManager to batch insert 5K records.

private <S extends T> void batchExecute(Iterable<S> s, Consumer<S> consumer) {
    Session unwrap = entityManager.unwrap(Session.class);
    try {
        unwrap.getTransaction().begin();
        Iterator<S> iterator = s.iterator();
        int index = 0;
        while (iterator.hasNext()) {
            consumer.accept(iterator.next());
            index++;
            if (index % BATCH_SIZE == 0) {
                entityManager.flush();
                entityManager.clear();
            }
        }
        if (index % BATCH_SIZE != 0) {
            entityManager.flush();
            entityManager.clear();
        }
        unwrap.getTransaction().commit();
    } catch (Exception e) {
        unwrap.getTransaction().rollback();
    }
}
Copy the code

A total of five transactions were executed in 255 seconds, saving query performance compared to SimpleJpaRepository#saveAll.

Insert multiple records using a single statement by concatenating SQL statements.

public void insertUsingConcat() { StringBuilder sb = new StringBuilder("insert into t_comment(id, content, name) values "); List<CommentPO> l = new ArrayList<>(); for (int i = 10000; i < 15000; i++) { sb.append("(") .append(i) .append(",'content of demo batch#") .append(i) .append("','name of demo batch#") .append(i) .append("'),"); } sb.deleteCharAt(sb.length() - 1); executeQuery(sb); } final EntityManager entityManager = ApplicationContextHolder.getApplicationContext() .getBean("entityManagerSecondary", EntityManager.class); @Transactional public void executeQuery(StringBuilder sb) { Session unwrap = entityManager.unwrap(Session.class); unwrap.setJdbcBatchSize(1000); try { unwrap.getTransaction().begin(); Query query = entityManager.createNativeQuery(sb.toString()); query.executeUpdate(); unwrap.getTransaction().commit(); } catch (Exception e) { e.printStackTrace(); unwrap.getTransaction().rollback(); }}Copy the code

It takes 0.2s to execute a transaction.

Mysql > select Variable_name from Variable_name WHERE Variable_name LIKE ‘max_allowed_packet’; Query, this is the size of packets that the Server accepts at a time, configured with my.ini.

Batch update

Batch update and batch insert are similar, they are also written in four ways, and the conclusions are the same. The difference is only in SQL writing:

@PutMapping("/concatUpdateClause")
public void updateUsingConcat() {
    List<CommentPO> l = getDemoBatches(5000, 10000, "new");
    StringBuilder sb = new StringBuilder("update t_comment set content = case");
    for (CommentPO commentPO : l) {
        sb.append(" when id = ").append(commentPO.getId()).append(" then '").append(commentPO.getContent())
                .append("'");
    }
    sb.append(" else content end")
            .append(" where id in (")
            .append(l.stream().map(i -> String.valueOf(i.getId())).collect(Collectors.joining(",")))
            .append(")");
    executeQuery(sb);
}
Copy the code