jpa查询方法案例使用技巧整理归纳

如何使用JPA查询,JPA查询例子,各种查询技巧,现在整理归纳,以便参考。
以下查询方法是由本人项目中初次使用整理出来的
关于jpa的查询封装,简化操作和其他开源中间件的集成,在这里有个项目
详情点击查看===》SpringBoot开源项目

1. 聚合函数查询

 public BigDecimal getTotalMoneyBizOrderForLawfirm(LawFirm lawFirm) {
     //查询结果
     CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
     // criteriaBuilder.createQuery(); 该方法可以在指定一个实体结果类型
     CriteriaQuery<Object> query = criteriaBuilder.createQuery();

     // 创建根结果集,以便下面条件可以使用 root.get("lawFirm") lawFirm属于BizOrder实体类中的属性
     Root<BizOrder> root = query.from(BizOrder.class);
     List<Predicate> predicates = new ArrayList<>();
     Date date = new Date();
     Date startDate = YCDate.INSTANCE.getTimeNodeWithDay(date, YCDate.DateNode.START);
     Date endDate = YCDate.INSTANCE.getTimeNodeWithDay(date, YCDate.DateNode.END);
     Predicate startPredicate = criteriaBuilder.and(criteriaBuilder.greaterThanOrEqualTo(root.get("createDate"), YCDate.INSTANCE.dateToLocalDateTime(startDate)));
     Predicate endPredicate = criteriaBuilder.and(criteriaBuilder.lessThanOrEqualTo(root.get("createDate"), YCDate.INSTANCE.dateToLocalDateTime(endDate)));
     Predicate lawfirmPredicate = criteriaBuilder.equal(root.get("lawFirm").as(LawFirm.class), lawFirm);

     predicates.add(startPredicate);
     predicates.add(endPredicate);
     predicates.add(lawfirmPredicate);

     // 增加条件
     query.where(predicates.toArray(new Predicate[predicates.size()]));

     // 指定结果集,可以指定多个列
     query.multiselect(criteriaBuilder.sum(root.get("orderMoney")));
     return (BigDecimal) entityManager.createQuery(query).getSingleResult();
 }

2. 起始时间节点查询

     public BigDecimal totdayTotalIncome(Integer lawFirmId) {
        String sql = "select sum(order_money) from biz_order where law_firm_id=%s and order_status=4 and create_date>='%s' and create_date<='%s'";
        Date date = new Date();
        String startDate = YCDate.INSTANCE.formatDate(YCDate.INSTANCE.getTimeNodeWithDay(date, YCDate.DateNode.START), YCDate.DateFormat.YYYY_MM_DD_HH_MM_SS);
        String endDate = YCDate.INSTANCE.formatDate(YCDate.INSTANCE.getTimeNodeWithDay(date, YCDate.DateNode.END), YCDate.DateFormat.YYYY_MM_DD_HH_MM_SS);
        sql = String.format(sql, lawFirmId, startDate, endDate);
        return (BigDecimal) this.entityManager.createNativeQuery(sql).getSingleResult();
    }

3. 使用in条件查询

    public List<Wallet> getLisWallet(List<Integer> listID) {
        Specification<Wallet> specification = (Specification<Wallet>) (root, query, criteriaBuilder) -> {
            Expression<String> exp = root.get("id");
            return criteriaBuilder.and(exp.in(listID));
        };
        return this.walletDao.findAll(specification);
    }

4. 使用entityManager对象去执行原生SQL语句

  public Lawyer getMaxCallLengthLawyer() {
       String sqlCallLog = "select callee_number as calleeNumber,sum(call_length) as callLength  from call_log group by callee_number order by callLength desc limit 1";
       Query query = entityManagerFactory.createEntityManager().createNativeQuery(sqlCallLog);
       Object[] callLog = (Object[]) query.getSingleResult();
       String phone = callLog[0].toString();
       // String callLength = callLog[1];
       return getLawyer(phone);
   }

5. 使用entityManager对象去执行原生SQL语句

  public BigDecimal totalCash(Integer lawFirmId) {
       String sql = "select sum(income_balance) from wallet w inner join user u on w.user_id=u.id where u.law_firm_id=%s";
       sql = String.format(sql, lawFirmId);
       return (BigDecimal) this.entityManager.createNativeQuery(sql).getSingleResult();
   }

6. 多表关联查询


      public Page<Flow> findFlow(FlowSearch flowSearch, LawFirm lawFirm, PageRequest pageRequest) {
        List<Predicate> predicates = new ArrayList<>();
        Specification<Flow> specification = (Specification<Flow>) (root, query, criteriaBuilder) -> {
            if (flowSearch.getStartTime() != null && flowSearch.getEndTime() != null) {
                Predicate startTimePredicate = criteriaBuilder.greaterThanOrEqualTo(root.get("createDate"), flowSearch.getStartTime());
                Predicate endTimePredicate = criteriaBuilder.lessThanOrEqualTo(root.get("createDate"), flowSearch.getEndTime());
                Predicate handleLawyerPredicate = criteriaBuilder.equal(root.get("lawFirm").as(LawFirm.class), lawFirm);
                predicates.add(criteriaBuilder.and(startTimePredicate, endTimePredicate, handleLawyerPredicate));
            }

            if (StringUtils.isNotEmpty(flowSearch.getFlowCode())) {
                Predicate specialtyPredicate = criteriaBuilder.equal(root.get("flowCode"), flowSearch.getFlowCode());
                predicates.add(criteriaBuilder.or(specialtyPredicate));
            }

            if (StringUtils.isNotEmpty(flowSearch.getName())) {
                User user = new User();
                user.setName(flowSearch.getName());
                User user1 = userService.findOne(Example.of(user)).orElse(null);
                if (user1 != null) {
                    User dbUser = new User();
                    dbUser.setId(user1.getId());
                    Predicate specialtyPredicate = criteriaBuilder.equal(root.get("activeUser").as(User.class), dbUser);
                    predicates.add(criteriaBuilder.or(specialtyPredicate));
                }
            }

            if (flowSearch.getTradeType() != null) {
                if (flowSearch.getTradeType() == ProductDefaultPrice.ProductType.RECHARGE) {
                    predicates.add(criteriaBuilder.isNotNull(root.get("iIncome")));
                } else {
                    // BizOrder bizOrder = new BizOrder();
                    // bizOrder.setProductType(flowSearch.getTradeType());
                    predicates.add(criteriaBuilder.isNotNull(root.get("iPay")));

                    // 查询关联对象的非主键属性,需要这么做哦,新技能,这里查询的flow中bizOrder对象中的productType产品类型
                    //类似 sql INNER JOIN biz_order bizorder1_ ON flow0_.biz_order_id = bizorder1_.id
                    //WHERE bizorder1_.product_type =?
                    Join<Supplier, BizOrder> p = root.join("bizOrder", JoinType.INNER);
                    Predicate specialtyPredicate = query.where(criteriaBuilder.equal(p.get("productType").as(ProductDefaultPrice.ProductType.class), flowSearch.getTradeType())).getRestriction();
                    predicates.add(specialtyPredicate);
                }
            }
            return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
        };
        return flowDao.findAll(specification, pageRequest);

    }

7. 按需要字段查询,模糊匹配查询,or 和 and 同时支持

public Page<Lawyer> searchLawyer(String lawyerName, String specialty, LawFirm lawFirm, PageRequest pageRequest) {
       List<Predicate> predicates = new ArrayList<>();
       Specification<Lawyer> specification = (Specification<Lawyer>) (root, query, criteriaBuilder) -> {

           if (StringUtils.isNotEmpty(lawyerName) && StringUtils.isNotEmpty(specialty)) {
               Predicate lawyerNamePredicate = criteriaBuilder.like(root.get("lawyerName"), "%" + lawyerName + "%");
               Predicate specialtyPredicate = criteriaBuilder.like(root.get("specialty"), "%" + specialty + "%");
               predicates.add(criteriaBuilder.or(lawyerNamePredicate, specialtyPredicate));
           } else if (StringUtils.isNotEmpty(lawyerName) && StringUtils.isEmpty(specialty)) {
               Predicate lawyerNamePredicate = criteriaBuilder.like(root.get("lawyerName"), "%" + lawyerName + "%");
               predicates.add(criteriaBuilder.or(lawyerNamePredicate));
           } else if (StringUtils.isEmpty(lawyerName) && StringUtils.isNotEmpty(specialty)) {
               Predicate specialtyPredicate = criteriaBuilder.like(root.get("specialty"), "%" + specialty + "%");
               predicates.add(criteriaBuilder.or(specialtyPredicate));
           }

           if (lawFirm != null) {
               predicates.add(criteriaBuilder.equal(root.get("lawFirm").as(LawFirm.class), lawFirm));
           }
           return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
       };
       return lawyerDao.findAll(specification, pageRequest);
   }

8. 使用hql语句查询

  String hql = "from LawFirm l where l.lawFirmPracticeNo is not null";
       List<LawFirm> lawFirmList = entityManager.createQuery(hql).getResultList();

9. 自动分页sql查询


  //直接调用下面封装的方法,实现sql自动分页,另外还有一种方法是在dao层的@Query注解,但是这种灵活度更高
  public Page<Lawyer> findPageLawyer(PageRequest pageRequest) {
     String sql = "select * from lawyer where id>50";
     return this.findPageEntity(sql, Lawyer.class, pageRequest);
  }

 //封装sql自动分页
  public <T> Page<T> findPageEntity(String sql, Class<T> entity, PageRequest pageRequest) {
     Query query = entityManager.createNativeQuery(sql, entity);
     query.setFirstResult((int) pageRequest.getOffset());
     query.setMaxResults(pageRequest.getPageSize());
     List<T> dataList = query.getResultList();

     int formIndex = sql.indexOf("from");
     String sqlCount = "select count(1) " + sql.substring(formIndex);
     Query queryCount = entityManager.createNativeQuery(sqlCount);
     BigInteger count = (BigInteger) queryCount.getSingleResult();
     return new PageImpl<T>(dataList, PageRequest.of(pageRequest.getPageNumber(), pageRequest.getPageSize()), count.longValue());
 }
注意事项

        Honor honor = new Honor();
        // 为什么要重新new一个 不能使用上面哪一个lawyer对象?
        // 因为 Lawyer 关联 Lawfirm  Lawfirm关联了User  User也关联了Lawfirm 形成了圆环依赖最终堆栈溢出
        // 在使用Example查的时候,其实可以指定ExampleMatcher匹配器,忽略或者保留那些字段查询
        // 默认的Example.of 会遍历对象的各个字段,如果该对象的属性也依赖对象本身,那么会导致堆栈溢出
        // 解决办法:1. 使用ExampleMatcher只保留你需要的字段如id 2. new一个新对象,赋值需要的字段
        Lawyer tempLawyer = new Lawyer();
        tempLawyer.setId(lawyerId);
        honor.setLawyer(tempLawyer);
        honor = honorService.findOne(Example.of(honor)).get();
 小米手机刷机经验
JS/JavaScript中Array Map Array使用方法和案例 
上一篇:小米手机刷机经验
下一篇:JS/JavaScript中Array Map Array使用方法和案例
评论

如果我的文章对你有帮助,或许可以打赏一下呀!

支付宝
微信
QQ