Java batch UPDATE/INSERT database
Originally published at Moishe Beshkin. You can comment here or there.
I was looking for some solution which will allow me to make more efficient update old and insert new item into MySQL database.
1. My story
I have some data, which should be imported from a side datasource into my database. So, there is no possibility to match ids of outer data and local.
Also imported data array size is unpredictable and maybe quite big.
2. How it was done before
Previously, I made a crawler on PHP. This happened only to due one reason - this was the language I knew at the moment the best. And the solution is quite ugly:
The following code was looped by the list of received data.
$query_string = sprintf("SELECT * FROM products WHERE `name` = '%s' AND `brand` = '%s' AND `sources_id` = '%s'", mysql_real_escape_string($product), mysql_real_escape_string($brand), mysql_real_escape_string($id)); $query = mysql_query($query_string) or die("check if exists: ".mysql_error()); $result = mysql_fetch_array($query, MYSQL_ASSOC); if ($result['id'] != "") { $query_string = sprintf("UPDATE products SET `name`='%s',`brand`='%s',`category`='%s', `IsKosher`='%s' WHERE id=".$result['id'], mysql_real_escape_string($product), mysql_real_escape_string($brand), mysql_real_escape_string($category), mysql_real_escape_string($isKosher), ); mysql_query($query_string)or die("update: ".mysql_error()); }else{ $query_string = sprintf("INSERT INTO products (`sources_id`,`name`,`brand`,`category`,`IsKosher` ) VALUES ('%s', '%s', '%s', '%s', '%s')", mysql_real_escape_string($id), mysql_real_escape_string($product), mysql_real_escape_string($brand), mysql_real_escape_string($category), mysql_real_escape_string($isKosher), ); mysql_query($query_string) or die(mysql_error()); }
As you see, the solution was abnormally insufficient. It took several hours for 60000 records to be updated or inserted.
3. What changed?
I developed a Java server on Glassfish and reworked crawling mechanism. Now works as following:
Received array of data is looped, but each item is matched with existing in database. If this matches, then item is added to UPDATE batch, else to INSERT batch. Each 100 of batches are executed.
private static String SQL_PRODUCTS_INSERT = "INSERT INTO products (sources_id, name, brand, isKosher) VALUES (?, ?, ?, ?)"; private static String SQL_PRODUCTS_UPDATE = "UPDATE products SET isKosher = ? WHERE id = ?"; private static String SQL_PRODUCTS_SELECT = "SELECT * FROM products WHERE sources_id = ?"; public void addProductsToDB(List
productItems, int sourceId) { PreparedStatement statement = null; PreparedStatement statementUpdate = null; PreparedStatement statementInsert = null; ResultSet rs = null; try { connection = ConnectionFactory.getConnection(); statement = connection.prepareStatement(SQL_PRODUCTS_SELECT); statementUpdate = connection.prepareStatement(SQL_PRODUCTS_UPDATE); statementInsert = connection.prepareStatement(SQL_PRODUCTS_INSERT); statement.setInt(1, sourceId); rs = statement.executeQuery(); List
productItemsDB = new ArrayList
(); while (rs.next()) { ProductItem productItem = new ProductItem(); productItem.setProduct(rs.getString("name")); productItem.setBrand(rs.getString("brand")); productItem.setDBId(rs.getInt("id")); productItemsDB.add(productItem); } int countUpdates = 0; int countIserts = 0; for (ProductItem productItem: productItems) { ProductItem productItemDb = null; for (ProductItem productItemDbLocal: productItemsDB) { if (productItemDbLocal.getProduct().equalsIgnoreCase(productItem.getProduct()) && productItemDbLocal.getBrand().equalsIgnoreCase(productItem.getBrand())) { productItemDb = productItemDbLocal; break; } } if (productItemDb!=null){ countUpdates++; statementUpdate.setInt(1, productItem.isKosher() ? 1 : 0); statementUpdate.setInt(2, productItemDb.getDBId()); statementUpdate.addBatch(); if (countUpdates > 100) { statementUpdate.executeBatch(); countUpdates = 0; } } else { countIserts++; statementInsert.setInt(1, sourceId); statementInsert.setString(2, (productItem.getProduct() == null) ? "" : productItem.getProduct()); statementInsert.setString(3, (productItem.getBrand()==null)?"":productItem.getBrand()); statementInsert.setInt(4, productItem.isKosher() ? 1 : 0); statementInsert.addBatch(); if (countIserts > 100) { statementInsert.executeBatch(); countIserts = 0; } } } statementUpdate.executeBatch(); statementInsert.executeBatch(); } catch (SQLException e) { LOGGER.severe(e.getMessage()); } finally { DbUtil.close(rs); DbUtil.close(statement); DbUtil.close(statementUpdate); DbUtil.close(statementInsert); DbUtil.close(connection); } }
Now array of 60000 entries is updated or inserted in 5 minutes with minimum load of MySQL.