我正在使用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。
有没有办法用“批处理方式”得到那个值?
