Spring integrates MyBatis bulk insertion

Keywords: QRCode Druid Java

The for loop has been used for single insertion before. When the traffic volume increases to tens of thousands, a Connection is null error will be reported. This is the first time I have encountered this business scenario. Record the following. If I know more, I will continue to update this article.

Combined posts: forum

configuration file

Finally, it comes to the problem of configuration file:
Connection timeout
druid.removeAbandonedTimeout=7200
If your project needs to run for a long time, this property value needs to be modified

Bulk insert

For mass insert, it needs to be divided into several times.

for (int i = 1; i <= tagsGenerateModel.getTagsCounts(); i++) {
                serialNumber ++;
                String selfGrowthNo = serialNumberServiceAccess.genSerialNumber(SysConstant.TAGS_BATCH_QR_CODE_TENANTID, "pqms:tags:qrcode"+tagsId);
                /** Generate unit identification code */
                String unitCode = tagsServiceComp.genUnitIdentiCode(tags, pesticideProduct, selfGrowthNo);
                //Create QR code
                QrCode qrCode = new QrCode();
                qrCode.setId(UuidUtils.newid());
                qrCode.setEnterpriseId(tagsGenerateModel.getEnterpriseId());
                qrCode.setQrCode(unitCode);
                qrCode.setScanCount(0);
                qrCode.setPesticideProductId(tagsGenerateModel.getProductId());
                qrCode.setProductBatchId("");
                qrCode.setQrCodeBatchId(tags.getId());
                qrCode.setStatus(0); // Status 0 normal 1 recall 2 void
                qrCode.setQrCodePath("");
                qrCode.setCreateTime(new Date());
                qrCode.setUpdateTime(new Date());
                qrCode.setSerialNumber(serialNumber);
                qrCode.setIsDownload("0"); // Have you downloaded 0? Have not downloaded 1
                qrCode.setIsActive(String.valueOf(tagsGenerateModel.getActiveMethod())); // Activate or not 0-activate, 1-not activate

                qrCodes.add(qrCode);
                unitCodeLst.add(unitCode);
                if (i % 1000 == 0) {//If the current number of times is 1000 integer times, it is a batch for batch insertion
                    qrCodeDao.batchInsert(qrCodes);
                    qrCodes.clear();//Clear the list and prepare for the next storage
                    long endTime = System.currentTimeMillis();
                    logger.info("**The first" + (i / 1000) + "It takes time to insert 1000 pieces of data per time:" + (endTime - startTime) + "Millisecond\r\n");
                    startTime = System.currentTimeMillis();
                }
                if (i == tagsGenerateModel.getTagsCounts() && qrCodes.size() > 0) {//If all data or remaining data 
                    qrCodeDao.batchInsert(qrCodes);
                    qrCodes = null;
                    long endTime = System.currentTimeMillis();
                    logger.info("**final" + (i % 1000) + "Time consuming data:" + (endTime - startTime) + "Millisecond\r\n");
                }
            }
<insert id="batchInsert" parameterType="java.util.List">
        insert into PQMS_QR_CODE (id, enterprise_id, qr_code,
        scan_count,
        pesticide_product_id, product_batch_id,
        qr_code_batch_id, status,
        qr_code_path,
        create_time, update_time, serial_number,
        is_download,
        is_active)
        values
        <foreach collection="list" item="item" index="index"
            separator=",">
            (#{item.id,jdbcType=VARCHAR}, #{item.enterpriseId,jdbcType=VARCHAR},
            #{item.qrCode,jdbcType=VARCHAR},
            #{item.scanCount,jdbcType=INTEGER}, #{item.pesticideProductId,jdbcType=VARCHAR},
            #{item.productBatchId,jdbcType=VARCHAR},
            #{item.qrCodeBatchId,jdbcType=VARCHAR}, #{item.status,jdbcType=INTEGER},
            #{item.qrCodePath,jdbcType=VARCHAR},
            #{item.createTime,jdbcType=TIMESTAMP}, #{item.updateTime,jdbcType=TIMESTAMP},
            #{item.serialNumber,jdbcType=INTEGER},
            #{item.isDownload,jdbcType=CHAR}, #{item.isActive,jdbcType=CHAR})
        </foreach>
    </insert>

Posted by dwfait on Mon, 04 May 2020 08:33:47 -0700