jOOQ and bridge tables

Question

I'm trying to imagine how to use jOOQ with bridge tables. Suppose you have

CREATE TABLE TableA (
  id BIGSERIAL PRIMARY KEY
)

CREATE TABLE TableB (
  id BIGSERIAL PRIMARY KEY
)

CREATE TABLE TableBridge (
  id BIGSERIAL,
  table_a_id INTEGER NOT NULL,
  table_b_id INTEGER NOT NULL,
  CONSTRAINT tablea_pk_id PRIMARY KEY (table_a_id)
   REFERENCES TableA (id) MATCH SIMPLE,
  CONSTRAINT tableb_pk_id PRIMARY KEY (table_b_id)
   REFERENCES TableB (id) MATCH SIMPLE
)

When mapping this schema using jOOQ there will be three record classes, TableARecord, TableBRecord and TableBridgeRecord.

If I want to persist through an insert a record for TableA, should I simply first create and persist the TableB records, then persit rows for TableB and then manually add the TableBridge rows? Isn't there any way to automatically save also the rows in the bridge table?

Answer 1

There are several ways to solve this kind of problem:

1. Do it with a "single" jOOQ statement (running three SQL statements)

The most idiomatic way to solve this kind of problem with standard jOOQ would be to write a single SQL statement that takes care of all three insertions in one go:

ctx.insertInto(TABLE_BRIDGE)
   .columns(TABLE_BRIDGE.TABLE_A_ID, TABLE_BRIDGE.TABLE_B_ID)
   .values(
        ctx.insertInto(TABLE_A)
           .columns(TABLE_A.VAL)
           .values(aVal)
           .returning(TABLE_A.ID)
           .fetchOne()
           .get(TABLE_A.ID),
        ctx.insertInto(TABLE_B)
           .columns(TABLE_B.VAL)
           .values(bVal)
           .returning(TABLE_B.ID)
           .fetchOne()
           .get(TABLE_B.ID)
   )
   .execute();

The above works with jOOQ 3.8. Quite possibly, future versions will remove some of the verbosity around returning() .. fetchOne() .. get().

2. Do it with a single SQL statement

I assume you're using PostgreSQL from your BIGSERIAL data type usage, so the following SQL statement might be an option to you as well:

WITH
  new_a(id) AS (INSERT INTO table_a (val) VALUES (:aVal) RETURNING id),
  new_b(id) AS (INSERT INTO table_b (val) VALUES (:bVal) RETURNING id)
INSERT INTO table_bridge (table_a_id, table_b_id)
SELECT new_a.id, new_b.id
FROM new_a, new_b

The above query is currently not supported entirely via jOOQ 3.8 API, but you can work around the jOOQ API's limitations by using some plain SQL:

ctx.execute(
  "WITH "
+ "  new_a(id) AS ({0}), "
+ "  new_b(id) AS ({1}) "
+ "{2}",

  // {0}
  insertInto(TABLE_A)
  .columns(TABLE_A.VAL)
  .values(aVal)
  .returning(TABLE_A.ID),

  // {1}
  insertInto(TABLE_B)
  .columns(TABLE_B.VAL)
  .values(bVal)
  .returning(TABLE_B.ID),

  // {2}
  insertInto(TABLE_BRIDGE)
  .columns(TABLE_BRIDGE.TABLE_A_ID, TABLE_BRIDGE.TABLE_B_ID)
  .select(
      select(field("new_a.id", Long.class), field("new_b.id", Long.class))
      .from("new_a, new_b")
   )
);

Clearly also here, there will be improvements in future jOOQ APIs.

3. Do it with UpdatableRecords

In this particular simple case, you could get away simply by calling:

TableARecord a = ctx.newRecord(TABLE_A);
a.setVal(aVal);
a.store();

TableBRecord b = ctx.newRecord(TABLE_B);
b.setVal(bVal);
b.store();

TableBridgeRecord bridge = ctx.newRecord(TABLE_BRIDGE);
bridge.setTableAId(a.getId());
bridge.setTableBId(b.getId());
bridge.store();