spring - Spring Boot JPA 中的全文搜索

Question

我正在实现基于姓氏的全文搜索，出现以下语法错误。请帮我解决这个问题

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

下面是语法错误

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

Answer 1

根据评论，JPQL 不能使用来自特定供应商的 native 函数或关键字，例如 MATCH/AGAINST。为了使用它，您需要使用 native 查询:

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

注意:我在这里假设列名和表名，因为它们不存在于问题中。随时更新到正确的。此外，不返回 Object[]，而是返回实体本身。