How to use MySQL's full text search from JPA

Question

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.

Rojas

Have you tried with createNativeQuery? – Aurangzeb

Answer 1

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.

Properly

can you give any example of this answer or can elaborate a bit – Lonnie

Answer 2

An improved answer of @Markus Barthlen which works for Hibernate.

Create custom dialect

public class MySQLDialectCustom extends MySQL5Dialect {
  public MySQLDialect() {
    super();
    registerFunction("match", new SQLFunctionTemplate(StandardBasicTypes.DOUBLE,
        "match(?1) against  (?2 in boolean mode)"));
  }
}

and register it by setting hibernate.dialect property.

Use it

in JPQL:

Query query = entityManager
    .createQuery("select an from Animal an " +
             "where an.type = :animalTypeNo " +
             "and match(an.name, :animalName) > 0", Animal.class)
    .setParameter("animalType", "Mammal")
    .setParameter("animalName", "Tiger");
List<Animal> result = query.getResultList();
return result;

or with Criteria API:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Animal> criteriaQuery = criteriaBuilder.createQuery(Animal.class);
Root<Animal> root = criteriaQuery.from(Animal.class);

List<Predicate> predicates = new ArrayList<>();

Expression<Double> match = criteriaBuilder.function("match", Double.class, root.get("name"),
criteriaBuilder.parameter(String.class, "animalName"));

predicates.add(criteriaBuilder.equal(root.get("animalType"), "Mammal"));
predicates.add(criteriaBuilder.greaterThan(match, 0.));

criteriaQuery.where(predicates.toArray(new Predicate[]{}));

TypedQuery<Animal> query = entityManager.createQuery(criteriaQuery);
List<Animal> result = query.setParameter("animalName", "Tiger").getResultList();

return result;

Some more details in this blog post: http://pavelmakhov.com/2016/09/jpa-custom-function

Inescutcheon

I wonder if there is still no concise and non-invasive solution for this in 2019, like we have with older API: ftsCriteria.add(Restrictions.sqlRestriction("MATCH ( {alias}.fts ) AGAINST (?)", text, StringType.INSTANCE))? – Oscoumbrian

Answer 3

Just to complete the answer: I had the same problem, but using the criteria builder. This is how you can get around the limitations in the standart implementation, if you are using EclipseLink:

1. Cast JPA expression to EclipseLink expression
2. Use the sql method
3. If you match against a compound index, create it using the function method

Example:

    JpaCriteriaBuilder cb = (JpaCriteriaBuilder) cb;

    List<String> args = new ArrayList();
    args.add("Keyword");

    Expression<Boolean> expr = cb.fromExpression (
      cb.toExpression(
         cb.function("", String.class,
           table.get(Table_.text1), table.get(Table_.text2)) 
      )                          
       .sql("MATCH ? AGAINST (?)", args)
     );

    query.where(expr);

If you need to cast the expression to a predicate use the following:

query.where( cb.gt(expr, 0));

Endomorphic

Answer 4

What about new SQL operator in EclipseLink 4.0? I think it can help you to do fulltext search from JPQL. But you have to upgrade to EclipseLink 4.0.

http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Support_for_Native_Database_Functions#SQL

Edit:
Sorry for late update.
Verified correct use of EclispeLink 2.4.0 "SQL" operator with MySQL fulltext search is

SELECT person FROM People person WHERE SQL('MATCH(name) AGAINST( ? )', :searchTerm)"

where name is column on which Fulltext index is defined. :searchTerm is string you use for searching.
Works without problems.

Faria

How do I use the 'new' operator in this circumstance? I can't see how – Rojas

Im going to try this particular use case of "SQL" operator myself in 2-3 days. If I will be successful I will edit my answer with exact solution. For start I've got idea that it should be used something like "FUNC" operator: "SELECT person FROM People person WHERE SQL('MATCH(name) AGAINST( ? )', :searchTerm)" – Faria

Answer 5

To elaborate on the answer of James:

It seems like I had luck extending the mysql dialect using

registerFunction("match", new SQLFunctionTemplate(DoubleType.INSTANCE,     "match(?1) against  (?2 in boolean mode)")); 

and invoking the function via the following jpql fragment

match(" + binaryDataColumn + ",'" + StringUtils.join(words, " ") + "') > 0 

I had to guess the return type, but this should get you started.

Hinayana

Answer 6

FInally work

if you set your table colums wit index full search

@NamedNativeQuery(name = "searchclient", query = "SELECT * FROM client WHERE MATCH(clientFullName, lastname, secondname, firstphone," + " secondphone, workphone, otherphone, otherphone1," + " otherphone2, detailsFromClient, email, company," + " address, contractType, paymantCondition) AGAINST(?)",

List list = em.createNamedQuery("searchclient").setParameter(1, searchKey).getResultList();

Coniah

Answer 7

The simplest variant is to use NativeQuery

Example of use it with mapping to JPA entity (FiasAddress):

public class FiasServiceBean implements FiasService {

    @PersistenceContext(unitName = "fias")
    EntityManager entityManager;

    @Override
    public Collection<FiasAddress> search(String name, int limit, int aolevel) {
        Query query = entityManager.createNativeQuery(
                "SELECT fa.* FROM fias.addressobject fa" +
                        " WHERE MATCH(FORMALNAME) AGAINST (:name IN NATURAL LANGUAGE MODE)" +
                        " AND AOLEVEL = :AOLEVEL" +
                        " LIMIT :limit",
                FiasAddress.class
        );
        query.setParameter("name", name);
        query.setParameter("limit", limit);
        query.setParameter("AOLEVEL", aolevel);
        Iterator iterator = query.getResultList().iterator();
        ArrayList<FiasAddress> result = new ArrayList<>();
        while (iterator.hasNext()) {
            result.add((FiasAddress) iterator.next());
        }
        return result;
    }
}

Japha