java - Named and typesafe correlated subquery
问题描述
I'm using JOOQ v3.14.3 and trying to do a simple select with a correlated subquery. In abstract ways:
Field<?> subquery = DSL.select(MY_TABLE.TIMESTAMP_FIELD)
.from(MY_TABLE)
.where(...)
.asField("timestamp");
var mainQuery = create.select(OTHER_TABLE.SOME_FIELD, subquery)....;
My issue here is, that the subquery is guaranteed to return OffsetDateTime
values, but in the resulting records I can only access those values using the plain String "timestamp"
and have to do the manual casting like in
OffsetDateTime timestamp = (OffsetDateTime) record.get("timestamp");
One can create a type-safe Field
like this:
Field<OffsetDateTime> subquery = DSL.field(DSL.select(MY_TABLE.TIMESTAMP_FIELD)
.from(MY_TABLE)
.where(...));
But this field doesn't seem to have a name so I can not easily retrieve it from the resulting Record. My subquery isn't constant so I have to recreate it everytime and can not simply keep a reference to this Field
.
I'd imagine something like this: Define a custom named field, then use it in subquery and in Record access
public static final Field<OffsetDateTime> CUSTOM_TIMESTAMP_FIELD = Whatever.customField("timestamp", OffsetDateTime.class);
// ...
Field<OffsetDateTime> subquery = DSL.select(MY_TABLE.TIMESTAMP_FIELD)
.from(MY_TABLE)
.where(...)
.asField(CUSTOM_TIMESTAMP_FIELD);
// ...
OffsetDateTime timestamp = record.get(CUSTOM_TIMESTAMP_FIELD);
What whould be the best/nicest way to solve this?
解决方案
Passing around the subquery reference
But this field doesn't seem to have a name so I can not easily retrieve it from the resulting Record.
Have you tried it? Because you can just use the subquery
reference to retrieve the value from the record:
OffsetDateTime value = record.get(subquery)
Of course, you'd probably rather be aliasing your subquery like this:
Field<OffsetDateTime> subquery = DSL.field(DSL.select(MY_TABLE.TIMESTAMP_FIELD)
.from(MY_TABLE)
.where(...))
.as("subquery");
But then, you'd still use that reference to fetch the desired value.
Using a global variable for the subquery name
The approach you were looking for can be achieved like this:
public static final Field<OffsetDateTime> CUSTOM_TIMESTAMP_FIELD =
DSL.field(DSL.name("timestamp"), OffsetDateTime.class);
You can now rename your subquery to it:
Field<OffsetDateTime> subquery = DSL.field(DSL.select(MY_TABLE.TIMESTAMP_FIELD)
.from(MY_TABLE)
.where(...))
.as(CUSTOM_TIMESTAMP_FIELD);
And then reuse that name in your record access:
OffsetDateTime value = record.get(CUSTOM_TIMESTAMP_FIELD)
推荐阅读
- kubernetes - 用证书加密的 Kubernetes 机密?
- php - Laravel 5.8 - 如何排序(orderBy)多重关系
- c# - Asp.net Jwt 令牌验证
- jar - 在 Jupyter notebook 中启动 Pyspark 后包括外部 jars
- memory - 在 Ncuts 实现中查找特征值而不计算距离矩阵
- json - 从 Coinmarketcap API 获取 Json 特定的变量数据
- python - 100k 行的限制
- swift - 构建包含 MapKit 的应用程序时找不到符号
- c# - CRLF 注入问题(使用 cookie)
- ruby - 如何在 Vim 上对齐 ruby 方法调用的元素