How to use oracle REGEXP_LIKE() in querydsl?

Question

I want to search string in Oracle db with diacritic, for i.e: input "o" then search "o" as well as "o", "ö" or "ô".
I can do it with the REGEXP_LIKE() function in native SQL with the Base Letter Operator [==] as following:

where REGEXP_LIKE(<column_name>,'[[=o=]]')

However, now, I dont know how to do it with QueryDSL. I tried the solution @https://github.com/querydsl/querydsl/issues/1713 but it's not successful.

The current code is as following:

return new JPAQuery<AEntity>(em)
            .from(QAEntity.aEntity)
            .where(Expressions.booleanTemplate("REGEXP_LIKE({0}, {1})", QAEntity.aEntity.name, "[[=o=]]"))
            .orderBy(QAEntity.aEntity.name.asc())
            .fetch();

It throws exception like that:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: ( near line 3, column 18 [select aEntity
from domain.AEntity aEntity
where REGEXP_LIKE(aEntity.name, ?1)
order by aEntity.name asc]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
    at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:268)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:190)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1894)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291)
    ... 123 common frames omitted

Can anybody please suggest some idea?

Michael Piankov · Accepted Answer · 2017-02-08 08:16:30Z

2

You forget quotes near {1}

it should be like

return new JPAQuery<AEntity>(em)
            .from(QAEntity.aEntity)
            .where(Expressions.booleanTemplate("REGEXP_LIKE({0}, '{1}')", QAEntity.aEntity.name, "[[=o=]]"))
            .orderBy(QAEntity.aEntity.name.asc())
            .fetch();

EDIT. I found that if you need to use regexp_like you should specify next

(Expressions.booleanTemplate
                ("function('REGEXP_LIKE', {0}, {1})", QAEntity.aEntity.name, "[[=o=]]"))

edited Feb 8, 2017 at 8:16

answered Feb 8, 2017 at 8:07

Michael Piankov

1,9971 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

doannx Over a year ago

thanks @MichaelPiankov, I tried but the error is still the same

Michael Piankov Over a year ago

I make mistake @doannx. THank you. I add correct answer.

doannx Over a year ago

I tried with your edit, unfortunately, the error still comes as following

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: function (REGEXP_LIKE) near line 3, column 7 [select aEntity from domain.AEntity aEntity where function('REGEXP_LIKE', aEntity.name, ?1) order by aEntity.name asc] 	at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) 	at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91)...

doannx · Accepted Answer · 2017-02-10 08:52:40Z

0

For those who are in the same problem: my final solution is configuring the DB & server (in my case is JBoss) to support linguitics search, then, no need to change my code.

answered Feb 10, 2017 at 8:52

doannx

1,39014 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to use oracle REGEXP_LIKE() in querydsl?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related