Selecting identical named columns in jOOQ

Question

Im currently using jOOQ to build my SQL (with code generation via the mvn plugin).

Executing the created query is not done by jOOQ though (Using vert.X SqlClient for that).

Lets say I want to select all columns of two tables which share some identical column names. E.g. UserAccount(id,name,...) and Product(id,name,...). When executing the following code

val userTable = USER_ACCOUNT.`as`("u")
val productTable = PRODUCT.`as`("p") 

create().select().from(userTable).join(productTable).on(userTable.ID.eq(productTable.AUTHOR_ID))

the build method query.getSQL(ParamType.NAMED) returns me a query like

SELECT "u"."id", "u"."name", ..., "p"."id", "p"."name", ... FROM ...

The problem here is, the resultset will contain the column id and name twice without the prefix "u." or "p.", so I can't map/parse it correctly.

Is there a way how I can say to jOOQ to alias these columns like the following without any further manual efforts ?

SELECT "u"."id" AS "u.id", "u"."name" AS "u.name", ..., "p"."id" AS "p.id", "p"."name" AS "p.name" ...

Im using the holy Postgres Database :)

EDIT: Current approach would be sth like

val productFields = productTable.fields().map { it.`as`(name("p.${it.name}")) }
val userFields = userTable.fields().map { it.`as`(name("p.${it.name}")) }

create().select(productFields,userFields,...)...

This feels really hacky though

Answer 1

How to correctly dereference tables from records

You should always use the column references that you passed to the query to dereference values from records in your result. If you didn't pass column references explicitly, then the ones from your generated table via Table.fields() are used.

In your code, that would correspond to:

userTable.NAME
productTable.NAME

So, in a resulting record, do this:

val rec = ...
rec[userTable.NAME]
rec[productTable.NAME]

Using Record.into(Table)

Since you seem to be projecting all the columns (do you really need all of them?) to the generated POJO classes, you can still do this intermediary step if you want:

val rec = ...
val userAccount: UserAccount = rec.into(userTable).into(UserAccount::class.java)
val product: Product = rec.into(productTable).into(Product::class.java)

Because the generated table has all the necessary meta data, it can decide which columns belong to it, and which ones don't. The POJO doesn't have this meta information, which is why it can't disambiguate the duplicate column names.

Using nested records

You can always use nested records directly in SQL as well in order to produce one of these 2 types:

• Record2<Record[N], Record[N]> (e.g. using DSL.row(table.fields()))
• Record2<UserAccountRecord, ProductRecord> (e.g using DSL.row(table.fields()).mapping(...), or starting from jOOQ 3.17 directly using a Table<R> as a SelectField<R>)

The second jOOQ 3.17 solution would look like this:

// Using an implicit join here, for convenience
create().select(productTable.userAccount(), productTable)
        .from(productTable)
        .fetch();

The above is using implicit joins, for additional convenience

Auto aliasing all columns

There are a ton of flavours that users could like to have when "auto-aliasing" columns in SQL. Any solution offered by jOOQ would be no better than the one you've already found, so if you still want to auto-alias all columns, then just do what you did.

But usually, the desire to auto-alias is a derived feature request from a misunderstanding of what's the best approch to do something in jOOQ (see above options), so ideally, you don't follow down the auto-aliasing road.