Incorrect MapParameterSource construction in @Query methods with SPEL

[mipo256]

I've created a simple, reproducible example to demonstrate the problem. Long store short, we have the following aggregate, repository and test, the database dialect is PostgreSQL:

enum class EnumClass {
  ACTIVE,
  DELETED;
}

@Table
class SimpleEnumClass( // it is a kotlin class, does not matter really in this case
  @Id
  var id: UUID,
  val enumClass: EnumClass
) { }

interface SimpleEnumClassRepository : CrudRepository<SimpleEnumClass, Long> {

  // language=sql
  @Modifying
  @Query(value = """
    INSERT INTO simple_enum_class(id, enum_class) 
    VALUES(:#{#simpleEnumClass.id}, :#{#simpleEnumClass.enumClass})
    """)
  fun saveCustom(@Param("simpleEnumClass") simpleEnumClass: SimpleEnumClass)
}

@Sql(statements = """
    CREATE TABLE simple_enum_class(
      id UUID PRIMARY KEY,
      enum_class TEXT
    );
    """)
void test() {
  repository.saveCustom(new SimpleEnumClass(UUID.randomUUID(), EnumClass.ACTIVE));
}

And this test method fails with an error:

Caused by: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of org.example.bug_in_enums_classes.EnumClass. Use setObject() with an explicit Types value to specify the type to use.
	at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1050)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java)
	at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:453)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:247)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:163)
	at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:287)
	at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:245)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:656)

I've conducted first, brief analysis of why it happens, and I guess I already found a problem. It is in our SPEL processing logic, in StringBasedJdbcQuery#evaluateExpressions.

We add parameters into the MapSqlParameterSource, but we do not specify the SQL types of the evaluated parameters at all. This leads to the problem since StatementCreatorUtils cannot propagate the appropriate type into the driver, and it falls back to common setObject method, hence we get this exception.

Possible solution:

I think it would be a good idea to specify the sqlType for evaluated parameters, at least when we're sure. For instance, when we're working with primitive values, or Enum's, like in this case.

[mipo256]

Hey @schauder! Can you assign this one to me? Thanks!

[schauder]

Consider it yours. Although I keep it formally assigned to me, since we keep assignments to team members.