在H2上的NamedParameterJDBCTemplate上使用整数时，数据类型未知[SPRING-BOOT] [英] Unknown data type when using an integer over NamedParameterJDBCTemplate on H2 [SPRING-BOOT]

问题描述

我正在使用带有H2的内存数据库来测试Dao.我正在将一个int传递给带有地图的查询以执行它.此查询在Oracle SQL上运行正常，但在H2中未成功.

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

查询

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

当我尝试在H2上执行此查询时，它返回:

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

错误

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]

我尝试执行对查询中的int进行硬编码的查询(SYSDATE = 4)，并且可以正常工作，还尝试将原始int包装到Integer.valueOf(days)中，并使用MapSqlParameterSource指定哪种数据类型，但是没有都有效.

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.

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

}

推荐答案

出现此异常是因为H2试图在编译时对语句进行类型检查，并且无法唯一地确定参数的类型:可能是日期，也可以是数字，或者其他.

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.

解决方法(在我提出的GitHub问题中提供)是要替换的

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

SYSDATE - ?

使用

SYSDATE - CAST(? AS INTEGER)

我已经检查了这一点，它在H2和Oracle上均可使用.

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

这篇关于在H2上的NamedParameterJDBCTemplate上使用整数时，数据类型未知[SPRING-BOOT]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！