JAVA BATCH UPDATE/INSERT DATABASE v2

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(List productItems, 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.

Leave a Reply

%d bloggers like this: