Py学习  »  DATABASE

获取在mysql上执行批处理语句的最后一个insert id

drenda • 4 年前 • 155 次点击  

我正在使用JavaSQL批处理将一个大的.csv文件导入MySQL。这是我的代码:

        Connection connection = null;
        String tempTableName = stringGenerator.generate(8);
        try {
            connection = connectionPoolManager.getConnection(tenantId, true);
            connection.setAutoCommit(false);

            Statement statement = null;
            StopWatch stopWatch = new StopWatch();
            stopWatch.start();
            try {
                statement = connection.createStatement();
                String sqlDisableAutocommit = "SET autocommit = 0";
                String sqlDisableUniqueChecks = "SET unique_checks = 0";
                String sqlDisableForeignKeys = "SET FOREIGN_KEY_CHECKS = 0";
                String sqlEnableUniqueChecks = "SET unique_checks = 1";
                String sqlEnableForeignKeys = "SET FOREIGN_KEY_CHECKS = 1";
                String createTemporaryTable = String.format("CREATE TEMPORARY TABLE %s SELECT * FROM contactlens WHERE 1=0", tempTableName);
                String loadInFile = String.format("LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ';' IGNORE 1 LINES "
                                + "(manufacturer,upc,sku,manufacturerCode,line,`name`,description,source,`type`,colorCode,colorDescription,material,design,family,duration,pack,baseCurveMin,baseCurveMax,diameterMin,diameterMax,sphereMin,sphereMax,cylinderMin,cylinderMax,axisMin,axisMax,additionMin,additionMax,@dominant,@trial,imageUrl,thumbUrl,purchasePrice,salesPrice,@taxRateValue) "
                                + "SET `createdBy`='system',createdDate='%s',lastModifiedBy='system',lastModifiedDate='%s',sid=UUID(),`version`=1,preset=TRUE,stock=%s,dominant=CAST(@dominant AS SIGNED),trial=CAST(@trial AS SIGNED),taxRate_id=@taxRateValue",
                        chunk.getFile().getAbsolutePath().replace("\\", "/"), tempTableName, getDateForMysql(importTime),
                        getDateForMysql(importTime), productAtStock);
                String insertIntoTable = String.format("INSERT INTO contactlens " + "SELECT * FROM  " + tempTableName
                                + " ON DUPLICATE KEY UPDATE line = VALUES(line),`name` = VALUES(`name`),`source` = VALUES(`source`),`type` = VALUES(`type`),`colorCode` = VALUES(`colorCode`),`colorDescription` = VALUES(`colorDescription`),`colorDescription` = VALUES(`colorDescription`),`material` = VALUES(`material`),`design` = VALUES(`design`),`family` = VALUES(`family`),`duration` = VALUES(`duration`),`pack` = VALUES(`pack`),`baseCurveMin` = VALUES(`baseCurveMin`),`baseCurveMax` = VALUES(`baseCurveMax`),`diameterMin` = VALUES(`diameterMin`),`diameterMax` = VALUES(`diameterMax`),`sphereMin` = VALUES(`sphereMin`),`sphereMax` = VALUES(`sphereMax`),`cylinderMin` = VALUES(`cylinderMin`),`cylinderMax` = VALUES(`cylinderMax`),`axisMin` = VALUES(`axisMin`),`axisMax` = VALUES(`axisMax`),`additionMin` = VALUES(`additionMin`),`additionMax` = VALUES(`additionMax`),`dominant` = VALUES(`dominant`),`trial` = VALUES(`trial`),`imageUrl` = VALUES(`imageUrl`),`thumbUrl` = VALUES(`thumbUrl`),`taxRate_id` = VALUES(`taxRate_id`), VERSION=contactlens.version+1,lastModifiedDate='%s',lastModifiedBy='system',stock=VALUES(stock)",
                        getDateForMysql(importTime));
                if (updatePurchasePrice)
                    insertIntoTable += ",`purchasePrice` = VALUES(`purchasePrice`) ";
                if (updateSalesPrice)
                    insertIntoTable += ", `salesPrice` = VALUES(`salesPrice`) ";
                String dropTemporaryTable = String.format("DROP TEMPORARY TABLE IF EXISTS %s", tempTableName);
                String analyzeTable = String.format("ANALYZE TABLE contactlens");
                String lastInsertedId = String.format("SELECT LAST_INSERT_ID()");

                statement.addBatch(sqlDisableAutocommit);
                statement.addBatch(sqlDisableUniqueChecks);
                statement.addBatch(sqlDisableForeignKeys);
                statement.addBatch(dropTemporaryTable);
                statement.addBatch(createTemporaryTable);
                statement.addBatch(loadInFile);
                statement.addBatch(sqlEnableUniqueChecks);
                statement.addBatch(sqlEnableForeignKeys);
                statement.addBatch(insertIntoTable);
                statement.addBatch(analyzeTable);
                statement.addBatch(lastInsertedId);
                int[] results = statement.executeBatch();
                log.debug("Results {}", results);

我需要得到最后一个insert id,但是当我打印结果时,我已经:

Results [0, 0, 0, 0, 0, 161, 0, 0, 322, -1, -1]

你可以看到最后的结果返回-1。

有没有办法用“批处理方式”得到那个值?

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/46381
 
155 次点击