Use of LIKE clause in sql prepared statement, spring, SimpleJDBCTemplate – This article will take you through the common SQL errors that you might encounter while working with sql, spring, jdbc. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I have the following sql prepared statement:
SELECT * FROM video WHERE video_name LIKE ?
Im using spring and jdbc.
i have a method, where term is a searchterm, sjt is a SimpleJdbcTemplate, VideoMapper is a RowMapper and searchForTermQuery is the string from above
...
return sjt.query(searchForTermQuery, new VideoMapper(), term);
My table has 2 videos that match the term.
However when I run the query none is found. I get an empty List.
I tried playing with % around the question mark, but it only gave badGrammarExceptions.
Solution :
You need to put the % around the value itself, not around the placeholder (the question mark).
So:
return sjt.query(searchForTermQuery, new VideoMapper(), "%" + term + "%");
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.