我有一个如下所示的数据访问对象代码。成功在注册表中插入数据后，我只想提取该特定行的注册表的CustId。因此，我使用UserName列在readCustomer（）方法中提取特定用户的CustId。该程序如下图所示：
public class RegisterDAO {
public static int custId;
private DataSource dataSource;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
public void createCustomer(Register register) {
String sql = "INSERT INTO signup "
+ "(CustId,FirstName,LastName,Address1,Address2,Country,State,City,ZipCode,UserName,EmailId,ContactNo,MobileNo,Status)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,'ONHOLD')";
Connection conn = null;
try {
conn = dataSource.createConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, register.getId());
ps.setString(2, register.getFirstName());
ps.setString(3, register.getLastName());
ps.setString(4, register.getAddress1());
ps.setString(5, register.getAddress2());
ps.setString(6, register.getCountry());
ps.setString(7, register.getState());
ps.setString(8, register.getCity());
ps.setInt(9, register.getZipCode());
ps.setString(10, register.getUserName());
ps.setString(11, register.getEmailId());
ps.setString(12, register.getContactNo());
ps.setString(13, register.getMobileNo());
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
catch (NullPointerException e1){
}
finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
}
public void readCustomer(String username) {
String sql= "select * from signup where UserName= '" + username + "'";
Connection conn = null;
try {
conn = dataSource.createConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
custId =rs.getInt("CustId");
System.out.println(custId);
}
}catch (SQLException e) {
throw new RuntimeException(e);
}
catch (NullPointerException e1){
}
finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
}
}
但是此代码将引发运行时异常为：
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'sindhura' in 'where clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2212)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at com.encryption.DAO.RegisterDAO.readCustomer(RegisterDAO.java:87)
... 23 more
我的问题是where子句不接受Strings，这是否需要解决该问题？
任何帮助表示赞赏。
下面的句子抛出运行时异常
String sql= "select * from signup where UserName= '" + username + "'";
最佳答案
请使用准备好的语句和已经建议的pr.setString（...）。这是要使用的正确readCustomer（...）方法：
public void readCustomer(String username) {
String sql= "select * from signup where UserName = ?";
Connection conn = null;
try {
conn = dataSource.createConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, username);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
custId =rs.getInt("CustId");
System.out.println(custId);
}
}catch (SQLException e) {
throw new RuntimeException(e);
}
catch (NullPointerException e1){
}
finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
}