Jooq - Support for Update ... Set ... Query with Arbitrary Degree

jOOQ - support for UPDATE ... SET ... query with arbitrary degree

Nice catch, there's a missing method on the UpdateSetFirstStep DSL API, which accepts RowN as a first argument, the type returned from DSL.row(Collection). This should be fixed for jOOQ 3.7:
https://github.com/jOOQ/jOOQ/issues/4475

As a workaround, and if you can live with the guilt of the hack, you could cast to raw types:

context.update(table).set((Row1) DSL.row(fields()), (Select) select())

You can cast DSL.row(fields()) to Row1, because the internal implementation type returned by DSL.row(fields()) implements all Row[N] types.

jOOQ - INSERT INTO ... SELECT ... RETURNING

That combination of INSERT keywords was indeed not possible until jOOQ 3.7 via #3779.

A workaround in the meantime would be to use plain SQL:

Result<Record> result = context.fetch("{0} returning *", insertInto(...).select(...));

Adhoc converter for row-value expressions with higher degree than 22

In jOOQ 3.15 - 3.16, there has been a missing RowN::mapping method which has been added to 3.17 with #12515.

As a workaround, you can use auxiliary nested records to avoid projecting all of your columns, e.g.:

row(
  row(
    TABLE_A.relation1().FIRST_NAME,
    TABLE_A.relation1().LAST_NAME
  ).mapping { f, l -> listOfNotNull(f, l).joinToString(" ") },
  ...
).mapping { rest -> ... }

Or, alternatively, move some of that logic to SQL. Specifically that joinToString(" ") method is just DSL.concat():

row(
  concat(
    TABLE_A.relation1().FIRST_NAME,
    inline(" "),
    TABLE_A.relation1().LAST_NAME
  ),
  ...
).mapping { rest -> ... }

Or, finally, do this (which is what these Row[N].mapping(...) methods are just convenience for):

field(row(
  TABLE_A.relation1().FIRST_NAME,
  TABLE_A.relation1().LAST_NAME,
  ...
)).convertFrom(r -> ...)

jooq single query with one to many relationship

There are many ways to materialise a nested collection with SQL, and / or with jOOQ. I'm just going through some of them:

Using joins

If you don't deeply nest those collections, denormalising (flattening) your results with a JOIN might do the trick for you, without adding too much overhead as data is being duplicated. Essentially, you'll write:

Map<ExperimentRecord, Result<Record>> map =
DSL.using(configuration)
   .select()
   .from(EXPERIMENT)
   .join(TAGS)
   .on(...)
   .fetchGroups(EXPERIMENT);

The above map contains experiment records as keys, and nested collections containing all the tags as values.

Creating two queries

If you want to materialise a complex object graph, using joins might no longer be optimal. Instead, you probably want to collect the data in your client from two distinct queries:

Result<ExperimentRecord> experiments = 
DSL.using(configuration)
   .selectFrom(EXPERIMENT)
   .fetch();

And

Result<TagsRecord> tags =
DSL.using(configuration)
   .selectFrom(TAGS)
   .where(... restrict to the previous experiments ...)
   .fetch();
 

And now, merge the two results in your client's memory, e.g.

experiments.stream()
           .map(e -> new ExperimentWithTags(
                e, 
                tags.stream()
                    .filter(t -> e.getId().equals(t.getExperimentId()))
                    .collect(Collectors.toList())
           ));

Nesting collections using SQL/XML or SQL/JSON

This question didn't require it, but others may find this question in search for a way of nesting to-many relationships with jOOQ. I've provided an answer here. Starting with jOOQ 3.14, you can use your RDBMS's SQL/XML or SQL/JSON capabilities, and then use Jackson, Gson, or JAXB to nest collections like this:

List<Experiment> experiments =
ctx.select(
      EXPERIMENT.asterisk(),
      field(
        select(jsonArrayAgg(jsonObject(TAGS.fields())))
        .from(TAGS)
        .where(TAGS.EXPERIMENT_ID.eq(EXPERIMENT.ID))
      ).as("tags")
    )
   .from(EXPERIMENT)
   .fetchInto(Experiment.class);

Where Experiment is a custom Java class like this:

class Experiment {
  long id;
  String name;
  List<Tag> tags;
}

class Tag {
  long id;
  String name;
}

Nesting collections using MULTISET

Even better than the above, you can hide using SQL/XML or SQL/JSON behind jOOQ 3.15's new MULTISET operator support. Assuming the above Java classes are Java 16 records (or any other immutable classes), you can even map nested collections type safely into your DTOs:

List<Experiment> experiments =
ctx.select(
      EXPERIMENT.ID,
      EXPERIMENT.NAME,
      multiset(
        select(TAGS.ID, TAGS.NAME)
        .from(TAGS)
        .where(TAGS.EXPERIMENT_ID.eq(EXPERIMENT.ID))
      ).as("tags").convertFrom(r -> r.map(Records.mapping(Tag::new)))
    )
   .from(EXPERIMENT)
   .fetch(Records.mapping(Experiment::new));

Where Experiment is a custom Java class like this:

record Experiment(long id, String name, List<Tag> tags) {}
record Tag(long id, String name) {}

See also this blog post for more information.

SQL generated by jOOQ for nested select doesn't work in MySQL/MariaDB

Unfortunately, MariaDB and MySQL don't allow for referencing columns "two levels up" in your correlated subqueries. But if you have MariaDB 10.2 or MySQL 8.0, you can use window functions for the job:

SQL Version

SELECT tr1.player_id, SUM(nsp_score) AS score
FROM (
  SELECT 
    tr2.player_id, 
    tr2.nsp_score, 
    ROW_NUMBER () OVER (PARTITION BY tr2.player_id ORDER BY tr2.nsp_score DESC) rn
  FROM tour_result AS tr2
) AS tr1
WHERE rn <= 3
GROUP BY tr1.player_id;

Filtering by ROW_NUMBER() will pick exactly 3 winning rows among the scores. If you want to have 3 or more rows, if they're tied (WITH TIES semantics), you can use RANK(). I've also blogged about this topic in the past.

jOOQ Version

This translates to the following jOOQ query:

val tr1 = TOUR_RESULT.as("tr1")
val tr2 = TOUR_RESULT.as("tr2")
val result = sql
    .select(tr1.PLAYER_ID, sum(tr1.NSP_SCORE).as("score"))
    .from(table(
       select(
         tr2.PLAYER_ID,
         tr2.NSP_SCORE,
         rowNumber().over(
           partitionBy(tr2.PLAYER_ID)
          .orderBy(tr2.NSP_SCORE.desc())).as("rn"))
      .from(tr2)
    ).as(tr1))
    .where(field(name("rn")).le(inline(3)))
    .groupBy(tr1.PLAYER_ID)
    .fetch()

The above is assuming an import of

import org.jooq.impl.DSL._

Update multiple tables with JOOQ

In principle, the JOIN operations are specified on an org.jooq.Table. There's a pending feature request to add "join convenience methods" also to UPDATE, just as they exist also on SELECT: #3266

Your original query can be written as such in jOOQ:

CustomerDid cd = CUSTOMER_DID.as("cd");
KnowService ks = KNOW_SERVICE.as("ks");
CustomerFlags cf = CUSTOMER_FLAGS.as("cf");

ctx.update(cd.leftJoin(kd)
             .on(ks.ID.eq(cd.CUSTOMER_SERVICE_ID))
             .leftJoin(cf)
             .on(ks.SERVICE_OWNER_ID.eq(cf.ACCOUNT_NUMBER)))
   .set(cd.IS_CLI_NUMBER, 1)
   .set(cf.IS_CLI_NUMBER, "0")
   .where(ks.SERVICE_OWNER_ID.eq(accountNumber))
   .and(cd.DID_NUMBER.eq(cliNumber))
   .execute();

On duplicate key with hstore merge using jooq

What might be the reason for add() not being correctly typed?

Try naming your function parameters, otherwise you cannot match them with the includeExpression property. To match the return value of the function, use the function name itself as an includeExpression. Of course, you could just leave the property away, and match all hstore types with a more generic binding:

types.add(new ForcedType()
            .withUserType("java.util.Map<String, String>")
            .withBinding("HStoreStringBinding")
            .withIncludeTypes("hstore"));

How can I tell jooq to use the original value in the merge as I do in the raw SQL query with t.hstore_data?

Once code generation works, you just use your add function, just like in the SQL version of your statement:

set(TABLE.HSTORE_DATA, Routines.add(TABLE.HSTORE_DATA, table.getHstoreData()))

---

Related Topics