title: Update MySQL from CSV using JAVA
body: <p>my problem is as follows:</p>
  
  <ol>
  <li><p>The CSV file is downloaded at given intervals from server.</p></li>
  <li><p>File has to be parsed (there are unnecessary spaces that have to be stripped).</p></li>
  <li><p>Item price, rrp and stock quantity must be updated for each barcode.</p></li>
  </ol>
  
  <p>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:</p>
  
  <pre><code>    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();
          }
      }
  }
  </code></pre>
  
  <p>So far fastest solution looks as suggested by @e4c5 with LOAD csv data to temporary table and using query:
  <code>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.</code> Any way to make this even faster?</p>