CODEWITHSUNDEEP
javasql-serverspringspring-jdbc
emad885
emad885

Reputation: 75

SQL JDBC Template Arguments if parameter value is use null then use what is in the database

I am using spring and JDBC template to update a row, my problem is that the object I am using to update the record for may have some null values on it and what I need is if the value is null then use the current value on the column and not set the value to null in the table, not sure if this is possible using a preparestatement?

The query would be like:

UPDATE CUSTOMER SET
activeType = ?,
readsBooks = ?,
readsKindle = ?
WHERE custID = ?

So in my reposistory:

Object[] parameters = new Object[] { 

customer.getActiveType,
customer.readsBooks,
customer.readsKindle 
    };
memberIterestsRowUpdated = jdbcTemplate.update(query, parameters);

So if readsBook or readKindle is null then I want to use the current values in the database and not set these to null.

Upvotes: 3

Views: 3143

Answers (2)

027
027

Reputation: 1641

Use the coalesce(param1,param2), It returns param2 if the param1 is null. It is supported in oracle and sql server.

UPDATE CUSTOMER T SET
T.activeType = ?,
T.readsBooks = coalesce(?,T.readsBooks ),
T.readsKindle = coalesce(?,T.readsKindle )
WHERE T.custID = ?

COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null. COALESCE (expr1, expr2, ..., exprn)

Upvotes: 4

FutbolFan
FutbolFan

Reputation: 13723

I am not familiar with Java. But, I think your syntax needs to look something like this to pass the object value (if not null), otherwise just value from the table itself.

UPDATE CUSTOMER SET
activeType = ?,
readsBooks = coalesce(@readsBooks,readsBooks),
readsKindle = coalesce(@readsKindle,readsKindle)
WHERE custID = ?

Upvotes: 4

Related Questions