JDBC MySql绑定变量语法错误在where子句中

Question

我收到此错误：com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException：您的SQL语法有错误。检查与您的MySQL服务器版本相对应的手册，以获取在’？’附近使用的正确语法。在第1行

public static Person getDetails(int id) {
    Connection conn = null;
       PreparedStatement stmt = null;
       Person newPerson = new Person();
       try{
          //STEP 2: Register JDBC driver
          Class.forName("com.mysql.jdbc.Driver");

          //STEP 3: Open a connection
          System.out.println("Connecting to database...");
          conn = DriverManager.getConnection(DB_URL,USER,PASS);

          //STEP 4: Execute a query
          System.out.println("Creating statement...");
          String sql = "SELECT firstName, lastName, birthday FROM person WHERE id=?";
          System.out.println("SQL Statement:\n\t" + stmt);
          stmt = conn.prepareStatement(sql);
          System.out.println("Prepared Statement before bind variables set:\n\t" + stmt.toString());
          //Bind values into the parameters.
          System.out.println("ID " + id);
          stmt.setInt(1, id);  // This would set id
          System.out.println("Prepared Statement after bind variables set:\n\t" + stmt.toString());

          // Let us select all the records and display them.
          ResultSet rs = stmt.executeQuery(sql);

          //STEP 5: Extract data from result set
          while(rs.next()){
             //Retrieve by column name
             String firstName = rs.getString("firstName");
             String lastName = rs.getString("lastName");
             Date birthday = rs.getDate("birthday");

             newPerson.setBirthday(birthday);
             newPerson.setFirstName(firstName);
             newPerson.setLastName(lastName);
             newPerson.setId(id);

             //Display values
             System.out.print("ID: " + id);
             System.out.print(", First: " + firstName);
             System.out.println(", Last: " + lastName);
             System.out.println(", Birthday: " + birthday);
          }
          //STEP 6: Clean-up environment
          rs.close();
          stmt.close();
          conn.close();
       }catch(SQLException se){
          //Handle errors for JDBC
          se.printStackTrace();
       }catch(Exception e){
          //Handle errors for Class.forName
          e.printStackTrace();
       }finally{
          //finally block used to close resources
          try{
             if(stmt!=null)
                stmt.close();
          }catch(SQLException se2){
          }// nothing we can do
          try{
             if(conn!=null)
                conn.close();
          }catch(SQLException se){
             se.printStackTrace();
          }//end finally try
       }//end try
       System.out.println("Goodbye!");
       return newPerson;
}

我成功执行了不带where子句的查询。我看过许多示例，但我没有尝试解决此问题。

Answer 1

如果我理解您的问题，那么您就使用了问题Statement.executeQuery(String)。我可以肯定您打算使用PreparedStatement.executeQuery()，

// Let us select all the records and display them.
ResultSet rs = stmt.executeQuery(sql);   // <-- adding sql here makes it use the
                                         //     Statement version.

你想用

// Let us select all the records and display them.
ResultSet rs = stmt.executeQuery();      // <-- use the version from PreparedStatement