JDBCType BIT for boolean classes

[kurtymckurt]

The mapping of Java classes to JDBCType is currently static.
We should provide a way to customize that mapping, possibly via the @Column annotation and allow for a customization by different Dialect implementations.

Original issue, as raised by @kurtymckurt

I'm bringing this as curiosity because i'm not entirely sure if its an issue. I was recently using a JDBC driver for a proprietary system and their driver does not support the BIT JDBC type. Therefore, when using boolean fields, it would fail. It made me look into it and noticed that your mappings of Java classes to JDBC Type had BIT for boolean/Boolean as you can see here:

spring-data-relational/spring-data-jdbc/src/main/java/org/springframework/data/jdbc/support/JdbcUtil.java

Line 77 in 967f0f9

sqlTypeMappings.put(Boolean.class, JDBCType.BIT);

However, the Spring framework JDBC core library seems to map Boolean/boolean to Types.BOOLEAN. You can see here:
https://github.com/spring-projects/spring-framework/blob/main/spring-jdbc/src/main/java/org/springframework/jdbc/core/StatementCreatorUtils.java#L92

One obvious solution is to ask the proprietary company to support BIT type. I am curious if the BIT for boolean/Boolean deviation was purposeful. As a work around, i'm currently using reflection to remap the types as its a small service and the impact of this change seems rather low. Am I missing any other impact from doing this?

    Class<?> clazz = JdbcUtil.class;
    Field field = clazz.getDeclaredField("sqlTypeMappings");
    field.setAccessible(true);
    Map<Class<?>, SQLType> map = (Map<Class<?>, SQLType>) field.get(null);
    map.put(Boolean.class, JDBCType.BOOLEAN);
    map.put(boolean.class, JDBCType.BOOLEAN);

[mp911de]

Type mapping is a concern on its own as there is a common set that applies to most databases. However, things deviate in the details as some databases do not have boolean types or bit types, or these would simply suggest using a specific type to map a certain Java type.

JdbcUtil is a static utility that doesn't allow for external customization.

Zooming out, we derive column types based either on their Java type or, as an alternative, on a registered custom converter that can return JdbcValue holding a SQLType.

You could also register a converter for your Boolean data types.

Generally, we currently do not support type hints on a per-property level (something like @Column(type = …)). Also, it would make sense to refine our typing information system for extensibility and consider a bit of the type mapping in our dialects.

Am I missing any other impact from doing this?

All boolean types will map into BIT. Assuming you're using the same database for all entities, that should be fine.

[kurtymckurt]

That sounds good, thanks! A converter definitely works for me. It would be nice at a property level, but I'm glad i had a solution outside of reflection!

[schauder]

#746 is related. It asks for a way to control the JDBCType to be used for method parameters.

[ryanrupp]

Note in this use case because the mapping is BIT for boolean values, Spring JDBC Template for setting arguments on a PreparedStatement ends up falling back to setObject here instead of the more specific setBoolean. This makes the support a bit variable depending on the JDBC driver implementation of setObject. It's unclear if maybe Spring JDBC Template should be modified to try to detect this scenario to call setBoolean instead or if the type mappings here should change from BIT ==> BOOLEAN.

I.e. in theory Spring JDBC template could do something like:

if (sqlType == BIT && inValue instanceof Boolean) {
     // use setBoolean
}

around here