JOOQ - Join table using string name

Question

I have an app in the process of switching from jooq to jdbc, I created a table using jdbc but I need to edit some jooq queries to join it in, problem is that the auto generated jooq tables class does not recognize it, so is there a way to reference the table by string name?

Here is some sample code:

private final Business BUSINESS = Tables.BUSINESS; // table that jooq auto generated in jar file
....

Map<BusinessRecord, List<BusinessAddressRecord>> resultMap = dslContext
.select()
.from(BUSINESS.leftOuterJoin(BUSINESS_ADDRESS).on(BUSINESS.ID.eq(BUSINESS_ADDRESS.BUSINESS_ID)))
.where(BUSINESS.ID.equal(id))
.and(BUSINESS_ADDRESS.DEACTIVATED_AT.isNull())
.fetchGroups(
    a -> a.into(BUSINESS),
    b -> b.into(BUSINESS_ADDRESS)
);

How would I join a 'connection' table here? There is no Tables.CONNECTION since it was created a different way.

There are a lot of queries so we were hoping to make the switch gradually as needed.

UPDATE: I tried adding the following, and the query runs, but the field I'm trying to get is always null, I'm guessing because the BusinessRecord table does not have the same fields as the DTO, so how can this be resolved?

Map<BusinessRecord, List<BusinessAddressRecord>> resultMap = dslContext
.select()
.from(BUSINESS)
.leftOuterJoin(BUSINESS_ADDRESS)
.on(BUSINESS.ID.eq(BUSINESS_ADDRESS.BUSINESS_ID))
.join("connection")
.on("CAST(connection.business_id as bigint) = business.id")
.where(BUSINESS.ID.equal(id))
.and(BUSINESS_ADDRESS.DEACTIVATED_AT.isNull())
.fetchGroups(
    a -> a.into(BUSINESS),
    b -> b.into(BUSINESS_ADDRESS)
);

Hi, I'm new to jooq so maybe I'm misunderstanding that question you linked, but it's talking about getting a table schema, and the answers also say to do that, it does not explain how I can join a string to a normal query. — Mankind1023
– Mankind1023, Commented Mar 29, 2021 at 18:29
What do you mean by " the auto generated jooq tables class does not recognize it" — Simon Martinelli
– Simon Martinelli, Commented Mar 30, 2021 at 6:01
jooq builds tables from the schemas, that's what used in the queries. — Mankind1023
– Mankind1023, Commented Mar 30, 2021 at 19:48

Lukas Eder · Accepted Answer · 2021-03-30 17:42:58Z

0

Nesting collections

You're currently running a to-many join query where for 1 BUSINESS you have many BUSINESS_ADDRESS entries. If you want to join another to-many relationship between that 1 BUSINESS and many CONNECTION entries, you'll get a cartesian product between BUSINESS_ADDRESS and CONNECTION, which is probably not what you want.

It seems that your desired output data structure looks like this:

class Business {
  // Business properties ...
  List<Address> addresses;
  List<Connection> connections;
}

With jOOQ, this kind of nesting of collection is best done directly with SQL either via arrays or via SQL/JSON or SQL/XML. See this blog post for details.

Note that this sort of thing will become much more difficult to achieve with JDBC directly.

Using table identifiers

You can mix tables from the code generator with tables that are not available to the code generator either using plain SQL templating, or using identifiers.

But once you no longer use generated code, jOOQ will no longer know in advance what columns your projection will produce. You're not specifying the projection explicitly, and thus select() will produce a SQL SELECT * query. Assuming that you really need all of these columns, the problem that you're currently having is:

You're not really mapping anything from the connection table. You're projecting its columns, but your fetchGroups() expression continues to reference only columns from the other two tables
When you mix generated code with plain SQL templates, there might be ambiguities between the columns of various tables, and jOOQ cannot resolve the mapping correctly anymore. This might also lead to wrong results.

So, in short: Know that you task isn't just simply adding a join. You might also have to update your projection (SELECT clause), and your mapping (fetchGroups()) expression. But I do recommend reviewing what the goal of this query is, and possibly revert to a nesting collections approach.

answered Mar 30, 2021 at 17:42

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mankind1023 Over a year ago

Thanks for the reply, in this case (at least for the foreseeable future), I will only get 1 connection, is there a way to easily add it as an extra field to the business fields for example?

Lukas Eder Over a year ago

@Mankind1023: You could do that with a view and use the code generated for the view instead of the table. A future version of jOOQ might even support synthetic views: github.com/jOOQ/jOOQ/issues/11054

Mankind1023 Over a year ago

I checked those links you provided, but I didn't fully understand them (I just started using jooq), do you happen to have a good code example where we get an object with multiple lists in it? Ideally without manually iterating over everything (something like the fetchInto), if that makes sense.

Lukas Eder Over a year ago

The blog post I've linked didn't help? What's the source of confusion there? You can find other examples on stack overflow, e.g. stackoverflow.com/a/42680565/521799

Collectives™ on Stack Overflow

JOOQ - Join table using string name

1 Answer 1

Nesting collections

Using table identifiers

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Nesting collections

Using table identifiers

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related