Geek Answers Handbook

How to implement simple full-text search in JPA (Spring Data JPA)?

I am using JPA 2.1 (Hibernate 4 as impl) and Spring Data JPA 1.9.0. How to implement full-text search?

My scenario is as follows. I have a User object, and in the user interface there is a table that displays most of the properties of users, and I want the user to provide a text box, enter the search term there and search in all the properties.

I see 2 options for this:

  • Download all user users from the database and filter them in Java
  • Write a JPQL query with many ORs and LIKE % :searchString %

Option 1 is not suitable for performance, but pretty nice to write.

Option 2 is an executable beacuse, executable on the database side, but cumbersome to write.

Currently, option 1 is assigned to me, because I need to translate boolean to "yes"/"no" , and also have an enumeration of the profile where I want to search by its description of the field, and not by the actual value of the enumeration.

In the User object, I have a method that returns all the fields that I want to find, separated by spaces:

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

In the service, I load all users from the database and filter 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 hand, in JPQL I get queries that I think are the easiest and easiest way to implement this function. There is also a problem with translatin boolean on "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?

+6
source share
2 answers

This was resolved by storing the search string on every tenacity and updating the database. First created a column for searchString:

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

Storage is cheap, DB overhead is not that big.

Then save on upgrade and save:

  @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 the Request example :

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

Well, in most cases, option 1 is not an alternative. If your application grows out of registers, memory and performance issues will hit you after a while. I guarantee you.

I do not see a problem in option 2 . It is not a performer, but simply understandable.

If database performance is a problem, you can create your own query to call your database’s own full-text search function. There will be no JPQL query, but for the type of query you are trying to make, this solution can be used.

0
source

More articles:


All Articles