springdata jpa 实现批量存储、更新

Jpa实现批量存储的方法

springdata jpa 实现批量存储、更新

使用springdata jpa的save()方法在存储数据时，都会先执行select语句,在执行insert语句，相当于每存储一条数据，就会执行两次sql，效率非常慢

解决方法

在properties配置文件中加入

.batch_size=5000
.batch_versioned_data=true
.order_inserts=true
.order_updates =true

这个batch_size的值可以是数据库每秒最大写入数

自己在repository里面定义两个接口 batchSave，batchUpdate并实现：

@Repository
public interface Repository {

    // 批量存储的方法
    <S extends T> Iterable<S> batchSave(Iterable<S> var1);

    // 批量更新的方法
    <S extends T> Iterable<S> batchUpdate(Iterable<S> var1);
}

@Service
public class RepositoryImpl implements Repository {

    private static final int BATCH_SIZE = 5000 ;

    private final  em;

    public ExamineResultsRepositoryImpl( em) {
         = em;
    }


    @Override
    @Transactional
    public <S extends T> Iterable<S> batchSave(Iterable<S> var1) {
        Iterator<S> iterator = ();
        int index = 0;
        while (()){
            (());
            index++;
            if (index % BATCH_SIZE == 0){
                ();
                ();
            }
        }
        if (index % BATCH_SIZE != 0){
            ();
            ();
        }
        return var1;
    }

    @Override
    public <S extends T> Iterable<S> batchUpdate(Iterable<S> var1) {
        Iterator<S> iterator = ();
        int index = 0;
        while (()){
            (());
            index++;
            if (index % BATCH_SIZE == 0){
                ();
                ();
            }
        }
        if (index % BATCH_SIZE != 0){
            ();
            ();
        }
        return var1;
    }
}

插入数据的入口调用自己的batchSave(Iterable var1)方法

        ArrayList<T> list = new ArrayList<>();
     
        (list);

运行结果只有insert语句，而且效率非常高

Hibernate: insert into examine_results (examine_id, target, quantity) values (?, ?, ?)
Hibernate: insert into examine_results (examine_id, target, quantity) values (?, ?, ?)
Hibernate: insert into examine_results (examine_id, target, quantity) values (?, ?, ?)
Hibernate: insert into examine_results (examine_id, target, quantity) values (?, ?, ?)
Hibernate: insert into examine_results (examine_id, target, quantity) values (?, ?, ?)
Hibernate: insert into examine_results (examine_id, target, quantity) values (?, ?, ?)
Hibernate: insert into examine_results (examine_id, target, quantity) values (?, ?, ?)
Hibernate: insert into examine_results (examine_id, target, quantity) values (?, ?, ?)
Hibernate: insert into examine_results (examine_id, target, quantity) values (?, ?, ?)
Hibernate: insert into examine_results (examine_id, target, quantity) values (?, ?, ?)
Hibernate: insert into examine_results (examine_id, target, quantity) values (?, ?, ?)
Hibernate: insert into examine_results (examine_id, target, quantity) values (?, ?, ?)