How to implement simple full text search in JPA (Spring Data JPA)?
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());
Robert Niestroj
I'am a Java (Wicket, Wildfly, Hibernate, JPA, Spring, Spring Data) and JavaScript (Vue, ES6, Quasar) Developer.
Updated on July 23, 2022Comments
-
Robert Niestroj almost 2 years
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:
- Load all users users from DB and filter them in Java
- Write a JPQL query with many
ORs
andLIKE % :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?