Fix SQL Type Mismatch Error for Null Parameters in JDBC Parameter Binding

[bjcoombs]

Environment Details

• Helidon Version: 4.0.4
• Helidon SE
• JDK version: 21

---

Problem Description

When attempting to insert null values into a database using the Helidon JDBC client, specifying Types.VARCHAR as the SQL type for null parameters leads to SQL type mismatch errors for columns that are not of type VARCHAR. This issue arises in scenarios where the application logic requires inserting null into columns with types such as NUMERIC, DATE, or other non-VARCHAR types. The current behavior assumes null values are always meant for VARCHAR columns, which is not universally applicable and causes exceptions in a variety of common use cases.

The expected behavior is for the JDBC client to not make assumptions about the column type for null parameters, allowing the underlying JDBC driver and database to interpret the null based on the context of the column it is being inserted into. This behavior would prevent type mismatch errors and make the library more robust and flexible for various database operations.

This problem is consistently reproducible whenever null values are inserted into non-VARCHAR columns.

Steps to reproduce

1. Create a database table named test_null_handling with three columns: id INT PRIMARY KEY, text_col VARCHAR(255), num_col NUMERIC.
2. Attempt to insert a record into the table with null values for text_col and num_col using Helidon DB Client's named parameters feature.
3. Query the table for the inserted record.

Sample Test Code

import io.helidon.config.Config;
import io.helidon.config.ConfigSources;
import io.helidon.dbclient.DbClient;
import io.helidon.dbclient.DbMapper;
import io.helidon.dbclient.DbRow;
import io.helidon.dbclient.spi.DbMapperProvider;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.testcontainers.containers.JdbcDatabaseContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.junit.jupiter.api.Assertions.fail;

@Testcontainers
public class HelidonJdbcStatementNullParameterHandlingTest {

    private static final String POSTRGRES_USER = "some_user";
    private static final String POSTRGRES_PWORD = "some_pword";
    private DbClient dbClient;

    @Container
    public static JdbcDatabaseContainer<?> postgreSQLContainer = new TimescalePostgisContainerProvider().newInstance("2.3.0-pg13")
            .withDatabaseName("ingest_db")
            .withUsername(POSTRGRES_USER)
            .withPassword(POSTRGRES_PWORD);


    @BeforeEach
    public void setupHelidonDbClient() {
        Config config = Config.builder()
                .sources(ConfigSources.create(Map.of(
                        "db.type", "jdbc",
                        "db.connection.url", postgreSQLContainer.getJdbcUrl(),
                        "db.connection.username", POSTRGRES_USER,
                        "db.connection.password", POSTRGRES_PWORD
                ))).build();

        dbClient = DbClient.builder(config.get("db"))
                .mapperProvider(new DbMapperProvider() {
                    @Override
                    public <T> Optional<DbMapper<T>> mapper(Class<T> type) {
                        //noinspection unchecked
                        return Optional.of((DbMapper<T>) new TestMapper());
                    }
                })
                .build();
        dbClient.execute().createDmlStatement("CREATE TABLE test_null_handling (id INT PRIMARY KEY, text_col VARCHAR(255), num_col NUMERIC)").execute();
    }
    @AfterEach
    public void tearDown() {
        dbClient.execute().createDmlStatement("DROP TABLE if exists test_null_handling").execute();
    }

    @Test
    public void testSetNullWithTypesNull() {
        TestNullHandling testNullHandling = new TestNullHandling(1, null, null);
        long execute = dbClient.execute().createInsert("INSERT INTO test_null_handling (id, text_col, num_col) VALUES (:id, :text_col, :num_col)")
                .namedParam(testNullHandling)
                .execute();
        assertEquals(1,execute);

        dbClient.execute().createQuery("SELECT * FROM test_null_handling WHERE id = 1")
                .execute()
                .findFirst()
                 .ifPresentOrElse(row ->
                                 assertTrue(true, "Inserted row found."),
                         () -> fail("Inserted row not found."));


    }

    private record TestNullHandling(Integer id,
                                    String txtCol,
                                    Integer numCol) {
    }
    private static class TestMapper implements DbMapper<TestNullHandling> {
        @Override
        public TestNullHandling read(DbRow row) {
            return new TestNullHandling(
                    row.column("id").getInt(),
                    row.column("text_col").getString(),
                    row.column("num_col").getInt());
        }

        @Override
        public Map<String, ?> toNamedParameters(TestNullHandling value) {
            Map<String, Object> namedParams = new HashMap<>();
            namedParams.put("id", value.id());
            namedParams.put("text_col", value.txtCol());
            namedParams.put("num_col", value.numCol());
            return namedParams;
        }

        @Override
        public List<?> toIndexedParameters(TestNullHandling value) {
            throw new UnsupportedOperationException();
        }
    }
}