我该如何解决 ?我测试了查询,它可以在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
我该如何解决?
解决方法:
似乎用户表只有两列,您可以通过调用获得
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());
}