ibatis参数设置的问题，困扰了很长时间了

[lz_tianyun]

主要问题是，查询的参数赋值总是出现问题，我怀疑是日期类型数据的错误，可是我在描述文件中无论指定什么样的类型似乎都有问题(Date,DateTime,Timestamp)，如果不指定类型也不行。请大家看看这个到底怎么回事？

类代码
public int getCountLessThanID(BigDecimal id,Date data)
{
HashMap temp = new HashMap();
temp.put("id", id);
temp.put("modifydate", data);
Object obj = getSqlMapClientTemplate().queryForObject(
"PLAN_FAMILY.getRecordsCountLesThanID", temp);
if (obj != null)
return (Integer) obj;
return 0;
}

ibatis描述文件：

<select id="getRecordsCountLesThanID" resultClass="java.lang.Integer" >
<![CDATA[

select count(t.ID) from PLAN_FAMILY t where t.ID <=#id:Decimal# and t.MODIFYDATE>=#modifydate:Date#
or exists (select p.ID from POP_POPULATE p where p.MODIFYDATE >=#modifydate:Date# and p.ID = t.POPID)

]]>
</select>


错误信息：


Exception in thread "timer" org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [72000]; error code [1013];
--- The error occurred in com/co/cpis/sqlmap/PLAN_FAMILY_SqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the PLAN_FAMILY.getRecordsCountLesThanID-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-01013: 用户请求取消当前的操作
; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/co/cpis/sqlmap/PLAN_FAMILY_SqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the PLAN_FAMILY.getRecordsCountLesThanID-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-01013: 用户请求取消当前的操作

Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/co/cpis/sqlmap/PLAN_FAMILY_SqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the PLAN_FAMILY.getRecordsCountLesThanID-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-01013: 用户请求取消当前的操作

at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:566)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:541)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
at org.springframework.orm.ibatis.SqlMapClientTemplate$1.doInSqlMapClient(SqlMapClientTemplate.java:243)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:193)
at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:241)
at com.co.cpis.dao.PlanFamilyDAOImpl.getCountLessThanID(PlanFamilyDAOImpl.java:308)
at com.co.cpis.scheduling.DataSynchronizer.loadPlanFamily2Target(DataSynchronizer.java:2055)
at com.co.cpis.scheduling.DataSynchronizer.execute(DataSynchronizer.java:77)
at com.co.cpis.scheduling.SynchronizationTimerTask.run(SynchronizationTimerTask.java:33)
at java.util.TimerThread.mainLoop(Unknown Source)
at java.util.TimerThread.run(Unknown Source)
Caused by: java.sql.SQLException: ORA-01013: 用户请求取消当前的操作

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1054)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:836)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1116)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:186)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
... 13 more

[lz_tianyun]

其实，最让人困扰的是有时候执行成功的，而有时候失败，这才是麻烦的事情

[老紫竹]

queryForObject("PLAN_FAMILY.getRecordsCountLesThanID"

设这样写吗？

queryForObject("getRecordsCountLesThanID"

我看上去更习惯写吧！

以上纯属猜测，我不会ibatis

[lz_tianyun]

这个问题是比较麻烦，我试验了所能用的各种方法，但是还是时好时坏。下面抓取部分statement错误：

2008-01-07 16:16:04,265 DEBUG - JakartaCommonsLoggingImpl.debug(27) | {pstm-100848} Executing Statement:                          select count(t.ID) from PLAN_FAMILY t where t.ID <=? and t.MODIFYDATE >=?        or exists (select p.ID from POP_POPULATE p where p.MODIFYDATE >=? and  p.ID = t.POPID)                   
2008-01-07 16:16:04,265 DEBUG - JakartaCommonsLoggingImpl.debug(27) | {pstm-100848} Parameters: [22652, 2008-01-04 09:45:23.0, 2008-01-04 09:45:23.0]
2008-01-07 16:16:04,265 DEBUG - JakartaCommonsLoggingImpl.debug(27) | {pstm-100848} Types: [java.math.BigDecimal, java.sql.Timestamp, java.sql.Timestamp]

很奇怪，java.sql.Timestamp这种类型我在程序中没有指定，但是，ibatis自动的转成这样了。但是我即使昨天把参数类型转成Timestamp，开始还好的，可是重起之后就over了。

[firefox_1983]

<select id="getRecordsCountLesThanID" parameter-class="java.util.HashMap" resultClass="java.lang.Integer" >
<![CDATA[

select count(t.ID) from PLAN_FAMILY t where t.ID <=#id# and t.MODIFYDATE> =#date#
or exists (select p.ID from POP_POPULATE p where p.MODIFYDATE > =#date# and p.ID = t.POPID)

]]>
</select>

[lz_tianyun]

加上 parameter-class="java.util.HashMap" 后结果还是一样。
这么说吧，网上有关解决这个问题的帖子看了好多，也按照很多方法去做了，但是依然是有问题。我很怀疑是环境原因造成的，可是也具体找到不到哪里的问题

[firefox_1983]

很简单呀，你直接把参组先在sql中写死看看,或者是把sql放到数据库客户端跑一下，看看sql是否正确