DB Client (PostgreSQL) Mapper failure on Boolean mapping

Isax03 commented 4 months ago

Environment Details

Helidon Version: 4.0.10
Helidon SE
JDK version: 21
OS: Windows and Ubuntu

Problem Description

In the implementation of a DbMapper for an entity following the Entity Pattern, we encountered an issue when mapping boolean values from a PostgreSQL database. Specifically, the error arises when attempting to map PostgreSQL boolean types to java.lang.Boolean in Kotlin. The mapper is designed to extract various columns from a database row, including several boolean fields.

Here is a simplified version of the code causing the issue:

class EntityMapper : DbMapper<Entity> {
    override fun read(row: DbRow): Entity {
        val field1 = row.column("field1").get(UUID::class.java)
        val field2 = row.column("field2").get(String::class.java)
        val booleanField = row.column("boolean_field").get(Boolean::class.java)
        // Other fields...

        return Entity(
            field1 = field1,
            field2 = field2,
            booleanField = booleanField,
            // Other fields...
        )
    }

    // Other methods...
}

The relevant portion of the error message is:

io.helidon.http.RequestException: Failed to map class java.lang.Boolean to boolean: Failed to find mapper. Qualifiers: dbclient, source of class 'java.lang.Boolean'
...
Caused by: io.helidon.common.mapper.MapperException: Failed to map class java.lang.Boolean to boolean: Failed to find mapper.
...

This issue consistently occurs when mapping boolean fields from the database. The expectation was that the boolean fields in the database, represented as boolean in PostgreSQL, would be seamlessly mapped to java.lang.Boolean in the application layer. However, the error indicates that the mapping infrastructure does not have a predefined mapper for this conversion, resulting in a MapperException.

Steps to Reproduce

Implement a DbMapper for an entity, including boolean fields, using Helidon DB Client.
Map the PostgreSQL boolean type fields to java.lang.Boolean in Kotlin.
Attempt to retrieve data from the database using the mapper.
Observe the error in the server logs indicating a failed mapping operation due to the lack of a suitable mapper for java.lang.Boolean.

This issue is reproducible consistently under the described conditions.

Questions

Is there a specific configuration or additional setup required in Helidon to handle mapping between PostgreSQL boolean types and java.lang.Boolean?
Does Helidon provide a built-in mapper for java.lang.Boolean that might not be documented or obvious in the current setup?
What is the recommended approach for handling boolean mappings in this scenario with Helidon DB Client?
Are there any known workarounds or custom mapper implementations that could resolve this issue?
Could this issue be related to specific versions of Helidon or other dependencies? If so, which versions should we use to avoid this problem?

Tomas-Kraus commented 1 week ago

This looks like missing mappers. We did not provide set of mappers for primitive types in DB client. They are implemented in tests, but maybe we should expose them as part of default mappers.

Tomas-Kraus commented 1 week ago

Unfortunately there is always some grey area in number conversion which needs to be defined somehow.

for target primitive types, null value as source will throw an NPE
number to boolean conversion uses value > 0 rule to return true. Values null, 0 and negative values are considered as false
boolean to number conversions uses simple mapping: true -> 1 and false -> 0

Tomas-Kraus commented 1 week ago

Please can you verify that https://github.com/Tomas-Kraus/helidon/tree/issue-9062 build fixes your issue?

Isax03 commented 1 week ago

I'm sorry but these days I can't. Btw, you can easily reproduce the bug.

Tomas-Kraus commented 1 week ago

I never touched Kotlin so I can work only on Java side and verify that test implemented in Java works. This was done as part of the PR:

Pokemon entity in integration tests contains primitive boolean attribute. Statements and mapping tests were updated to work with it.
tests are being executed with MySQL, PostgreSQL, and Oracle 23 databases and they passed.

private static final class PokemonMapper implements DbMapper<Pokemon> {

    @Override
    public Pokemon read(DbRow row) {
        return new Pokemon(row.column("id").get(Integer.class),
                           row.column("name").get(String.class),
                           row.column("healthy").get(Boolean.class));
    }

    ...
}

public record Pokemon(int id, String name, boolean healthy, List<Type> types) {

    public Pokemon(int id, String name, boolean healthy, Type... types) {
        this(id, name, healthy, List.of(types));
    }

    public Pokemon(int id, String name, Type... types) {
        this(id, name, true, types);
    }

    ...
}

But as you can see, it's the same as your Kotlin code.

I did not see any problem with the mappers I added. Also Postgres test was working fine even without boolean mappers in Java environment. The only database that required mappers was Oracle.

So it sounds quite strange, that custom mapper did not work. Mapper framework is being used for a long time and should be reliable. Were you able to reproduce it in pure Java environment too?

helidon-io / helidon