是否可以通过@Query进行灵活的订购? [英] Is it possible to make flexible order by in @Query?

问题描述

我正在制作一个项目(基础商店)，目前我坚持订购(例如按价格，名称等).我想知道在任何情况下都可以使用简单的方法对我的商品使用order by进行1个灵活的查询.因为我的代码返回了无序的对象列表.

I'm making a project(basic shop) and currently i stuck on ordering(for example by price, name, etc.). I want to know is there any easy way to make 1 flexible query for many cases, using order by for my commodities. Cause with my code it's returning unordered list of objects.

因此，在此项目中，我在用户在组合框元素中选择排序类型后将请求发送到服务器.在我的控制器选择排序方式之后，它将调用查询，然后返回我们的对象.

So in this project i send request to the server after user chooses sorting type in the combo box element. After that my controller is selecting the way of ordering, it's calling the query and after that returns our objects.

请帮助，您是否有机会告诉我，在Controller或Service Implementation中，哪种switch(order_type)更好的方式?

Please help, and by the chance can you tell me what way of switch(order_type) is better, in Controller or Service Implementation?

P.S.如果我以[ http://localhost:9999/commodities/category-name/CPU/c.name%20asc ]控制器仅看到不带.name asc的c，这就是我为此使用switch的原因.

P.S. i use switch cause if i send request to the server as [ http://localhost:9999/commodities/category-name/CPU/c.name%20asc ] controller only see c without .name asc, that's why i used switch for that.

代码:

控制器:

@RestController
@RequestMapping("commodities")
public class CommodityController {

@GetMapping("category-name/{name}/{order_type}")
    public ResponseEntity<List<CommodityDTO>> getCommodityByCategoryNameWithOrder(@PathVariable("name") String categoryName, @PathVariable("order_type") String order_type){
        List<CommodityDTO> byCategoryWithOrder;

        switch(order_type) {

        case "name_asc":
            byCategoryWithOrder = commodityService.getAllByCategoryNameWithOrder(categoryName, "c.name asc");
            break;

        case "name_desc":
            byCategoryWithOrder = commodityService.getAllByCategoryNameWithOrder(categoryName, "c.name desc");
            break;

        case "price_asc":
            byCategoryWithOrder = commodityService.getAllByCategoryNameWithOrder(categoryName, "c.price asc");
            break;

        case "price_desc":
            byCategoryWithOrder = commodityService.getAllByCategoryNameWithOrder(categoryName, "c.price desc");
            break;

        default:
            return null;
        }
        return new ResponseEntity<List<CommodityDTO>>(byCategoryWithOrder,HttpStatus.OK);
    }

服务实施:

@Service
@Transactional
public class CommodityServiceImpl implements CommodityService{

    @Autowired
    private CommodityRepository commodityRepository;

    @Autowired
    private ObjectMapperUtils objectMapperUtils;

    @Autowired
    private CloudinaryService cloudinaryService;

    @Override
    public List<CommodityDTO> getAllByCategoryNameWithOrder(String categoryName, String order_type){
            System.out.println("\n\n\n\nOrder type:\n"+order_type+"\n\n\n\n");
            return objectMapperUtils.mapAll(commodityRepository.findAllByCategoryNameWithOrder(categoryName, order_type), CommodityDTO.class);
        }

}

存储库:

    public interface CommodityRepository  extends JpaRepository<Commodity, Integer>{
    @Query("Select c FROM Commodity c "
                + "Join Category ct on c.category.id = ct.id "
                + "where ct.name = ?1 "
                + "order by ?2")
        List<Commodity> findAllByCategoryNameWithOrder(String categoryName, String order_type);
    }

推荐答案

您应该看看如何使用QueryDSL扩展来处理Spring数据.结合Spring Data Web扩展，您可以通过属性的任何组合来过滤实体，并应用排序和分页，而无需编写任何代码.

You should look at using the QueryDSL extension for Spring data. This, in combination with the Spring Data web extension, will allow you to filter the entities by any combination of properties and apply sorting and paging without writing any code.

将以下内容添加到您的配置中，然后您的代码将如下所示:

Add the following to your config and your code will then look like the below:

@EnableSpringDataWebSupport

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#core.extensions.querydsl

https: //docs.spring.io/spring-data/jpa/docs/current/reference/html/#core.web.type-safe

请参见此处，了解如何设置您的项目以支持QueryDsl:

See here on how to set-up your project to support QueryDsl:

https://www.baeldung.com/querydsl-with-jpa-tutorial

控制器

@GetMapping("/searchCommodities")
public ResponseEntity<List<CommodityDTO>> findCommodities(
         @QuerydslPredicate(root = Commodity.class) Predicate predicate,Pageable pageable){

     return new ResponseEntity<List<CommodityDTO>> 
                (commodityService.getCategories(predicate, pageable), HttpStatus.OK);
}

服务

public class CommodityServiceImpl implements CommodityService{
    @Override
    public List<CommodityDTO> getCategories(Predicate predicate, Pageable pageable){
        return objectMapperUtils.mapAll(commodityRepository.findAll(
                       predicate, pageable), CommodityDTO.class);
}

存储库

public interface CommodityRepository  extends JpaRepository<Commodity, Integer>, 
                     QueryDslPredicateExecutor<Commodity>{
    //no query methods required
}

您现在可以拨打，例如:

You can now call with, for example:

/searchCommodities?name=someName&sort=someProperty

/searchCommodities?name=someName&someOtherproperty=xyz&sort=someProperty,desc&sort=someProperty,asc

/searchCommodities?name=x&name=y&name=z //name = x or y or z

这篇关于是否可以通过@Query进行灵活的订购?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！