Ensure Helidon DB Client Named Parameter Handling Aligns with Provided Map Implementation Behaviour

[bjcoombs]

Environment Details

• Helidon Version: 4.0.x
• Helidon SE
• JDK version: 21
• OS: N/A
• Docker version (if applicable): N/A

Title: Enhance Helidon DB Client's Handling of Named Parameters to Align with Map Interface Contract

Description:

The Helidon DB Client's treatment of named parameters needs enhancement to align more closely with the Map interface contract regarding null handling. While immutable maps (e.g., created with Map.of()) cannot store null values or keys, they, like all map implementations, return null for non-existent keys. The current handling within the DB Client does not fully leverage this aspect of map behavior, especially concerning the flexibility offered by mutable map implementations such as HashMap.

Issue Detail:

The current implementation for preparing statements with named parameters does not distinguish between the absence of a key (which should result in a null value being used) and the map's capability to store null values. This oversight restricts the use of optional parameters in SQL queries, especially when utilizing maps that support null values.

Example Illustration:

A custom LocationMapper demonstrates the need for improved handling:

public class LocationMapper implements DbMapper<Location> {
    ...

    @Override
    public Map<String, ?> toNamedParameters(Location value) {
        Map<String, Object> params = new HashMap<>();
        // Expected to leverage HashMap's ability to handle null values for missing keys
        return params;
    }
}

public List<Location> getLocations(Location location) {
    String query = """
            SELECT *
            FROM location l
            WHERE (coalesce(:name, l.name) = l.name)
            """;
    return this.dbClient.execute()
            .createQuery(query)
            .namedParam(location)
            .execute()
            .map(dbRow -> dbRow.as(Location.class))
            .toList();
}

Current Implementation Concern:

private PreparedStatement prepareNamedStatement(String stmtName, String stmt, Map<String, Object> parameters) {
    ...
    for (String name : namesOrder) {
        if (!parameters.containsKey(name)) {
            // This behavior is restrictive and does not fully utilize the Map interface's contract.
            throw new DbClientException(namedStatementErrorMessage(namesOrder, parameters));
        }
        ...
    }
    ...
}

Proposed Enhancement:

Revise the DB Client to utilize null for absent keys in parameter maps, respecting the Map interface's contract. This change should apply universally, acknowledging that null is a valid return value for absent keys, while also respecting the immutability constraints of certain map implementations:

• For all maps: Treat absent keys as null, leveraging SQL's handling of null for optional parameters.
• For mutable maps (e.g., HashMap): Continue to allow the explicit setting of null values.
• For immutable maps (e.g., Map.of()): No change needed as these maps inherently comply by not storing null but still returning null for non-existent keys.

Conclusion:

By refining the named parameter handling to universally treat absent keys as null, the Helidon DB Client will better align with the Java Map interface contract, enhancing its flexibility and usability across a wider range of use cases. This adjustment ensures a more consistent and predictable developer experience when constructing dynamic SQL queries with optional parameters.

[Tomas-Kraus]

Introduced missing-map-parameters-as-null boolean argument of DbClient config:

• when true: missing named parameters values in the parameters Map are considered as null values
• when false (default): missing named parameters values in the parameters Map will cause an exception