如何在 Hibernate HQL 中使用 Oracle 的 regexp_like？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11793159/
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 use Oracle's regexp_like in Hibernate HQL?
提问by manurajhada
I am using oracle 10gand hibernate 3.3.2. I have used regular expression in sql before, now for the first time I am using it in HQL.
我正在使用oracle 10g和hibernate 3.3.2。之前在sql中使用过正则表达式，现在是第一次在HQL中使用。
Query query = getSession().createQuery("From Company company
where company.id!=:companyId and
regexp_like(upper(rtrim(ltrim(company.num))), '^0*514619915$' )");
This is my hql, when i run it without regex_likefunction it runs as expected. But I am not able to execute it with regex_likeexpression.
这是我的 hql，当我在没有regex_like功能的情况下运行它时，它会按预期运行。但我无法用regex_like表达式执行它。
It says..
它说..
nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 66.....
嵌套异常是 org.hibernate.hql.ast.QuerySyntaxException：意外的 AST 节点：（第 1 行附近，第 66 列.....
Kindly help, how can I use regex_likein hibernate native query? OR some other alternative to do so.
请帮忙，我如何regex_like在休眠本机查询中使用？或其他一些替代方法。
回答by Marcelo Liberato
Actually, you can't compare the result of REGEXP_LIKE to anything except in conditional statements in PL/SQL.
实际上，除了 PL/SQL 中的条件语句之外，您无法将 REGEXP_LIKE 的结果与任何内容进行比较。
Hibernate seems to not accept a custom function without a returnType, as you always need to compare the output to something, i.e:
Hibernate 似乎不接受没有 returnType 的自定义函数，因为您总是需要将输出与某些内容进行比较，即：
REGEXP_LIKE('bananas', 'a', 'i') = 1
As Oracle doesn't allow you to compare this function's result to nothing, I came up with a solution using case condition:
由于 Oracle 不允许您将此函数的结果与空进行比较，因此我想出了一个使用 case 条件的解决方案：
public class Oracle10gExtendedDialect extends Oracle10gDialect {
public Oracle10gExtendedDialect() {
super();
registerFunction(
"regexp_like", new SQLFunctionTemplate(StandardBasicTypes.BOOLEAN,
"(case when (regexp_like(?1, ?2, ?3)) then 1 else 0 end)")
);
}
}
And your HQL should look like this:
你的 HQL 应该是这样的：
REGEXP_LIKE('bananas', 'a', 'i') = 1
It will work :)
它会工作:)
回答by Steve Ebersole
You can most definitely use any type of database-specific function you wish with Hibernate HQL (and JPQL as long as Hibernate is the provider). You simply have to tell Hibernate about those functions. In 3.3 the only option for that is to provide a custom Dialect and register the function from the Dialect's constructor. If you take a look at the base Dialect class you will see lots of examples of registering functions. Usually best to extend the exact Dialect you currently use and simply provide your extensions (here, registering the function).
您绝对可以在 Hibernate HQL（和 JPQL，只要 Hibernate 是提供者）中使用您希望的任何类型的特定于数据库的函数。您只需将这些功能告诉 Hibernate。在 3.3 中，唯一的选择是提供自定义方言并从方言的构造函数注册该函数。如果您查看基本的 Dialect 类，您将看到许多注册函数的示例。通常最好扩展您当前使用的确切方言并简单地提供您的扩展（在这里，注册该功能）。
An interesting note is that Oracle does not classify regexp_like as a function. They classify it as a condition/predicate. I think this is mostly because Oracle SQL does not define a BOOLEAN datatype, even though their PL/SQL does and I would bet regexp_like is defined as a PL/SQL function returning BOOLEAN...
一个有趣的注意事项是 Oracle 没有将 regexp_like 归类为函数。他们将其归类为条件/谓词。我认为这主要是因为 Oracle SQL 没有定义 BOOLEAN 数据类型，即使他们的 PL/SQL 定义了并且我敢打赌 regexp_like 被定义为返回 BOOLEAN 的 PL/SQL 函数...
Assuming you currently use Oracle10gDialect, you would do:
假设您当前使用 Oracle10gDialect，您将执行以下操作：
public class MyOracle10gDialect extends Oracle10gDialect {
public Oracle10gDialect() {
super();
registerFunction(
"regexp_like",
new StandardSQLFunction( "regexp_like", StandardBasicTypes.BOOLEAN )
);
}
}
I cannot remember if the HQL parser likes functions returning booleans however in terms of being a predicate all by itself. You may instead have to convert true/false to something else and check against that return:
我不记得 HQL 解析器是否喜欢返回布尔值的函数，但是就其本身而言，它是一个谓词。您可能必须将 true/false 转换为其他内容并检查该返回：
public class MyOracle10gDialect extends Oracle10gDialect {
public Oracle10gDialect() {
super();
registerFunction(
"regexp_like",
new StandardSQLFunction( "regexp_like", StandardBasicTypes.INTEGER ) {
@Override
public String render(
Type firstArgumentType,
List arguments,
SessionFactoryImplementor factory) {
return "some_conversion_from_boolean_to_int(" +
super.render( firstArgumentType, arguments, factory ) +
")";
}
}
);
}
}
回答by Big Ed
You might try using the standard LIKE operator:
您可以尝试使用标准的 LIKE 运算符：
where company.num like '%514619915'
and then filter out the unwanted ones using a Java regex. That should reduce the number of unneeded rows that would be returned.
然后使用 Java 正则表达式过滤掉不需要的。这应该会减少将返回的不需要的行数。
This would not use an index because it begins with a '%'.
这不会使用索引，因为它以“%”开头。
回答by Augusto
You can't access specific database functions unless JPAQL/HQL provide a way to do so, and neither provide anything for regular expressions. So you'll need to write a native SQL query to use regexes.
您不能访问特定的数据库函数，除非 JPAQL/HQL 提供了一种方法，并且既不为正则表达式提供任何内容。因此，您需要编写本机 SQL 查询以使用正则表达式。
On another, and very important point, a few colleagues (Oracle DBAs) told me to never use regexes in oracle, as they can't be indexed, which ends up in the DB performing a full DB scan. If the table has a few entries, then it's ok, but if it has lots of rows, it might cripple the performance.
在另一个非常重要的一点上，一些同事（Oracle DBA）告诉我永远不要在 oracle 中使用正则表达式，因为它们不能被索引，这最终导致 DB 执行完整的 DB 扫描。如果表有几个条目，那么没关系，但是如果它有很多行，它可能会削弱性能。
回答by Kisanagaram
For those using Hibernate criterion with sqlRestriction (Hibernate Version 4.2.7)
对于那些使用带有 sqlRestriction 的 Hibernate 标准的人（Hibernate 版本 4.2.7）
Criterion someCriterion = Restrictions.sqlRestriction("regexp_like (column_name, ?, 'i')", "(^|\s)"+searchValue+"($|\s|.$)", StringType.INSTANCE);
回答by Chris
Or another option is to create similar function in oracle which will return numeric value based on operation result. Something like that
或者另一种选择是在 oracle 中创建类似的函数，它将根据操作结果返回数值。类似的东西
CREATE OR REPLACE FUNCTION MY_REGEXP_LIKE(text VARCHAR2, pattern VARCHAR2)
RETURN NUMBER
IS function_result NUMBER;
BEGIN
function_result := CASE WHEN REGEXP_LIKE(text, pattern)
THEN 1
ELSE 0
END;
RETURN(function_result);
END MY_REGEXP_LIKE;
and you will be able to use
你将能够使用
MY_REGEXP_LIKE('bananas', 'a') = 1
