如何在JPA(Spring Data JPA)中实现简单的全文本搜索? [英] How to implement simple full text search in JPA (Spring Data JPA)?
问题描述
我的情况如下.我有一个User实体，在UI上有一个显示大多数用户属性的表，我希望用户在文本框中输入搜索词并搜索所有属性.
我看到2种方法可以做到这一点:
- 从数据库加载所有用户，并用Java过滤用户
- 写一个包含许多
ORs和LIKE % :searchString %的JPQL查询
选项1 不利于性能，但编写起来相当不错.
选项2 是高性能的，因为在DB端执行但编写起来很麻烦.
现在立即执行选项1，因为我需要将布尔值转换为"yes"/"no"，并且还需要一个配置文件枚举，我想在其中按字段描述而不是实际枚举值进行搜索.
在User实体中，我有一个方法，该方法返回要搜索的所有字段，并用空格分隔:
public String getSearchString(){
return StringUtils.join(
Arrays.asList(
login,
firstName,
lastName,
email,
active ? "yes" : "no",
profile.getDescription())
, " ");
}
在一项服务中，我从数据库加载了所有用户，并通过此搜索字符串进行了过滤:
@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;
}
在JPQL的另一端，我最终遇到这样的查询，我认为这不是实现此功能的最好，最简单的方法.将布尔值转换为是"和否"也存在问题.
@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);
有更好的解决方案吗?
通过在每个持久存储上保存搜索字符串并更新到数据库来解决此问题. 首先为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:
- Load all users users from DB and filter them in Java
- Write a JPQL query with many
ORsandLIKE % :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());
这篇关于如何在JPA(Spring Data JPA)中实现简单的全文本搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
