java - SimpleJDBCTemplate 和日期参数在某些情况下会给出奇怪的结果

Question

我为我的一个名为 my_schema 的 Oracle 模式创建了一个函数，该函数称为 date2epoch:

create or replace function date2epoch(p_ora_date in date, p_offset in varchar2) return number is
  l_epoch_time number;
begin
  select floor((p_ora_date - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400 -
                                  to_number(substr(tz_offset(p_offset),1,3)) * 3600) ts into l_epoch_time from dual;
  return l_epoch_time;
end;

我还制作了一个名为 date_to_et_epoch 的函数:

create or replace function date_to_et_epoch(p_ora_date in date) return number is
begin
  return date2epoch(p_ora_date, 'Europe/Tallinn');
end;

如果我使用 SQLPlus 执行以下 SQL，它们都会起作用:

select my_schema.date_to_et_epoch(trunc(to_date('22-JAN-2014'), 'MM')) from dual;
select 1 from dual where my_schema.date_to_et_epoch(SYSDATE) > my_schema.date_to_et_epoch(trunc(to_date('22-JAN-2014'), 'MM'));

现在我创建一个 J2EE 应用程序并希望在该应用程序中执行这些 SQL。

第一个:

select my_schema.date_to_et_epoch(trunc(?, 'MM')) from dual

所以我基本上做的(这里有一点简化版本)是:

org.springframework.jdbc.core.namedparam.MapSqlParameterSource paramSource = new org.springframework.jdbc.core.namedparam.MapSqlParameterSource();
paramSource.addValue("mydate", new java.util.Date());
getSpringSimpleJDBCTemplate().query("select my_schema.date_to_et_epoch(trunc(:mydate, 'MM')) from dual", new MyRowMapper(), paramSource);

这很好用。

但是如果我尝试第二个:

org.springframework.jdbc.core.namedparam.MapSqlParameterSource paramSource = new org.springframework.jdbc.core.namedparam.MapSqlParameterSource();
paramSource.addValue("mydate", new java.util.Date());
getSpringSimpleJDBCTemplate().query("select 1 from dual where my_schema.date_to_et_epoch(SYSDATE) > my_schema.date_to_et_epoch(trunc(:mydate, 'MM'))", new MyRowMapper(), paramSource);

我遇到异常:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select 1 from dual where my_schema.date_to_et_epoch(SYSDATE) > my_schema.date_to_et_epoch(trunc(?, 'MM'))]; nested exception is java.sql.SQLException: ORA-06553: PLS-306: wrong number or types of arguments in call to 'DATE_TO_ET_EPOCH'.

我不明白这是为什么。我认为这与 Spring-s JDBC 模板或 Oracle 驱动程序处理将 java Date 对象映射为参数的方式与 SQL 中的 to_date 不同。但是，我在概念上是否做错了什么，或者我可以做些什么来解决这个问题？我更愿意在 Java 端而不是 SQL 端修复此问题，因为 SQL 是由第三方编写的，并且可能随时会发生变化。

Answer 1

正如 Pavel Horak 在问题下的评论中所建议的那样，在 Java 代码中显式添加参数数据类型挽救了这一天。

以各种方式工作的代码示例:

org.springframework.jdbc.core.namedparam.MapSqlParameterSource paramSource = new org.springframework.jdbc.core.namedparam.MapSqlParameterSource();
paramSource.addValue("mydate", new java.util.Date(), java.sql.Types.DATE);
getSpringSimpleJDBCTemplate().query("select 1 from dual where my_schema.date_to_et_epoch(SYSDATE) > my_schema.date_to_et_epoch(trunc(:mydate, 'MM'))", new MyRowMapper(), paramSource);