Oracle试用PreparedStatement出现java.sql.SQLException: 无效的列索引

最新推荐文章于 2021-10-29 11:19:11 发布

iteye_15819

最新推荐文章于 2021-10-29 11:19:11 发布

阅读量339

点赞数

文章标签：数据库 java

本文介绍了一种在Java程序中调用SQL语句时遇到的关于日期参数设置的问题及解决方案。通过调整SQL语句中日期参数的格式声明，成功解决了因无效的列索引引发的异常。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql语句如下：select bu.deptno,count(bu.hiredate) StartMonthCount from userinfo bu,dept d where bu.deptno=d.deptno and bu.hiredate<to_date(‘?’,'yyyy-mm-dd') group by bu.deptno

java程序中调用：pstmt.setString(1, firstDay);

出现如下异常

java.sql.SQLException: 无效的列索引

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5329)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5321)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:135)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:135)
at com.neusoft.hr.model.dao.impl.ReportDaoImpl.findStartMonthCount(ReportDaoImpl.java:258)
at com.neusoft.hr.model.service.ReportService.findMonthReportList(ReportService.java:77)
at com.neusoft.hr.controller.ReportAction.monthReport(ReportAction.java:98)
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.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:269)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:170)
at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.neusoft.hr.utils.EncodingFilter.doFilter(EncodingFilter.java:29)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:861)
at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1584)

at java.lang.Thread.run(Thread.java:619)

经过仔细排查，原来是日期转换的时候出问题了

将select bu.deptno,count(bu.hiredate) StartMonthCount from userinfo bu,dept d where bu.deptno=d.deptno and bu.hiredate<to_date(‘?’,'yyyy-mm-dd') group by bu.deptno

改为select bu.deptno,count(bu.hiredate) StartMonthCount from userinfo bu,dept d where bu.deptno=d.deptno and bu.hiredate<to_date(?,'yyyy-mm-dd') group by bu.deptno

问题随之解决了。