CODEWITHSUNDEEP
javasqlpostgresqljooq
Tobias Marschall
Tobias Marschall

Reputation: 2579

JOOQ insert if condition is met

I have a table called TimeTracks:

CREATE TABLE "TimeTracks" (
    "id" uuid PRIMARY KEY,
    "startTime" timestamp(3) NOT NULL,
    "endTime" timestamp(3),
    "taskId" uuid NOT NULL REFERENCES "Tasks"("id"),
    UNIQUE ("taskId", "endTime")
);

When inserting the time tracks the following condition must be met:

The timespan between startTime and endTime of time tracks with the same taskId may not overlap.

When using raw SQL people suggest doing something like that:

insert into TimeTrack (id, startTime, endTime, taskId)
select
    'some Id', 'some startTime', 'some endTime', 'some taskId'
where not exists (
    select * from TimeTrack where
    (('some startTime' between startTime and endTime)
    or ('some endTime' between startTime and endTime))
    and ('some taskId' == taskId)
);

How can I do the same thing (or something with the equivalent outcome) in JOOQ?

(I am using PostgreSQL)

Upvotes: 4

Views: 2500

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220842

This translates directly to jOOQ SQL:

Timetracks t = TIMETRACKS;

ctx.insertInto(t)
   .columns(t.ID, t.STARTTIME, t.ENDTIME, t.TASKID)
   .select(
       select(val(someId), val(someStartTime), val(someEndTime), val(someTaskId))
      .whereNotExists(
           selectFrom(t)
          .where( val(someStartTime).between(t.STARTTIME).and(t.ENDTIME)
              .or(val(someEndTime).between(t.STARTTIME).and(t.ENDTIME)) )
          .and(val(someTaskId).eq(t.TASKID))
       )
   )
   .execute();

The above is, as always, assuming the following static import:

import static org.jooq.impl.DSL.*;

Upvotes: 5

Related Questions