JDBC总是测试MySQL表的最后一行？ [英] JDBC always tests the last row of MySQL table?
问题描述
我有一个Manager类，可以在SQL表中保存数据，也可以从SQL表中获取结果并测试这些数据。当我运行程序时，会显示一帧获取ID和密码，如果它们是正确的，其他框架将会显示。但我不知道为什么它只是测试SQL表的最后一行？我的意思是，如果我将这些文本字段设置为除了最后一行之外的其他ID和密码。它将显示数据错误（我之前已为错误数据设置）
I have a Manager class that saves data in the SQL table and also get result from SQL table and test these data.when I run my program,one frame will be shown that gets ID and password and if they are correct ,the other frame will be shown.BUT I don't know that why it just test the last row of SQL table?? i mean if I set those text fields with the other IDs and passwords except from last row.it will show the data are wrong(I have set it before for wrong data)
经理类：
public static boolean Test(String userName, String password) {
boolean bool = false;
Statement stmt = null;
try {
stmt = conn.createStatement();
ResultSet rst = null;
rst = stmt.executeQuery("SELECT yahooId , password FROM clienttable");
while (rst.next()) {
if (rst.getString(1).equalsIgnoreCase(userName) && rst.getString(2).equalsIgnoreCase(password)) {
bool = true;
} else {
bool = false;
}
}
} catch (SQLException ex) {
Logger.getLogger(Manager.class.getName()).log(Level.SEVERE, null, ex);
}
return bool;
}
我的按钮的执行动作在获取ID和密码并测试它的框架中：
my button's perform action in the frame which get the ID and password and test it:
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
try {
submit();
} catch (ConnectException ex) {
JOptionPane.showMessageDialog(this, "You coudn't connect to the server successfully,try it again", "Sign_In Problem", JOptionPane.OK_OPTION);
}
clear();
}
private void submit() throws ConnectException {
String id = idField.getText();
char[] pass1 = passField.getPassword();
String pass = new String(pass1);
if (id.equals("") || pass.equals("")) {
Toolkit.getDefaultToolkit().beep();
JOptionPane.showMessageDialog(this, "You should enter an ID and password", "Sign_In Problem", JOptionPane.OK_OPTION);
return;
} else {
boolean b = Manager.Test(id, pass);
client.setCurrentName(id);
if (b == true) {
this.setVisible(false);
ListFrame frame = new ListFrame(client);
frame.setVisible(true);
} else {
JOptionPane.showMessageDialog(this, "You have entered wrong datas,try it again", "Sign_In Problem", JOptionPane.OK_OPTION);
return;
}
}
}
编辑：
我已编辑了我的经理课程（测试方法），但仍然像过去一样工作!!
I have edited my manager class(test method) but still it works like past!!
public static boolean Test(String userName, String password) {
boolean bool = false;
PreparedStatement stmt = null;
ResultSet resultSet = null;
try {
stmt = conn.prepareStatement("SELECT id FROM clienttable WHERE yahooId = ? AND password = ?");
stmt.setString(1, userName);
stmt.setString(2, password);
resultSet = stmt.executeQuery();
bool = resultSet.next();
} catch (SQLException ex) {
Logger.getLogger(Manager.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
resultSet.close();
stmt.close();
conn.close();
} catch (SQLException ex) {
Logger.getLogger(Manager.class.getName()).log(Level.SEVERE, null, ex);
}
}
return bool;
}
推荐答案
因为您将整个数据库表拖入Java内存并在while循环中测试每个行。如果找到匹配项，则不会中断循环，以便它继续覆盖布尔结果，直到最后一行。
Because you're hauling the entire database table down into Java's memory and testing every row in a while loop. You don't break the loop if a match is found so that it continues overwriting the boolean outcome until with the last row.
那就是说，你真的不想要在Java中进行比较。只需使用 SQL WHERE 子句。这很多更有效率，而且确实是DB应该做的任务。不要试图接管DB在Java中的工作，它只会效率低下。
That said, you really don't want to do the comparison in Java. Just make use of the SQL WHERE clause. That's much more efficient and really the task a DB is supposed to do. Don't try to take over the DB's work in Java, it's only going to be inefficient.
public boolean exists(String username, String password) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
boolean exists = false;
try {
connection = database.getConnection();
preparedStatement = connection.prepareStatement("SELECT id FROM client WHERE username = ? AND password = ?");
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
resultSet = preparedStatement.executeQuery();
exists = resultSet.next();
} finally {
close(resultSet);
close(preparedStatement);
close(connection);
}
return exists;
}
您看到我做了一些改进：
You see that I made a few enhancements:
- 使用preparedstatement。
- 不要使用equalsignorecase。密码FooBar不应与foobar相同。
- 轻轻获取并关闭相同范围内的资源以避免泄漏。
- 使用独立且可重复使用的非静态DAO方法。
- Use preparedstatement.
- Do not use equalsignorecase. A password of "FooBar" should NOT be the same as "foobar".
- Gently acquire and close resources in same scope to avoid leaking.
- Have it in an independent and reuseable non-static DAO method.
要了解有关使用JDBC的更多信息，请找到正确的方法这个基础教程很有用。
To learn more about using JDBC the proper way you may find this basic tutorial useful.
这篇关于JDBC总是测试MySQL表的最后一行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
