Wildcards in Java PreparedStatements

问题:

Here's my current SQL statement:

SEARCH_ALBUMS_SQL = "SELECT * FROM albums WHERE title LIKE ? OR artist LIKE ?;";

It's returning exact matches to the album or artist names, but not anything else. I can't use a '%' in the statement or I get errors.

How do I add wildcards to a prepared statement?

(I'm using Java5 and MySQL)

Thanks!

回答1:

You put the % in the bound variable. So you do

   stmt.setString(1, "%" + likeSanitize(title) + "%");
   stmt.setString(2, "%" + likeSanitize(artist) + "%");

You should add ESCAPE '!' to allow you to escape special characters that matter to LIKE in you inputs.

Before using title or artist you should sanitize them (as shown above) by escaping special characters (!, %, _, and [) with a method like this:

public static String likeSanitize(String input) {
    return input
       .replace("!", "!!")
       .replace("%", "!%")
       .replace("_", "!_")
       .replace("[", "![");
}