使用JAVA从CSV更新MySQL

我的问题如下： CSV文件以给定的时间间隔从服务器下载.必须解析文件(必须删除不必要的空格).必须为每个条形码更新项目价格,rrp和库存数量.每个CSV行包含商品编号,产品标题,仓库ID,仓库名称,价格,rrp价格,库存和...

我的问题如下：

> CSV文件以给定的时间间隔从服务器下载.
>必须解析文件(必须删除不必要的空格).
>必须为每个条形码更新项目价格,rrp和库存数量.

每个CSV行包含商品编号,产品标题,仓库ID,仓库名称,价格,rrp价格,库存和条形码. items表包含大约71000行.并且条形码不是数据库中的唯一键(因为具有相同条形码的项目可以在不同的仓库中).问题是在localhost MySQL服务器上更新需要一个多小时(大约半小时到SQLite).有没有办法优化SQL查询以使事情更快？我当前的代码如下所示：

    public void updateData (BufferedReader bufferedReader, Connection sqlConnection){
    String csvLine = null;
    PreparedStatement preparedStatement = null;
    String sqlString = "UPDATE items SET price = ?, rrp = ?, stock = ? WHERE departmentid = ? AND barcode = ?";
    try {
        preparedStatement = sqlConnection.prepareStatement(sqlString);
        while ((csvLine = bufferedReader.readLine()) != null) {
            String[] splitLine = csvLine.split(";");
            preparedStatement.setBigDecimal(1, new BigDecimal(splitLine[4].trim()).setScale(2, RoundingMode.CEILING));
            preparedStatement.setBigDecimal(2, new BigDecimal(splitLine[5].trim()).setScale(2, RoundingMode.CEILING));
            preparedStatement.setInt(3, Integer.parseInt(splitLine[6].trim()));
            preparedStatement.setString(4, splitLine[2].trim());
            preparedStatement.setString(5, splitLine[8].trim());
            preparedStatement.executeUpdate();
        }
    } catch (IOException | SQLException exc) {
        System.out.println(exc.getMessage());
    } finally {
        try {
            sqlConnection.commit();
            preparedStatement.close();
            sqlConnection.close();
        } catch (SQLException exc) {
            exc.printStackTrace();
        }
    }
}

到目前为止,最快的解决方案看起来像@ e4c5建议使用LOAD csv数据到临时表并使用查询：
UPDATE项目INNER JOIN temp_table ON items.barcode = temp_table.barcode SET items.rrp = temp_table.rrp,items.price = temp_table.price,items.stock = temp_table.stock WHERE items.barcode = temp_table.barcode AND items.departmentid = temp_table.departmentid.有什么方法可以让它更快？

解决方法:

我认为在你的情况下最好的方法是使用Statement batching这里是一个例子：

sqlConnection.setAutoCommit(false);//<<------------
try {
    preparedStatement = sqlConnection.prepareStatement(sqlString);
    while ((csvLine = bufferedReader.readLine()) != null) {
        String[] splitLine = csvLine.split(";");
        preparedStatement.setBigDecimal(1, new BigDecimal(splitLine[4].trim()).setScale(2, RoundingMode.CEILING));
        preparedStatement.setBigDecimal(2, new BigDecimal(splitLine[5].trim()).setScale(2, RoundingMode.CEILING));
        preparedStatement.setInt(3, Integer.parseInt(splitLine[6].trim()));
        preparedStatement.setString(4, splitLine[2].trim());
        preparedStatement.setString(5, splitLine[8].trim());

        preparedStatement.addBatch();//<<-----------add a batch
    }

    //execute your multiple statement as one
    statement.executeBatch();//<<------------
    sqlConnection.commit();//<<--------------
}

编辑

喜欢@Mick Mnemonic在评论中提到：

You could try if splitting into smaller batches of say 500 rows makes
any difference

因此,只需一次性执行批次,您就可以小批量拆分批次,例如：

sqlConnection.setAutoCommit(false);
try {
    int nbrBatch = 500;
    int count = 0;
    preparedStatement = sqlConnection.prepareStatement(sqlString);
    while ((csvLine = bufferedReader.readLine()) != null) {
        //Your code here
        preparedStatement.addBatch();
        if (count % nbrBatch == 0) {
            statement.executeBatch();
        }
        count++;
    }
    //rest of your batch not executed
    statement.executeBatch();
    sqlConnection.commit();
}