Re: Issue with Selective Update Clause in JOOQ

[Lukas Eder]

Hi Buddhika,

I'm more than happy to help you with your support request, but in order to be able to scale our support efforts at Data Geekery, I hope you understand that we'll have this discussion not privately - but on the jOOQ User Group:

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

Thanks for replying to future E-Mails directly on the user group.

Regarding your questions: Of course, you *could* just concatenate string pieces to a full query string and then have jOOQ execute the string directly, but why would you use jOOQ, then? jOOQ is about composing your query directly in Java via jOOQ's API. Essentially, you can always choose to use either jOOQ's DSL API (best suited for static SQL) or model API (best suited for dynamic SQL):

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

DSL API Example:

DSL.using(configuration)

   .update(A)

   .set(A.FIELD1, "This is Firebird")

   .set(A.FIELD2, 2)

   .where(A.ID.eq(20))

   .execute();

Model API Example:

UpdateQuery<ARecord> query = DSL.using(configuration).updateQuery(A);
query.addValue(A.FIELD1, "This is Firebird");
query.addValue(A.FIELD2, 2);
query.addConditions(A.ID.eq(20));
query.execute();

If you really want to run string-based SQL via jOOQ, then I suspect the problem you were having is the simple fact that you should replace double quotes (") by apostrophes (') when assigning VARCHAR values to A.Field1

Cheers,

Lukas

2014-11-18 9:20 GMT+01:00 Buddhika E. <buddhika....@gmail.com>:

Hi Lukas, I have decided move with JOOQ API to connect to a Firebird database. Here I'm building a spring boot web service that enables few crud operations over spring-jooq connectivity.

So far, all went fine, except the update statement (did not move to insert yet). I'm struggling to build a selective update statements where actual updated fields will only be ported to build the statement. 

i.e: if relation A has 5 fields and only 2 arbitrary fields are updated, (I'm looping on fields and only appends actual updated fields into a stringbuilder, latter into a String representation) 

myQry = "UPDATE A SET A.Field1="This is Firebird", SET A.Field2=2 WHERE A.Id=20 ";
dsl.execute(String myQry);

But unfortunately this didn't work. Could you light me up little bit? 

Thanks and Regards,

Buddhika E.

[buddhika....@gmail.com]

Hi Lukas, In a mean time, I did a workaround with <Record>. First I loaded <Record> A with given ID. (i.e:20). Then I manually checked parsed value and stored value were modified.if modified, I set the new value using Record>>setterXX and  update the Changed(<Field>,boolean) flag accordingly. I hope your second suggestion might done the same. Correct me if I'm wrong. 

Thanks,

Buddhika E.

[Lukas Eder]

Hi Buddhika,

Yes, when you use UpdatableRecords, internally, jOOQ will do exactly the same as what I suggested in the second example. More information about UpdatableRecords can be found here:

http://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords/

Best Regards,

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.