1

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)
);
Mankind1023
  • 7,198
  • 16
  • 56
  • 86

1 Answers1

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • 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? – Mankind1023 Mar 30 '21 at 18:34
  • @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: https://github.com/jOOQ/jOOQ/issues/11054 – Lukas Eder Mar 31 '21 at 07:06
  • 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. – Mankind1023 Apr 04 '21 at 18:44
  • [The blog post I've linked](https://blog.jooq.org/2020/10/09/nesting-collections-with-jooq-3-14s-sql-xml-or-sql-json-support/) didn't help? What's the source of confusion there? You can find other examples on stack overflow, e.g. https://stackoverflow.com/a/42680565/521799 – Lukas Eder Apr 05 '21 at 08:26