I'm working on a jOOQ query for MySQL where I need to fetch a single row from a main table along with one-to-many related data from two other tables. My schema consists of three tables:

• TABLE_A: Contains a primary key (USER_ID) and a field NAME (plus other settings).
• TABLE_B: Contains related values (e.g., B_VALUE) and a foreign key USER_ID.
• TABLE_C: Contains related values (e.g., C_VALUE) and a foreign key USER_ID.

I need to map the result into the following POJOs:

class APojo {
    public Int userId;
    public List<String> listOfBValue;
    public List<String> listOfCValue;
}

The expected JSON output would be something like:

{
  "userId": 1,
  "listOfBValue": [ "bvalue1", "bvalue2" ],
  "listOfCValue": [ "cvalue1" ]
}

Initially, I tried writing the query inspired by this answer:

dslContext.select(
    TABLE_A.USER_ID,
    array(
        dslContext.select(row(TABLE_B.B_VALUE))
            .from(TABLE_B)
            .where(TABLE_B.USER_ID.eq(userId))
    ),
    array(
        dslContext.select(row(TABLE_C.C_VALUE))
            .from(TABLE_C)
            .where(TABLE_C.USER_ID.eq(userId))
    )
)
.from(TABLE_A)
.where(TABLE_A.USER_ID.eq(userId))
.fetchOne()

However, this query throws a "jOOQ; bad SQL grammar" exception. How to fix that?

I'm working on a jOOQ query for MySQL where I need to fetch a single row from a main table along with one-to-many related data from two other tables. My schema consists of three tables:

• TABLE_A: Contains a primary key (USER_ID) and a field NAME (plus other settings).
• TABLE_B: Contains related values (e.g., B_VALUE) and a foreign key USER_ID.
• TABLE_C: Contains related values (e.g., C_VALUE) and a foreign key USER_ID.

I need to map the result into the following POJOs:

class APojo {
    public Int userId;
    public List<String> listOfBValue;
    public List<String> listOfCValue;
}

The expected JSON output would be something like:

{
  "userId": 1,
  "listOfBValue": [ "bvalue1", "bvalue2" ],
  "listOfCValue": [ "cvalue1" ]
}

Initially, I tried writing the query inspired by this answer:

dslContext.select(
    TABLE_A.USER_ID,
    array(
        dslContext.select(row(TABLE_B.B_VALUE))
            .from(TABLE_B)
            .where(TABLE_B.USER_ID.eq(userId))
    ),
    array(
        dslContext.select(row(TABLE_C.C_VALUE))
            .from(TABLE_C)
            .where(TABLE_C.USER_ID.eq(userId))
    )
)
.from(TABLE_A)
.where(TABLE_A.USER_ID.eq(userId))
.fetchOne()

However, this query throws a "jOOQ; bad SQL grammar" exception. How to fix that?

• mysql
• spring-boot
• kotlin
• jooq
• jooq-codegen

• You might try using multiset instead of array. Also, you might have to chain it with .as("column_name") to make it a valid query. Finally, th jooq etror should contain a detailed message, or a cause with details about the syntax error. Can you attach it to the question please ? – amanin Commented Feb 15 at 16:25
• The answer you've linked was outdated. I've updated it with MULTISET examples. – Lukas Eder Commented Feb 17 at 7:56

1 Answer 1

MySQL doesn't have native support for ARRAY types, and as of jOOQ 3.19, there's no emulation for those operators using SQL/XML or SQL/JSON, as there is for MULTISET:

• https://github/jOOQ/jOOQ/issues/12029

As such, just use MULTISET directly, which can be emulated using MySQL's JSON capabilities. The following example assumes you have the appropriate constructors (e.g. written manually or by switching to record classes), so you can use ad-hoc conversion:

ctx.select(
    TABLE_A.USER_ID,
    multiset(
        select(TABLE_B.B_VALUE)
        .from(TABLE_B)
        .where(TABLE_B.USER_ID.eq(userId))
    ).convertFrom(r -> r.map(Record1::value1))),
    multiset(
        select(TABLE_C.C_VALUE)
        .from(TABLE_C)
        .where(TABLE_C.USER_ID.eq(userId))
    ).convertFrom(r -> r.map(Record1::value1)))
)
.from(TABLE_A)
.where(TABLE_A.USER_ID.eq(userId))
.fetchOne(Records.mapping(APojo::new))

Or, since jOOQ 3.19, with implicit join path correlation available, you could also write this, instead:

ctx.select(
    TABLE_A.USER_ID,
    multiset(
        select(TABLE_A.tableB().B_VALUE)
        .from(TABLE_A.tableB()) // Implicit join path correlation
    ).convertFrom(r -> r.map(Record1::value1))),
    multiset(
        select(TABLE_A.tableC().C_VALUE)
        .from(TABLE_A.tableC()) // Implicit join path correlation
    ).convertFrom(r -> r.map(Record1::value1)))
)
.from(TABLE_A)
.where(TABLE_A.USER_ID.eq(userId))
.fetchOne(Records.mapping(APojo::new))

Note that alternatively, you can just use the JSON APIs directly