Unknown data type when using an integer over NamedParameterJDBCTemplate on H2 [SPRING-BOOT]

Question

I'm testing a Dao with an In-Memory DB with H2. I'm passing an int to the query with a map to execute it. This query is working OK on Oracle SQL, but is not succeding in H2.

DAO

    @Override
public int deleteCancelled(int days) {
    final Map<String, Object> namedParameters = new HashMap<String, Object>();

    namedParameters.put(DAYS, days);
    namedParameters.put(STATUS, StatusEnum.CANCELLED.toString());

    int updated = this.namedParameterJdbcTemplate.update(Query.QUERIES.DELETE_CANCELLED, namedParameters);

    return updated;
}

QUERIES

      public static final String DELETE_CANCELLED = "DELETE FROM MY_TABLE "
      + "WHERE UPDATE_TS < SYSDATE - :days AND STATUS = :status";

When I try to execute this query on H2, it returns:

Error

org.springframework.jdbc.UncategorizedSQLException: 
PreparedStatementCallback; uncategorized SQLException for SQL [DELETE FROM 
MY_TABLE WHERE UPDATE_TS < SYSDATE - ? AND STATUS = ?]; SQL state 
[HY004]; error code [50004]; Unknown data type : "?"
Unknown data type: "?"; SQL statement:
DELETE FROM MY_TABLE WHERE UPDATE_TS < SYSDATE - ? AND STATUS = ? 
[50004-196]; nested exception is org.h2.jdbc.JdbcSQLException: Unknown data 
type : "?"
Unknown data type: "?"; SQL statement:
DELETE FROM MY_TABLE WHERE UPDATE_TS < SYSDATE - ? AND STATUS = ? 
[50004-196]

I tried to execute the query hardcoding the int in the query (SYSDATE = 4) and it worked, also tried to wrap primitive int into Integer.valueOf(days) and using MapSqlParameterSource to specify which data type is, but none of both worked.

Why is it not working? Anyone knows? Thanks in advance.

EDIT:

StatusEnum

public enum StatusEnum {

CANCELLED("Cancelled"), 
CONFIRMED("Confirmed"), 
PENDING("Pending"), 
SENT("Sent"), 


private final String text;

/**
 * @param text
 */
private StatusEnum(final String text) {
    this.text = text;
}

/* (non-Javadoc)
 * @see java.lang.Enum#toString()
 */
@Override
public String toString() {
    return text;
}

}

Answer 1

This exception appears to arise because H2 is trying to type-check the statement at compile time and can't uniquely determine the type of the parameter: it could be a date or it could be a number, or perhaps something else.

The workaround (provided in the GitHub issue I raised) is to replace

SYSDATE - ?

with

SYSDATE - CAST(? AS INTEGER)

I've checked this and it works on both H2 and Oracle.

Answer 2

I'm using namedQuery and this Worked for me. if it can help others.

CAST(:variable AS double)

It must be lowercase to respect hibernate types: https://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#mapping-types-basictypes

the solution of @Luke is working but I had to change to lowercase

Answer 3

For integer param (Spring data) there is workaround: embrace parameter and add 0:

@Query(value = "DELETE FROM my_table WHERE update_ts < SYSDATE - (:days + 0)")
void cleanup(@Param("days") Integer days);