preparedStatement SQL Error

问题:

Here is my code:

public int checklogin(String email, String key){
int res = -1;
try
{
    String selectSQL = "SELECT id FROM table WHERE email = ? AND key = ?";
    PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
    preparedStatement.setString(1, email);
    preparedStatement.setString(2, key);

    ResultSet rs = preparedStatement.executeQuery();
    if (rs.next()) 
        res = rs.getInt("id");

    rs.close();   
    preparedStatement.close();          
} catch (Exception e) { e.printStackTrace(); }

return res;
}

But i get:

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 'key = 'AAA'' at line 1

Where is the problem?

回答1:

KEY is a reserved word in MySQL. It needs to be escaped

String selectSQL = "SELECT id FROM table WHERE email = ? AND `key` = ?";

Avoiding the reserved keywords is always the best solution when naming column names.