我目前使用JOOQ构建我的SQL（通过MVN插件生成代码）。
jOOQ 并未执行创建的查询（使用Vert.x sqlclient为此）。
假设我想选择两个共享一些相同列名称的两个表的所有列。例如 useraccount（ id ， name ，...）和 product（ id ，名称，...）。执行以下代码时，
val userTable = USER_ACCOUNT.`as`("u")
val productTable = PRODUCT.`as`("p")
create().select().from(userTable).join(productTable).on(userTable.ID.eq(productTable.AUTHOR_ID))
构建方法 query.getSQL（paramtype.mamed）返回我的查询，例如
SELECT "u"."id", "u"."name", ..., "p"."id", "p"."name", ... FROM ...
这里的问题，结果集将包含列 id 和 名称 两次没有前缀“ u”。或“ p。”，所以我无法正确映射/解析。
有什么办法如何对Jooq说出像以下任何手动努力一样以下这些列来吸引这些列的方式？
SELECT "u"."id" AS "u.id", "u"."name" AS "u.name", ..., "p"."id" AS "p.id", "p"."name" AS "p.name" ...
我正在使用Holy Postgres数据库:)
编辑：当前的方法是STH，
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,...)...
虽然感觉真的很骇人听闻
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
发布评论
评论（1）
如何从记录中正确解释表
您应始终使用将您传递给查询的列引用到结果中的记录中的删除值。如果您没有明确通过列引用，则通过
table.fields（）。在您的代码中，这将与：
因此，在结果记录中，执行此操作：
使用
record.into（table），因为您似乎正在投影所有列（您真的需要所有这些列？）对于生成的POJO类，您仍然可以执行此中介步骤：
因为生成的表具有所有必要的元数据，因此它可以决定哪些列属于它，哪些列不属于它。 POJO没有此元信息，这就是为什么它不能歧义重复的列名。
使用嵌套记录，
您可以始终使用也直接在SQL中，以生产这两种类型之一：
record2＆lt; record [n]，record [n]＆gt;（例如使用dsl.Row（表） .fields（）））record2＆lt; userAccountrecord，productrecord＆gt;（例如使用dsl.Row（table.fields（））。映射（...），或直接从JOOQ 3.17开始使用table＆lt; r＆gt;作为selectfield＆lt; r＆gt;）第二个JOOQ 3.17解决方案看起来像这样：
上面是使用隐式连接的，以提供其他便利性
自动使所有列的列
叠，在SQL中的“自动化”列时，用户可以拥有大量的口味。 JOOQ提供的任何解决方案都不会比您已经找到的解决方案更好，因此，如果您仍然想自动化所有列，那么只需做您所做的事情即可。
但是通常，对自动化的渴望是误解在Jooq中做某事的最佳选择（请参见上文选项），这是一个派生的功能请求，因此理想情况下，您不会跟随自动化的道路。
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:
So, in a resulting record, do this:
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:
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. usingDSL.row(table.fields()))Record2<UserAccountRecord, ProductRecord>(e.g usingDSL.row(table.fields()).mapping(...), or starting from jOOQ 3.17 directly using aTable<R>as aSelectField<R>)The second jOOQ 3.17 solution would look like this:
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.