尝试创建准备好的语句以更新MySQL时出错

Question

我是Java的新手，正在尝试适应语法和将数据推送到MySQL表中。我有这个问题，不知道我做错了什么。当执行my update命令时，它会出现以下错误。

java.sql.SQLSyntaxErrorException: 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 ''occupation' = 'cat' WHERE first_name = 'kevin' AND last_name = 'hudgens'' at line 1
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
        at Driver.updateContact(Driver.java:172)
        at Main.main(Main.java:119)

主文件

System.out.println("Please enter the first name of the contact you want to update.");
                                first_name = input.nextLine();
                                System.out.println("Please enter the last name of the contact you want to update.");
                                last_name = input.nextLine();

                                System.out.println("Are you sure you want to update " + first_name + " " + last_name
                                                + "'s information. YES or NO");

                                String verifyUpdate = input.nextLine();
                                // lower for comparison
                                // verifyUpdate = verifyUpdate.toLowerCase();

                                if (verifyUpdate.equals("YES")) {
                                        break;
                                } else if (verifyUpdate.equals("NO")) {
                                        System.out.println(
                                                        "Please enter the correct first and last name of the contact you would like to update");
                                } else {
                                        System.out.println("You didnt enter the correct answer. YES or NO");
                                }
                        }
                        // inform user what they can update
                        System.out.println("What would you like to update? Options are:"
                                        + "\nFIRST NAME \n LAST NAME \n PHONE NUMBER \n EMAIL \n OCCUPATION");

                        // Collect the choices
                        String updateColumnChoice = input.nextLine();
                        System.out.println("What would you like to update it to? ");
                        String updatedValue = input.nextLine();

                        driver.updateContact(first_name, last_name, updateColumnChoice, updatedValue);

这是准备好的声明

public static void updateContact(String first_name, String last_name, String updatedColumn,
        String toBeUpdatedValue) {
    try {
        // Get connection to database
        Connection myConn = DriverManager.getConnection(info);

        // Create sql command for deleting
        String query = "UPDATE contacts SET ? = '?' WHERE first_name = '?' AND last_name = '?' ";

        PreparedStatement preparedStmt = myConn.prepareStatement(query);
        preparedStmt.setString(1, updatedColumn);
        preparedStmt.setString(2, toBeUpdatedValue);
        preparedStmt.setString(3, first_name);
        preparedStmt.setString(4, last_name);

        // push prepared statement to the database
        preparedStmt.executeUpdate();

        // close the connection to the database
        myConn.close();
    } catch (Exception exc) {
        exc.printStackTrace();
    }
}

我们将非常感谢在这方面的任何帮助。以及对我的代码的任何一般性批评。

Answer 1

如果条目不存在，更新时会出现错误。但上面说这是你的罪过。我所做的一个诀窍是使用PHP admin并将它生成的语句复制到Java中。我的陈述是正确的，起作用了。

抱歉已经有一段时间了。

Answer 2

据我所知，您可以将PreparedStatement的参数仅用于值，而不能用于元数据。如果需要updatedcolumn是动态的，可以执行以下操作：

String query = "UPDATE contacts SET " + updatedColumn + " = '?' WHERE first_name = '?' AND last_name = '?' ";

注意，您必须确保updatedcolumn正确地引用/转义，特别是当它来自用户数据时（即SQL注入攻击）。