JOOQ Record created by Object erases fields that were not found

[Oleg]

Hi

I have the following code tfor table ENTITY_TABLE = (id, name, code)

Record record = dsl.newRecord(getTable(), entity);
dsl.update(getTable())
        .set(record)
        .where(getTableId().eq(record.getValue(getTableId())))
        .execute();

and DTO Entity (id, name) - using which JOOQ record is created

This expression generates UPDATE for 3 fields (id, name, code) instead of 2.

Hence, code value in DB become null.

I would expect only 2 fields to be updated.

I tried record.changed(CODE, false), but this did not word.

Is there a way to generate update for only those columns that were actually found in DTO without manual setting them?

Regards.

[Lukas Eder]

Hello,

I'm sorry for the delay. You're right, the changed() flag should affect the query - this is a bug:

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

As a workaround, you can either set each column individually, or use DSLContext.executeUpdate(UpdatableRecord), which already implements the query as you expected it:

http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#executeUpdate-R-

Hope this helped,

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.

[Lukas Eder]

#4161 is now implemented for jOOQ 3.6.0. We won't merge this into 3.5.x, because it will subtly change the behaviour of the method - which might be unexpected in a patch release and in semantic versioning.

Cheers,

Lukas

[Oleg Kuznetsov]

Hello, Lukas,

It's great that changed() has been implemented.

One more question: if a dto does not have number of fields that record has, will dsl.executeUpdate(record) on the record converted from this dto still generate update for all fields now?

Regards.

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/DIw1KfQaCs0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

2015-04-01 23:59 GMT+02:00 Oleg Kuznetsov <olkuz...@gmail.com>:

Hello, Lukas,

It's great that changed() has been implemented.

One more question: if a dto does not have number of fields that record has, will dsl.executeUpdate(record) on the record converted from this dto still generate update for all fields now?

You have to look at it this way: Loading a record from a DTO is one step. Generating an update statement from a record is another step. The two steps are completely independent. 

When you load a record from a DTO, all the fields that are present in both record and DTO will be "changed". If a field is not present in the DTO (but in the Record), it will be ignored.

When you generate an update statement from a record, all the fields that have been changed will be updated.

[Oleg Kuznetsov]

Yes, but currently all fields are marked changed, not only intersected.

--

[Lukas Eder]

Hi Oleg,

I'm sorry, I'm not quite sure what you mean by this. The following test passes, for instance:

    @Test

    public void testRecordFrom() throws Exception {

        Table1Record record = create.newRecord(TABLE1);

        assertFalse(record.changed());

        T1 t1 = new T1();

        t1.ID1 = 1;

        t1.NAME1 = "A";

        t1.DUMMY = "X";

        record.from(t1);

        assertTrue(record.changed());

        assertTrue(record.changed(TABLE1.ID1));

        assertTrue(record.changed(TABLE1.NAME1));

        assertFalse(record.changed(TABLE1.DATE1));

        assertEquals(1, (int) record.getValue(TABLE1.ID1));

        assertEquals("A", record.getValue(TABLE1.NAME1));

        assertNull(record.getValue(TABLE1.DATE1));

    }

    static class T1 {

        Integer ID1;

        String NAME1;

        String DUMMY;

    }

Am I perhaps missing something?

--

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.

[Oleg Kuznetsov]

Hi Lukas,

Please, add the following line in the end:

String sql = sl.update(TABLE1).set(record).getSQL(ParamType.INLINED);

so you can see, that sql string will contain SET statements for all fields (including DATE1), not only changed.

Regards,

Oleg

[Lukas Eder]

Hi Oleg,

There might be some confusion about what "changed" means. In the previous example, DATE1 is not "changed" when calling record.from(t1), because t1 doesn't contain a DATE1 field. If it did contain a DATE1 field with a NULL value in it, then the DATE1 column would be "changed" to NULL (if it is a nullable column).

Does that help, or do you perhaps have a different case in mind?

Cheers,

Lukas

[Oleg Kuznetsov]

Hi Lukas,

I completely agree with your point, but it does not equal to mine :)

assertFalse(record.get(record.changed(TABLE1.DATE1))) - this is correct to me.

But as a user of JOOQ, I expect that if column is not changed in record, then update SQL generated on this record will not contain 'SET date1 = null'.

Regards,

Oleg

[Lukas Eder]

Oleg, maybe we're a bit out of sync here... :)

2015-06-05 15:15 GMT+02:00 Oleg Kuznetsov <olkuz...@gmail.com>:

Hi Lukas,

I completely agree with your point, but it does not equal to mine :)

assertFalse(record.get(record.changed(TABLE1.DATE1))) - this is correct to me.

How does this work? record.change(Field<?>) returns a boolean, and there's no record.get(boolean) method. At least not in jOOQ's API...

 

But as a user of JOOQ, I expect that if column is not changed in record, then update SQL generated on this record will not contain 'SET date1 = null'.

Absolutely, and we've implemented that (according to our understanding) in 3.6.0:

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

But the point here is: What do you mean by "column is not changed"? The record's setter for a field is called: setValue(). This will always set the "changed" flag to true for that column, regardless if the value "changes". There are several reasons for this:

1. You may have triggers that depend on this

2. You may want to omit DEFAULT expressions from applying

3. You may want to enforce a certain type of SQL statement, to prevent excessive hard-parsing of different possible insert / update statement combinations. This can help prevent cursor cache contention, e.g. in Oracle

So, do we mean the same thing when we say "changed" ?

[Oleg Kuznetsov]

My mistake - assertFalse(record.changed(TABLE1.DATE1))  - correct to me.

Well, for me changed - that field was actually changed by setter. In this case it was not.

I agree, stable SQL is good point, but sometimes it is necessary not to generate it in order not to erase untouched fields.

 

Coould you comment here or on gitHub the way you solve this problem? Will changed() be always taken into account or there will be some flexibility for a progammer? The way to tell JOOQ to consider really changed field?

Regards,

Oleg

[Lukas Eder]

2015-06-05 15:32 GMT+02:00 Oleg Kuznetsov <olkuz...@gmail.com>:

My mistake - assertFalse(record.changed(TABLE1.DATE1))  - correct to me.

Well, for me changed - that field was actually changed by setter. In this case it was not.

I agree, stable SQL is good point, but sometimes it is necessary not to generate it in order not to erase untouched fields.

 

Coould you comment here or on gitHub the way you solve this problem? Will changed() be always taken into account or there will be some flexibility for a progammer? The way to tell JOOQ to consider really changed field?

The idea is that whenever "changed" information is available, it should be considered and only "changed" values should make it into SQL statements (sometimes there are bugs, of course).

You have full control over those "changed" flags via Record.changed(Field, boolean):

http://www.jooq.org/javadoc/latest/org/jooq/Record.html#changed-org.jooq.Field-boolean-

Is that what you were looking for?

[Oleg Kuznetsov]

Yes, thank you!

--

[Robert DiFalco]

Sorry for belaboring this but I still don't see a clear answer to the question. I suppose I could just look at the code but...

Many of us have beans whose fields we may populate from the database. Then a user may modify some of the field in this bean/record. When we then do an update are only the fields that have been modified updated for that row? I would expect JOOQ to only update the fields that have been modified in code, not those that were retrieved from the database and have not been "changed". 

[Oleg]

I think JOOQ cannot detect changes made in DTO, because there is no connection between it and jooq record.

As I understand, since 3.6 changes made via record will cause update only changed fields.

Also, converting DTO, where number of fields is less than in JOOQ record, to JOOQ record will also cause update only fields contained in DTO.

среда, 10 июня 2015 г., 18:30:28 UTC+3 пользователь Robert DiFalco написал:

[Lukas Eder]

OK, no worries. Let's review the jOOQ API. Perhaps, you're confusing different methods and their behaviours / implications on the resulting query

среда, 10 июня 2015 г., 18:30:28 UTC+3 пользователь Robert DiFalco написал:

Sorry for belaboring this but I still don't see a clear answer to the question. I suppose I could just look at the code but...

Many of us have beans whose fields we may populate from the database. Then a user may modify some of the field in this bean/record. When we then do an update are only the fields that have been modified updated for that row? I would expect JOOQ to only update the fields that have been modified in code, not those that were retrieved from the database and have not been "changed". 

Let's establish two terms first, to avoid confusion.

- "changed" is what jOOQ understands and implements in Record.changed(). A value may be "changed" without being "modified"

- "modified" is what you probably understand by transitioning a value from A to B, such that java.util.Objects.equals(A, B) == false

How does "changed" work?

1. As Oleg also correctly mentioned, jOOQ cannot intercept what you do with your Beans / DTOs ("POJOs"). Other frameworks might make use of instrumentation / bytecode magic, but we don't. So modifications to the beans and its attributes are transparent to jOOQ

2. A Record contains a state machine that takes into account a variety of ActiveRecord use-cases. Most importantly, it will avoid rendering "unchanged" values to resulting INSERT or UPDATE statements. jOOQ does this to allow for the application of triggers and DEFAULT values in the database, mostly on INSERT.

3. The "changed" flag for a Record's Field is set to true if:

    a) You set it to true explicitly via a Record.changed() method

    b) The field is a non-primary key field and you call Record.setValue() (or some overload / generated variant thereof) on it

    c) The field is a primary key field and you call Record.setValue() (or some ...) on it with Settings.updatablePrimaryKeys set to true

    d) The field is a primary key field of a "new" record (not previously fetched from the database) and you call Record.setValue() (or some ...) on it.

    e) You call Record.setValue() (...) on a primary key field of a previously fetched record, "modifying" it to a new value (this is a legacy feature in jOOQ, as jOOQ used to interpret primary key modifications as a hint to copy a record. Obviously only when Settings.updatablePrimaryKeys is false)

4. The Record.from() method simply uses reflection to take each attribute from your POJO and set the corresponding value of your record using setValue(), unless:

    a) The value is null and the corresponding Field is NOT NULL

I suspect that your confusion originates from a missing 4b), which would imply that Record.from() should not call setValue() on values that will not be "modified". We cannot, however, implement such a change backwards-compatibly, and I think that it would be wrong in the event of a nullable column with a DEFAULT value.

I hope this clarifies things a bit?

 

2015-06-10 17:51 GMT+02:00 Oleg <olkuz...@gmail.com>:

I think JOOQ cannot detect changes made in DTO, because there is no connection between it and jooq record.

As I understand, since 3.6 changes made via record will cause update only changed fields.

We haven't really changed the behaviour of all of this, just made the behaviour more consistent, such that the following two will have roughly the same effect:

record.store()

DSL.using(configuration).update(TABLE).set(record).where(...).execute();

 

Also, converting DTO, where number of fields is less than in JOOQ record, to JOOQ record will also cause update only fields contained in DTO.

Exactly.

[Oleg]

Hi Lukas

Just to clarify:

>> 4. The Record.from() method simply uses reflection to take each attribute from your POJO and set the corresponding value of your record using setValue(), unless:

>>    a) The value is null and the corresponding Field is NOT NULL

That means that if POJO has field with null value, than record filed will be unchanged after Record.from() ? It shold be changed.

четверг, 11 июня 2015 г., 12:17:09 UTC+3 пользователь Lukas Eder написал:

[Lukas Eder]

2015-06-11 12:01 GMT+02:00 Oleg <olkuz...@gmail.com>:

Hi Lukas

Just to clarify:

>> 4. The Record.from() method simply uses reflection to take each attribute from your POJO and set the corresponding value of your record using setValue(), unless:

>>    a) The value is null and the corresponding Field is NOT NULL

That means that if POJO has field with null value, than record filed will be unchanged after Record.from() ? It shold be changed.

If and only if the Field is a NOT NULL field. Details here:

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

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

[Oleg]

What will happen in this case:

Entity e = new Entity(id = 1, value = null);

String sql = dsl.update(ENTITY_RECORD).set(e).where(...).getSql();

I believe that sql should be like UPDATE entity SET id = 1, value = null ... but 4a does not mean it, because NULL is excluded as not changed.

четверг, 11 июня 2015 г., 13:28:44 UTC+3 пользователь Lukas Eder написал:

[Lukas Eder]

That's a bit hypothetical, as it's pseudo-code (I guess)? Is Entity a Record? What's the DDL of the table? Is "value" a nullable column or not? How do you set "value = null" in real code? If it's not a Record, you can't pass it to the set(...) method.

--

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.

[Oleg]

Ok, I fixed the code a bit:

Entity entityDto = new Entity(id = 1, value = null); // value is nullable

String sql = dsl.update(ENTITY_RECORD).set(Record.from(entityDto)).where(...).getSql();

четверг, 11 июня 2015 г., 13:40:06 UTC+3 пользователь Lukas Eder написал:

[Lukas Eder]

2015-06-11 13:13 GMT+02:00 Oleg <olkuz...@gmail.com>:

Ok, I fixed the code a bit:

Entity entityDto = new Entity(id = 1, value = null); // value is nullable

String sql = dsl.update(ENTITY_RECORD).set(Record.from(entityDto)).where(...).getSql();

The generated SQL should be

UPDATE entity SET id = 1, value = null WHERE ...