I am using prepared statements to execute mysql database queries. And I want to implement a search functionality based on a keyword of sorts.
For that I need to use LIKE
keyword, that much I know. And I have also used prepared statements before, but I do not know how to use it with LIKE
because from the following code where would I add the 'keyword%'
?
Can I directly use it in the pstmt.setString(1, notes)
as (1, notes+"%")
or something like that. I see a lot of posts on this on the web but no good answer anywhere.
PreparedStatement pstmt = con.prepareStatement(
"SELECT * FROM analysis WHERE notes like ?");
pstmt.setString(1, notes);
ResultSet rs = pstmt.executeQuery();
You need to set it in the value itself, not in the prepared statement SQL string.
So, this should do for a prefix-match:
notes = notes
.replace("!", "!!")
.replace("%", "!%")
.replace("_", "!_")
.replace("[", "![");
PreparedStatement pstmt = con.prepareStatement(
"SELECT * FROM analysis WHERE notes LIKE ? ESCAPE '!'");
pstmt.setString(1, notes + "%");
or a suffix-match:
pstmt.setString(1, "%" + notes);
or a global match:
pstmt.setString(1, "%" + notes + "%");