Java Update PreparedStatement

Question

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.setFloat(1,inventory.getQuantity());
        ps.setFloat(2,inventory.getFullStock());
        ps.setFloat(3,inventory.getReorderLevel());
        ps.setFloat(4,inventory.getPrice());
        ps.setInt(5, inventory.getDeleteStatus());
        ps.setInt(6, inventory.getProdId());
        ps.setInt(7, inventory.getId());
        ps.setInt(8, inventory.getProdId());
        ps.executeUpdate();
    } catch(SQLException e){ e.printStackTrace();}
    finally{
        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.

try this link.. http://stackoverflow.com/questions/32018418/update-columns-if-input-values-are-not-null-otherwise-ignore-and-keep-the-existi — Dil., Jul 11 '16 at 07:12
Not the object itself but attribute. e.g i want to update only price but quantity is not set, so i assume price attribute will contain a value but quantity will be zero — Yunus Einsteinium, Jul 11 '16 at 07:12
@pippilongstocking: the link you provided is what i am trying to do but in my case for all columns. The problem is if attribute is 0, still takes the attribute value to update instead of column value — Yunus Einsteinium, Jul 11 '16 at 07:18
try using JPA instead and create entity Objects for your records - you are more liking to be able to cleanly control this type of dynamic updating using JPA. — Scary Wombat, Jul 11 '16 at 07:23
I believe the prepared statement may remember the last values used, so you need to specifically update each field. However - some databases/drivers require to specifically "null" the value using .setNull(..) method. — gusto2, Jul 11 '16 at 07:50

score 1 · Answer 1 · answered Jul 11 '16 at 08:37

1

You don't have anything that actually sets the value to the supplied parameter.

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)

should be

SET quantity=IFNULL(?, quantity, ?), ...

etc. This means you will have to duplicate every parameter, of course.

answered Jul 11 '16 at 08:37

user207421

305,947
44
307
483

I added ? as third parameter of IFNULL function as you suggested, an error was thrown `java.sql.SQLException: No value specified for parameter 9` as parameters supposed to be only 8 but your suggestions adds more ? . .. – Yunus Einsteinium Jul 11 '16 at 08:46

Joop Eggen · Accepted Answer · 2016-07-11T10:25:16.967

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 IFNULL(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());

Java Update PreparedStatement

2 Answers2