Insert only some fields from a POJO

[Marko Topolnik]

I'm using a JOOQ-generated POJO as the receiver of JSON data coming from a REST request. This is a simple CRUD request, specifically Create. For a number of columns I have default values defined in the database schema and, naturally, the primary key is generated at the database end. However, the POJO has all these properties, which end up being null, indistinguishable from "Undefined" in Java. Then I convert the POJO into a JOOQ Record and try to insert, which fails with NOT NULL constraint violation.

What would be my options to get this right, with as little boilerplate as possible? These are the constraints:

1) if I use a simple Map to receive the request data, then I don't benefit from the automatic conversions which the JSON mapper does for me (e.g., I have a byte[] column, seamlessy transferred as Base64-encoded string);

2) if I use the POJO, I really have no automatic way to distinguish null fields from undefined (missing) ones.

Thanks,

Marko Topolnik

[Marko Topolnik]

Let's say I'd be happy to treat each null field in the POJO as missing. What would be the least painful way to formulate such a Record from the POJO? Perhaps build a Map<String,Object> from the POJO using some custom code which skips nulls?

[Marko Topolnik]

This is the code I have come up with (the variable user holds the POJO with the request data):

      final Record inputRec = sql.newRecord(USER);

      inputRec.from(user);

      final List<Field<?>> fields = new ArrayList<>();

      final List<Object> values = new ArrayList<>();

      for (Field<?> f : inputRec.fields()) {

          final Object v = inputRec.getValue(f);

          if (v != null) { fields.add(f); values.add(v); }

      }

      sql.insertInto(u, fields).values(values).execute();

Please let me know if there's a cleaner/shorter approach.

-- Marko 

[Lukas Eder]

Hello,

A similar problem has been reported before: 

https://groups.google.com/forum/#!topic/jooq-user/_cp2-mrtZ7k/discussion. 

It has lead to this feature requests (among others): 

- https://github.com/jOOQ/jOOQ/issues/2700

- https://github.com/jOOQ/jOOQ/issues/2702

With jOOQ 3.2, #2702 has now been implemented and the jOOQ runtime is aware of nullable and defaulted columns, although this information is not yet used. In the future, there might be a change in how jOOQ deals with "NOT NULL DEFAULT" columns from UpdatableRecords, when they're set to NULL, i.e. when a Java NULL value clearly means that it is undefined.

In the mean time, your workaround is one way to do this, another would be this:

      final Record inputRec = sql.newRecord(USER);

      inputRec.from(user);

      for (Field<?> f : inputRec.fields()) {

          if (inputRec.getValue(f) == null)

              inputRec.changed(false);

      }

      inputRec.store();

Let me know if this helps,

Cheers

Lukas

2013/10/16 Marko Topolnik <marko.t...@gmail.com>

--
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/groups/opt_out.

[Marko Topolnik]

On Thursday, October 17, 2013 10:59:03 AM UTC+2, Lukas Eder wrote:

Hello,

A similar problem has been reported before: 

https://groups.google.com/forum/#!topic/jooq-user/_cp2-mrtZ7k/discussion. 

It has lead to this feature requests (among others): 

- https://github.com/jOOQ/jOOQ/issues/2700

- https://github.com/jOOQ/jOOQ/issues/2702

I was quite convinced that I'm not the first one with this issue, but I unfortunately failed to pinpoint the earlier posts, sorry :)

 

In the mean time, your workaround is one way to do this, another would be this:

      final Record inputRec = sql.newRecord(USER);

      inputRec.from(user);

      for (Field<?> f : inputRec.fields()) {

          if (inputRec.getValue(f) == null)

              inputRec.changed(false);

      }

      inputRec.store();

Great, I was experimenting with this approach involving changed. However, I overlooked the store method and used creat.insertInto(...), which always inserted nulls regardless of their changed status.

Thanks!

--Marko

[Lukas Eder]

2013/10/17 Marko Topolnik <marko.t...@gmail.com>

On Thursday, October 17, 2013 10:59:03 AM UTC+2, Lukas Eder wrote:

Hello,

A similar problem has been reported before: 

https://groups.google.com/forum/#!topic/jooq-user/_cp2-mrtZ7k/discussion. 

It has lead to this feature requests (among others): 

- https://github.com/jOOQ/jOOQ/issues/2700

- https://github.com/jOOQ/jOOQ/issues/2702

I was quite convinced that I'm not the first one with this issue, but I unfortunately failed to pinpoint the earlier posts, sorry :)

No worries! I just tend to link to these things to create more cohesion between threads. This helps me find stuff, later on.

 

 

In the mean time, your workaround is one way to do this, another would be this:

      final Record inputRec = sql.newRecord(USER);

      inputRec.from(user);

      for (Field<?> f : inputRec.fields()) {

          if (inputRec.getValue(f) == null)

              inputRec.changed(false);

      }

      inputRec.store();

Great, I was experimenting with this approach involving changed. However, I overlooked the store method and used creat.insertInto(...), which always inserted nulls regardless of their changed status.

Yes, if you're into CRUD, jOOQ's UpdatableRecord has a couple of useful methods:

http://www.jooq.org/javadoc/latest/org/jooq/UpdatableRecord.html

The explicit DSL API (e.g. create.insertInto(...) ) does not communicate with UpdatableRecord's changed flags. They're merely used for the insert(), update(), store() methods.

[Marko Topolnik]

Yes, now I've looked it up and will keep in mind for the future. I happened to use the plain Record type for my variable, which is another thing that threw me off. So here's your code, corrected for that and for the changed call (to benefit any future users seeing this thread):

UpdatableRecord rec = create.newRecord(u);

rec.from(user);

for (Field<?> f : rec.fields()) if (rec.getValue(f) == null) rec.changed(f, false);

rec.store();

 

[Lukas Eder]

That's a fancy almost-one-liner :-)

I wonder if I should add some more fluency to that API, to be ready for a Java 8 version of the above: 

create.newRecord(u)

      .from(user)

      .eachField((ctx) -> { 

         if (ctx.rec().getValue(ctx.field()) == null) ctx.rec().changed(ctx.field(), false); 

      })

      .store();

;-)

[Marko Topolnik]

Oh, yes! To tell you the truth, each void method in your API suprises me, you got me hooked on fluency!

 

[Lukas Eder]

Nice. Feel free to send more feature requests or sample code to the group. There's lots of room for improvement.

Btw, if you're hooked, you might appreciate jOOX: https://code.google.com/p/joox

[Marko Topolnik]

I've already taken a look at it and this is my view: in my typical request-response code the work is split between querying one XML and building another one. I like to query with XPath and I have had my own JDOM builder for some time. It's only two screenfuls of implementation code (single class) and allows very condensed client code. I've got one incarnation of it on a Github project of mine. One nice aspect I have is optimized syntax for simple elements: builder.textel("el1", "val1", "el2", "val2", ...). 

jOOX is much more ambitious and supports XML tree manipulation, a la jQuery. I'd definitely have it as number one consideration if I had a use case which called for that.

--Marko

[Lukas Eder]

Yes, I think that's a good approach. JDOM is quite fast for DOM building. The textel method is indeed quite useful for some quick XML building. I guess it would be a bit more verbose with jOOX, and a lot more verbose with DOM.

 

Cheers

Lukas

[Marko Topolnik]

I've come across a related problem to the above. Now I need to to a standard UPDATE...SET...WHERE, but I've got more than one type of change--for example:

      (passwordChange?

            sql.update(u).set(u.PASSWORD, user.getPassword())

                         .set(u.SALT, user.getSalt())

          : sql.update(u).set(u.FIRST_NAME, user.getFirstName())

                         .set(u.LAST_NAME, user.getLastName())

                         .set(u.EMAIL, user.getEmail())

      ).where(u.USERNAME.eq(user.getUsername())).execute();

As you can see, there's still a bit of boilerplate there (in the set invocations). I would like to use a similar approach as above with records (automatically select any non-null value from the user POJO as a field to be updated), but I don't know how to incorporate the WHERE part when storing a record (username is not the primary key, but it's a NOT NULL UNIQUE column). I'm not in favor of an extra fetch from the DB to get the primary key when a direct UPDATE is enough.

--Marko

[Marko Topolnik]

Once again, I've come up with something which works but may not be the optimal usage of JOOQ (u is the USER table object, user is the POJO):

      final Record rec = sql.newRecord(u);

      rec.from(user);

      final Map<Field<?>, Object> toUpdate = new HashMap<>();

      for (Field<?> f : rec.fields()) {

          if (f == u.USERNAME) continue;

          final Object v = rec.getValue(f);

          if (v != null) toUpdate.put(f, v);

      }

      sql.update(u).set(toUpdate).where(u.USERNAME.eq(user.getUsername())).execute();

[Marko Topolnik]

And here's the same method, but with specifics abstracted away:

<R extends UpdatableRecord<R>> void updateWhere(Table<R> t, Object pojo, Field<?>... uniqueKey) {

    final R rec = sql.newRecord(t);

    rec.from(pojo);

    final Map<Field<?>, Object> toUpdate = new HashMap<>();

    final Collection<Condition> uniqueKeyConditions = new ArrayList<>();

    nextField: for (Field<?> f : rec.fields()) {

        final Object v = rec.getValue(f);

        for (Field<Object> keyField : (Field<Object>[])uniqueKey)

            if (f == keyField) { uniqueKeyConditions.add(keyField.eq(v)); continue nextField; }

        if (v != null) toUpdate.put(f, v);

    }

    sql.update(t).set(toUpdate).where(uniqueKeyConditions).execute();

[Lukas Eder]

Hi Marko,

2013/10/18 Marko Topolnik <marko.t...@gmail.com>

I've come across a related problem to the above. Now I need to to a standard UPDATE...SET...WHERE, but I've got more than one type of change--for example:

      (passwordChange?

            sql.update(u).set(u.PASSWORD, user.getPassword())

                         .set(u.SALT, user.getSalt())

          : sql.update(u).set(u.FIRST_NAME, user.getFirstName())

                         .set(u.LAST_NAME, user.getLastName())

                         .set(u.EMAIL, user.getEmail())

      ).where(u.USERNAME.eq(user.getUsername())).execute();

Interesting technique. Using conditional expressions to return a partially initialised query step is quite clever!

 

As you can see, there's still a bit of boilerplate there (in the set invocations). I would like to use a similar approach as above with records (automatically select any non-null value from the user POJO as a field to be updated), but I don't know how to incorporate the WHERE part when storing a record (username is not the primary key, but it's a NOT NULL UNIQUE column). I'm not in favor of an extra fetch from the DB to get the primary key when a direct UPDATE is enough.

I think it is difficult to avoid this boilerplate in a sensible way. There might be some potential for convenience methods on the generated UNIQUE contraint in Keys.java, e.g. UniqueKey.eq(user.getUsername()). But I wonder how well such convenience methods would be adopted.

Note, there is DSLContext.executeUpdate(R, Condition):

http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#executeUpdate(R, org.jooq.Condition)

This might help you generate an UPDATE statement with a custom predicate based on a non-primary unique key, although you'd still have to handle nulls yourself.

[Marko Topolnik]

Hi Lukas,

On Monday, October 21, 2013 11:43:56 AM UTC+2, Lukas Eder wrote:

Hi Marko,

I think it is difficult to avoid this boilerplate in a sensible way. There might be some potential for convenience methods on the generated UNIQUE contraint in Keys.java, e.g. UniqueKey.eq(user.getUsername()). But I wonder how well such convenience methods would be adopted.

I was just looking how to associate e.g. the username field with the username property in the POJO (which is an obvious link and I consider it boilerplate if I have to write it out). The approach from my post above gives me that fairly cleanly:

      final Record rec = sql.newRecord(u);

      rec.from(user);

      final Map<Field<?>, Object> toUpdate = new HashMap<>();

      for (Field<?> f : rec.fields()) {

          if (f == u.USERNAME) continue;

          final Object v = rec.getValue(f);

          if (v != null) toUpdate.put(f, v);

      }

      sql.update(u).set(toUpdate).where(u.USERNAME.eq(user.getUsername())).execute();

although it's somewhat roundabout because it involves a Map instead of just a Record. Basically, I use a Record to do the reflection part (POJO -> Record) and then transfer the record to a Map with the logic which weeds out null fields and separates the "update these" fields from "where these match" fields.

 

Note, there is DSLContext.executeUpdate(R, Condition):

http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#executeUpdate(R, org.jooq.Condition)

This might help you generate an UPDATE statement with a custom predicate based on a non-primary unique key, although you'd still have to handle nulls yourself.

This looks quite usable for my need! I think I can adapt the above code and avoid the Map. I'll see into it, thanks!

-Marko 

[Lukas Eder]

Hi Marko,

2013/10/25 Marko Topolnik <marko.t...@gmail.com>

Hi Lukas,

On Monday, October 21, 2013 11:43:56 AM UTC+2, Lukas Eder wrote:

Hi Marko,

I think it is difficult to avoid this boilerplate in a sensible way. There might be some potential for convenience methods on the generated UNIQUE contraint in Keys.java, e.g. UniqueKey.eq(user.getUsername()). But I wonder how well such convenience methods would be adopted.

I was just looking how to associate e.g. the username field with the username property in the POJO (which is an obvious link and I consider it boilerplate if I have to write it out).

I think the most sensible way to tackle the actual problem is to implement #2704:

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

The problem of setting a changed flag to true when re-assigning the pre-existing value to a Record is being discussed in a parallel discussion:

https://groups.google.com/forum/#!topic/jooq-user/gtMxNzg02NA

This has been requested 4 times very recently, so I guess I'll make #2704 a very high priority for jOOQ 3.3 :-)

 

The approach from my post above gives me that fairly cleanly:

      final Record rec = sql.newRecord(u);

      rec.from(user);

      final Map<Field<?>, Object> toUpdate = new HashMap<>();

      for (Field<?> f : rec.fields()) {

          if (f == u.USERNAME) continue;

          final Object v = rec.getValue(f);

          if (v != null) toUpdate.put(f, v);

      }

      sql.update(u).set(toUpdate).where(u.USERNAME.eq(user.getUsername())).execute();

although it's somewhat roundabout because it involves a Map instead of just a Record. Basically, I use a Record to do the reflection part (POJO -> Record) and then transfer the record to a Map with the logic which weeds out null fields and separates the "update these" fields from "where these match" fields.

 

Note, there is DSLContext.executeUpdate(R, Condition):

http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#executeUpdate(R, org.jooq.Condition)

This might help you generate an UPDATE statement with a custom predicate based on a non-primary unique key, although you'd still have to handle nulls yourself.

This looks quite usable for my need! I think I can adapt the above code and avoid the Map. I'll see into it, thanks!

-Marko 

--

[Marko Topolnik]

Hi Lukas,

I think I'm still missing something regarding #2704: it seems to me that this issue is about fetching a record from the db, then updating it with incoming data from a request. On the other hand, I am keen on avoiding any fetching: I want an unconditional UPDATE tableX SET (columns_to_update) WHERE (these_columns_match). The values for both column groups come from a JSON request, uniformly in a single map, and the service layer must know in advance which values belong to which group. Note that, in general, these_columns_match is not required to form a unique key: in that case many database record may be updated. This is the main reason why I don't want fetching, but even if they do form a unique key, I may want to perform bulk updates, where again fetching would hurt performance and complicate the logic.

All said, I am quite satisfied with the solution I already have and I believe I can improve on it using executeUpdate(R, Condition).

-Marko

[Lukas Eder]

Hi Marko,

2013/10/26 Marko Topolnik <marko.t...@gmail.com>

Hi Lukas,

I think I'm still missing something regarding #2704: it seems to me that this issue is about fetching a record from the db, then updating it with incoming data from a request.

Mostly, yes, although other use-cases aren't excluded per se.

 

On the other hand, I am keen on avoiding any fetching: I want an unconditional UPDATE tableX SET (columns_to_update) WHERE (these_columns_match). The values for both column groups come from a JSON request, uniformly in a single map, and the service layer must know in advance which values belong to which group. Note that, in general, these_columns_match is not required to form a unique key: in that case many database record may be updated. This is the main reason why I don't want fetching, but even if they do form a unique key, I may want to perform bulk updates, where again fetching would hurt performance and complicate the logic.

All said, I am quite satisfied with the solution I already have and I believe I can improve on it using executeUpdate(R, Condition).

I see, so yes, the executeUpdate() with Condition method is really the most appropriate way to go forward for you, specifically, once it is possible to influence "null" semantics with respect to a Record's changed flags.

Cheers

Lukas