Full Text Search in Spring Boot JPA

user_13050732 0
Viewed 2016

I am implementing full text search based on lastName, I am getting below syntax error. Please help me on this

public interface FullTextSearchEmployeeRepository extends JpaRepository<Employee, Integer> {
        @Query("SELECT emp FROM Employee emp WHERE MATCH (emp.firstName, emp.address, emp.passportNo) AGAINST (:lastName IN NATURAL LANGUAGE MODE)")
        public List<Object[]> findFullTextSearchByLastName(@Param("lastName") String lastName);
    }

Below is the syntax error

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token:
AGAINST near line 1,  column 217 [SELECT emp FROM com.model.Employee
emp WHERE MATCH (emp.firstName, emp.address, emp.passportNo)  AGAINST
(:lastName IN NATURAL LANGUAGE MODE)]
spring-data-jpa spring-boot hibernate spring
2 Answers

Following the comments, JPQL cannot use native functions or keywords from a specific vendor such as MATCH / AGAINST. In order to use such, you need to use native queries:

public interface FullTextSearchEmployeeRepository extends 
    JpaRepository<Employee, Integer> {

    @Query(value = "SELECT emp.* FROM employee emp WHERE MATCH (emp.first_name, emp.address, emp.passport_no) AGAINST (:lastName IN NATURAL LANGUAGE MODE)", nativeQuery = true)
    public List<Employee> findFullTextSearchByLastName(@Param("lastName") String lastName);
}

NOTE: I am assuming the column names and table name here because they are not present in the question. Feel free to update to the correct ones. Also, instead of returning an Object[], return the entity itself.

user_31548830

Queries in @Query annotations of Spring Data JPA are assumed to be JPQL queries. JPQL does not support full text search.

You can set nativeQuery=true if you want to use SQL (in the dialect of the database you are using), but this still doesn't look like a valid query, because your select list contains the table alias, which is fine in JPQL but SQL requires a list of expressions (columns most of the time) or a *.

Jens_Schauder_666860
Related