Implement @SQLType annotation to support custom mapping of @Param parameters to java.sql.SQLType

[mipo256]

As a result of the following discussion, I'm filing an issue into this repository.

Related: #2000

[mipo256]

@schauder

I can think of an adding the following annotation:

@Documented
@Target(ElementType.PARAMETER)
@Retention(RetentionPolicy.RUNTIME)
public @interface SqlType {

	/**
	 * Returns the {@code SQLType} name that represents a SQL data type.
	 *
	 * @return The name of this {@code SQLType}.
	 */
	String name();

	/**
	 * Returns the name of the vendor that supports this data type. The value
	 * returned typically is the package name for this vendor.
	 *
	 * @return The name of the vendor for this data type
	 */
	String vendor();

	/**
	 * Returns the vendor specific type number for the data type.
	 *
	 * @return An Integer representing the vendor specific data type
	 */
	int vendorTypeNumber();
}

to be later used like:

@Query(value = "SELECT ... WHERE my_field = :myField")
Optional<MyEntity> find(@Param("myField") @SqlType(name = "Uint64", vendor="YDB", vendorTypeNumber = 10214) Integer myField);

The main topic of discussion is about the attributes of the annotation. The attributes of the annotation reflect the interface SQLType. Using an enum JDBCType will kind of work here, but the main pain point is to support mapping of dialect specific types, such as in YDB Uint64. This is not included in the JDBCType so I do not think relying on this enum makes sense.

Using a simple int to represent the numeric value of the vendor type is not sufficient, so the only optimal solution I can see here is to reflect the java.sql.SQLType - that would integrate seamlessly and allow for customization.

CC: @mp911de

[mp911de]

It goes into the right direction. I would keep it really simple, adding value() with an alias for name() and defaulting to the initial values. @SqlType should be really a hint first and then, I think we need some resolver at the JdbcDialect level that takes the annotation information in the form of a value object (that also holds on to the source AnnotatedElement) and the resolver can either resolve the value on its own or within the context of a connection (that is likely the more difficult part).

[mipo256]

So, basically, something like that:

public interface JdbcDialect extends Dialect {
    

    Optional<java.sql.SQLType> resolveSqlType(AnnotatedElement element);
}

Where the AnnotatedElement is likely the java.lang.reflect.Parameter. And then the Spring Data would ask the appropriate JdbcDialect for the custom SQLType mapping. We might also wrap the SQLType with java.util.Optional, I think, to signal that the dialect does not know the exact type and want to rely on Spring Data mapping infrastructure.

By the way, I'm not sure in this case the @SqlType annotation is required as such, since the custom dialects for Spring Data would be able to introduce their own annotations and place the resolution logic inside resolveSqlType.

CC: @mp911de @schauder

[mp911de]

I prefer a dedicated interface as in SqlTypeResolver. I am not sure that AnnotatedElement is a good parameter type as we might want to employ type resolution for other use-cases as well, therefore a value object is better suited.

[mipo256]

So, basically, something like this:

public interface JdbcDialect extends Dialect {
    
    SqlTypeResolver getSqlTypeResolver();
}

public interface SqlTypeResolver {

    Optional<java.sql.SQLType> resolveSqlType(SomeValueType element); 
} 

The exact shape of the 'SomeValueType' is not yet clear, but we can figure it out during implementation and following discussion during review. What are your thoughts on this, Mark @mp911de ?

If we agree on the general shape, then I can start working on it.

[mp911de]

Remove the Optional for @Nullable, and see #2020 (comment) for guidance. The rest looks proper.

[mipo256]

@mp911de, just as a side note, Hibernate 6 introduced @JdbcTypeCode annotation, which basiaclly serves the similar purpose of letting users specify the sql type explictely. However, it is defined like this:

@Target({METHOD, FIELD, ANNOTATION_TYPE})
@Inherited
@Retention(RUNTIME)
public @interface JdbcTypeCode {
	/**
	 * The standard {@linkplain java.sql.Types JDBC Types} code or a custom code.
	 * This ultimately decides which {@link JdbcType} is used to "understand" the
	 * described SQL data type.
	 */
	int value();
}

So, basically, Hibernate only allows for customization of type's vendor number (such as those in java.sql.Types).

I'm not saying that we should copy this solution, I just found it useful to attach information this to the issue history, and to get a general clue on how this problem is sovled in other Java ORM technologies.

[mp911de]

I don't like the int value() approach because it limits usage to a numeric code only without the possibility to adapt to other kinds of resolution information. @JdbcType on the other hand is glued to too much complexity that might not be necessary in all scenarios.

Looking at Postgres and enum types, enum types get an individual oid at the time they are created. Forcing code to reflect a runtime configuration state in the domain model asks for trouble.

[mipo256]

I don't like the int value() approach because it limits usage to a numeric code only without the possibility to adapt to other kinds of resolution information.

I agree, that is why I have included into@SqlType the vendor's type name in the PR. The AbstractSqlParameterSource that we have in the spring-jdbc module only includes the name and the vendor code. So I thnk the type name and type int code is sufficient.

@JdbcType on the other hand is glued to too much complexity that might not be necessary in all scenarios.

I agree. You can look up the details of the implementation in the PR associated with this issue