在PostgreSQL数据库中使用jOOQ插入java SQL枚举
我正在尝试使用jOOQ在数据库中插入SQL枚举
使用Liquibase设置数据库迁移,如:
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd">
<changeSet id="202105031559" author="pk">
<sql>CREATE TYPE my_type AS ENUM ('TYPE_A', 'TYPE_B')</sql>
<createTable tableName="a">
<column name="type" type="my_type" defaultValue='TYPE_A'>
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
</databaseChangeLog>
迁移中的默认枚举值按预期工作
正在尝试使用jOOQ插入枚举:
var result = dslContext
.insertInto(A,
A.TYPE
)
.values(
MyType.TYPE_B
)
.execute();
触发错误:
org.postgresql.util.PSQLException: ERROR: column "type" is of type my_type but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
我的jOOQ配置如下所示:
@Configuration
public class JooqConfig {
private final DataSource dataSource;
public JooqConfig(DataSource dataSource) {
this.dataSource = dataSource;
}
@Bean
public DataSourceConnectionProvider connectionProvider() {
return new DataSourceConnectionProvider(new TransactionAwareDataSourceProxy(dataSource));
}
@Bean
public DefaultDSLContext dsl() {
Settings settings = new Settings()
.withRenderNameCase(RenderNameCase.LOWER)
.withRenderQuotedNames(RenderQuotedNames.NEVER)
.withExecuteLogging(true);
return new DefaultDSLContext(connectionProvider(), SQLDialect.POSTGRES, settings);
}
}
我的Gradle设置:
jooq {
version.set(dependencyManagement.importedProperties["jooq.version"])
edition.set(nu.studer.gradle.jooq.JooqEdition.OSS)
configurations {
create("main") {
jooqConfiguration.apply {
generator.apply {
database.apply {
name = "org.jooq.meta.extensions.liquibase.LiquibaseDatabase"
withProperties(
org.jooq.meta.jaxb.Property()
.withKey("scripts")
.withValue("src/main/resources/db/changelog/changelog-ddl.xml"),
org.jooq.meta.jaxb.Property()
.withKey("includeLiquibaseTables")
.withValue("false"),
org.jooq.meta.jaxb.Property()
.withKey("database.liquibaseSchemaName")
.withValue("public"),
org.jooq.meta.jaxb.Property()
.withKey("changeLogParameters.contexts")
.withValue("!test"),
)
}
}
}
}
}
}
有人知道如何用jOOQ插入enum吗
# 1 楼答案
从jOOQ 3.15开始,
org.jooq.meta.extensions.liquibase.LiquibaseDatabase
将DDL语句转换为H2,并在内存H2数据库上模拟迁移。这意味着您不能使用特定于供应商的PostgreSQL功能(H2支持枚举,但它们有许多缺陷)相反,推荐的方法是使用testcontainers和实际的PostgreSQL数据库来生成代码。关于如何做到这一点的一些想法记录在本期:https://github.com/jOOQ/jOOQ/issues/6551或本例:https://github.com/jOOQ/jOOQ/tree/main/jOOQ-examples/jOOQ-testcontainers-example