jpa 使用问题小结

查询分页参数

Pageable page =new PageRequest(dto.getPageLimit()-1,dto.getPageSize());

PageLimit：前端的查询位置起始页；从1 开始
pageSize:查询页数量大小

Page  page =new PageImpl(list,new PageRequest(result.getNumber(),result.getSize()),result.getTotalElements())

返回前端的分页对象 ：list = 分页查询结果 result.getCont();
Pageable 对象的构造需要通过 result 的结果来构造，手写的话，可能造成返回给前端的分页结果数量对不上；

JPA and中添加or查询条件

如果需要查询类似这样的查询语句

sql:select * from task where user_id=1 and billing_status=1 and （ status=1 or status=0）

public List<EmailGradeTask> findTaskByUserIdAndStatusAndBillingStatus(Integer userId){

	List<EmailGradeTask> gradeTaskList = emailGradeTaskRepository.findAll(new Specification<EmailGradeTask>() {
		@Nullable
		@Override
		public Predicate toPredicate(Root<EmailGradeTask> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder 
		criteriaBuilder) {
			//封装and查询条件
			List<Predicate> list = new ArrayList<>();
			list.add(criteriaBuilder.equal(root.get("userId"), userId));
			list.add(criteriaBuilder.equal(root.get("billingStatus"), "1"));
			Predicate[] predicates = new Predicate[list.size()];
			Predicate predicateWhere = criteriaBuilder.and(list.toArray(predicates));

			//封装or查询条件
			List<Predicate> listPermission = new ArrayList<>();
			listPermission.add(criteriaBuilder.equal(root.get("status"), 1));
			listPermission.add(criteriaBuilder.equal(root.get("status"), 0));
			Predicate[] predicatesPermissionArr = new Predicate[listPermission.size()];
			
			//设置两个查询条件为or
			 Predicate predicatesPermission = criteriaBuilder.or(listPermission.toArray(predicatesPermissionArr));


			//两条件合并
			Predicate restriction = criteriaQuery.where(predicateWhere, predicatesPermission).getRestriction();
			return restriction;
		}
	});
	return  gradeTaskList;
}
}

criteriaBuilder:查询条件构造器，设置查询字段的操作符，返回的是个 javax.persistence.criteria.Predicate 类型

criteriaQuery：查询语句构造器，

root：
实体查询字段构造器

root.get("warningTime").as(String.class)

JPA 一对多 多方条件查询

通过查询的关键子查询 JoinType.LEFT

if(CollectionUtils.isNotEmpty(dutyIssuedConditionQueryDto.getOrgIndexList())){
                List<Predicate> predicates = new ArrayList<Predicate>();
                Specification specification = (root,criteriaQuery,criteriaBuilder)->{
                    Join<DutyReceive, DutyReceiveOrg> join = root.join("dutyReceiveOrgList", JoinType.LEFT);
                    CriteriaBuilder.In in = criteriaBuilder.in(join.get("orgIndexCode"));
                    dutyIssuedConditionQueryDto.getOrgIndexList().forEach(t->{
                        in.value(t);
                    });
                    predicates.add(in);
                    Predicate[] pre = new Predicate[predicates.size()];
                    criteriaQuery.where(predicates.toArray(pre));
                    return criteriaBuilder.and(predicates.toArray(pre));
                };
                List<DutyReceiveResDto> resustList =   findDutyReceiveResDtos(specification);
                return BaseResult.success(resustList);
            }else{
                List<DutyReceive> result = dutyReceiveReposity.findAll();
                List<DutyReceiveResDto> dutyInfoDtoList = convertDutyReceiveInfo(result);
                return BaseResult.success(dutyInfoDtoList);
            }```