How to implement 'regexp' operator for MySQL Dialect?

Hibernate ORM
1

Greetings!

Please, help.

I tried to figure out with custom dialect for regexp function.

I did this:

public class CustomMySQLDialect extends MySQLDialect {
    public CustomMySQLDialect() {
        super();
        registerFunction("regexp", new SQLFunctionTemplate(IntegerType.INSTANCE, "?1 regexp ?2"));
    }
}

But I still have a problem with my filter:

 @Filter(name = FILTER_CATALOG, condition = "url regexp '^/catalog/$'")

ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘seoprimary0_.regexp ‘^/catalog/$’ order by rand() limit 1’ at line 1

2

The @Filter annotation takes an SQL clause ad explained in the User Guide, so you don’t need to register that function in the Dialect.

That would only be needed if you want to reference the function in JPQL.

The problem you are facing is because of a bug that got fixed. Just update to the latest 5.2.16 version.

3

Vlad, thank you for a reply.

I updated hibernate:

INFO: HHH000412: Hibernate Core {5.2.16.Final}

But still no success:
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘seoprimary0_.regexp ‘^/catalog/$’ order by rand() limit 1’ at line 1

4

That was fixed for @Where. In this case, it looks like Hibernate thinks that the regexp is a column as well.

Try to replicate it with this test case template, and then open a Jira ticket.

Meanwhile, you can try to workaround it like this:

@Filter(
    name="FILTER_CATALOG",
    condition="{sp}.url= :active {empty}regexp '^/catalog/$'",
    aliases = {
        @SqlFragmentAlias( alias = "sp", table= "seoprimary"),
        @SqlFragmentAlias( alias = "empty", table= "")
    }
)

Maybe it works.