JDBC SQL Template Arguments, if parameter value is null then use what is in the database
I am using spring and JDBC pattern to update a row, my problem is that the object I am using to update a record can have multiple null values on it and I need if the value is null then use the current value in the column and not set to null on the table, not sure if this is possible with preparation?
The request will look like this:
UPDATE CUSTOMER SET activeType = ?, readsBooks = ?, readsKindle = ? WHERE custID = ?
So, in my repository:
Object[] parameters = new Object[] {
customer.getActiveType,
customer.readsBooks,
customer.readsKindle
};
memberIterestsRowUpdated = jdbcTemplate.update(query, parameters);
So, if readsBook or readKindle is null, I want to use the current values in the database and not set them to null.
source to share
Use coalesce(param1,param2)
, it returns param2 if param1 is null. It is supported by 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 expression in the list of expressions. You must specify at least two expressions. If all occurrences of expr are null, then the function returns null. COALESCE (expr1, expr2, ..., exprn)
source to share
I am not familiar with Java. But, I think your syntax should be something like this to pass the value of the object (if not null), otherwise just the value from the table itself.
UPDATE CUSTOMER SET activeType = ?, readsBooks = coalesce(@readsBooks,readsBooks), readsKindle = coalesce(@readsKindle,readsKindle) WHERE custID = ?
source to share