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

Sixthpoint · Accepted Answer · 2018-01-09 17:31:58Z

4

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);

answered Jan 9, 2018 at 17:31

Sixthpoint

1,1933 gold badges14 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dimirsen Z · Accepted Answer · 2018-01-09 09:14:29Z

1

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);

?

answered Jan 9, 2018 at 9:14

dimirsen Z

89114 silver badges18 bronze badges

Collectives™ on Stack Overflow

Spring JPA Specification Full Text Search with MYSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related