如何在 Java 中使用准备好的语句进行选择查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24692296/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use prepared statement for select query in Java?
提问by jasim
I had tried several times using prepared statements but it returns SQL exception. here is my code:
我曾多次尝试使用准备好的语句，但它返回 SQL 异常。这是我的代码：
public ArrayList<String> name(String mobile, String password) {
ArrayList<String> getdata = new ArrayList<String>();
PreparedStatement stmt = null;
try {
String login = "select mobile, password from tbl_1 join tbl_2 on tbl_1.fk_id=2.Pk_ID where mobile=? and password=?";
String data = "select * from tbl_2 where password='" + password + "'";
PreparedStatement preparedStatement = conn.prepareStatement(login);
preparedStatement.setString(1, mobile);
preparedStatement.setString(1, password);
ResultSet rs = preparedStatement.executeQuery(login);
Statement stmts = (Statement) conn.createStatement();
if (rs.next()) {
System.out.println("Db inside RS");
ResultSet data = stmts.executeQuery(data);
while (data.next()) { /* looping through the resultset */
getdata.add(data.getString("name"));
getdata.add(data.getString("place"));
getdata.add(data.getString("age"));
getdata.add(data.getString("job"));
}
}
} catch (Exception e) {
System.out.println(e);
}
return getdata;
}
While running this, I got the following SQL exception:
运行此程序时，我收到以下 SQL 异常：
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? and password=?' at line 1.
Any suggestion to make this work? any piece of code is appreciated.
有什么建议可以使这项工作？任何一段代码都值得赞赏。
采纳答案by a_horse_with_no_name
You need to use:
您需要使用：
preparedStatement.executeQuery();
instead of
代替
preparedStatement.executeQuery(login);
when you pass in a string to executeQuery()thatquery is executed literally and thus the ?is send to the database which then creates the error. By passing query string you are not execution the "cached" prepared statement for which you passed the values.
当您将字符串传递给executeQuery()该查询时，它会按字面执行，因此将?发送到数据库，然后创建错误。通过传递查询字符串，您不会执行为其传递值的“缓存”准备好的语句。
回答by Jens
For both parameter you use preparedStatement.setString(1, ..);so the first parameter is set two times. but you never set the value for second parameter.
对于您使用的两个参数，preparedStatement.setString(1, ..);因此第一个参数设置两次。但您从未设置第二个参数的值。
so change
所以改变
preparedStatement.setString(1, mobile);
preparedStatement.setString(1, password);
to
到
preparedStatement.setString(1, mobile);
preparedStatement.setString(2, password);
