在 JOOQ 中使用多重集时，布尔值未正确映射

Question

The ProductCategoryDto class ：

public class ProductCategoryDto {
private UUID id;
private String title;
private String description;
private Timestamp updatedAt;
private Timestamp createdAt;
private List<CategoryAttribute> attributes;
}

类别属性类 ：

public class CategoryAttribute {
private UUID id;
private String title;
private String attributeType;
private Boolean multiValued;
private Boolean nullable;
private Boolean isUnique;
private Integer indexValue;
private String description;
private Boolean isActive;
private UUID categoryId;
private Timestamp updatedAt;
private Timestamp createdAt;
}

需要执行的查询是

this.dslContext.select(
PRODUCT_CATEGORY.asterisk(),
multiset(
selectFrom(CATEGORY_ATTRIBUTE).where(CATEGORY_ATTRIBUTE.CATEGORY_ID.eq(PRODUCT_CATEGORY.ID))
).as("attributes")
)
.from(PRODUCT_CATEGORY)
.where(PRODUCT_CATEGORY.ID.eq(id))
.fetchOneInto(ProductCategoryDto.class);

查询的输出为

{
"id": "7e8d5560-62dc-48fc-b835-65642024fa0a",
"title": "Mobile",
"description": "It is an electronic device",
"updatedAt": "2022-02-12T08:29:04.923+00:00",
"createdAt": "2022-02-12T08:28:14.764+00:00",
"attributes": [
{
"id": "8bfd98ca-101f-43ef-8a53-770e933265b6",
"title": "RAM",
"attributeType": "8",
"multiValued": null,
"nullable": null,
"isUnique": null,
"indexValue": 1,
"description": "description",
"isActive": null,
"categoryId": "7e8d5560-62dc-48fc-b835-65642024fa0a",
"updatedAt": "2022-02-12T08:42:02.297+00:00",
"createdAt": "2022-02-12T08:42:02.297+00:00"
}
]
}

数据库创建语句：

Product Category:
CREATE TABLE `product_category` (
`id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` datetime(6) DEFAULT NULL,
`description` longtext COLLATE utf8mb4_unicode_ci,
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`updated_at` datetime(6) DEFAULT NULL,
PRIMARY KEY (`id`)
)
Attribute table:
CREATE TABLE `category_attribute` (
`id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
`attribute_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`category_id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` datetime(6) DEFAULT NULL,
`description` longtext COLLATE utf8mb4_unicode_ci,
`index_value` int NOT NULL,
`is_active` bit(1) NOT NULL DEFAULT b'1',
`is_unique` bit(1) NOT NULL DEFAULT b'0',
`multi_valued` bit(1) NOT NULL DEFAULT b'0',
`nullable` bit(1) NOT NULL DEFAULT b'0',
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`updated_at` datetime(6) DEFAULT NULL,
PRIMARY KEY (`id`)
)

pom.xml中的代码生成配置：

<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<executions>
<execution>
<id>jooq-codegen</id>
<phase>generate-sources</phase>
<goals>
<goal>generate</goal>
</goals>
<configuration>
<jdbc>
<driver>com.mysql.cj.jdbc.Driver</driver>
<!--suppress UnresolvedMavenProperty -->
<url>${spring.datasource.url}</url>
<!--suppress UnresolvedMavenProperty -->
<user>${spring.datasource.username}</user>
<!--suppress UnresolvedMavenProperty -->
<password>${spring.datasource.password}</password>
</jdbc>
<generator>
<database>
<name>org.jooq.meta.mysql.MySQLDatabase</name>
<includes>.*</includes>
<excludes/>
<!--suppress UnresolvedMavenProperty -->
<inputSchema>${database.name}</inputSchema>
<forcedTypes>
<forcedType>
<userType>java.util.UUID</userType>
<converter>com.project.ecommerce.converters.jooq.JooqUUIDConverter</converter>
<includeExpression>.*.ID</includeExpression>
<includeTypes>.*</includeTypes>
</forcedType>
</forcedTypes>
</database>
<generate>
<pojos>false</pojos>
<pojosEqualsAndHashCode>
true
</pojosEqualsAndHashCode>
<javaTimeTypes>true</javaTimeTypes>
<fluentSetters>true</fluentSetters>
</generate>
<target>
<packageName>
com.jooq.models
</packageName>
<directory>
target/generated-sources/jooq
</directory>
</target>
</generator>
</configuration>
</execution>
</executions>
</plugin>

我将所有布尔字段都显示为空，而它们在实际数据中不为空。 如何正确映射布尔字段，以便在使用多重集时布尔字段中没有空值

Answer 1

这是一个错误，应该很快就会在jOOQ 3.17.0,3.16.5,3.15.9中修复：https://github.com/jOOQ/jOOQ/issues/13089

这与 jOOQ 在使用 SQL/JSONMULTISET仿真时未正确序列化BIT(1)值有关。如果您使用TINYINT而不是BIT(1)来编码布尔值，情况会更好。另请注意，jOOQ 的未来版本可能会修复他们对BIT类型的理解，这更像是一种java.lang.BitSet而不是java.lang.Boolean：https://github.com/jOOQ/jOOQ/issues/10323

虽然您当前无法影响 SQL/JSON 模拟，但您可以将BIT列显式转换为BOOLEAN，如下所示：CATEGORY_ATTRIBUTE.NULLABLE.cast(SQLDataType.BOOLEAN)