通过在每个持久存储上保存搜索字符串并更新到数据库来解决此问题. 首先为searchString创建一列:

   @Column(name = "SEARCH_STRING", length = 1000)
   private String searchString;

存储很便宜，数据库的开销也不是很大.

然后保存更新并保留:

   @PreUpdate
   @PrePersist
   void updateSearchString() {
      final String fullSearchString = StringUtils.join(Arrays.asList(
              login,
              firstName,
              lastName,
              email,
              Boolean.TRUE.equals(active) ? "tak" : "nie",
              profile.getDescription()),
              " ");
      this.searchString = StringUtils.substring(fullSearchString, 0, 999);
   }

然后我可以使用LIKE进行普通的JPQL查询:

SELECT u FROM User u WHERE u.searchString LIKE '%' || :text || '%'

或使用按示例查询:

  ExampleMatcher matcher = ExampleMatcher.matching().
          withMatcher("searchString", ExampleMatcher.GenericPropertyMatcher.of(ExampleMatcher.StringMatcher.CONTAINING).ignoreCase());

i'm using JPA 2.1 (Hibernate 4 as impl) and Spring Data JPA 1.9.0. How do i implement full text search?

My scenario is as follows. I have a User entity and on the UI a have a table which display's most of users properties and i want the user to give text box enter there a search term and search in all properties.

I see 2 options to do this:

1. Load all users users from DB and filter them in Java
2. Write a JPQL query with many ORs and LIKE % :searchString %

Option 1 is not good for performance but quite nice to write.

Option 2 is performant beacuse executed on DB side but cumbersome to write.

Right now im suing option 1 because i need to translate boolean to "yes"/"no" and also have a profile enum where i want to search by it's field description and not by actual enum value.

In the User entity i have a method which returns all fields i want to be searched seperated by spaces:

   public String getSearchString(){
      return StringUtils.join(
              Arrays.asList(
                      login, 
                      firstName, 
                      lastName, 
                      email, 
                      active ? "yes" : "no", 
                      profile.getDescription())
              , " ");
   }

The in a service i load all users from DB and filter by this search string:

   @Override
   public List<User> getUsers(final String searchText) {
      final List<User> users = getUsers();
      if(StringUtils.isBlank(searchText)){
         return users;
      }
      CollectionUtils.filter(users, new Predicate<User>() {
         @Override
         public boolean evaluate(User object) {
            return StringUtils.containsIgnoreCase(object.getSearchString(), searchText);
         }
      });
      return users;
   }

On the other side in JPQL i end up with queries like this, which i dont think is the nice'est and easiest way to implement this functionality. Also there is a problem with translatin boolean to "yes" and "no".

@Query("SELECT r FROM User r WHERE "
        + "r.firstname LIKE '%' || :searchString || '%' "
        + "OR r.lastname LIKE '%' || :searchString || '%' "
        + "OR r.login LIKE '%' || :searchString || '%' "
        + "OR r.profile.description LIKE '%' || :searchString || '%' "
        + "OR r.active LIKE '%' || :searchString || '%' "
        + "OR r.email LIKE '%' || :searchString || '%'")
List<User> selectUsers(@Param("searchString")String searchString, Pageable page);

Is there a better solution to this problem?

Solved this by saving the search string on every persist and update to the DB. First created a column for the searchString:

   @Column(name = "SEARCH_STRING", length = 1000)
   private String searchString;

Storage is cheap, overhead on DB is not that big.

Then the saving on update and persist:

   @PreUpdate
   @PrePersist
   void updateSearchString() {
      final String fullSearchString = StringUtils.join(Arrays.asList(
              login,
              firstName,
              lastName,
              email,
              Boolean.TRUE.equals(active) ? "tak" : "nie",
              profile.getDescription()),
              " ");
      this.searchString = StringUtils.substring(fullSearchString, 0, 999);
   }

Then i can have a normal JPQL query with LIKE:

SELECT u FROM User u WHERE u.searchString LIKE '%' || :text || '%'

Or using Query By Example:

  ExampleMatcher matcher = ExampleMatcher.matching().
          withMatcher("searchString", ExampleMatcher.GenericPropertyMatcher.of(ExampleMatcher.StringMatcher.CONTAINING).ignoreCase());