如何使用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());
}10. jpa中使用函数查询，注意要使用比较符
// 使用CriteriaBuilder查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<BizOrder> query = criteriaBuilder.createQuery(BizOrder.class);
Root<BizOrder> root = query.from(BizOrder.class);
query.select(root);
//使用函数查询指定判断条件，必须保证左右两边有值，中间用比较符，find_in_set是最小值是1 查询索引所在位置
query.where(criteriaBuilder.greaterThanOrEqualTo(criteriaBuilder.function("find_in_set", Integer.TYPE, criteriaBuilder.literal(116), root.get("lawyerId")), 1));
return entityManager.createQuery(query).getResultList();
//使用hql语句，注意必须使用比较符，function关键字
String sql = "select a from BizOrder as a where 1<=function('find_in_set',166,lawyer_id) ";
List<BizOrder> bizOrderList = entityManager.createQuery(sql, BizOrder.class).getResultList();
10. 支持 and 包裹多个or条件，支持调用mysql函数 find_in_set
public Page<BizOrder> searchBizOrder(BizOrderSearch bizOrderSearch, PageRequest pageRequest) {
List<Predicate> predicates = new ArrayList<>();
Specification<BizOrder> specification = (Specification<BizOrder>) (root, query, criteriaBuilder) -> {
if (bizOrderSearch.getStartTime() != null && bizOrderSearch.getEndTime() != null) {
Predicate startTimePredicate = criteriaBuilder.greaterThanOrEqualTo(root.get("createDate"), bizOrderSearch.getStartTime());
Predicate endTimePredicate = criteriaBuilder.lessThanOrEqualTo(root.get("createDate"), bizOrderSearch.getEndTime());
predicates.add(criteriaBuilder.and(startTimePredicate, endTimePredicate));
}
// 这个地方用法巧妙，一个是调用的mysql函数，一个是支持多个or条件 实现的效果 where (a=1 or b=1) and c=6
if (StringUtils.isNotEmpty(bizOrderSearch.getLawyerName())) {
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("lawyerName", ExampleMatcher.GenericPropertyMatchers.contains());
Lawyer tempLawyer = new Lawyer();
tempLawyer.setLawyerName(bizOrderSearch.getLawyerName());
tempLawyer.setAuStatus(Lawyer.AuStatus.PASS);
List<Lawyer> lawyerList = lawyerService.findAll(Example.of(tempLawyer, matcher));
if (lawyerList.isEmpty()) {
throw new YCException("该律师不存在");
}
List<Predicate> predicateList = new ArrayList<>();
for (Lawyer lawyer : lawyerList) {
Predicate lawyerNamePredicate1 = criteriaBuilder.greaterThanOrEqualTo(criteriaBuilder.function("find_in_set", Integer.TYPE, criteriaBuilder.literal(lawyer.getId()), root.get("lawyerId")), 1);
predicateList.add(criteriaBuilder.or(lawyerNamePredicate1));
}
predicates.add(criteriaBuilder.or(predicateList.toArray(new Predicate[predicateList.size()])));
}
if (bizOrderSearch.getLawyerId() != null) {
Lawyer lawyer = lawyerService.getOne(bizOrderSearch.getLawyerId());
if (lawyer != null) {
Predicate lawyerNamePredicate1 = criteriaBuilder.greaterThanOrEqualTo(criteriaBuilder.function("find_in_set", Integer.TYPE, criteriaBuilder.literal(bizOrderSearch.getLawyerId()), root.get("lawyerId")), 1);
predicates.add(criteriaBuilder.or(lawyerNamePredicate1));
}
}
if (StringUtils.isNotEmpty(bizOrderSearch.getLawyerCellphone())) {
Lawyer lawyer = lawyerService.findLawyerByPhone(bizOrderSearch.getLawyerCellphone());
if (lawyer != null) {
Predicate lawyerNamePredicate1 = criteriaBuilder.greaterThanOrEqualTo(criteriaBuilder.function("find_in_set", Integer.TYPE, criteriaBuilder.literal(lawyer.getId()), root.get("lawyerId")), 1);
predicates.add(criteriaBuilder.or(lawyerNamePredicate1));
}
}
if (StringUtils.isNotEmpty(bizOrderSearch.getOrderNo())) {
Predicate specialtyPredicate = criteriaBuilder.equal(root.get("orderNo"), bizOrderSearch.getOrderNo());
predicates.add(criteriaBuilder.or(specialtyPredicate));
}
if (bizOrderSearch.getOrderStatus() != null) {
Predicate specialtyPredicate = criteriaBuilder.equal(root.get("orderStatus").as(BizOrder.OrderStatus.class), bizOrderSearch.getOrderStatus());
predicates.add(criteriaBuilder.or(specialtyPredicate));
}
if (bizOrderSearch.getProductType() != null) {
Predicate specialtyPredicate = criteriaBuilder.equal(root.get("productType").as(BizOrder.BusinessType.class), bizOrderSearch.getProductType());
predicates.add(criteriaBuilder.or(specialtyPredicate));
}
if (bizOrderSearch.getCouponId() != null) {
Coupon coupon = new Coupon();
ActivityCoupon activityCoupon = new ActivityCoupon();
activityCoupon.setId(bizOrderSearch.getCouponId());
coupon.setActivityCoupon(activityCoupon);
Join<Supplier, BizOrder> p = root.join("coupon", JoinType.INNER);
Predicate specialtyPredicate = query.where(criteriaBuilder.equal(p.get("activityCoupon").as(ActivityCoupon.class), activityCoupon)).getRestriction();
predicates.add(specialtyPredicate);
}
return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
};
return bizOrderDao.findAll(specification, pageRequest);
}注意事项
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();