MySql JDBC 模板上的 StatementCallback 错误

Question

我该如何解决这个问题？我测试了我的查询，它可以在 MySQL 命令行和工作台中工作，但是当我尝试在我的 java 程序上使用此 SQL 查询时，为什么会出现此错误？？？

这是我的测试类

package test;
import dao.FinanceDao;
import javax.swing.JTextArea;
import java.util.Scanner;
import java.util.List;
import javax.swing.JOptionPane;
import domainmodel.User;
import javax.sql.DataSource;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

    public class TestDrive {

        public static void main(String[] args){
            String employeeID = null;
            String password = null;
            JTextArea text = new JTextArea();
            FinanceDao finance = new FinanceDao();
            DataSource  dataSource = SourceObj.getSource();

            finance.setDataSource(dataSource);

            String idNumber = JOptionPane.showInputDialog("Enter Employee ID to Search");

            List<User> test = finance.select(idNumber);
            for(User u: test){
            employeeID = u.getEmpID();
            password = u.getPassword();
            if(employeeID == null){
                text.setText("No Result");
            }else{
                text.setText("\tEmployeeID: "+employeeID+"\n\tPassword: "+password);
                }
            }

            List <User> test1 = finance.selectAll();
            for(User ux: test1){
                employeeID = ux.getEmpID();
                password = ux.getPassword();
                System.out.println("\tEmployeeID: "+employeeID+"\n\tPassword: "+password);
            }

            UI U = new UI(text);
        }
    }

另一个类

package dao;

import javax.sql.DataSource;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import dao.mapper.UserRowMapper;
import domainmodel.User;

public class FinanceDao implements Manage {

    private DataSource ds;

    @Override
    public void setDataSource(DataSource ds) {
        this.ds = ds;

    }

    @Override
    public void Add(String empID, String password) {
        JdbcTemplate Add = new JdbcTemplate(ds);
        Add.update("INSERT INTO user (empID,password) VALUES(?,?)",
        new Object[] { empID, password });
    }

    @Override
    public void Delete(String empID , String password) {
        JdbcTemplate Delete = new JdbcTemplate(ds);
        Delete.update("Delete from User where emp_id = '?'",new Object[]{empID});
    }


    public List<User> select(String empID) {
        JdbcTemplate select = new JdbcTemplate(ds);
        return select.query(
                "select * FROM USER where EMPID = ? ",
                new Object[] { empID }, new UserRowMapper());
    }

    public List<User>selectAll(){
        JdbcTemplate showAll = new JdbcTemplate(ds);
        return showAll.query("Select empID from User", new UserRowMapper());
    }

}

这是我的结果提取器的

package dao.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.ResultSetExtractor;

import domainmodel.User;

public class UserResultSetExtractor implements  ResultSetExtractor{

    @Override
    public Object extractData(ResultSet rs) throws SQLException{
            User user = new User();
            user.setEmpID(rs.getString(1));
            user.setPassword(rs.getString(2));
        return user;
    }

}

最后这是错误

Dec 6, 2011 12:05:02 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Dec 6, 2011 12:05:09 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
Dec 6, 2011 12:05:09 PM org.springframework.jdbc.support.SQLErrorCodesFactory <init>
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [Select empID from User]; SQL state [S1009]; error code [0]; Column Index out of range, 2 > 1. ; nested exception is java.sql.SQLException: Column Index out of range, 2 > 1. 
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:120)
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:276)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:353)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:404)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:412)
    at dao.FinanceDao.selectAll(FinanceDao.java:42)
    at test.TestDrive.main(TestDrive.java:42)
Caused by: java.sql.SQLException: Column Index out of range, 2 > 1. 
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
    at com.mysql.jdbc.ResultSetImpl.checkColumnBounds(ResultSetImpl.java:818)
    at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5651)
    at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5570)
    at dao.mapper.UserResultSetExtractor.extractData(UserResultSetExtractor.java:16)
    at dao.mapper.UserRowMapper.mapRow(UserRowMapper.java:13)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:92)
    at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:394)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:342)
    ... 4 more

我该如何解决这个问题？

Answer 1

似乎用户表只有 2 列，您可以通过调用获取
user.setEmpID(rs.getString(0));
user.setPassword(rs.getString(1));
当你使用
user.setPassword(rs.getString(2));
您正在尝试获取不存在的列号 3，这就是您收到此错误的原因。

---

更改您的查询以返回所有列：

public List<User>selectAll(){
    JdbcTemplate showAll = new JdbcTemplate(ds);
    return showAll.query("Select * from User", new UserRowMapper());
}

Answer 2

您可以按照 Harry 的建议进行操作，也可以手动选择所有列，例如

    public List<User>selectAll(){
        JdbcTemplate showAll = new JdbcTemplate(ds);
        return showAll.query("Select empID, password from User", new UserRowMapper());
    }

在实际代码中，您仅选择一列，而在 ResultSetExtractor 中，您尝试获取两列

   public Object extractData(ResultSet rs) throws SQLException{
        User user = new User();
        user.setEmpId(rs.getString(1));
        user.setPassword(rs.getString(2));//Selecting one column and trying to get two, hence the error
        return user;
    }