Java PreparedStatements中的通配符 [英] Wildcards in Java PreparedStatements

问题描述

这是我当前的SQL语句:

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?

(我正在使用Java5和MySQL)

(I'm using Java5 and MySQL)

谢谢！

推荐答案

您将％放入绑定变量中.你呢

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

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

您应该添加 ESCAPE'！'，以便在输入中转义对 Like 很重要的特殊字符.

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

在使用 title 或 artist 之前，您应通过转义特殊字符(！，％， _ 和 [])，方法如下:

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("[", "![");
} 

这篇关于Java PreparedStatements中的通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！