Updating MySQL from CSV using JAVA

Question

my problem is this:

• The CSV file is downloaded at specified intervals from the server.

• The file must be analyzed (there are unnecessary spaces that need to be removed).

• Product price, rrp and inventory quantity should be updated for each barcode.

Each CSV line contains the article number, product name, warehouse identifier, warehouse name, price, rrp price, stock and barcode. The item table contains about 71,000 rows. And the barcode is not a unique key in the database (since an item with the same barcode can be in different warehouses). The problem is that it takes about one hour (about half an hour to SQLite) to upgrade on the localhost MySQL server. Is there a way to optimize the SQL query to speed things up? My current code is as follows:

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

So far, the fastest solution looks like @ e4c5 suggested with LOAD csv data in a temporary table and using a 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 do this even faster?

Answer 1

, - Statement, :

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 :

, , , 500 ,

, , , , :

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

Answer 2

Java. LOAD DATA INFILE

, LOAD DATA CSV. . java, java .

Java- CSV CSV, LOAD DATA. 70000 - .

LOAD DATA ON DUPLICATE KEY UPDATE. REPLACE ( , , - , , , Java ).

, . LOAD DATA ,

INSERT INTO mytable SELECT * FROM temp_table ON DUPLICATE KEY ....