java 如何在JPA（Spring Data JPA）中实现简单的全文搜索？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33917442/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to implement simple full text search in JPA (Spring Data JPA)?
提问by Robert Niestroj
i'm using JPA 2.1 (Hibernate 4 as impl) and Spring Data JPA 1.9.0. How do i implement full text search?
我正在使用 JPA 2.1（Hibernate 4 作为 impl）和 Spring Data JPA 1.9.0。如何实现全文搜索？
My scenario is as follows. I have a Userentity 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.
我的情况如下。我有一个User实体，在 UI 上有一个表格，其中显示了大部分用户属性，我希望用户给文本框输入一个搜索词并在所有属性中搜索。
I see 2 options to do this:
我看到 2 个选项可以做到这一点：
- Load all users users from DB and filter them in Java
- Write a JPQL query with many
ORsandLIKE % :searchString %
- 从数据库加载所有用户用户并在 Java 中过滤它们
- 编写一个包含许多
ORs和的 JPQL 查询LIKE % :searchString %
Option 1is not good for performance but quite nice to write.
选项 1对性能不利，但写起来很好。
Option 2is performant beacuse executed on DB side but cumbersome to write.
选项 2是高性能的，因为在 DB 端执行但编写起来很麻烦。
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.
现在我正在起诉选项 1，因为我需要将布尔值转换为"yes"/"no"并且还有一个配置文件枚举，我想通过它的字段描述而不是实际的枚举值进行搜索。
In the User entity i have a method which returns all fields i want to be searched seperated by spaces:
在 User 实体中，我有一个方法可以返回我想用空格分隔搜索的所有字段：
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".
另一方面，在 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);
Is there a better solution to this problem?
这个问题有更好的解决方案吗？
回答by Robert Niestroj
Solved this by saving the search string on every persist and update to the DB. First created a column for the searchString:
通过在每次持久化和更新到数据库时保存搜索字符串来解决这个问题。首先为 searchString 创建一列：
@Column(name = "SEARCH_STRING", length = 1000)
private String searchString;
Storage is cheap, overhead on DB is not that big.
存储很便宜，DB 上的开销并不大。
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:
然后我可以有一个普通的 JPQL 查询LIKE：
SELECT u FROM User u WHERE u.searchString LIKE '%' || :text || '%'
Or using Query By Example:
或者使用Query By Example：
ExampleMatcher matcher = ExampleMatcher.matching().
withMatcher("searchString", ExampleMatcher.GenericPropertyMatcher.of(ExampleMatcher.StringMatcher.CONTAINING).ignoreCase());
回答by Dherik
Well, for the most cases, option 1is not a really an alternative. If your application grow up of registers the memory and performance problems will hit you after some time. I guarantee you.
嗯，在大多数情况下，选项 1并不是真正的替代方案。如果您的应用程序随着寄存器的增长而增长，内存和性能问题会在一段时间后打击您。我向你保证。
I cannot see a problem in the option 2. Is not performant but it's simple to understand.
我在选项 2中看不到问题。性能不高，但很容易理解。
If performance on database is a problem, you can create a native query to call the native function of your database to do a full text search. Will not be a JPQL query, but for this kind of query you are trying to do this solution can be used.
如果数据库性能有问题，您可以创建本机查询以调用数据库的本机功能进行全文搜索。不会是 JPQL 查询，但对于您正在尝试执行的此类查询，可以使用此解决方案。
