WHERE子句中'?)AND(Date =?)'附近的语法错误 [英] Syntax error in WHERE clause near '?) AND (Date = ?)'

问题描述

我正在尝试将SQL准备好的语句发送到MySQL DB.这就是我所拥有的:

I am trying to send a SQL prepared statement to MySQL DB. This is what I have:

    String sql1 = "SELECT idReimbursed_Funds As idReimFunds FROM reimbursedfunds Where ReimFundsName = ? AND Date = ?";
    PreparedStatement pstmt1 = conn.prepareStatement(sql1);
    pstmt1.setString(1, reimfund.getReimFundsName());
    pstmt1.setDate(2, (Date) reimfund.getDate());
    ResultSet rs1 = pstmt1.executeQuery(sql1);
    while(rs1.next()){                            
         idReimFunds = rs1.getInt("idReimFunds");                                                   
     }

搜索完此问题后，我找到了在问号或整个where子句周围使用括号的解决方案，例如:

After googling this problem, I found solutions to use parenthesis around the question marks or the whole where clause such as:

    String sql1 = "SELECT idReimbursed_Funds As idReimFunds FROM reimbursedfunds Where (ReimFundsName = ?) AND (Date = ?)";

这没有用.我收到由原始代码生成的相同错误消息:

This didn't work though. I get the same error message that is generated by my original code:

您的SQL语法有误；请在与MySQL服务器版本相对应的手册中找到在第1行附近的'?)AND(Date =?)'附近使用的正确语法.

"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 (Date = ?)' at line 1.

当我尝试在MySQL Workbench中使用SQL语句时，效果很好.有没有办法在JDBC中使用2 where子句?我知道在其他帖子中人们已经回答过，必须将其作为两个不同的查询发送，但是我想我想问的是，以防其他人阅读此帖子并知道某种方式.谢谢！

When I try the SQL statement in MySQL Workbench is works fine. Is there a way to use 2 where clauses with JDBC? I know in other posts people have answered that it has to be sent as two different queries, but I thought I would ask just in case someone else reads this posts and knows of a way. Thank you!

推荐答案

问题所在(除了 bgp 提到的Date问题)，是以下一行:

The problem (apart from the Date issue as mentioned by bgp), is the line:

ResultSet rs1 = pstmt1.executeQuery(sql1);

您正在尝试在准备好的语句上执行查询字符串，这是JDBC标准不允许的(MySQL应该实际上抛出异常，而不是像当前那样将其发送到服务器，但是最终结果是相同的). Statement.executeQuery(String sql) 说:

You are trying to execute a query string on a prepared statement, which is not allowed by the JDBC standard (MySQL should actually throw an exception instead of sending it to the server as it currently does, but the end result is the same). The documentation of Statement.executeQuery(String sql) says:

抛出:
 < c2>-如果发生数据库访问错误，则在封闭的Statement上调用此方法，给定的SQL语句将生成除单个ResultSet对象 在PreparedStatement或CallableStatement

Throws:
   SQLException - if a database access error occurs, this method is called on a closed Statement, the given SQL statement produces anything other than a single ResultSet object, the method is called on a PreparedStatement or CallableStatement

(重点是我的)

原因是您要执行准备好的语句，而不要执行其他任何查询.您应该调用 PreparedStatement.executeQuery() (因此没有参数):

The reason is that you want to execute the prepared statement, not any other query. You should call PreparedStatement.executeQuery() (so without a parameter):

ResultSet rs1 = pstmt1.executeQuery();

这篇关于WHERE子句中'?)AND(Date =?)'附近的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！