SpringBoot使用JdbcTemplate批量保存

    @Autowired
    DataSourceProperties dataSourceProperties;

    @Autowired
    ApplicationContext applicationContext;

public List<SubjectKycFileVO> batch() {

        // JDBC模板依赖于连接池来获得数据的连接，所以必须先要构造连接池
        DataSource dataSource = applicationContext.getBean(DataSource.class);
//        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
//        dataSource.setUrl("jdbc:mysql://localhost:3306/leasing-boot?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Hongkong&nullCatalogMeansCurrent=true");
//        dataSource.setUsername("root");
//        dataSource.setPassword("root");
        String sql = " INSERT INTO sj_kyc_file (id, createdAt, modifiedAt, createdBy, modifiedBy, version, isDelete, companyId, subjectId, numericalOrder, fileName, filePath, uploadDate) " +
                " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
        JdbcTemplate batchUpdate = new JdbcTemplate(dataSource);
//        batchUpdate.setDataSource(dataSource);
        List<SubjectKycFile> list = new ArrayList<>();
        String currentUserId = super.getCurrentAuditor().get();
        LocalDateTime now = LocalDateTime.now();
        List<Object[]> objectList = new ArrayList<>();
        for (int i = 0; i < 10000; i++) {
            SubjectKycFile subjectKycFile = new SubjectKycFile();
            subjectKycFile.setCreatedAt(now);
            subjectKycFile.setModifiedAt(now);
            subjectKycFile.setCreatedBy(currentUserId);
            subjectKycFile.setModifiedBy(currentUserId);
            subjectKycFile.setIsDelete(0);
            subjectKycFile.setVersion(0);
            subjectKycFile.setId(UUID.randomUUID().toString());
            subjectKycFile.setCompanyId("26e5bacb-e3d3-49a9-92a2-1252068ebd66");
            subjectKycFile.setNumericalOrder(i);
            subjectKycFile.setFileName("" + i);
            subjectKycFile.setFilePath("" + i);
            subjectKycFile.setUploadDate(LocalDate.now());
            subjectKycFile.setSubjectId("2a90f646-c47f-43a5-87c8-5c7f945b7b69");
            list.add(subjectKycFile);
        }
        for (SubjectKycFile subjectKycFile : list) {
            objectList.add(new  Object[] { subjectKycFile.getId(), subjectKycFile.getCreatedAt(),subjectKycFile.getModifiedAt(),subjectKycFile.getCreatedBy(),subjectKycFile.getModifiedBy()
            ,subjectKycFile.getVersion(),subjectKycFile.getIsDelete(),subjectKycFile.getCompanyId(),subjectKycFile.getSubjectId(),subjectKycFile.getNumericalOrder(),subjectKycFile.getFileName()
            ,subjectKycFile.getFilePath(),subjectKycFile.getUploadDate()});
        }
        batchUpdate.batchUpdate(sql, objectList);
        return null;
    }

package com.cloudkeeper.leasing.subject.domain;

import com.cloudkeeper.leasing.base.constant.BaseConstants;
import com.cloudkeeper.leasing.base.domain.SubjectBaseEntity;
import com.fasterxml.jackson.annotation.JsonIgnore;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.experimental.Accessors;
import org.hibernate.annotations.GenericGenerator;
import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;

import javax.persistence.*;
import java.time.LocalDate;
import java.time.LocalDateTime;

/**
 * kyc附件
 * @author lixin.shao
 */
@ApiModel(value = "kyc附件", description = "kyc附件")
@Getter
@Setter
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "sj_kyc_file")
public class SubjectKycFile {

    @Id
    @GeneratedValue(generator = "idGenerator")
    @GenericGenerator(name = "idGenerator", strategy = "uuid2")
    @Column(length = 36)
    @ApiModelProperty(value = "主键id", position = 1)
    private String id;

    /** 创建时间 */
    @CreatedDate
    @ApiModelProperty(value = "创建时间", position = 2)
    private LocalDateTime createdAt;

    /** 更新时间 */
    @LastModifiedDate
    @ApiModelProperty(value = "更新时间", position = 3)
    private LocalDateTime modifiedAt;

    /** 创建人 */
    @Column(length = 36)
    @CreatedBy
    @ApiModelProperty(value = "创建人", position = 4)
    private String createdBy;

    /** 更新人 */
    @Column(length = 36)
    @LastModifiedBy
    @ApiModelProperty(value = "更新人", position = 5)
    private String modifiedBy;

    /** 版本（乐观锁） */
    @Version
    @ApiModelProperty(value = "版本（乐观锁）", position = 6)
    private Integer version;

    /** 逻辑删除 */
    @ApiModelProperty(value = "逻辑删除", position = 7)
    private Integer isDelete = BaseConstants.Boolean.FALSE.ordinal();

    /** 公司id */
    @ApiModelProperty(value = "公司id", position = 8)
    @Column(length = 36)
    private String companyId;

    /** 案件id*/
    @ApiModelProperty(value = "案件id", position = 9)
    @Column(name = "subjectId", length = 36)
    private String subjectId;

    /** 案件*/
    @ApiModelProperty(value = "案件")
    @ManyToOne
    @JoinColumn(name = "subjectId", updatable = false, insertable = false)
    @JsonIgnore
    private Subject subject;

    /** 序号 */
    @ApiModelProperty(value = "序号", position = 10)
    private Integer numericalOrder;

    /** 文件名称 */
    @ApiModelProperty(value = "文件名称", position = 10)
    @Column(length = 100)
    private String fileName;

    /** 文件路径 */
    @ApiModelProperty(value = "文件路径", position = 10)
    @Column(length = 200)
    private String filePath;

    /** 上传时间 */
    @ApiModelProperty(value = "上传时间", position = 10)
    private LocalDate uploadDate;

}

 

使用jdbcTemplate BatchUpdate批量插入效率慢

rewriteBatchedStatements=true

url: jdbc:mysql://localhost:3306/leasing-boot?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Hongkong&rewriteBatchedStatements=true