“?"附近的 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.

我的代码有什么问题?

相关代码如下:

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);

这是我得到的例外:

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 行

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#executeQuery() 方法代替 Statement#executeQuery(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!

<小时>

与问题无关，您的代码正在泄漏资源.几个小时后，数据库将耗尽它们，您的应用程序将崩溃.要解决此问题，您需要遵循 JDBC 习惯用法在 finally 块中关闭 Connection、Statement 和 ResultSet获取它们的 try 块.查看 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屋！