Some time ago, I wrote an article about update/insert data to MySQL database JAVA BATCH UPDATE/INSERT DATABASE
since that time, I realized how this method doesn’t suit my needs.
So, I got an advice from my old friend and colleague Dmitri Zutskov and used INSERT … ON DUPLICATE KEY UPDATE method.
To make this method suit my needs, I had to add another column hash, which has UNIQUE flag.
As a result my code looks the following now:
private static String SQL_PRODUCTS_INSERT = "INSERT INTO products (sources_id, name, brand, condition_type, hash) " + "VALUES ( ?, ?, ?, ?, md5( ? )) ON DUPLICATE KEY UPDATE condition_type= ?"; public void addProductsToDB(ListproductItems, int sourceId) { PreparedStatement statementInsert = null; try { connection = ConnectionFactory.getConnection(); statementInsert = connection.prepareStatement(SQL_PRODUCTS_INSERT); int countIserts = 0; int[] affectedRecords; for (ProductItem productItem: productItems) { countIserts++; statementInsert.setInt(1, sourceId); statementInsert.setString(2, (productItem.getProduct() == null) ? "" : productItem.getProduct()); statementInsert.setString(3, (productItem.getBrand()==null)?"":productItem.getBrand()); statementInsert.setString(4, (productItem.getConditions()==null)?"":productItem.getConditions()); String hash = ""+sourceId; hash += (productItem.getProduct() == null) ? "" : productItem.getProduct(); hash += (productItem.getBrand()==null)?"":productItem.getBrand(); statementInsert.setString(5, hash); statementInsert.setString(6, (productItem.getConditions()==null)?"":productItem.getConditions()); statementInsert.addBatch(); if (countIserts > 100) { affectedRecords = statementInsert.executeBatch(); LOGGER.info("Inserted rows: " + affectedRecords.length); countIserts = 0; } } affectedRecords = statementInsert.executeBatch(); LOGGER.info("Inserted rows: " + affectedRecords.length); } catch (SQLException e) { LOGGER.severe(e.getMessage()); } finally { DbUtil.close(statementInsert); DbUtil.close(connection); } }
This method improved import of 50000 items, so it take now up to 30 seconds.