how to use Oracle's regexp_like in Hibernate HQL?

user_1411866 0
Viewed 18842

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.

oracle hql hibernate sql java
7 Answers

You can use Specification.

 Specification<YourEntity> specification = (root, query, builder) -> builder.equal(builder.selectCase()
            .when(builder.function("regexp_like", Boolean.class, root.get("your_field"), builder.literal("^0*514619915$")), 1)
            .otherwise(0), 1);
    List<YourEntity> yourEntities = yourEntityRepository.findAll(specification);
user_91585690
user_93758380
Related