如何在java中实现像'LIKE'运算符这样的SQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/898405/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to implement a SQL like 'LIKE' operator in java?
提问by Chris
I need a comparator in java which has the same semantics as the sql 'like' operator. For example:
我需要一个 java 中的比较器，它与 sql 'like' 运算符具有相同的语义。例如：
myComparator.like("digital","%ital%");
myComparator.like("digital","%gi?a%");
myComparator.like("digital","digi%");
should evaluate to true, and
应该评估为真，并且
myComparator.like("digital","%cam%");
myComparator.like("digital","tal%");
should evaluate to false. Any ideas how to implement such a comparator or does anyone know an implementation with the same semantics? Can this be done using a regular expression?
应该评估为假。任何想法如何实现这样的比较器，或者有没有人知道具有相同语义的实现？这可以使用正则表达式完成吗？
回答by Bob
.* will match any characters in regular expressions
.* 将匹配正则表达式中的任何字符
I think the java syntax would be
我认为java语法是
"digital".matches(".*ital.*");
And for the single character match just use a single dot.
对于单字符匹配，只需使用一个点。
"digital".matches(".*gi.a.*");
And to match an actual dot, escape it as slash dot
并匹配一个实际的点，将其转义为斜线点
\.
回答by job
Java strings have .startsWith() and .contains() methods which will get you most of the way. For anything more complicated you'd have to use regex or write your own method.
Java 字符串具有 .startsWith() 和 .contains() 方法，它们可以帮助您完成大部分工作。对于任何更复杂的事情，您必须使用正则表达式或编写自己的方法。
回答by Dave Webb
You could turn '%string%'to contains(), 'string%'to startsWith()and '%string"'to endsWith().
你可以把'%string%'到contains()，'string%'到startsWith()和'%string"'到endsWith()。
You should also run toLowerCase()on both the string and pattern as LIKEis case-insenstive.
您还应该toLowerCase()在LIKE不区分大小写的情况下同时运行字符串和模式。
Not sure how you'd handle '%string%other%'except with a Regular Expression though.
'%string%other%'除了使用正则表达式之外，不确定你会如何处理。
If you're using Regular Expressions:
如果您使用正则表达式：
回答by Michael Myers
Yes, this could be done with a regular expression. Keep in mind that Java's regular expressions have different syntax from SQL's "like". Instead of "%", you would have ".*", and instead of "?", you would have ".".
是的，这可以用正则表达式来完成。请记住，Java 的正则表达式与 SQL 的“like”具有不同的语法。代替“ %”，您将拥有“ .*”，而不是“ ?”，您将拥有“ .”。
What makes it somewhat tricky is that you would also have to escape any characters that Java treats as special. Since you're trying to make this analogous to SQL, I'm guessing that ^$[]{}\shouldn't appear in the regex string. But you will have to replace "." with "\\." before doing any other replacements. (Edit:Pattern.quote(String)escapes everything by surrounding the string with "\Q" and "\E", which will cause everything in the expression to be treated as a literal (no wildcards at all). So you definitelydon't want to use it.)
有点棘手的是，您还必须转义 Java 视为特殊的任何字符。由于您试图使其类似于 SQL，我猜它^$[]{}\不应该出现在正则表达式字符串中。但是在进行任何其他替换之前，您必须将“ .”替换为“ ” \\.。（编辑：Pattern.quote(String)通过用“ \Q”和“ \E”包围字符串来转义所有内容，这将导致表达式中的所有内容都被视为文字（根本没有通配符）。所以你绝对不想使用它。）
Furthermore, as Dave Webb says, you also need to ignore case.
此外，正如 Dave Webb 所说，您还需要忽略大小写。
With that in mind, here's a sample of what it might look like:
考虑到这一点，以下是它可能的外观示例：
public static boolean like(String str, String expr) {
expr = expr.toLowerCase(); // ignoring locale for now
expr = expr.replace(".", "\."); // "\" is escaped to "\" (thanks, Alan M)
// ... escape any other potentially problematic characters here
expr = expr.replace("?", ".");
expr = expr.replace("%", ".*");
str = str.toLowerCase();
return str.matches(expr);
}
回答by tommyL
i dont know exactly about the greedy issue, but try this if it works for you:
我不完全知道贪婪的问题，但如果它对你有用，试试这个：
public boolean like(final String str, String expr)
{
final String[] parts = expr.split("%");
final boolean traillingOp = expr.endsWith("%");
expr = "";
for (int i = 0, l = parts.length; i < l; ++i)
{
final String[] p = parts[i].split("\\\?");
if (p.length > 1)
{
for (int y = 0, l2 = p.length; y < l2; ++y)
{
expr += p[y];
if (i + 1 < l2) expr += ".";
}
}
else
{
expr += parts[i];
}
if (i + 1 < l) expr += "%";
}
if (traillingOp) expr += "%";
expr = expr.replace("?", ".");
expr = expr.replace("%", ".*");
return str.matches(expr);
}
回答by Peter Lawrey
Regular expressions are the most versatile. However, some LIKE functions can be formed without regular expressions. e.g.
正则表达式是最通用的。但是，可以在没有正则表达式的情况下形成某些 LIKE 函数。例如
String text = "digital";
text.startsWith("dig"); // like "dig%"
text.endsWith("tal"); // like "%tal"
text.contains("gita"); // like "%gita%"
回答by Alan Moore
Every SQL reference I can find says the "any single character" wildcard is the underscore (_), not the question mark (?). That simplifies things a bit, since the underscore is not a regex metacharacter. However, you still can't use Pattern.quote()for the reason given by mmyers. I've got another method here for escaping regexes when I might want to edit them afterward. With that out of the way, the like()method becomes pretty simple:
我能找到的每个 SQL 参考都说“任何单个字符”通配符是下划线 ( _)，而不是问号 ( ?)。这稍微简化了一些事情，因为下划线不是正则表达式元字符。但是，Pattern.quote()由于mmyers给出的原因，您仍然无法使用。当我以后可能想编辑它们时，我在这里有另一种方法来转义正则表达式。有了这个，该like()方法变得非常简单：
public static boolean like(final String str, final String expr)
{
String regex = quotemeta(expr);
regex = regex.replace("_", ".").replace("%", ".*?");
Pattern p = Pattern.compile(regex,
Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
return p.matcher(str).matches();
}
public static String quotemeta(String s)
{
if (s == null)
{
throw new IllegalArgumentException("String cannot be null");
}
int len = s.length();
if (len == 0)
{
return "";
}
StringBuilder sb = new StringBuilder(len * 2);
for (int i = 0; i < len; i++)
{
char c = s.charAt(i);
if ("[](){}.*+?$^|#\".indexOf(c) != -1)
{
sb.append("\");
}
sb.append(c);
}
return sb.toString();
}
If you really want to use ?for the wildcard, your best bet would be to remove it from the list of metacharacters in the quotemeta()method. Replacing its escaped form -- replace("\\?", ".")-- wouldn't be safe because there might be backslashes in the original expression.
如果您真的想使用?通配符，最好的办法是将它从quotemeta()方法中的元字符列表中删除。替换其转义形式 -- replace("\\?", ".")-- 不安全，因为原始表达式中可能存在反斜杠。
And that brings us to the real problems: most SQL flavors seem to support character classes in the forms [a-z]and [^j-m]or [!j-m], and they all provide a way to escape wildcard characters. The latter is usually done by means of an ESCAPEkeyword, which lets you define a different escape character every time. As you can imagine, this complicates things quite a bit. Converting to a regex is probably still the best option, but parsing the original expression will be much harder--in fact, the first thing you would have to do is formalize the syntax of the LIKE-like expressions themselves.
这给我们带来了真正的问题：大多数 SQL 风格似乎都支持形式[a-z]和[^j-m]或中的字符类[!j-m]，并且它们都提供了一种转义通配符的方法。后者通常通过ESCAPE关键字来完成，它可以让您每次定义不同的转义字符。可以想象，这使事情变得相当复杂。转换为正则表达式可能仍然是最好的选择，但解析原始表达式会困难得多——事实上，您必须做的第一件事就是将LIKE-like 表达式本身的语法形式化。
回答by OscarRyz
Apache Cayanne ORM has an "In memory evaluation"
Apache Cayanne ORM 有一个“内存评估”
It may not work for unmapped object, but looks promising:
它可能不适用于未映射的对象，但看起来很有希望：
Expression exp = ExpressionFactory.likeExp("artistName", "A%");
List startWithA = exp.filterObjects(artists);
回答by John O
The Comparatorand Comparableinterfaces are likely inapplicable here. They deal with sorting, and return integers of either sign, or 0. Your operation is about finding matches, and returning true/false. That's different.
该比较器和可比较的接口很可能不适用在这里。它们处理排序，并返回任一符号或 0 的整数。您的操作是关于查找匹配项，并返回真/假。那不一样。
回答by Rich MacDonald
http://josql.sourceforge.net/has what you need. Look for org.josql.expressions.LikeExpression.
http://josql.sourceforge.net/有你需要的东西。查找 org.josql.expressions.LikeExpression。
