JDBC throws SQLSyntaxErrorException while executing UPDATE query while the query works using MySQL c-CodePudding

I am writing a student management app and have created a function to update student data-

 public static void updateStudent(int id, int input) throws SQLException {
    Scanner sc = new Scanner(System.in);   //Scanner object
    Connection connection = ConnectionSetup.CreateConnection();  //Setting up connection
    String updateStatement = "UPDATE student_details SET ? = ? WHERE 's_id' = ?;"; //Initializing query
    PreparedStatement pstmt = connection.prepareStatement(updateStatement);

    System.out.println("Enter new name: ");
    String newName = sc.nextLine();
    pstmt.setString(1,"s_name"); //sets first ? to the columnname
    pstmt.setString(2,newName); //sets the second ? to new name
    pstmt.setString(3, String.valueOf(id));  //sets the third ? to the student ID
    pstmt.execute(); //executes the query

All the other CRUD functions work fine, but this one throws the following error after inputting all the info-

Exception in thread "main" 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 ''s_name' = 'Prateek' WHERE 's_id' = '6'' 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.execute(ClientPreparedStatement.java:371)
at com.Student.manage.StudentFunc.updateStudent(StudentFunc.java:76)
at Start.main(Start.java:58)

I tried printing the finalized query and it has correct syntax and works in the MySQL Console-

SQL Query is: UPDATE student_details SET 's_name' = 'new name' WHERE 's_id' = '6';

What is the bug here? Please help me understand.

CodePudding user response：

You can't use query parameters for column names (or any other identifer, or SQL keywords, etc.). When you use a query parameter, it is interpreted as a constant value. So your UPDATE statement is executed as if you had written it this way:

UPDATE student_details SET 's_name' = 'new name' WHERE 's_id' = '6';

This does not work. You can't use a string constant value as the left hand side of an assignment^*. When I test it in my local MySQL client, I get this error:

ERROR 1064 (42000): 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 ''s_name' = 'new name' WHERE 's_id' = '6'' at line 1

The error reports it got confused at 's_name', because a quoted string literal is not valid at that place in an UPDATE statement.

The WHERE clause is also a problem. It's not a syntax error, but it doesn't do what you probably intended.

WHERE 's_id' = '6';

This compares the string value 's_id' to the string value '6', it does not compare the column s_id to a value. Obviously the string 's_id' is not equal to '6', so the condition will always evaluate as false, and no rows will be updated.

^* You can't put a constant value on the left hand of an assignment in most other programming languages, either.