Yunus Einsteinium Yunus Einsteinium - 1 year ago 71
MySQL Question

Java Update PreparedStatement

I am trying to come up with aמ update query that I can use across all my application to update a table(Inventory).This is what I have so far:

public void updateInventory(Inventory inventory){
PreparedStatement ps=null;
try {
String query =
"UPDATE "+TableName.INVENTORY +" "+
"SET quantity=IFNULL(?, quantity), full_stock=IFNULL(?, full_stock), reorder_level=IFNULL(?, reorder_level), selling_price=IFNULL(?, selling_price), delete_status=IFNULL(?, delete_status), product_id=IFNULL(?, product_id) "+
"WHERE id = ? OR product_id=IFNULL(?, product_id) ";
connection = hikariDS.getConnection();
ps = connection.prepareStatement(query);
ps.setInt(5, inventory.getDeleteStatus());
ps.setInt(6, inventory.getProdId());
ps.setInt(7, inventory.getId());
ps.setInt(8, inventory.getProdId());
} catch(SQLException e){ e.printStackTrace();}
if( connection != null ){
try {connection.close();}
catch (SQLException ex) {logger.log(Level.SEVERE, null, ex);}
if( ps != null){
try { ps.close();}
catch (SQLException ex) { logger.log(Level.SEVERE, null, ex);}

The query above is supposed to update columns with new values in object only if they are set, but if not set just feed it with existing values from the column.

The problem is: if values retrieved from Inventory object are null or 0 values, it does not update with existing values.

Answer Source

I assume your java-side values (of inventory) may be null, and you want to have one piece of code for every possible combination of values.

The SQL seems fine, though to acquire IF(NULL, ...), assuming that your getters return an Object wrapper, like:

Float getQuantity()

then you need to call

ps.setObject(1, inventory.getQuantity());

By the way, BigDecimal on java side and DECIMAL on SQL schema side are a better choice. For the rounding errors of floating point. This would enable:

ps.setBigDecimal(1, inventory.getQuantity());

For non-null getters use an IF():

SET quantity = IF(? = 0.0, quantity, ?),

ps.setDouble(1, inventory.getQuantity());
ps.setDouble(2, inventory.getQuantity());
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download