使用 jOOQ 从具有正确类型转换的值() 中选择插入

Question

我正在使用jOOQ将相当多的行插入到多对多关系的表中。代码有效，生成的SQL符合预期，我的问题是我希望jOOQ代码可以更简单。

我有一个简化的结构(所有内容都重命名了，大多数字段被删除，大多数约束被删除，这只是一个愚蠢但准确的结构示例)：

CREATE TABLE person (
person_id BIGSERIAL PRIMARY KEY,
person_name VARCHAR(64) NOT NULL UNIQUE
);
CREATE TABLE company (
company_id BIGSERIAL PRIMARY KEY,
company_name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE employment_contract (
company_id BIGINT NOT NULL REFERENCES company,
person_id BIGINT NOT NULL REFERENCES person,
PRIMARY KEY (company_id, person_id),
salary INT NOT NULL,
creation_date_time TIMESTAMP NOT NULL
);

我的插入码：

Table<Record4<String, String, Integer, Timestamp>> insertValues = values(
row(
cast(null, COMPANY.COMPANY_NAME),
cast(null, PERSON.PERSON_NAME),
cast(null, EMPLOYMENT_CONTRACT.SALARY),
cast(null, EMPLOYMENT_CONTRACT.CREATION_DATE_TIME)
)
).as("insert_values",
COMPANY.COMPANY_NAME.getName(),  -- these lines are bugging me
PERSON.PERSON_NAME.getName(),
EMPLOYMENT_CONTRACT.SALARY.getName(),
EMPLOYMENT_CONTRACT.CREATION_DATE_TIME.getName()
);
Insert<AffectedSubscriberRecord> insert = insertInto(EMPLOYMENT_CONTRACT)
.columns(EMPLOYMENT_CONTRACT.COMPANY_ID,
EMPLOYMENT_CONTRACT.PERSON_ID,
EMPLOYMENT_CONTRACT.SALARY,
EMPLOYMENT_CONTRACT.CREATION_DATE_TIME
)
.select(
select(
COMPANY.COMPANY_ID,
PERSON.PERSON_ID,
insertValues.field(EMPLOYMENT_CONTRACT.SALARY),
insertValues.field(EMPLOYMENT_CONTRACT.CREATION_DATE_TIME)
)
.from(insertValues)
.join(COMPANY).using(COMPANY.COMPANY_NAME)
.join(PERSON).using(PERSON.PERSON_NAME)
);

然后我将所有行绑定到context.batch(insert)并执行该操作。我确信person和company引用的键已经存在，并且原始代码也解决了重复项，我们在这里不需要关心这些事情。

让我烦恼的是insertValues表 - 我需要在容易出错的复制粘贴中指定列类型和名称两次，使用.getName()调用，这些调用会掩盖整个代码并且很容易被错误地交换。我尝试了什么：

Table<Record4<String, String, Integer, Timestamp>> insertValues = values(
row( (String)null, (String)null, (Integer)null, (Timestamp)null )
).as("insert_values",
COMPANY.COMPANY_NAME.getName(),
PERSON.PERSON_NAME.getName(),
EMPLOYMENT_CONTRACT.SALARY.getName(),
EMPLOYMENT_CONTRACT.CREATION_DATE_TIME.getName()
);

这显然是行不通的，jOOQ 和 Postgres 都不知道插入的类型，DB 猜测 varchar 并失败。我们需要 jOOQ 至少为查询中的第一行生成类型转换。再试一次：

Table<Record4<String, String, Integer, Timestamp>> insertValues = values(
row( COMPANY.COMPANY_NAME, PERSON.PERSON_NAME, EMPLOYMENT_CONTRACT.SALARY, EMPLOYMENT_CONTRACT.CREATION_DATE_TIME )
).as("insert_values");

这将是炸弹。JOOQ 以这种方式知道正确的类型，并且可以为我生成转换，所有代码重复都消失了，事情是安全的。但是，这也失败了。JOOQ 不明白我给它一排满了空值。

有没有办法在没有不干净的.getName()调用的情况下实现相同(或等效)的结果查询，直接将字段传递到某个地方？

Answer 1

所以 jOOQ 中的values()给你一个表构造函数。 默认情况下，select()按原样为您提供所有列，这意味着 jOOQ 将定义为Field<object>

我认为思考这个问题的方法是，jOOQ在这一点上是认识到两件事。 您可以尝试在输出中定义字段，也可以接受对象并尝试在返回时处理类型大小写。 我更喜欢输出中的字段。

这里要考虑的一个重要方面是，对于给定列，行必须具有相同的类型，但在某些情况下，SQL 中的隐式强制转换可能会与 jOOQ 中的隐式转换混淆。 例如，如果在第一行中插入数值，在第二行中插入整数，则该整数将隐式转换为数字。 为选择定义字段可避免此问题。 因此，就问题而言，我将单独定义字段，特别是在"从值中选择"部分中定义字段。 这应该可以解决这个问题。 使用VALUES()，您已经独自一人，jOOQ 无法安全地推断类型。

因此，您的代码应该变为：

Table<Record4<String, String, Integer, Timestamp>> insertValues = values(
row(
cast(null, COMPANY.COMPANY_NAME),
cast(null, PERSON.PERSON_NAME),
cast(null, EMPLOYMENT_CONTRACT.SALARY),
cast(null, EMPLOYMENT_CONTRACT.CREATION_DATE_TIME)
)
).as("insert_values", "company_name", "person_name", "salary", "creation_time");
Field<String> ivCompanyNmae = field("insert_values.company_name". Stirng.class);
Field<Integer> ivSalary = field("insert_values.salary", Integer.class);
...
Insert<AffectedSubscriberRecord> insert = insertInto(EMPLOYMENT_CONTRACT)
.columns(EMPLOYMENT_CONTRACT.COMPANY_ID,
EMPLOYMENT_CONTRACT.PERSON_ID,
EMPLOYMENT_CONTRACT.SALARY,
EMPLOYMENT_CONTRACT.CREATION_DATE_TIME
)
.select(
select(
COMPANY.COMPANY_ID,
PERSON.PERSON_ID,
ivSalary,
ivCreatedTime
)
.from(insertValues)
.join(COMPANY).using(COMPANY.COMPANY_NAME)
.join(PERSON).using(PERSON.PERSON_NAME)
);

正是在这一点上，jOOQ 生成了强制转换。未知数可以成为弓箭手，但随后将被明确正确施放。