catalogue
Type Exception Report Message Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [delete from user_detail where username=?]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'username' in 'where clause' Description The server encountered an unexpected condition that prevented it from fulfilling the request.
Zero. Preface
A frequently tested web page suddenly reported an error of 500. It hasn't happened before, but suddenly an error is reported. You can only correct the error first, otherwise you can't enter the page. Why is it possible to call a bug statement that has not happened before? This problem can only be attributed to the compiler.
Baidu has made a lot of mistakes and found that there are many similar mistakes, but everyone's mistakes are different. The occurrence of problems is an example, which can not be mechanically applied. It needs to be corrected according to the error prompt and its own engineering code. Other bloggers pointed out their mistakes and solved them, so that novice I could not understand them. Finally, the problem was not solved. Therefore, I explored for a long time and finally solved it. Here I explain the solution ideas in detail, so that I can provide ideas and methods for others to solve their own problems.
1, Look at the error prompt
Cause:
1,org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [delete from user_detail where username=?]
2,com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'username' in 'where clause'
2, Analysis error
This involves SQL statements, and then Unknown column 'username' in 'where clause', which indicates that the problem is caused by the inconsistency between SQL statements and database table structure! That is, the reason is the unknown field "username", user_ Missing field in detail table!
First, display user in mysql_ Structure and content of detail table:
It was found that the username field was indeed missing. (why do you need to operate this field if it is missing?)
3, Positioning problem
There's no one in here_ The field username in the detail table is missing because the first problem is in user_ The username field is manipulated in the detail table. In fact, the first error is often the key to solving the problem. Therefore, query the operation location and find that the delete operation needs to delete the user information of username according to the users table. In the users table is username
Field, which corresponds to user_ The detail table corresponds to real_name field, resulting in an error.
users table structure:
4, Problem solving
Find the calling function location, call removeUserDetail, find the corresponding removeUserDetail definition location, and modify the operation field.
//delete userService.removeUser(localname); userService.removeUserDetail(localname);
Before correction:
public void removeUserDetail(String userName){ String sql = "delete from " + ModelMeta.USER_DETAIL + " where username=?"; this.genericEntityDao.update(sql, new Object[] { userName }); }
After correction:
public void removeUserDetail(String userName){ String sql = "delete from " + ModelMeta.USER_DETAIL + " where real_name=?"; this.genericEntityDao.update(sql, new Object[] { userName }); }
After modification, HTTP Status 200 can access the page normally.