HQL doesn't allow REGEXP or RLIKE for MySQL with Hibernate 5

Question

I'm trying to add RLIKE condition to an HQL query but I'm getting "unexpected token: rlike" error message on HQL validation.

I'm using Spring boot with Spring-data and Hibernate 5 over MySQL.

Query:

@Query("from PortEntity p where  p.name rlike '(gigabit)-(\\d)\\/(\\d)' ")
@EntityGraph(attributePaths = {"parentSlot", "parentSlot.device"})
List<PortEntity> findGigabitDevices(Pageable pageable);

And the error log:

01:26:21.033 16751 [restartedMain] DEBUG o.h.hql.internal.ast.ErrorCounter incident: - throwQueryException() : no errors 
01:26:21.035 16753 [restartedMain] DEBUG o.h.h.i.ast.QueryTranslatorImpl incident: - parse() - HQL: from com.***.***.entity.PortEntity p where p.name rlike '(gigabit)-(\d)\/(\d)'  
01:26:21.037 16755 [restartedMain] ERROR o.h.hql.internal.ast.ErrorCounter incident: - line 1:75: unexpected token: rlike 
01:26:21.043 16761 [restartedMain] ERROR o.h.hql.internal.ast.ErrorCounter incident: - line 1:75: unexpected token: rlike 
antlr.NoViableAltException: unexpected token: rlike
        at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:784)
        at org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:316)
        at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:198)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:283)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:186)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141)
        at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
        at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
        at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
        at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:546)
        at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:655)
        at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23)
        at sun.reflect.GeneratedMethodAccessor64.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:347)
        at com.sun.proxy.$Proxy152.createQuery(Unknown Source)
        at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:86)
        at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:62)
        at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:72)
        at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromQueryAnnotation(JpaQueryFactory.java:53)
        at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:144)
        at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:212)
        at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:77)

I've discovered that in new Hibernate versions, HQL is parsed via ANTLR-based parser, and it's grammar just doesn't have REGEX or RLIKE keywords: Hibernate HQL ANTLR grammar

I've tried to switch to the classical HQL parser by adding Hibernate property

"hibernate.query.factory_class" = "org.hibernate.hql.internal.classic.ClassicQueryTranslatorFactory"

but in this case @EntityGraph stops working with message "EntityGraphs cannot be applied queries using the classic QueryTranslator":

01:56:59.412 91498 [http-nio-8080-exec-1] WARN  o.s.w.s.m.m.a.ExceptionHandlerExceptionResolver incident:REQID3302215661531 - Resolved exception caused by Handler execution: org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.QueryException: EntityGraphs cannot be applied queries using the classic QueryTranslator!; nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: EntityGraphs cannot be applied queries using the classic QueryTranslator!

Should something be configured to allow RLIKE / REGEXP usage in Hibernate 5 with ANTLR-based parser, or it's bug?

Ele · Answer 1 · 2017-12-20T12:19:46.173

0

Basically, to use MySQL rlike function in Hibernate we need to create a "SQLFunctionTemplate".

Now, how to do it:

First: Create a class called "AppMySQLDialect" and extends from MySQLDialect then override the empty constructor and finally register the regexp function:

public class AppMySQLDialect extends MySQLDialect {
    public AppMySQLDialect() {
        super();
        /**
         * Function to evaluate regexp in MySQL
         */
        registerFunction("rlike", new SQLFunctionTemplate(Hibernate.INTEGER, "?1 rlike ?2"));
    }
}

Ok, now lets use it as follow:

FROM PortEntity p where rlike(p.name, '(gigabit)-(\\d)\\/(\\d)') = 1

Create your HibernateQuery and execute.

For Hibernate 5 you can check this: https://stackoverflow.com/a/41369853/1715121

edited Dec 20 '17 at 12:19

answered Dec 20 '17 at 00:54

Ele

33,468
7
37
75

Unfortunately, seems it doesn't work for Hibernate 5. As I'm aware, dialect is responsible for generating SQL, but not parsing HQL. Also, Hinernate.Integer was deprecated in 3.6 – Stas Norochevskiy Dec 20 '17 at 12:07
You're setting the new dialect `AppMySQLDialect` in your Hibernate configuration? – Ele Dec 20 '17 at 12:09
Yes, I've double checked the solution you've suggested, but I got same ANTLR parsing error. – Stas Norochevskiy Dec 20 '17 at 12:10
Also has no effect :( It seems to me, the problem is not related to dialect or SQL generation, because Hibernate just cannot parse HQL. I've debugged org.hibernate.hql.internal.antlr.HqlBaseParser and seems it's just a bug. There's no code that checks for registered functions there. I'll try to fire a bug in the Hibernate tracker. – Stas Norochevskiy Dec 20 '17 at 14:27
Thanks. For anyone trying to use this in future, you also need to set the custom class in the Hibernate properties: org.hibernate.jpa.HibernatePersistenceProvider DefaultDS – user1242321 Jan 28 '20 at 19:35

score 0 · Answer 2 · answered Dec 21 '17 at 00:07

0

Seems it's just not possible. Hibernate 5 doesn't work with HQL, but works with JPQL that has a standard. And this standard (implemented by ANTLR parser) just doesn't declare REGEXP / RLIKE support.

JPQL Language Reference

answered Dec 21 '17 at 00:07

Stas Norochevskiy

53
5

HQL doesn't allow REGEXP or RLIKE for MySQL with Hibernate 5

2 Answers2