MySQLSyntaxErrorException靠近“？”在尝试执行PreparedStatement时 [英] MySQLSyntaxErrorException near "?" when trying to execute PreparedStatement
问题描述
我正在尝试使用Java中的PreparedStatement执行查询。
I'm trying to execute a query using a PreparedStatement in Java.
当我尝试执行查询时，我收到错误号1064（语法错误）。
I am getting error number 1064 when I try to execute my query (syntax error).
我在MySQL查询浏览器中使用替换值对此进行了测试，结果正常。
I have tested this in MySQL query browser with substituted values which works fine.
我的代码出了什么问题？
What's wrong with my code?
以下是相关代码：
String query = "select MemberID, MemberName from members where MemberID = ? or MemberName = ?";
Connection conn = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);
PreparedStatement s = conn.prepareStatement(query);
s.setInt(1, 2);
s.setString(2, "zen");
ResultSet rs = s.executeQuery(query);
以下是我得到的例外情况：
Here's the exception I'm getting:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException：SQL语法中有
错误;查看与
MySQL服务器版本对应的手册，以便在'附近使用正确的语法？或第1行的会员名
=？'
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 '? or MemberName = ?' at line 1
推荐答案
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException：
您的SQL语法中有错误;检查与您的MySQL服务器版本对应的手册，以便在'附近使用正确的语法？或第1行的MemberName =？'
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 '? or MemberName = ?' at line 1
MySQL不理解的含义？在SQL查询中。这确实是无效的SQL语法。所以它不会被 PreparedStatement 取代。猜猜是什么？
MySQL doesn't understand the meaning of ? in the SQL query. It's indeed invalid SQL syntax. So somehow it's not been replaced by PreparedStatement. And guess what?
PreparedStatement s = conn.prepareStatement(query);
s.setInt(1, intValue);
s.setString(2, strValue);
rs = s.executeQuery(query); // Fail!
您正在使用原始查询覆盖准备好的查询！您需要调用无参数的 PreparedStatement #cuteQuery（） 方法而不是 Statement #cuteQuery（String） 。
You're overridding the prepared query with the original query! You need to call the argumentless PreparedStatement#executeQuery() method instead of Statement#executeQuery(String).
PreparedStatement s = conn.prepareStatement(query);
s.setInt(1, intValue);
s.setString(2, strValue);
rs = s.executeQuery(); // OK!
与此问题无关，您的代码漏掉了资源。几个小时后，数据库将耗尽它们，您的应用程序将崩溃。要解决此问题，您需要遵循关闭连接，语句和 ResultSet的JDBC惯用法在最后块的尝试块中获取它们。有关详细信息，请查看 JDBC基础教程。
Unrelated to the problem, your code is leaking resources. The DB will run out of them after several hours and your application will crash. To fix this, you need to follow the JDBC idiom of closing Connection, Statement and ResultSet in the finally block of the try block where they're been acquired. Check the JDBC basic tutorial for more detail.
这篇关于MySQLSyntaxErrorException靠近“？”在尝试执行PreparedStatement时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
