快捷導(dǎo)航

JPA?通過(guò)Specification如何實(shí)現(xiàn)復(fù)雜查詢(xún)

更新時(shí)間：2021年11月23日 09:29:06 作者：三3三

這篇文章主要介紹了JPA?通過(guò)Specification如何實(shí)現(xiàn)復(fù)雜查詢(xún)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

JPA 通過(guò)Specification實(shí)現(xiàn)復(fù)雜查詢(xún)

JPA中繼承BaseRepo之后，可以使用最基本的增刪改查，如果想實(shí)現(xiàn)復(fù)雜查詢(xún)，則需要借助Specification來(lái)完成這個(gè)功能：

下面就簡(jiǎn)單介紹一下Specification的使用

public void findAll(ConstructPlanPageReqEntity constructPlanPageReqEntity) {
	Integer pageNum = page.getPageNum();
        Integer pageSize = page.getPageSize();
        String costType = constructPlanPageReqEntity.getCostType();
        String name = constructPlanPageReqEntity.getName();
        String planMoneyStart = constructPlanPageReqEntity.getPlanMoneyStart();
        String planMoneyEnd = constructPlanPageReqEntity.getPlanMoneyEnd();
        String singMoneyEnd = constructPlanPageReqEntity.getSingMoneyEnd();
        String signMoneyStart = constructPlanPageReqEntity.getSignMoneyStart();
        long projectId = Long.parseLong(constructPlanPageReqEntity.getProjectId());
        String status = constructPlanPageReqEntity.getStatus();
        //分頁(yè)
        pageNum=pageNum-1;
        Pageable pageable = PageRequest.of(pageNum, pageSize);
//多條件匹配查詢(xún)
        Specification specification= new Specification<ContractPlanBean>() {
            @Override
            public Predicate toPredicate(Root<ContractPlanBean> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                ArrayList<Predicate> list = new ArrayList<>();
                Path<String> costType1 = root.get("costType");
                Path<String> name1 = root.get("name");
                Path<Long> projectId1 = root.get("projectId");
                Path<Object> status1 = root.get("status");
                if (projectId>0){
                    list.add(criteriaBuilder.equal(projectId1,projectId));
                }
                if (StringUtil.isNotEmpty(status)){
                    list.add(criteriaBuilder.equal(status1,status));
                }
                //條件查詢(xún)
                if (StringUtil.isNotEmpty(costType)){
                    list.add(criteriaBuilder.equal(costType1,costType));
                }
                //模糊查詢(xún)
                if (StringUtil.isNotEmpty(name)){
                    list.add(criteriaBuilder.like(name1,"%"+name+"%"));
                }
                //范圍查詢(xún)
                if (StringUtil.isNotEmpty(planMoneyStart)&&StringUtil.isNotEmpty(planMoneyEnd)){
                    try {
                        list.add(criteriaBuilder.between(root.get("planMoney"),NumberUtil.strToDouble(planMoneyStart),NumberUtil.strToDouble(planMoneyEnd)));
                    } catch (Exception e) {
                        throw new ApiException("規(guī)劃金額查詢(xún)失敗");
                    }
                }
                 //排序
                criteriaQuery.orderBy(criteriaBuilder.asc(root.get("name")));
                Predicate[] array = new Predicate[list.size()];
                return criteriaBuilder.and(list.toArray(array));
            }
        };
}

以上代碼實(shí)現(xiàn)了多條件查詢(xún)，其中需要重寫(xiě)toPredicate方法，具體參數(shù)：

用root.get()獲取bean中的數(shù)據(jù)庫(kù)對(duì)應(yīng)字段
用criteriaBuilder來(lái)組建條件查詢(xún)語(yǔ)句

在這里插入圖片描述

上圖是criteriaBuilder各種sql符號(hào)的方法名，根據(jù)需求組建不同的sql語(yǔ)句

criteriaBuilder.and(list.toArray(array))這句是最后定義各個(gè)sql查詢(xún)條件的關(guān)系，這里用的and

至此，復(fù)雜sql語(yǔ)句就拼接完成，本人對(duì)Specification的使用未進(jìn)行深入研究，個(gè)人覺(jué)得相對(duì)filter Strem的復(fù)雜查詢(xún)來(lái)說(shuō)Specification更繁瑣，因此更傾向于通過(guò)Strem的復(fù)雜查詢(xún)，這回就不多說(shuō)了，下次就介紹下如何使用Stream進(jìn)行復(fù)雜查詢(xún)

spring-data-jpa Specification拼接復(fù)雜查詢(xún)

  public Page<ServiceItem> findAll(Map<String, String[]> params, ServiceItemConsumeStatus serviceItemConsumeStatus,ServiceItemStatus serviceItemStatus, Pageable pageable) {
        return dao.findAll(spec(serviceItemConsumeStatus, serviceItemStatus, params), pageable);
    }
    private Specification<ServiceItem> spec(final ServiceItemConsumeStatus serviceItemConsumeStatus, 
    final ServiceItemStatus serviceItemStatus, Map<String, String[]> params) {
        Collection<SearchFilter> filters = SearchFilter.parse(params).values();
        final Specification<ServiceItem> fsp = SearchFilter.spec(filters, ServiceItem.class);
        Specification<ServiceItem> sp = new Specification<ServiceItem>() {
            public Predicate toPredicate(Root<ServiceItem> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Predicate pred = fsp.toPredicate(root, query, cb);
                if (ServiceItemConsumeStatus.可消費(fèi).equals(serviceItemConsumeStatus)) {
                    pred = cb.and(pred, cb.gt(root.get("countLeft").as(int.class), 0));
                } else if (ServiceItemConsumeStatus.消費(fèi)完畢.equals(serviceItemConsumeStatus)) {
                    pred = cb.and(pred, cb.le(root.get("countLeft").as(int.class), 0));
                }
                if (serviceItemStatus != null) {
                    pred = cb.and(pred, cb.equal(root.get("status"), serviceItemStatus));
                }
                return pred;
            }
        };
        return sp;
    }

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: