Spring JPA Specification Full Text Search with MYSQL

Question

I am attempting to create a full text search using a Spring JPA specification. The query I am trying to accomplish is below.

SELECT * FROM Station WHERE MATCH(Slogan, Description) AGAINST('searchText' IN BOOLEAN MODE);

How do I write this query in a Spring JPA specification which allows me to pass in my "searchText" value? This is my attempt thus far.

public static Specification<Station> fullTextSearch(String searchText) {
    if (searchText == null) {
        return null;
    }

    return (Root<Station> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {

        List<Predicate> predicates = new ArrayList<>();
        Expression<Double> match = cb.function("match", Double.class,
                root.get("description"),
                cb.parameter(String.class, "searchText"));

        // TODO: How do I set "searchText"?????

        predicates.add(cb.greaterThan(match, 0.));
        return cb.and(predicates.toArray(new Predicate[]{}));
    };
}

Then it can be used by calling:

 Specifications<Station> spec = Specifications.where(StationSpecifications.fullTextSearch(query));
 stationRepository.findAll(spec, pageable);

Article used for my first attempt: http://pavelmakhov.com/2016/09/jpa-custom-function

score 4 · Accepted Answer · answered Jan 09 '18 at 17:31

The solution I came up with wasn't ideal, but is based on @dimirsen suggestion. The pagable object is added to the query and is working as expected.

@Query(value = "SELECT * FROM Station s WHERE MATCH(s.slogan, s.description) AGAINST(?1 IN BOOLEAN MODE) ORDER BY ?#{#pageable}",
        countQuery = "SELECT * FROM Station s WHERE MATCH(s.slogan, s.description) AGAINST(?1 IN BOOLEAN MODE)",
        nativeQuery = true)
Page<Station> fullTextSearch(String searchText, Pageable pageable);

score 1 · Answer 2 · answered Jan 09 '18 at 09:14

Looks like Jpa Specification API doesn't allow this. Method toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) has a query param which doesn't let inject a parameter, it's not compatible. And you don't have access to entityManager. What about using native MySQL query like (please notice nativeQuery = true ):

@Query(value = "SELECT * FROM accounts WHERE (COALESCE(first_name,'') LIKE %:firstName% AND COALESCE(last_name,'') LIKE %:lastName%)", nativeQuery = true)
public List<Account> searchByFirstnameAndLastname(@Param("firstName")String firstName,@Param("lastName")String lastName);

?

Spring JPA Specification Full Text Search with MYSQL

2 Answers2