Boolean values are not mapping properly when using multiset in JOOQ

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;
}

CategoryAttribute class :

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;
}

The query with needs to be executed is

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);

The output for the query is

{
    "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"
        }
    ]
}

Database create statements:

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`)
)

Code generation configuration in 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>

I am getting all the boolean fields as null while they are not null in Actual data. How to map the boolean fields properly so that there will be no null values in boolean fields when using multiset

Answer

That’s a bug, which should be fixed, soon, in jOOQ 3.17.0, 3.16.5, 3.15.9: https://github.com/jOOQ/jOOQ/issues/13089

It is related to jOOQ not serialising BIT(1) values correctly when using the SQL/JSON MULTISET emulation. Things would look better if you had used TINYINT instead of BIT(1) to encode your boolean values. Note also that future versions of jOOQ might fix their understanding of the BIT type, which is more of a java.lang.BitSet than a java.lang.Boolean: https://github.com/jOOQ/jOOQ/issues/10323

While you cannot currently influence the SQL/JSON emulation, you could cast your BIT columns to BOOLEAN explicitly, as such: CATEGORY_ATTRIBUTE.NULLABLE.cast(SQLDataType.BOOLEAN)