如何在Hibernate HQL中使用Oracle的regexp_like? [英] how to use Oracle's regexp_like in Hibernate 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 $')) ;
这是我的hql,当我运行它时没有 regex_like
函数按预期运行。但我无法用 regex_like
表达式执行它。
它说..
嵌套异常是org.hibernate.hql。 ast.QuerySyntaxException:
意外的AST节点:(靠近第1行,第66列.....
请帮忙,我如何在hibernate本地查询中使用 regex_like
?或者可以使用>
Hibernate似乎不接受自定义函数没有returnType,因为你总是需要比较输出的东西,即:
pre $ REGEXP_LIKE('bananas','a ','i')= 1
由于Oracle不允许您将此函数的结果与没有,我想出了一个使用case条件的解决方案:
public class Oracle10gExtendedDialect extends Oracle10gDialect {
公共区域c Oracle10gExtendedDialect(){
super();
registerFunction(
regexp_like,new SQLFunctionTemplate(StandardBasicTypes.BOOLEAN,
(case(when regexp_like(?1,?2,?3))then 1 else 0 end))
);
}
}
你的HQL应该看起来像这样:
REGEXP_LIKE('bananas','a','i')= 1
它可以工作:)
I am using oracle 10g
and hibernate 3.3.2
. I have used regular expression in sql before, now for the first time I am using it in 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_like
function it runs as expected. But I am not able to execute it with regex_like
expression.
It says..
nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 66.....
Kindly help, how can I use regex_like
in hibernate native query? OR some other alternative to do so.
Actually, you can't compare the result of REGEXP_LIKE to anything except in conditional statements in PL/SQL.
Hibernate seems to not accept a custom function without a returnType, as you always need to compare the output to something, i.e:
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:
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:
REGEXP_LIKE('bananas', 'a', 'i') = 1
It will work :)
这篇关于如何在Hibernate HQL中使用Oracle的regexp_like?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!