英文:
Dynamic search term SQL query with Spring JPA or QueryDSL
问题
我正在尝试学习 QueryDSL，以便从 Postgres 返回单个搜索词的结果：
@GetMapping("/product/contains/{matchingWords}")
public List<ProductModel> findByTitleContaining(@PathVariable String matchingWords) {
QProductModel product = QProductModel.productModel;
JPAQuery<?> query = new JPAQuery<>(em);
List<ProductModel> products = query.select(product)
.from(product)
.where(product.title.toLowerCase()
.contains(matchingWords.toLowerCase()))
.fetch();
return products;
}
但我还想搜索任意数量的搜索词，例如，假设这是我用加号分隔的搜索词列表：
String[] params = matchingWords.split("[+]");
如何在 QueryDSL 或任何 Java/Spring 查询框架中根据变量数量的参数动态创建 contains(params[0]) AND/OR contains(params[1]) AND/OR ... contains(params[n]) 查询？我看到 QueryDSL 有一个谓词系统，但我仍然不明白如何基于同一列中的可变数量参数动态创建查询。
英文:
I am trying to learn QueryDSL in order to return the results of a single search term from Postgres:
@GetMapping("/product/contains/{matchingWords}")
public List<ProductModel> findByTitleContaining(@PathVariable String matchingWords) {
QProductModel product = QProductModel.productModel;
JPAQuery<?> query = new JPAQuery<>(em);
List<ProductModel> products = query.select(product)
.from(product)
.where(product.title.toLowerCase()
.contains(matchingWords.toLowerCase()))
.fetch();
return products;
}
But I also want to search for any number of search terms, for example, say this is my list of search terms divided by the plus symbol:
String[] params = matchingWords.split("[+]");
How can I dynamically create contains(params[0]) AND/OR contains(params[1] AND/OR ... contains(params[n]) using either QueryDSL or any Java/Spring query framework? I see QueryDSL has a predicate system, but I still don't understand how to dynamically create a query based on a variable number of parameters searching in the same column.
答案1
得分: 1
我明白了。这有点不直观，但是使用BooleanBuilder和JPAQuery<?>，你可以创建一个动态的布尔谓词系列，它返回一个匹配项列表。
示例：
QProductModel product = QProductModel.productModel;
JPAQuery<?> query = new JPAQuery<>(//在这里放入实体管理器//);
// 以+分隔的搜索参数示例列表
String[] params = matchingWords.split("[+]");
BooleanBuilder builder = new BooleanBuilder();
for(String param : params) {
builder.or(product.title.containsIgnoreCase(param));
}
// 然后你可以像这样组装查询：
List<ProductModel> products = query.select(product)
.from(product)
.where(builder)
.fetch();
return products;
英文:
I figured it out. It's a little non-intuitive, but using BooleanBuilder and JPAQuery<?> you can create a dynamic series of boolean predicates, which return a list of matches.
Example:
QProductModel product = QProductModel.productModel;
JPAQuery<?> query = new JPAQuery<>(//entity manager goes here//);
// example list of search parameters separated by +
String[] params = matchingWords.split("[+]");
BooleanBuilder builder = new BooleanBuilder();
for(String param : params) {
builder.or(product.title.containsIgnoreCase(param));
}
// then you can put together the query like so:
List<ProductModel> products = query.select(product)
.from(product)
.where(builder)
.fetch();
return products;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论