使用JAVA从CSV更新MySQL [英] Update MySQL from CSV using JAVA

问题描述

我的问题如下:

1. 以给定的时间间隔从服务器下载CSV文件.

1. The CSV file is downloaded at given intervals from server.

必须分析文件(必须删除不必要的空格).

File has to be parsed (there are unnecessary spaces that have to be stripped).

必须为每个条形码更新商品价格，建议零售价和库存数量.

Item price, rrp and stock quantity must be updated for each barcode.

每个CSV行均包含商品编号，产品标题，仓库编号，仓库名称，价格，建议零售价，库存和条形码.项目表包含约71000行.而且条形码不是数据库中的唯一键(因为具有相同条形码的物料可能位于不同的仓库中).问题是在本地主机MySQL服务器上更新需要一个多小时(到SQLite大约需要半小时).有什么方法可以优化SQL查询以使事情更快?我当前的代码如下:

Each CSV line contains article number, product title, warehouse id, warehouse name, price, rrp price, stock and barcode. The items table contains about 71000 rows. And barcode is not unique key in database (because item with same barcode can be in different warehouses). Issue is that it takes more than one hour to update on localhost MySQL server (about half hour to SQLite). Is there any way to optimize SQL query to make things faster? My current code looks like this:

    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所建议的那样，将CSV数据加载到临时表并使用查询: UPDATE items 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.有什么方法可以使速度更快?

So far fastest solution looks as suggested by @e4c5 with LOAD csv data to temporary table and using query: UPDATE items 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. Any way to make this even faster?

推荐答案

我认为在您的情况下，最好的方法是使用语句批处理":

I think the best way in your case is to use Statement batching here is an example :

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助记符:

Like @Mick Mnemonic mention in comment :

您可以尝试将其分成几小段，例如500行 有任何区别

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

因此，要一次性完成批处理，您可以将批处理分成小批，例如:

So instead to execute your batch in one shot you can split your batch in small batches for example :

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();
}

这篇关于使用JAVA从CSV更新MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！