Summarize the various SQL problems encountered in the work.

bad SQL Grammar:SQLexception is java.sql.SQLException: No value specified for parameter 3

Problem description

Error message

bad SQL grammar [ update station set reserve2 = ? ,status = ? , phone = (case when phone is null or phone = '' then ? else phone end) , RESERVE4 = (case when RESERVE4 is null or RESERVE4 = '' then ? else RESERVE4 end) where no = ? and code = ? and sta_code  = ? and org_code = ? and ( status != 'finish' or status is null ) and create_time > ? ] ; nested exception is java.sql.SQLException: No value specified for parameter 3Copy the code

Error code:

 int digestUpdate = getJdbcTemplate().update(digestSql, new Object[] { id, status, phone, phoneLastPart,result[0], result[1], result[2], result[3]},query7Days);
Copy the code

Cause analysis,

No value specified for parameter 3: What is the cause of this problem?

  • The number of fields required by the placeholder is different from the actual number of fields given: for example, the placeholder is 5, but only 4 fields are actually given values

  • Bad SQL Grammar: Just thinking about SQL, it’s all about SQL. So think about it this way.

That is, one of the fields in the middle has no value.

The third parameter indicated by the error is not necessarily the third parameter. So it needs to be checked carefully.

This is not written by me, but submitted without testing after being corrected. When it was handed over to me, I looked at the commit record and found that the problem was that the query7Days field was added. Look at it again, and there is something wrong with it.

The solution

Query7Days should be within {}. Modified:

 int digestUpdate = getJdbcTemplate().update(digestSql, new Object[] { id, status, phone, phoneLastPart,result[0], result[1], result[2], result[3],query7Days)};
Copy the code

analyse

  • Details can be time-consuming: one careless punctuation mark can lead to a time-consuming problem search. So after you write it, verify the problem.

  • Pay attention to errors: Take a good look at the code in the errors to see if there are any problems.