mysql批量提交多条数据

数据库url需加上 &rewriteBatchedStatements=true&useServerPrepStmts=false 配置

rewriteBatchedStatements为true，useServerPrepStmts为true，mysql的jdbc连接器采用预处理的方式提交数据，将预处理数据线发送到数据库服务器，然后再发送一个一个的参数数据，一个操作需要发送两次

rewriteBatchedStatements为true，useServerPrepStmts为false，mysql的jdbc连接器将需要插入的值拼接在插入语句中，多值一次发送：“insert into table(colName) values('a'),('b').....”形式；这样数据只需要一次提交数据到数据库服务器

代码

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

@Service
@Transactional
public class TestService {
    @PersistenceContext
    private EntityManager entityManager;

    public void saveAllBySessionWork(List<ModelComponentMainDTO> modelComponentMainDTOList) {
        if (modelComponentMainDTOList.size() > 0) {
            String fileId = modelComponentMainDTOList.get(0).getFileId();
            String fileRev = modelComponentMainDTOList.get(0).getFileRev();
            log.info("ModelComponentMainService ----> Batch Save ModelComponentMain Begin. FileId : {}, FileRev : {}", fileId, fileRev);
            long start = System.currentTimeMillis();
            // 注意sql中VALUES中结尾的S不能少，缺少S则会按照一条数据一个sql的方式提交
            final String INSERT_FULL =
                    "INSERT INTO model_component_main" +
                            "(file_id, file_rev, name, floor_name, major, project_id, component_no, price, component_attr, component_id, component_type, id, is_deleted) " +
                            "VALUES (?,?,?,?,?,?,?,?,?,?,?, UUID(), '0')";
            Session session = (Session) entityManager.getDelegate();
            session.doWork(connection -> {
                PreparedStatement psSave = connection.prepareStatement(INSERT_FULL);
                ModelComponentMainDTO modelComponentMainDTO;
                for (int i = 0; i < modelComponentMainDTOList.size(); i++) {
                    modelComponentMainDTO = modelComponentMainDTOList.get(i);
                    psSave.setString(1, modelComponentMainDTO.getFileId());
                    psSave.setLong(2, modelComponentMainDTO.getFileRev() == null ? 0l : Long.parseLong(modelComponentMainDTO.getFileRev()));
                    psSave.setString(3, modelComponentMainDTO.getName());
                    psSave.setString(4, modelComponentMainDTO.getFloorName());
                    psSave.setString(5, modelComponentMainDTO.getMajor());
                    psSave.setString(6, modelComponentMainDTO.getProjectId());
                    psSave.setLong(7, modelComponentMainDTO.getComponentNo() == null ? 0l : Long.parseLong(modelComponentMainDTO.getComponentNo()));
                    psSave.setDouble(8, modelComponentMainDTO.getPrice() == null ? 0.0d : modelComponentMainDTO.getPrice());
                    psSave.setString(9, modelComponentMainDTO.getComponentAttr());
                    psSave.setString(10, modelComponentMainDTO.getComponentId());
                    psSave.setString(11, modelComponentMainDTO.getComponentType());

                    psSave.addBatch();
                    if (i % 1000 == 0 || i == modelComponentMainDTOList.size() - 1) {
                        psSave.executeBatch();
                    }
                }
            });
//            session.flush();
            long end = System.currentTimeMillis();
            log.info("ModelComponentMainService ----> Batch Save ModelComponentFull End. FileId : {}, FileRev : {}, Count : {}, Spend : {}s",
                    fileId, fileRev, modelComponentMainDTOList.size(), (end - start) / 1000);
        } else {
            log.info("ModelComponentFullService ----> Batch Save ModelComponentMain : ModelComponentFullDTO Is Empty.");
        }
    }
}