如何创建一个带分页的动态Spring（Boot）JPA查询？

huangapple
未分类评论40阅读模式
英文:

How to make a dynamic Spring (Boot) JPA query with paging?

问题

为了过于简化这个问题：我们有一个类/表格 Wine（表格"wines"），其中包含以下属性之一：

  • name：String
  • description：String
  • origin：Origin

...其中Origin是另一个类（带有表格"origins"），只有region：String和country：String。

我正在尝试创建一个在Repository中供RestController使用的搜索方法。

在RestController中的方法声明如下：

  1. @GetMapping("/search")
  2. public Wine searchProduct(
  3.         @RequestParam Optional<String> searchTerm,
  4.         @RequestParam Optional<Origin> origin) {
  5.     // ???
  6. }

我现在要做的是：为数据库创建一个查询，如果给定了searchTerm，则使用它，origin也是同理。查询还应该支持分页。
示例：

  1. SELECT * FROM wines JOIN origins ON wines.origin_id = origins.id 
  2.     WHERE (name LIKE $searchTerm OR description LIKE $searchTerm) AND (/*origin check*/)

如果没有提供搜索条件，查询中就不应该有整个"() AND"部分。如果没有提供Origin... 你明白的。

我尝试过的方法：

  1. （天真地）在我的Repository（实现了CrudRepository）中构建一个庞大的查询，就像这里一样。

    Page<Wine> findWinesByNameLikeOrDescriptionLikeAndOriginEquals(..., Pageable pageable);

    • 除了非常丑陋（特别是对于更多的属性），这可能不起作用，因为：
      1. 没有定义OR或AND更重要（没有括号）。
      2. 不知道是否可以直接将一个"Origins"对象放进去让它起作用。
      3. 不知道Pageable是否在这种自定义方法中起作用。

  2. 使用"Specifications and Querydsl"，如spring.io 这里所建议的。

    • 我太愚笨了，无法理解，尤其是在帖子底部的那些Q类，或者开头的下划线类。这似乎对于它应该做的事情来说过于复杂。
    • 而且没有分页选项。但是，有一个可能的解决方法，就像这里所示，但是由于使用那些下划线和/或Q类太过繁琐，所以我没有尝试。

  3. 此外，这里是我从2013年找到的一个示例，我甚至不能从总体上理解，但看起来似乎非常合适。

英文:

To oversimplify the problem: we have a class/table Wine (table "wines") which has, among other attributes:

  • name: String
  • description: String
  • origin: Origin

... where Origin is another class (with table "origins") with just region: String and country: String.

What I'm trying to make is a search method in my repository for the RestController to use.

The method declaration in the RestController is like this:

  1. @GetMapping(&quot;/search&quot;)
  2. public Wine searchProduct(
  3. 		@RequestParam Optional&lt;String&gt; searchTerm,
  4. 		@RequestParam Optional&lt;Origin&gt; origin) {
  5. 	// ???
  6. }

What I'm trying to do now is the following: creating a query for the database where the searchTerm is used if given, same with origin. And it should be pageable.
Example:

  1. SELECT * FROM wines JOIN origins ON wines.origin_id = origins.id 
  2.     WHERE (name LIKE $searchTerm OR description LIKE $searchTerm) AND (/*origin check*/)

If no search term is given, the whole "() AND" part should not be in the query. If no Origin is given... you get it.

Things I've tried:

  1. (Naively) building a massive query in my repository (implements CrudRepository) like here.

    Page&lt;Wine&gt; findWinesByNameLikeOrDescriptionLikeAndOriginEquals(..., Pageable pageable);

    • Which (apart from being super ugly, especially for more attributes) probably doesn't work because:
      1. It's not defined whether OR or AND are more important (no brackets).
      2. No idea whether I can just shove an "Origins" object in there for it to work.
      3. No idea whether Pageable even works if that's a custom method.

  2. Using "Specifications and Querydsl" as suggested by spring.io here.

    • Which I'm just to stupid to understand, especially with those Q classes at the bottom of the post or the _ classes in the beginning. It just seems way too compicated for what it should do.
    • Also there are no pagination options. There is, however, a possible fix like here but I didn't get to it since using those _ and/or Q classes was to much of an overhead to start with.

  3. Also this is just an example I found from 2013 which I don't even understand in general but kinda looks like it would be very fitting.

答案1

得分: 3

如果你发现很难理解规格说明，只需使用这两个搜索参数，并假设Wine实体与Origin有@ManyToOne关系，你可以进行以下操作：

  1.     @Query("SELECT w FROM wines w join w.origin o where " +
  2.             "(   :searchTerm is null or " +
  3.             "    w.name like CONCAT('%',:searchTerm ,'%') or " +
  4.             "    w.description like CONCAT('%', :searchTerm ,'%')) " +
  5.             "AND (:origin is null or  o = :origin)")
  6.     Page<Wine> searchWines(String searchTerm, Origin origin, Pageable p);

当你调用它时，

  1.     wineRepository.searchWines(searchTerm.orElse(null), origin.orElse(null));
英文:

If you find it difficult to understand Specifications, only have those two search parameters and assuming Wine entity has a @ManyToOne to Origin, you can do the following:

  1.     @Query(&quot;SELECT w FROM wines w join w.origin o where &quot; +
  2.             &quot;(   :searchTerm is null or &quot; +
  3.             &quot;    w.name like CONCAT(&#39;%&#39;,:searchTerm ,&#39;%&#39;) or &quot; +
  4.             &quot;    w.description like CONCAT(&#39;%&#39;, :searchTerm ,&#39;%&#39;)&quot; +
  5.             &quot;) &quot; +
  6.             &quot;AND (:origin is null or  o = :origin)&quot;)
  7.     Page&lt;Wine&gt; searchWines(String searchTerm, Origin origin, Pageable p);

And when you call it,

  1.     wineRepository.searchWines(searchTerm.orElse(null), origin.orElse(null));

答案2

得分: 2

我实际上已经有一个可行的解决方案了！对于好奇的人，以下是我是如何做到的：

ProductController.java

  1. @GetMapping("search")
  2. public Page<Wine> searchProducts(
  3.         @RequestParam(name = "text", required = false) String searchTerm,
  4.         @RequestParam(required = false) Origin origin,
  5.         @RequestParam(required = false) Integer page) {
  6.     // 根据传入的 Integer page 生成 PageRequest：
  7.     Pageable pageRequest = PageRequest.of(page != null ? page : 0, 10);
  8.     if(Objects.isNull(searchTerm) && Objects.isNull(origin)) {
  9.         return wineService.findAll(pageRequest);
  10.     }
  11.     return wineService.searchWines(
  12.             searchTerm,
  13.             origin,
  14.             pageRequest
  15.     );
  16. }

WineService.java

  1. public Page<Wine> searchWines(String searchTerm, Origin origin, Pageable pageable) {
  2.     List<Specification<Wine>> specifications = new LinkedList<>();
  3.     if (searchTerm != null) {
  4.         specifications.add(ProductSpecification.hasSearchStringInNameOrDescription(searchTerm));
  5.     }
  6.     if (origin != null) {
  7.         specifications.add(ProductSpecification.hasOrigin(origin));
  8.     }
  9.     if (specifications.isEmpty()) {
  10.         return wineRepository.findAll(pageable);
  11.     } else {
  12.         Specification<Wine> query = Specification.where(specifications.remove(0));
  13.         for (Specification<Wine> wineSpecification : specifications) {
  14.             query = query.and(wineSpecification);
  15.         }
  16.         return wineRepository.findAll(query, pageable);
  17.     }
  18. }

以名称/描述字符串为例，以下是规范：

ProductSpecification.java

  1. public static Specification<Wine> hasSearchStringInNameOrDescription(String input) {
  2.     final String searchTerm = input.toLowerCase();
  3.     return (root, criteriaQuery, criteriaBuilder) -> {
  4.         log.info("SearchTerm: " + searchTerm);
  5.         Predicate pName = criteriaBuilder.like(
  6.                 criteriaBuilder.lower(root.get(Wine_.NAME)),
  7.                 "%" + searchTerm + "%"
  8.         );
  9.         Predicate pDescription = criteriaBuilder.like(
  10.                 criteriaBuilder.lower(root.get(Wine_.DESCRIPTION)),
  11.                 "%" + searchTerm + "%"
  12.         );
  13.         return criteriaBuilder.or(pName, pDescription);
  14.     };
  15. }

生成那些 Wine_ 类是使用 Maven 插件自动生成的。参见 这里。此外，要将请求体片段转换为像 Origin 这样的复杂对象，需要包含一个转换器类，类似于 这里的示例。

英文:

So I actually got a solution working! For curious people, here's how I did it:

ProductController.java

  1. @GetMapping(&quot;search&quot;)
  2. public Page&lt;Wine&gt; searchProducts(
  3. 		@RequestParam(name = &quot;text&quot;, required = false) String searchTerm,
  4. 		@RequestParam(required = false) Origin origin,
  5. 		@RequestParam(required = false) Integer page) {
  6. 	// generate PageRequest based on Integer page if given:
  7. 	Pageable pageRequest = PageRequest.of(page != null ? page : 0, 10);
  8. 	if(Objects.isNull(searchTerm) &amp;&amp; Objects.isNull(origin)) {
  9. 		return wineService.findAll(pageRequest);
  10. 	}
  11. 	return wineService.searchWines(
  12. 			searchTerm,
  13. 			origin,
  14. 			pageRequest
  15. 	);
  16. }

WineService.java

  1. public Page&lt;Wine&gt; searchWines(String searchTerm, Origin origin, Pageable pageable) {
  2. 	List&lt;Specification&lt;Wine&gt;&gt; specifications = new LinkedList&lt;&gt;();
  3. 	if (searchTerm != null) {
  4. 		specifications.add(ProductSpecification.hasSearchStringInNameOrDescription(searchTerm));
  5. 	}
  6. 	if (origin != null) {
  7. 		specifications.add(ProductSpecification.hasOrigin(origin));
  8. 	}
  9. 	if (specifications.isEmpty()) {
  10. 		return wineRepository.findAll(pageable);
  11. 	} else {
  12. 		Specification&lt;Wine&gt; query = Specification.where(specifications.remove(0));
  13. 		for (Specification&lt;Wine&gt; wineSpecification : specifications) {
  14. 			query = query.and(wineSpecification);
  15. 		}
  16. 		return wineRepository.findAll(query, pageable);
  17. 	}
  18. }

as an example, here's the specification for the name/description string:

ProductSpecification.java

  1. public static Specification&lt;Wine&gt; hasSearchStringInNameOrDescription(String input) {
  2. 	final String searchTerm = input.toLowerCase();
  3. 	return (root, criteriaQuery, criteriaBuilder) -&gt; {
  4. 		log.info(&quot;SearchTerm: &quot; + searchTerm);
  5. 		Predicate pName = criteriaBuilder.like(
  6. 				criteriaBuilder.lower(root.get(Wine_.NAME)),
  7. 				&quot;%&quot; + searchTerm + &quot;%&quot;
  8. 		);
  9. 		Predicate pDescription = criteriaBuilder.like(
  10. 				criteriaBuilder.lower(root.get(Wine_.DESCRIPTION)),
  11. 				&quot;%&quot; + searchTerm + &quot;%&quot;
  12. 		);
  13. 		return criteriaBuilder.or(pName, pDescription);
  14. 	};
  15. }

The generation of those Wine_ classes is automatic with a Maven plugin. See this.
Additionally, for the conversion of request body pieces to complex objects like Origin, you need to include a converter class like here.

huangapple
  • 本文由 发表于 2020年7月24日 03:02:37
  • 转载请务必保留本文链接：https://java.coder-hub.com/63061380.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定