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;
}

}

Aforetime

Post your StatusEnum. – Eschatology

Posted, but is not the problem, though. If I hardcode the int in the query and keep "Status" as a parameter, the query executes without a problem. So I think the problem is in the int... – Aforetime

What is the value of DAYS and STATUS ? – Accomplish

There are two litterals, DAYS = "days" and STATUS = "status" – Aforetime

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.

Misplay

It works!!! Thank you very much for your answer! I had reached the conclussion I had a problem with the type of the parameter, but I didn't know how to solve it! Thank you! – Aforetime

@Luke - Could you pls guide me here - #69164483? – Emcee

Answer 2

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);

Tailor

You saved me, however Luke Woodward suggestion not working for me – Emcee

Could you pls guide me here - #69164483? – Emcee

Answer 3

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

Epiphenomenon

Could you pls guide me here - #69164483? – Emcee

Answer 4

I used this code:

As an argument, a floating-point number (double type) was passed in sql query.

.... SYSDATE - CAST(? AS DOUBLE PRECISION);

The examples given here did not help solve the problem. I needed to use h2 in memory, in integration tests. Since I was checking the interaction with the database, and the work when working with the repository, the project used JdbcTemplate and native sql, so I had to find a solution that would work on Oracle and, when using integration tests, interacted with h2.

Conceptualize