Partial Updates; ignore null values

[Fredrik Hörte]

Hi folks,

I have use case where I want to do a partial update of an object.

My object has the following fields and both fields are nullable.

String name

Integer age

My default update query looks like this:

@SqlUpdate("update user set name = :name, age = :age where id = :it")

public void updateUser(@Bind int id, @BindBean User user);

In my case the object can be partially updated from an API which means that if the name is updated but the age are not then the object looks like follows:

name = "Fredrik"

age = null

If I run the default update query the age field will be set to null, but it should not be updated at all (only the name field).

Unless there are some cool annotation that ignores NULL values I see two solutions.

1) Merge the two objects in my code and build the complete Object and then use the my default update query.

2) Create a custom binder which creates the query so it updates the fields which are not null with new values and updates the fields that are null with the same value.

The final query string would then be: "update user set name = "Fredrik", age = age where id = :it". 

Though I don't know how to set the "age = age" part. In my example below with a custom binder the age would end up as a string (age = "age").

if(arg.getAge() != null)

   q.bind("age", arg.getAge());

else 

  q.bind("age", "age);

Does any of you have any pointers how it could be solved without actually merging the two objects in my code.

Thanks!

[Brian McCallister]

Your best bet is probably approach 2, doing #1 opens you up to either needing a "start; select … for update; update …; commit;" which is much more stinky then just the update on autocommit. of course, it may not matter in your particular use case.

I'd probably approach it by making my own binding + customizer annotation, @BindNonNull or something like that. It would populate something onto the statement context with the fields to bind. You can look at the implementation of @BindIn for a close analogue.

 

-Brian

Thanks!

--
 
 

[Steven Schlansker]

On Dec 30, 2012, at 9:13 AM, Brian McCallister <bri...@skife.org> wrote:

> On Sun, Dec 30, 2012 at 8:21 AM, Fredrik Hörte <fredri...@gmail.com> wrote:

> I have use case where I want to do a partial update of an object.
>
> My object has the following fields and both fields are nullable.
>
> String name
> Integer age
>
> My default update query looks like this:
>
> @SqlUpdate("update user set name = :name, age = :age where id = :it")
> public void updateUser(@Bind int id, @BindBean User user);
>
>
> In my case the object can be partially updated from an API which means that if the name is updated but the age are not then the object looks like follows:
>
> name = "Fredrik"
> age = null
>
> If I run the default update query the age field will be set to null, but it should not be updated at all (only the name field).

I believe this problem is easily solved by using COALESCE.
Have you tried
@SqlUpdate("UPDATE user SET name=COALESCE(:name, name), age=COALESCE(:age, age) WHERE id=:id")
or something like that?

http://www.postgresql.org/docs/9.2/interactive/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL

[Fredrik Hörte]

Thanks, works like a charm!

[Steve Kingsland]

2 years later, I have the same requirement... has the guidance on how to perform a partial UPDATE changed at all?

I have a POJO / DTO with about ~20 fields, that are conditionally set based on a number of factors. Since I only set a subset of the fields, and the POJO is always created fresh in memory (instead of populated from the existing values from the database), I want to make sure the update doesn't overwrite any existing values in the database with null. I'd like to hand this off to a JDBI SQL Object with a straightforward @SqlUpdate like so:

@SqlUpdate("update my_table set a = :a, b = :b, c = :c, ... where id = :it")

I'm using MySQL, so I can wrap each field in a COALESCE() function call if I need to:

@SqlUpdate("update my_table set a = COALESCE(:a, a), b = COALESCE(:b, b), c = COALESCE(:c, c), ... where id = :it")

But even better would be a @BindNotNull (or @BindBeanNotNull, or @Bind(updateWithNull=false), etc.) annotation, so my SQL update statement could stay clean and simple.

Alternatively, does the OptionalArgumentFactory allow for this, if my fields are Optional and isPresent() == false?