我现在的项目是Strust2+Spring+ MyBatis3.0,因为项目中有个需求需要调用存储过程,我在MyBatis的配置文件里这样写了<!-- 调用存储过程计算请假时长 -->
<parameterMap type="map" id="LeaveApplyDetailMap">
<parameter property="strEmployeeNo" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="beginDate" jdbcType="DATE" javaType="java.util.Date" mode="IN"/>
<parameter property="endDate" jdbcType="DATE" javaType="java.util.Date" mode="IN"/>
<parameter property="typeNo" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="resultCode" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
<parameter property="resultData" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
</parameterMap>
<!-- 检查请假日期是否有效 -->
<select id="LeaveCheck" parameterMap="LeaveApplyDetailMap">
CALL PKG_ATT.SP_Get_LeaveCheck(#{strEmployeeNo},#{beginDate},#{endDate},#{typeNo},#{resultCode},#{resultData})
</select>
这时在程序里调用时是这样写的:Map map = new HashMap<String, Object>();
map.put("strEmployeeNo",detail.getStrEmployeeNo());
map.put("beginDate", detail.getBeginDate());
map.put("endDate", detail.getEndDate());
map.put("typeNo", detail.getStrTypeNo());
map.put("resultCode", "0");
map.put("resultData", "0");
dao.get("LeaveCheck", map);程序调用时就报错的,
DEBUG - ==> Executing: CALL PKG_ATT.SP_Get_LeaveCheck(?,?,?,?,?,?)
DEBUG - ==> Parameters: 11050020(String), 2011-06-15 09:21:00.0(Timestamp), 2011-06-15 20:21:00.0(Timestamp), 1005(String), 0(String), 0(String)
INFO - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
INFO - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
DEBUG - xxx Connection Closed
INFO - [c3p0] Exceptions occurred while trying to close a PooledConnection's resources normally.
ERROR - mapperKeyId:【LeaveCheck】执行失败 ;错误原因:org.springframework.jdbc.UncategorizedSQLException:
### Error querying database. Cause: java.sql.SQLException: 无法从套接字读取更多的数据
### The error may involve cn.machi.oa.atte.dao.impl.LeaveApplyDAOImpl.LeaveApplyDetailMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: 无法从套接字读取更多的数据
; uncategorized SQLException for SQL []; SQL state [null]; error code [17410]; 无法从套接字读取更多的数据; nested exception is java.sql.SQLException: 无法从套接字读取更多的数据
org.springframework.jdbc.UncategorizedSQLException:
### Error querying database. Cause: java.sql.SQLException: 无法从套接字读取更多的数据
### The error may involve cn.machi.oa.atte.dao.impl.LeaveApplyDAOImpl.LeaveApplyDetailMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: 无法从套接字读取更多的数据
; uncategorized SQLException for SQL []; SQL state [null]; error code [17410]; 无法从套接字读取更多的数据; nested exception is java.sql.SQLException: 无法从套接字读取更多的数据
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:65)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:345)
at $Proxy0.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:158)
at cn.machi.framework.dao.impl.BaseDAOImpl.get(BaseDAOImpl.java:66)
at cn.machi.framework.dao.impl.BaseDAOImpl$$FastClassByCGLIB$$fd01e960.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:42)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:55)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at cn.machi.oa.atte.dao.impl.LeaveApplyDAOImpl$$EnhancerByCGLIB$$5c7d549c.get(<generated>)
at cn.machi.oa.atte.service.impl.LeaveApplyServiceImpl.computeTotalHour(LeaveApplyServiceImpl.java:313)
在网上找了很多类似的问题,都没能解决,不知何故,
这里说明一下,数据库是Oracle,连接池是c3p0
当调用没有返回值的存储过程时是正常的,有返回参数的都报这样的错。不知是我的写法不对还是jar包的问题。
求解!
<parameterMap type="map" id="LeaveApplyDetailMap">
<parameter property="strEmployeeNo" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="beginDate" jdbcType="DATE" javaType="java.util.Date" mode="IN"/>
<parameter property="endDate" jdbcType="DATE" javaType="java.util.Date" mode="IN"/>
<parameter property="typeNo" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="resultCode" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
<parameter property="resultData" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
</parameterMap>
<!-- 检查请假日期是否有效 -->
<select id="LeaveCheck" parameterMap="LeaveApplyDetailMap">
CALL PKG_ATT.SP_Get_LeaveCheck(#{strEmployeeNo},#{beginDate},#{endDate},#{typeNo},#{resultCode},#{resultData})
</select>
这时在程序里调用时是这样写的:Map map = new HashMap<String, Object>();
map.put("strEmployeeNo",detail.getStrEmployeeNo());
map.put("beginDate", detail.getBeginDate());
map.put("endDate", detail.getEndDate());
map.put("typeNo", detail.getStrTypeNo());
map.put("resultCode", "0");
map.put("resultData", "0");
dao.get("LeaveCheck", map);程序调用时就报错的,
DEBUG - ==> Executing: CALL PKG_ATT.SP_Get_LeaveCheck(?,?,?,?,?,?)
DEBUG - ==> Parameters: 11050020(String), 2011-06-15 09:21:00.0(Timestamp), 2011-06-15 20:21:00.0(Timestamp), 1005(String), 0(String), 0(String)
INFO - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
INFO - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
DEBUG - xxx Connection Closed
INFO - [c3p0] Exceptions occurred while trying to close a PooledConnection's resources normally.
ERROR - mapperKeyId:【LeaveCheck】执行失败 ;错误原因:org.springframework.jdbc.UncategorizedSQLException:
### Error querying database. Cause: java.sql.SQLException: 无法从套接字读取更多的数据
### The error may involve cn.machi.oa.atte.dao.impl.LeaveApplyDAOImpl.LeaveApplyDetailMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: 无法从套接字读取更多的数据
; uncategorized SQLException for SQL []; SQL state [null]; error code [17410]; 无法从套接字读取更多的数据; nested exception is java.sql.SQLException: 无法从套接字读取更多的数据
org.springframework.jdbc.UncategorizedSQLException:
### Error querying database. Cause: java.sql.SQLException: 无法从套接字读取更多的数据
### The error may involve cn.machi.oa.atte.dao.impl.LeaveApplyDAOImpl.LeaveApplyDetailMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: 无法从套接字读取更多的数据
; uncategorized SQLException for SQL []; SQL state [null]; error code [17410]; 无法从套接字读取更多的数据; nested exception is java.sql.SQLException: 无法从套接字读取更多的数据
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:65)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:345)
at $Proxy0.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:158)
at cn.machi.framework.dao.impl.BaseDAOImpl.get(BaseDAOImpl.java:66)
at cn.machi.framework.dao.impl.BaseDAOImpl$$FastClassByCGLIB$$fd01e960.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:42)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:55)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at cn.machi.oa.atte.dao.impl.LeaveApplyDAOImpl$$EnhancerByCGLIB$$5c7d549c.get(<generated>)
at cn.machi.oa.atte.service.impl.LeaveApplyServiceImpl.computeTotalHour(LeaveApplyServiceImpl.java:313)
在网上找了很多类似的问题,都没能解决,不知何故,
这里说明一下,数据库是Oracle,连接池是c3p0
当调用没有返回值的存储过程时是正常的,有返回参数的都报这样的错。不知是我的写法不对还是jar包的问题。
求解!
解决方案 »
- spring结合mybatis多数据源
- 内网网站如何获得外网客户端IP
- struts1的actionform中的对象中的属性为Long型得值错误
- 关于WebSphere的集群环境下spring的filterChain不可用的问题
- 关于html:checkbox传输数组问题
- 一道小JAVA题 请高手指点!!!!谢谢
- 关于【jdbc.properties】cannot be opened because it does not exist
- ■■你们学习JSP一般多去哪些比较优秀的专业网站
- 寻觅专业开发商:公司要在十天内上马一个BBS系统,有意者从速!
- 巨分求解(200):这是不是包的问题,如果是,怎样解决?
- 求问spring基础问题!
- springMVC 表单提交有问
据说是需要更换oracle的驱动包
存储过程写的不正确
你把这些参数
Parameters: 11050020(String), 2011-06-15 09:21:00.0(Timestamp), 2011-06-15 20:21:00.0(Timestamp), 1005(String), 0(String), 0(String)替换到存储过程中 用SQL直接调用看是否报错
classes12.jar
ojdbc14_g.jar
都换到工程lib目录下试了下,
还是报同样的错
classes12.jar
classes12dms.jar
ojdbc14.jar
ojdbc14_g.jar
ojdbc14dms.jar
ojdbc14dms_g.jar
<select id="LeaveCheck" parameterMap="LeaveApplyDetailMap">
CALL PKG_ATT.SP_Get_LeaveCheck(#{strEmployeeNo},#{beginDate},#{endDate},#{typeNo},#{resultCode},#{resultData})
</select>
换成<procedure id="LeaveCheck" parameterMap="LeaveApplyDetailMap">
{CALL PKG_ATT.SP_Get_LeaveCheck(?,?,?,?,?,?)}
</procedure>试试
一般数据量大的话 是先执行存储过程将数据插入到临时表里然后通过查询临时表 将数据显示并不是你想的 直接些个调用存储过程的配置 正好将数据返回出来
存储过程的配置只能返回游标
{
Connection conn = null;//getSqlSession().getConnection();
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@10.134.11.237:1521:OA", "test", "test");
CallableStatement cs = conn.prepareCall("{CALL PKG_ATT.SP_Get_LeaveCheck(?,?,?,?,?,?)}");
cs.setString(1, "11050020");
cs.setDate(2, new Date(new java.util.Date().getTime()));
cs.setDate(3, new Date(new java.util.Date().getTime()+6000));
cs.setString(4, "1005");
cs.registerOutParameter(5, Types.VARCHAR);
cs.registerOutParameter(6, Types.VARCHAR);
cs.execute();
String str = cs.getString(5);
System.out.println("result: "+str);
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally
{
if(null != conn)
{
try
{
conn.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} }System.out.println("result: "+str);
打印出来的信息是:result: -1:该公司假别编号为1005的假别基本资料未维护!如果我用c3p0的Connection的话,就会报错。
代码是这样写的:只改了上面的一句:Connection conn = getSqlSession().getConnection();
说conn是关闭的,
WARN - java.version ''1.6.0_10-rc2'' could not be parsed. Defaulting to JDK 1.1.
ERROR - Error calling Connection.prepareCall:
java.sql.SQLException: You can't operate on a closed Connection!!!
[Cause: java.lang.NullPointerException
at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareCall(NewProxyConnection.java:527)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:39)
at $Proxy7.prepareCall(Unknown Source)
at cn.machi.framework.dao.impl.BaseDAOImpl.textXX(BaseDAOImpl.java:86)
at cn.machi.framework.dao.impl.BaseDAOImpl$$FastClassByCGLIB$$fd01e960.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:42)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:55)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at cn.machi.oa.atte.dao.impl.LeaveApplyDAOImpl$$EnhancerByCGLIB$$d19ada1f.textXX(<generated>)
at cn.machi.oa.atte.service.impl.LeaveApplyServiceImpl.computeTotalHour(LeaveApplyServiceImpl.java:303)
我看到网上给出的例子都有这个属性, <select id="LeaveCheck" parameterMap="LeaveApplyDetailMap" statementType="CALLABLE">
CALL PKG_ATT.SP_Get_LeaveCheck(#{strEmployeeNo},#{beginDate},#{endDate},#{typeNo},#{resultCode},#{resultData})
</select>
可是一套在我这里用的话,就会报另一个错
ERROR - mapperKeyId:【LeaveCheck】执行失败 ;错误原因:org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.lang.ClassCastException: org.apache.ibatis.executor.statement.CallableStatementHandler cannot be cast to org.apache.ibatis.executor.statement.PreparedStatementHandler
### The error may exist in file [F:\Workspaces\workspace\oamanage\bin\cn\machi\oa\atte\resources\LeaveApply_Mapper.xml]
### The error may involve cn.machi.oa.atte.dao.impl.LeaveApplyDAOImpl.LeaveCheck
### The error occurred while executing a query
### Cause: java.lang.ClassCastException: org.apache.ibatis.executor.statement.CallableStatementHandler cannot be cast to org.apache.ibatis.executor.statement.PreparedStatementHandler
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.lang.ClassCastException: org.apache.ibatis.executor.statement.CallableStatementHandler cannot be cast to org.apache.ibatis.executor.statement.PreparedStatementHandler
### The error may exist in file [F:\Workspaces\workspace\oamanage\bin\cn\machi\oa\atte\resources\LeaveApply_Mapper.xml]
### The error may involve cn.machi.oa.atte.dao.impl.LeaveApplyDAOImpl.LeaveCheck
### The error occurred while executing a query
### Cause: java.lang.ClassCastException: org.apache.ibatis.executor.statement.CallableStatementHandler cannot be cast to org.apache.ibatis.executor.statement.PreparedStatementHandler
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:67)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:345)
at $Proxy5.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:158)
at cn.machi.framework.dao.impl.BaseDAOImpl.get(BaseDAOImpl.java:72)
at cn.machi.framework.dao.impl.BaseDAOImpl$$FastClassByCGLIB$$fd01e960.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:42)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:55)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at cn.machi.oa.atte.dao.impl.LeaveApplyDAOImpl$$EnhancerByCGLIB$$8fff6ab5.get(<generated>)
at cn.machi.oa.atte.service.impl.LeaveApplyServiceImpl.computeTotalHour(LeaveApplyServiceImpl.java:312)
Map map = new HashMap();
map.put("x", 1);
map.put("y", 2);
map.put("z", 0);
this.getSqlSession().selectOne("selectByProc", map);mapper文件配置:
<select id="selectByProc" parameterType="map">
{call pp(#{x},#{y},#{z,mode=OUT,jdbcType=INTEGER})}
</select> 运行报错:索引中丢失 IN 或 OUT 参数:: 3如果mapper文件修改成:
<select id="selectByProc" parameterType="map" statementType="CALLABLE">
{call pp(#{x},#{y},#{z,mode=OUT,jdbcType=INTEGER})}
</select> 运行报错:
org.apache.ibatis.executor.statement.CallableStatementHandler cannot be cast to org.apache.ibatis.executor.statement.PreparedStatementHandler
StatementHandler handler = (StatementHandler) ReflectUtil
.getFieldValue(statement, "delegate"); // 增加CallableStatementHandler判断,如果是存储过程,则不执行下面的过程
if (handler instanceof PreparedStatementHandler)
{
.......
}增加一个判断,就不会强行转化成preparedstatement了,呵呵