如何在JPA条件构建器中使用本机db运算符(postgres〜)? [英] How to use a native db operator (postgres ~ ) with JPA criteria builder?

问题描述

我正在使用JPA 2.0标准来构建以下查询(简化):

I am using JPA 2.0 criteria to build the following query (simplified):

select n from notif n where n.message ~ 'b.*la'

我正在使用postgresql db，我真的需要〜运算符，而不是喜欢.我可以使用与CriteriaBuilder.function等效的东西吗?另外，在postgres中有〜运算符的函数形式，所以我可以使用提到的cb.function方法.我只找到了postgresql regexp_matches函数，但它返回的是一个不为布尔值的匹配数组.

I am using postgresql db, and I really need the ~ operator,not like. Is there something equivalent to the CriteriaBuilder.function I can use? Alternatively, is there a function form of the ~ operator in postgres so I can use the mentioned cb.function method. I have only found the postgresql regexp_matches function, but it returns an array of matches not a boolean.

解决方案: 由于不可能将标准API转换为JPQL，因此我最终编写了一个postgres函数:

Solution: Since moving from the criteria API to JPQL was out of the question, I ended up writing a postgres function:

'CREATE OR REPLACE FUNCTION "regexp_search"(character varying,character varying) RETURNS boolean AS \'select $1 ~ $2;\' LANGUAGE sql;'

并使用cb.function调用它:

And calling it with cb.function:

Expression<Boolean> regexp_search = cb.function("regexp_search", Boolean.class, message,cb.literal(re));

推荐答案

REGEXP或〜不属于JPQL标准.您可以使用本机SQL查询.

REGEXP or ~ are not part of the JPQL standard. You could use a native SQL query.

如果使用EclipseLink，则支持REGEXP，并且可以在Postgres上使用

If using EclipseLink, REGEXP is supported, and will work on Postgres,

http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/j_regexp.htm#regexp

您还可以使用SQL函数在JPQL中调用任何SQL特定的语法，

You can also use the SQL function to call any SQL specific syntax inside JPQL,

http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/j_sql.htm#sql

这篇关于如何在JPA条件构建器中使用本机db运算符(postgres〜)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！