Idiomatic way of handling null values for update

[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

Hi Ben,

Hey Lukas,

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.
https://github.com/jOOQ/jOOQ/issues/2541
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
--
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
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Hi Ben,

Post by 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
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?

Post by Ben Hood
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

Post by Ben Hood
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]

Post by Lukas Eder
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.

Post by Lukas Eder
The latest state is this: https://github.com/jOOQ/jOOQ/issues/3582

OK, good to know.

Post by Lukas Eder
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.

Post by Lukas Eder
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.

Post by Lukas Eder
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.

Post by Lukas Eder
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.

Post by Lukas Eder
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.

Post by Lukas Eder
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.

Post by Lukas Eder
You can probably do that even better
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]

Post by Ben Hood

Post by Lukas Eder
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

Post by Ben Hood
the upgrade. Everything else was smooth.

Great to know!

Post by Ben Hood

Post by Lukas Eder
I'm going to try to translate that lingo to what I think you mean. ;)

You're

Post by Lukas Eder
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.

;-)

Post by Ben Hood
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.

Post by Ben Hood

Post by Lukas Eder
You can probably do that even better
with jOOQ, both with the DSL API, and even more easily with the model

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

Post by Lukas Eder
If you can show an example of what you'd like to achieve, I can show you

a

Post by Lukas Eder
code example.

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

Post by Ben Hood
Thanks for all of your time, BTW.

You're welcome! :-)
Cheers,
Lukas