Idiomatic way of handling null values for update

[Ben Hood]

Hi Lukas,

I've got a bunch of code that programmatically checks whether a value
is not null in the context of building an update statement.

Basically the code extracts non-null fields from a POJO in order to avoid

ctx.UPDATE(FOO).set(FOO.BAR, bar)

when bar == null

But this seems cumbersome. Is there some magic flag somewhere that can
just ignore non-null fields when issuing updates?

Cheers,

Ben

[Ben Hood]

On Fri, May 23, 2014 at 1:59 PM, Ben Hood <0x6e...@gmail.com> wrote:
> But this seems cumbersome. Is there some magic flag somewhere that can
> just ignore non-null fields when issuing updates?

Not sure whether this is idiomatic but the following update seems to work:

return db.execute(ctx -> {
UsersRecord user = ctx.newRecord(USERS);
user.from(u);
for (Field<?> f : user.fields()) {
if (user.getValue(f) == null)
user.changed(f, false);
}
return user.update() == 1;
}
);

Admittedly it does use the CRUDy part of JOOQ (it would be nicer if it
could just be an option on the plain jane fluent API), but it seems to
do the job.

[Ben Hood]

On Fri, May 23, 2014 at 4:20 PM, Ben Hood <0x6e...@gmail.com> wrote:
> Admittedly it does use the CRUDy part of JOOQ (it would be nicer if it
> could just be an option on the plain jane fluent API), but it seems to
> do the job.

Actually I've decided I don't like this solution much because it
circumvents the whole type safety thing, which is the main reason to
use JOOQ in the first place. I wonder if there is a type safe solution
for this.

[Lukas Eder]

Hi Ben,

You will find this discussion here inspiring:

https://groups.google.com/d/msg/jooq-user/8MBETRVrLCM/xPeL-COW26QJ

Marko Topolnik has played around with the jOOQ API for a while to find a suitable solution for a similar problem. Essentially, his solution resembles yours.

Clearly, there is an additional use-case hidden in there somewhere that should be better supported by the jOOQ API, i.e. the distinction of the two possible NULL semantics:

1. Java null = not initialised = SQL "don't touch" = DEFAULT at INSERT, unchanged at UPDATE

2. Java null = explicit null = SQL NULL both at INSERT and at UPDATE

The problem in differentiating such semantics mostly originates from transferring DTO / POJO content into an UpdatableRecord, prior to storage. jOOQ applies semantics 1) when a value was never explicitly set through UpdatableRecord.setValue(), but semantics 2) when it was explicitly set to NULL through UpdatableRecord.setValue(). Your workaround using the changed flag enforces semantics 1)

Another idea was to check for nullability in Field metadata. If a Field is NOT NULL, then the semantics 2) never makes sense, so people have suggested to apply semantics 1), then. To me, that seems a bit too clever.

So far, I think your workaround is really the best solution to this problem, right now...

Cheers

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ben Hood]

Hey Lukas,

On Sun, May 25, 2014 at 9:57 AM, Lukas Eder <lukas...@gmail.com> wrote:
> You will find this discussion here inspiring:
> https://groups.google.com/d/msg/jooq-user/8MBETRVrLCM/xPeL-COW26QJ

Thanks for the pointer - that is an interesting discussion.

> Clearly, there is an additional use-case hidden in there somewhere that
> should be better supported by the jOOQ API, i.e. the distinction of the two
> possible NULL semantics:
>
> 1. Java null = not initialised = SQL "don't touch" = DEFAULT at INSERT,
> unchanged at UPDATE
> 2. Java null = explicit null = SQL NULL both at INSERT and at UPDATE
>
> The problem in differentiating such semantics mostly originates from
> transferring DTO / POJO content into an UpdatableRecord, prior to storage.
> jOOQ applies semantics 1) when a value was never explicitly set through
> UpdatableRecord.setValue(), but semantics 2) when it was explicitly set to
> NULL through UpdatableRecord.setValue(). Your workaround using the changed
> flag enforces semantics 1)
>
> Another idea was to check for nullability in Field metadata. If a Field is
> NOT NULL, then the semantics 2) never makes sense, so people have suggested
> to apply semantics 1), then. To me, that seems a bit too clever.
>
> So far, I think your workaround is really the best solution to this problem,
> right now...

I'd like to keep on using the regular fluent API - I'm not a great fan
of the DTO/POJO binding facilities. So because of this, I was
wondering if you could augment the the set(Field<T> field, T value)
methods on the InsertSetStep fluent API to do something like
setIfNotNull(Field<T> field, T value)? Or you could put the
ifNotNull() on the InsertSetMoreStep if that keeps the API cleaner. Or
you could stick a flag on the entire statement builder instance to get
it to ignore all null values.

My point is that JOOQ shouldn't need to be too clever about devising a
heuristic (at least in the case of the fluent API) - it just needs to
allow an app to say "on update, please ignore these specific fields if
the Java value is null".

Cheers,

Ben

[Lukas Eder]

Hi Ben,

2014-05-26 11:38 GMT+02:00 Ben Hood <0x6e...@gmail.com>:

I'd like to keep on using the regular fluent API - I'm not a great fan
of the DTO/POJO binding facilities. So because of this, I was
wondering if you could augment the the set(Field<T> field, T value)
methods on the InsertSetStep fluent API to do something like
setIfNotNull(Field<T> field, T value)? Or you could put the
ifNotNull() on the InsertSetMoreStep if that keeps the API cleaner. Or
you could stick a flag on the entire statement builder instance to get
it to ignore all null values.

My intuition tells me that this would quickly evolve into something like setIfNotNull(...), setIfNotEmpty(...), setIfNotBlank(...), setIfNull(...), setIfGtZero(...)

And what about the equivalent counterpart in the values() clause? valuesIfNotNull(...)

What about fluent predicate building? Condition.andIfNotNull(something, Condition)

:-) 

On a more serious note, we're going to be evaluating the use of some more functional elements in the SQL DSL that will allow for generalising your idea:

https://github.com/jOOQ/jOOQ/issues/3258

Maybe, there is room for a more general setIf(Predicate, ...) API, but I still feel that these kinds of additions will be a huge overkill, if we want to cover all use-cases for the entire DSL.

Note that you can always just keep a reference to the InsertSetMoreStep, and write a couple of if-else statements.

Another way forward here would then be to use the model API through DSLContext.updateQuery();

http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#insertQuery(org.jooq.Table)

My point is that JOOQ shouldn't need to be too clever about devising a
heuristic (at least in the case of the fluent API) - it just needs to
allow an app to say "on update, please ignore these specific fields if
the Java value is null".

Yes, that use-case will be covered by this Setting here:

https://github.com/jOOQ/jOOQ/issues/2704

Although, only for UpdatableRecords. There is not that much point in saying:

UPDATE table 

SET    column1 = 1, 

       column2 = NULL,

       column3 = 3

OH AND BY THE WAY I DIDNT MEAN THE NULL PARTS;

... because 1) simply don't set the NULL values, 2) what if something evaluates to NULL?

UPDATE table 

SET    column1 = 1, 

       column2 = CASE WHEN 1 = 1 THEN 1 ELSE NULL END,

       column3 = 3

OH AND BY THE WAY I DIDNT MEAN THE NULL PARTS;

Cheers,

Lukas

[Ben Hood]

Hey Lukas,

On Mon, May 26, 2014 at 1:25 PM, Lukas Eder <lukas...@gmail.com> wrote:
> My intuition tells me that this would quickly evolve into something like
> setIfNotNull(...), setIfNotEmpty(...), setIfNotBlank(...), setIfNull(...),
> setIfGtZero(...)
> And what about the equivalent counterpart in the values() clause?
> valuesIfNotNull(...)
> What about fluent predicate building? Condition.andIfNotNull(something,
> Condition)
>
> :-)

I'm sure there's a monad that can handle all of that in one line, but
I'm not sure what color it should be :-)

> Maybe, there is room for a more general setIf(Predicate, ...) API, but I
> still feel that these kinds of additions will be a huge overkill, if we want
> to cover all use-cases for the entire DSL.

Sure, I get that - and to put it in perspective, this functionality is
only ever a nice to have - all it does is save a bit of verbosity.

> Note that you can always just keep a reference to the InsertSetMoreStep, and
> write a couple of if-else statements.

That's exactly what my current code does, and that was the reason why
I started this thread. In practice, the couple of if statements turns
into quite a few, and then multiply that by the number of tables you
are writing to.

Using the Record based API (as indicated earlier in this thread) does
work and scores well for its relative terseness. So it does give me
the ability to compromise - I can go for the type safe variant with a
bunch of if statements or I can for the terse ORM variant.

Man, it's so difficult to a type safe hipster these days ;-)

Cheers,

Ben

[Ben Hood]

On Tue, May 27, 2014 at 7:18 AM, Ben Hood <0x6e...@gmail.com> wrote:
> Man, it's so difficult to a type safe hipster these days ;-)

s/to/to be/

[Lukas Eder]

Hi Ben,

2014-05-27 8:18 GMT+02:00 Ben Hood <0x6e...@gmail.com>:

Hey Lukas,

On Mon, May 26, 2014 at 1:25 PM, Lukas Eder <lukas...@gmail.com> wrote:
> My intuition tells me that this would quickly evolve into something like
> setIfNotNull(...), setIfNotEmpty(...), setIfNotBlank(...), setIfNull(...),
> setIfGtZero(...)
> And what about the equivalent counterpart in the values() clause?
> valuesIfNotNull(...)
> What about fluent predicate building? Condition.andIfNotNull(something,
> Condition)
>
> :-)

I'm sure there's a monad that can handle all of that in one line, but
I'm not sure what color it should be :-)

Precisely. Don't get me wrong, I also think that *something* should / could be done, but it doesn't appear to be a low-hanging fruit. I suspect that for true monadic SQL statement composition, jOOQ will have to significantly transform its API again, as there are so many use-cases to keep in mind.

Take this idea for instance:

https://github.com/jOOQ/jOOQ/issues/2541

Originating from here:

https://groups.google.com/d/msg/jooq-user/h7U3Q9qhzGk/IDx0C4jgxWQJ

The idea was to be able to inject arbitrary string-based clauses pretty much anywhere into a statement through the DSL. The best way, oviously, is to implement custom query parts, or VisitListeners. But the use-case for ad-hoc custom SQL clauses is compelling as well, given all the vendor-specific syntaxes for execution hints, etc.

So, there's a huge demand for more DSL goodness. But it's very hard to find the right solution.

> Maybe, there is room for a more general setIf(Predicate, ...) API, but I
> still feel that these kinds of additions will be a huge overkill, if we want
> to cover all use-cases for the entire DSL.

Sure, I get that - and to put it in perspective, this functionality is
only ever a nice to have - all it does is save a bit of verbosity.

... which I get as well :-)

 

> Note that you can always just keep a reference to the InsertSetMoreStep, and
> write a couple of if-else statements.

That's exactly what my current code does, and that was the reason why
I started this thread. In practice, the couple of if statements turns
into quite a few, and then multiply that by the number of tables you
are writing to.

So, can you show a full example of what you're currently working on? It's always easier to discuss by example. Maybe, after all, there *is* a low-hanging fruit.

 

Using the Record based API (as indicated earlier in this thread) does
work and scores well for its relative terseness. So it does give me
the ability to compromise - I can go for the type safe variant with a
bunch of if statements or I can for the terse ORM variant.

Man, it's so difficult to a type safe hipster these days ;-)

Too many options. But in the past, we've consolidated all sorts of "deviating ideas" into common ones, e.g. through SPIs like the ExecuteListener, or the VisitListener. I'm positive that we can tackle this issue as well...

Cheers

Lukas 

[Ben Hood]

Hi Lukas,

I've just updated an app that uses the above mentioned skullduggery to
3.6.0 and the use of

void from(Object source) throws MappingException;

on an UpdateableRecord caused the JOOQ record not to get populated
from the underlying application object. On face value this appears to
be because that application still had @Column annotations on a couple
of fields (this was just cruft). So I nuked the annotations and now
the app works with 3.6.0.

That all said, I was wondering whether it is high time that I stopped
using the value tracking facilities from UpdateableRecord and instead
used a SQL builder variant to avoid issuing UPDATE clauses for Java
null values.

So I was wondering if the JOOQ state of the art has moved on and there
is now a way of using the fluent API to skip null fields of Java
objects? Not sure if I've alluded to this previously, but iBatis had
this neat way of building SQL in the thread local so that you could
struct a statement with plain jane Java if statements.

Is there something like that for JOOQ hipsters?

Cheers,

Ben

[Lukas Eder]

Hi Ben,

2015-04-24 5:47 GMT+02:00 Ben Hood <0x6e...@gmail.com>:

Hi Lukas,

I've just updated an app that uses the above mentioned skullduggery to
3.6.0 and the use of

void from(Object source) throws MappingException;

on an UpdateableRecord caused the JOOQ record not to get populated
from the underlying application object. On face value this appears to
be because that application still had @Column annotations on a couple
of fields (this was just cruft). So I nuked the annotations and now
the app works with 3.6.0.

Hmm, but that has always been the case in previous jOOQ implementations, hasn't it? From what version did you upgrade?

 

That all said, I was wondering whether it is high time that I stopped
using the value tracking facilities from UpdateableRecord and instead
used a SQL builder variant to avoid issuing UPDATE clauses for Java
null values.

So I was wondering if the JOOQ state of the art has moved on and there
is now a way of using the fluent API to skip null fields of Java
objects?

The latest state is this: https://github.com/jOOQ/jOOQ/issues/3582

If your column has a NOT NULL constraint, and your POJO that you pass to Record.from(Object) has a null value, then the Record's internal changed flag for that column will not be set, when you call Record.store(), insert(), or update()

This change of behaviour was introduced in jOOQ 3.5.

As of jOOQ 3.6, we've also adapted the UPDATE .. SET [ Record ] implementation to consider only those values in a Record that have their changed() flag set to true: https://github.com/jOOQ/jOOQ/issues/4161.

Did you try this syntax?

DSLContext ctx = DSL.using(configuration);

MyTableRecord record = ctx.newRecord(MY_TABLE, pojo);

ctx.update(MY_TABLE).set(record).where(...).execute();

 

Or, did you have something else in mind?

Not sure if I've alluded to this previously, but iBatis had
this neat way of building SQL in the thread local so that you could
struct a statement with plain jane Java if statements.

Is there something like that for JOOQ hipsters?

I'm going to try to translate that lingo to what I think you mean. ;) You're talking about this MyBatis API, right?

http://adamgent.com/post/30974973820/mybatis-sqlbuilder-rewritten

Let's not discuss the "intriguing" idea of doing this via ThreadLocals but the composability of SQL statements. You can probably do that even better with jOOQ, both with the DSL API, and even more easily with the model API:

http://www.jooq.org/doc/latest/manual/sql-building/sql-statements/dsl-and-non-dsl

If you can show an example of what you'd like to achieve, I can show you a code example.

Cheers,
Lukas

[Ben Hood]

On Fri, Apr 24, 2015 at 7:54 AM, Lukas Eder <lukas...@gmail.com> wrote:
> Hmm, but that has always been the case in previous jOOQ implementations,
> hasn't it? From what version did you upgrade?

From 3.5.1

I can reproduce the issue by just changing the version of JOOQ in my
POM, without any other changes.

But I do agree with JOOQ's handling of the particular class definition
in 3.6.0 - it seems that for some reason it was more lenient in 3.5.1.

I did find it interesting that this was the only issue I ran into with
the upgrade. Everything else was smooth.

But I'm assuming that ultimately the cause of the problem is due to
the crufty code constellation in my app, and since this was an area
that I've been meaning to refactor, I thought it would be better to
put effort into bringing the application code base up to scratch as
opposed to looking at JOOQ as the potential culprit.

> The latest state is this: https://github.com/jOOQ/jOOQ/issues/3582

OK, good to know.

> If your column has a NOT NULL constraint, and your POJO that you pass to
> Record.from(Object) has a null value, then the Record's internal changed
> flag for that column will not be set, when you call Record.store(),
> insert(), or update()

OK, point taken.

> This change of behaviour was introduced in jOOQ 3.5.

Also good to know. As stated before, although my app was previously
running 3.5.1, I think it's more likely that there is subtle bug in my
app rather than a change in behavior between 3.5.1 and 3.6.0.

> As of jOOQ 3.6, we've also adapted the UPDATE .. SET [ Record ]
> implementation to consider only those values in a Record that have their
> changed() flag set to true: https://github.com/jOOQ/jOOQ/issues/4161.
>
> Did you try this syntax?
>
> DSLContext ctx = DSL.using(configuration);
> MyTableRecord record = ctx.newRecord(MY_TABLE, pojo);
> ctx.update(MY_TABLE).set(record).where(...).execute();

No, but that suggestion looks sane :-)

I'll give it a go.

>
> Or, did you have something else in mind?

No, that example looks pretty good. If the

ctx.newRecord(MY_TABLE, pojo)

handles the POJO null checks, that would solve the issue.

> I'm going to try to translate that lingo to what I think you mean. ;) You're
> talking about this MyBatis API, right?
> http://adamgent.com/post/30974973820/mybatis-sqlbuilder-rewritten

I should be more careful with what I write.

Note to self: don't use the words "neat" and "playing around with the
thread local" in the same sentence.

Yes, I was referring to something I tried out years ago with
MyBatis/iBatis, and I didn't realize that the project had it changed
its name.

> Let's not discuss the "intriguing" idea of doing this via ThreadLocals but
> the composability of SQL statements.

Agreed. I was talking too metaphorically about how MyBatis supports an
imperative composition of an SQL string. To be clear, I am not putting
forward the MyBatis solution as a good idea.

I was just asking about a compact way to intertwine Java if statements
using the DSL API. For more complex compositional stuff I naturally
use the model API to build up the SQL statement.

> You can probably do that even better
> with jOOQ, both with the DSL API, and even more easily with the model API:
> http://www.jooq.org/doc/latest/manual/sql-building/sql-statements/dsl-and-non-dsl
>
> If you can show an example of what you'd like to achieve, I can show you a
> code example.

This particular use case is (in pseudo-code):

ctx.update(USERS).
set(USERS.LAST_NAME, userPojo.getLastName()).
if (userPojo.getFirstName() != null) {
set(USERS.FIRST_NAME);
}
where(USERS.ID.eq(id)).
execute();

which is obviously not valid code.

I think maybe something along the lines of:

UpdateSetMoreStep s =
ctx.update(USERS).
set(USERS.LAST_NAME, u.getLastName());

if (u.getFirstName() != null) {
s.set(USERS.FIRST_NAME, u.getFirstName());
}

s.where(USERS.ID.eq(u.getId())).
execute();

might be slightly more idiomatic.

Thanks for all of your time, BTW.

Ben

[Lukas Eder]

2015-04-24 13:16 GMT+02:00 Ben Hood <0x6e...@gmail.com>:

On Fri, Apr 24, 2015 at 7:54 AM, Lukas Eder <lukas...@gmail.com> wrote:
> Hmm, but that has always been the case in previous jOOQ implementations,
> hasn't it? From what version did you upgrade?

From 3.5.1

I can reproduce the issue by just changing the version of JOOQ in my
POM, without any other changes.

But I do agree with JOOQ's handling of the particular class definition
in 3.6.0 - it seems that for some reason it was more lenient in 3.5.1.

Hmm, I vaguely remember implementing a change where Record.from() and Record.into() were "harmonized" to follow the specification of DefaultRecordMapper... But I cannot seem to find the appropriate issue right now.

I did find it interesting that this was the only issue I ran into with
the upgrade. Everything else was smooth.

Great to know!

  

> I'm going to try to translate that lingo to what I think you mean. ;) You're
> talking about this MyBatis API, right?
> http://adamgent.com/post/30974973820/mybatis-sqlbuilder-rewritten

I should be more careful with what I write.

Note to self: don't use the words "neat" and "playing around with the
thread local" in the same sentence.

;-)

 

Yes, I was referring to something I tried out years ago with
MyBatis/iBatis, and I didn't realize that the project had it changed
its name.

Oh, that happened a while ago! It also moved out of the Apache Foundation, which is probably why they had to change the name / trademarks, as you can never really reclaim anything that has gone beyond the singularity of the Apache Foundation.

 

> You can probably do that even better
> with jOOQ, both with the DSL API, and even more easily with the model API:
> http://www.jooq.org/doc/latest/manual/sql-building/sql-statements/dsl-and-non-dsl
>
> If you can show an example of what you'd like to achieve, I can show you a
> code example.

This particular use case is (in pseudo-code):

ctx.update(USERS).
      set(USERS.LAST_NAME, userPojo.getLastName()).
      if (userPojo.getFirstName() != null) {
        set(USERS.FIRST_NAME);
      }
      where(USERS.ID.eq(id)).
      execute();

which is obviously not valid code.

I think maybe something along the lines of:

UpdateSetMoreStep s =
    ctx.update(USERS).
        set(USERS.LAST_NAME, u.getLastName());

if (u.getFirstName() != null) {
  s.set(USERS.FIRST_NAME, u.getFirstName());
}

s.where(USERS.ID.eq(u.getId())).
execute();

might be slightly more idiomatic.

Yes, that would work. Or you could resort to the model API, which simply operates on UpdateQuery...

 

Thanks for all of your time, BTW.

You're welcome! :-)

Cheers,

Lukas