1

I'm using MYSQL and JOOQ and I'm trying to write an update query. In this query I want to update field_datetime1 (type datetime) and also update field_timedifference (type bigint) with the time difference in milliseconds between field_datetime1 and field_datetime2.

How do I achive that with JOOQ?

I tried to write this code:

update(table)
.set(field_datetime1, now())
.set(field_timedifference, timestampDiff(field_datetime1, field_datetime2))

But it is not compiling, I get this error:

Cannot resolve method 'set(org.jooq.TableField<MyRecordType,java.lang.Long>, org.jooq.Field<org.jooq.types.DayToSecond>, org.jooq.TableField<MyRecordType,java.lang.Long>)'

I tried to wrap it in DSL.val and it does compile but it sends null in the query.

This is the query that I need to run, I tested it on MYSQL workbanch and it is exactly what I need:

update myTable 
set field_timedifference = 
TIMESTAMPDIFF(microsecond, field_datetime1, field_datetime2)
Shani Madar
  • 21
  • 4

1 Answers1

1

My coworker investigated and found an easy way to write it:

update(table)
.set(field_datetime1, now())
.set(field_timedifference, DSL.field("timestampDiff({0}, {1}, {2})", Long.class,  DSL.keyword(DatePart.MICROSECOND.toSQL()), field_datetime1, field_datetime2))

This way jooq generates an actual query in the set :)

cbmanica
  • 3,502
  • 7
  • 36
  • 54
Shani Madar
  • 21
  • 4