Using "like" wildcard in prepared statement
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 + "%");
Code it like this:
PreparedStatement pstmt = con.prepareStatement( "SELECT * FROM analysis WHERE notes like ?");pstmt.setString(1, notes + "%");`Make sure that you DO NOT include the quotes ' ' like below as they will cause an exception.
pstmt.setString(1,"'%"+ notes + "%'");
We can use the CONCAT SQL function.
PreparedStatement pstmt = con.prepareStatement( "SELECT * FROM analysis WHERE notes like CONCAT( '%',?,'%')";pstmt.setString(1, notes);ResultSet rs = pstmt.executeQuery();This works perfectly for my case.