Search code examples
jpafull-text-searcheclipselink

How to use MySQL's full text search from JPA

I want to use MySQL's full text search features using JPA, without having to use a native query.

I am using EclipseLink, which has a function to support native SQL commands: FUNC. However, the help examples only show this being use with simple MySQL functions. My best effort attempt to get it to work with MATCH & AGAINST is as follows:

@PersistenceContext(name="test")
EntityManager em;

Query query = em.createQuery("SELECT person FROM People person WHERE FUNC('MATCH', person.name) FUNC('AGAINST', :searchTerm)");
...
query.getResultList();

Which gives the following exception:

Caused by: NoViableAltException(32@[()* loopback of 822:9: (m= MULTIPLY right= arithmeticFactor | d= DIVIDE right= arithmeticFactor )*])
    at org.eclipse.persistence.internal.libraries.antlr.runtime.DFA.noViableAlt(DFA.java:159)
    at org.eclipse.persistence.internal.libraries.antlr.runtime.DFA.predict(DFA.java:116)
    at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticTerm(JPQLParser.java:4557)
    ... 120 more

I am open to alternatives other that using the FUNC method.

I am using EJB 3 and EclipseLink 2.3.1.

Solution

  • FUNC only works with normal printed functions,

    i.e. MATCH(arg1, arg2)

    since MATCH arg1 AGAINST arg2 is not printed the way a function is normally printed, FUNC cannot be used to call it.

    EclipseLink ExpressionOperators do support printing functions like this, so you could define your own ExpressionOperator, but ExpressionOperators are only supported through EclipseLink Expression queries currently, not through JPQL. You could log an enhancement to have operator support in JPQL.

    You could also use a native SQL query.