CODEWITHSUNDEEP
javamysqlprepared-statement
Yunus Einsteinium
Yunus Einsteinium

Reputation: 1180

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.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.

Upvotes: 0

Views: 491

Answers (2)

Joop Eggen
Joop Eggen

Reputation: 109547

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

Upvotes: 1

user207421
user207421

Reputation: 310893

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.

Upvotes: 1

Related Questions