Exception HandlingJavaJDBC

java.sql.SQLException: Invalid column index

3 Mins read

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.

5 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *