Java 在 JPA CriteriaBuilder 中使用正则表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24995881/
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
Use Regular Expressions in JPA CriteriaBuilder
提问by jp-jee
I'm using the JPA CriteriaBuilder to select entities of type MyEntityfrom a MySQL db as follows:
我正在使用 JPA CriteriaBuilderMyEntity从 MySQL 数据库中选择类型的实体，如下所示：
String regExp = "(abc|def)"
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery( MyEntity.class );
root = query.from( MyEntity.class );
predicates = new ArrayList<Predicate>();
predicates.add( cb.like( root.<String>get( "name" ), regExp ) );
Thus, the query result should contain any entity where the namevalue matches the given regExp. But the result list is always empty.
Changing the regExp to /(abc|def)/ghas no effect, neither does adding the wildcard %
因此，查询结果应包含name值与给定 regExp 匹配的任何实体。但结果列表始终为空。将 regExp 更改为/(abc|def)/g无效，添加通配符也无效%
How to make the pattern matching work?
如何使模式匹配工作？
Alternatively: How can I use native MySQL REGEXP together with the CriteriaBuilder?
或者：如何将本机 MySQL REGEXP 与 CriteriaBuilder 一起使用？
回答by zbig
Pattern matching in JPA queries is limited only to
JPA 查询中的模式匹配仅限于
_- any character%- any string
_- 任何字符%- 任何字符串
REGEXPhas operator syntax in MySQL (SELECT 'a' REGEXP 'A') so it cannot be used with CriteriaBuilder.function()API. I'm afraid the best is to run native SQL query.
REGEXP在 MySQL ( SELECT 'a' REGEXP 'A') 中有运算符语法，因此它不能与CriteriaBuilder.function()API一起使用。恐怕最好的方法是运行本机 SQL 查询。
If you are using Hibernate you have one more option. You can wrap REGEXPoperator in SQLFunctionTemplate, extend hibernate dialectand run with CriteriaBuilder.function().
如果您正在使用 Hibernate，您还有一种选择。您可以将REGEXP运算符包装在 中SQLFunctionTemplate，扩展休眠方言并使用CriteriaBuilder.function().
回答by LinuxLars
Maybe this snippet will help. We had to exclude characters in a search, and we using Oracle. CriteriaBuilder (at least as of 2.1) will let you call a function.
也许这个片段会有所帮助。我们不得不在搜索中排除字符，我们使用 Oracle。CriteriaBuilder（至少从 2.1 开始）会让你调用一个函数。
private static final Pattern UNDESIRABLES = Pattern.compile("[(){},.;!?<>%_-]");
private static final String UNDESIRABLE_REPLACEMENT = "";
...
In the search method, create a Predicate to use in your where clause:
在搜索方法中，创建一个 Predicate 以在您的 where 子句中使用：
Expression<String> undesirables = cb.literal(UNDESIRABLES.toString());
Expression<String> replaceWith = cb.literal(UNDESIRABLE_REPLACEMENT);
Expression<String> regExp = cb.function("REGEXP_REPLACE", String.class, client.get(Client_.companyName),
undesirables, replaceWith);
Predicate companyNameMatch = cb.equal(cb.trim(cb.lower(regExp)), removeUndesireables(name).trim());
...
And create a method for the right hand comapare that uses the same values as the left:
并为右侧的 comapare 创建一个使用与左侧相同的值的方法：
private String removeUndesireables(String name) {
return UNDESIRABLES.matcher(name).replaceAll(UNDESIRABLE_REPLACEMENT).toLowerCase();
}
回答by Simon B
I came across this recently and used the first post to implement the hibernate mysql function option.
我最近遇到了这个，并使用了第一篇文章来实现hibernate mysql函数选项。
To help save some time for others this is what I did:
为了帮助其他人节省一些时间，这就是我所做的：
set up the function in your custom dialect file in hibernate:
在休眠的自定义方言文件中设置函数：
public class MySQLDialect extends Dialect {
public MySQLDialect() {
super();
...
registerFunction("regexp", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "?1 REGEXP ?2"));
...
}
...
}
then within the criteria builder section:
然后在标准构建器部分：
CriteriaBuilder builder = ...;
Pattern regexPattern = Pattern.compile("^[0-9]\|[0-9]+");
Expression<String> patternExpression = builder.<String>literal(regexPattern.pattern());
Path<String> path = ... ;// regex comparison column
// regexp comes from the name of the regex function
// defined in the Mysql Dialect file above
Predicate theRegexPredicate = builder.equal(builder.function("regexp", Integer.class, path, patternExpression), 1);
Then use theRegexPredicate to construct the where clause in your CriteriaBuilder query.
然后使用 RegexPredicate 在 CriteriaBuilder 查询中构造 where 子句。
