I have seen many beginners asking this question and searching on Google:
I am getting below exception, while running a Java Database Connectivity (JDBC) query. I have checked my query its working fine when I run directly from a SQL client. What could be the reason?
why am I getting java.sql.sqlexception: invalid column index? How to resolve java.sql.sqlexception: invalid column index. What does it mean when I get java.sql.sqlexception: invalid column index. how to solve java.sql.sqlexception: invalid column index, error using JDBC query java.sql.sqlexception: invalid column index
java.sql.SQLException: Invalid column index 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.setLongInternal(OraclePreparedStatement.java:4680) at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9023) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8790) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9512) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9495) at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:346) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:217) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:145) at org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:51) at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:646) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:641) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:670) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:678) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:710) at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.query(SimpleJdbcTemplate.java:187) at com.dornerconsulting.rma.spring.hibernate.dao.SystemItemJDBCDao.findBySql(SystemItemJDBCDao.java:49) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at $Proxy23.findBySql(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.binding.method.MethodInvoker.invoke(MethodInvoker.java:93) at org.springframework.webflow.action.AbstractBeanInvokingAction.doExecute(AbstractBeanInvokingAction.java:127) at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:192) at org.springframework.webflow.engine.AnnotatedAction.execute(AnnotatedAction.java:146) at org.springframework.webflow.engine.ActionExecutor.execute(ActionExecutor.java:59) at org.springframework.webflow.engine.ActionList.execute(ActionList.java:153) at org.springframework.webflow.engine.ViewState.doEnter(ViewState.java:94) at org.springframework.webflow.engine.State.enter(State.java:191) at org.springframework.webflow.engine.Transition.execute(Transition.java:212) at org.springframework.webflow.engine.TransitionableState.onEvent(TransitionableState.java:107) at org.springframework.webflow.engine.Flow.onEvent(Flow.java:534) at org.springframework.webflow.engine.impl.RequestControlContextImpl.signalEvent(RequestControlContextImpl.java:205) at org.springframework.webflow.engine.impl.FlowExecutionImpl.signalEvent(FlowExecutionImpl.java:202) at org.springframework.webflow.executor.FlowExecutorImpl.resume(FlowExecutorImpl.java:222) at org.springframework.webflow.executor.support.FlowRequestHandler.handleFlowRequest(FlowRequestHandler.java:111) at org.springframework.webflow.executor.mvc.FlowController.handleRequestInternal(FlowController.java:165) at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153) at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511) 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 org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76) 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:128) 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:286) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447) at java.lang.Thread.run(Unknown Source)
Answer : Most of the time, If you are getting this exception while running a JDBC query from a Java program check if you are passing correct number of parameters to the prepared statement query.
So for example if you have a select statement like below
select * from emp where empId = ? and deptId = ?
In this case there are 2 parameters need to be passed to the prepared statement. If you pass less or more you are going to get the above exception and query will not execute.