Spring JDBCTemplate出现InvalidResultSetAccessException异常：无效的列索引。

Question

Spring JDBCTemplate出现InvalidResultSetAccessException异常：无效的列索引。

4

我使用Spring的SimpleJDBCTemplate来访问Oracle数据库。以下是我的代码。

String sql = "SELECT from_bin_code FROM hbin_import_mapping";
return jt.query(sql, new BeanHbinImportMappingMapper(), (Object) null);

行映射器是：

public class BeanHbinImportMappingMapper
    implements
        RowMapper<BeanHbinImportMapping> {
    public BeanHbinImportMapping mapRow(ResultSet rs, int rowno)
        throws SQLException {
        int fromBinCode = rs.getInt("from_bin_code");
//        char fromBinCodeChar = rs.getString("from_bin_code_char").charAt(0);
//        boolean fromBinCodeAllowed = rs.getString("from_bin_code_allowed")
//            .equals("Y") ? true : false;
//        int oliBinCode = rs.getInt("oli_bin_code");
//        String oliBinQuality = rs.getString("oli_bin_quality");
//        String oliBinGroup = rs.getString("oli_bin_group");
//        String oliBinDesc = rs.getString("oli_bin_desc");
//        boolean olibinRef = rs.getString("oli_bin_ref").equals("Y") ? true
//            : false;

        BeanHbinImportMapping bean = new BeanHbinImportMapping(fromBinCode);
//      BeanHbinImportMapping bean = new BeanHbinImportMapping(1, 'c', true);
        return bean;
    }
}

这个SQL已经测试通过。

异常堆栈跟踪：

javax.servlet.ServletException: org.springframework.jdbc.InvalidResultSetAccessException: PreparedStatementCallback; invalid ResultSet access for SQL [SELECT from_bin_code FROM hbin_import_mapping]; nested exception is java.sql.SQLException: Invalid column index
    org.apache.struts.action.RequestProcessor.processException(RequestProcessor.java:545)
    org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:486)
    org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
    org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
    org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
    org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)


root cause 

org.springframework.jdbc.InvalidResultSetAccessException: PreparedStatementCallback; invalid ResultSet access for SQL [SELECT from_bin_code FROM hbin_import_mapping]; nested exception is java.sql.SQLException: Invalid column index
    org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
    org.springframework.jdbc.core.simple.SimpleJdbcTemplate.query(SimpleJdbcTemplate.java:200)
    com.st.mas.wmr.persistence.atom.AtomStifOliBinConversion.getLoaderMatrix(AtomStifOliBinConversion.java:52)
    com.st.mas.wmr.persistence.process.ProcessStifOliBinConversion.getLoaderMatrix(ProcessStifOliBinConversion.java:50)
    com.st.mas.wmr.action.StifBinConversionAction.viewLoaderMapping(StifBinConversionAction.java:35)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    java.lang.reflect.Method.invoke(Method.java:585)
    org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
    com.st.mas.wmr.action.AnyDispatchAction.execute(AnyDispatchAction.java:107)
    org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
    org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
    org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
    org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
    org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)


root cause 

java.sql.SQLException: Invalid column index
    oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
    oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:161)
    oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:226)
    oracle.jdbc.driver.OraclePreparedStatement.setNullInternal(OraclePreparedStatement.java:3661)
    oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:3652)
    org.apache.commons.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:108)
    org.apache.commons.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:108)
    org.springframework.jdbc.core.StatementCreatorUtils.setNull(StatementCreatorUtils.java:252)
    org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:213)
    org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
    org.springframework.jdbc.core.ArgPreparedStatementSetter.doSetValue(ArgPreparedStatementSetter.java:65)
    org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:46)
    org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:641)
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
    org.springframework.jdbc.core.simple.SimpleJdbcTemplate.query(SimpleJdbcTemplate.java:200)
    com.st.mas.wmr.persistence.atom.AtomStifOliBinConversion.getLoaderMatrix(AtomStifOliBinConversion.java:52)
    com.st.mas.wmr.persistence.process.ProcessStifOliBinConversion.getLoaderMatrix(ProcessStifOliBinConversion.java:50)
    com.st.mas.wmr.action.StifBinConversionAction.viewLoaderMapping(StifBinConversionAction.java:35)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    java.lang.reflect.Method.invoke(Method.java:585)
    org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
    com.st.mas.wmr.action.AnyDispatchAction.execute(AnyDispatchAction.java:107)
    org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
    org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
    org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
    org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
    org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)

谢谢。

- Will Sumekar

如果将rs.getInt("from_bin_code")替换成rs.getInt(1)会发生什么？ - skaffman

不行，即使我将表名更改为不存在的表，我仍然会得到相同的异常。这里可能出了什么问题？ - Will Sumekar

2个回答

1

** RowMapper **

使用此接口的优点是将每行映射到结果对象。mapRow() 方法不应调用 ResultSet 的 next() 方法；它只需要映射当前行的值，并返回对象列表。

applicationConttext.xml

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/yash" />
        <property name="username" value="root" />
        <property name="password" value="" />
    </bean>

    <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource" />
    </bean>
<bean id="empdao" class="gmail.daos.EmpDao">
        <property name="jt" ref="jt"/>
</bean>

DAO Class *

public class EmpDao {
    private JdbcTemplate jt;
public List<EmpDto> listEmpoyees(){
 String sql = "select * FROM emp";
  List<EmpDto> emplist = jt.query(sql, new RowMapper() {

                public EmpDto mapRow(ResultSet rs, int rowNum) throws SQLException {
                    EmpDto emp = new EmpDto();

                    emp.setId(rs.getInt("id"));
                    emp.setName(rs.getString("name"));
                    emp.setAge(rs.getInt("age"));
                    emp.setSalary(rs.getInt("salary"));
                    emp.setAddress(rs.getString("address"));

                    System.out.println("***");

                    return emp;
                }

            });      
            return emplist;
}}

如果您使用这种方法，就不会出现ReselutSetExecption异常。

- Yash

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Will Sumekar · Accepted Answer

您正在传递参数（在此情况下为null），而查询没有任何查询参数... 要么向查询添加参数，要么从方法调用中删除参数。

http://forum.springsource.org/showthread.php?t=108788

感谢Martin Deinum