JPA内将时间作为查询条件，分页查询当天的数据库数据的两种写法(涉及Specification、Predicate、CriteriaBuilder)

JPA内将时间作为查询条件，分页查询当天的数据库数据的两种写法(涉及Specification、Predicate、CriteriaBuilder)

最近做项目时公司要用jpa,遇到关于时间的查询条件的问题，总结如下：废话不多说，直接上代码

1. 使用localDateTime

//可以按照某个条件排序，比如  “auditUnique”
Sort sort = new Sort(Sort.Direction.DESC, "auditUnique");
        if (!org.springframework.util.StringUtils.isEmpty(field) && !org.springframework.util.StringUtils.isEmpty(sord)) {
            if ("ASC".equals(sord)) {
                sort = new Sort(Sort.Direction.ASC, field);
            } else {
                sort = new Sort(Sort.Direction.DESC, field);
            }
        }

Specification<PmAuditDeviceResultEntity> pmAuditSumEntitySpecification = new  Specification<PmAuditDeviceResultEntity>() {
            @Override
            public Predicate toPredicate(Root<PmAuditDeviceResultEntity> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                Predicate predicate = criteriaBuilder.conjunction();
                //设置查询条件
                if (null != pmAuditDeviceResultEntity.getAuditUnique()) {
                    predicate.getExpressions().add(criteriaBuilder.like(root.get("auditUnique"), "%" + pmAuditDeviceResultEntity.getAuditUnique().trim() + "%"));
                }
                if (null != pmAuditDeviceResultEntity.getResSpecId()) {
                    predicate.getExpressions().add(criteriaBuilder.equal(root.get("resSpecId"), pmAuditDeviceResultEntity.getResSpecId()));
                }
                if (null != pmAuditDeviceResultEntity.getStatus()) {
                    predicate.getExpressions().add(criteriaBuilder.equal(root.get("status"), pmAuditDeviceResultEntity.getStatus()));
                }
				//获取 年月日
				LocalDateTime localDateTime = LocalDateTime.now();
				int year = localDateTime.getYear();
				int month = localDateTime.getMonthValue();
				int day = localDateTime.getDayOfMonth();

				LocalDateTime createDateStart = LocalDateTime.of(year, month, day, 00, 00, 00);
				LocalDateTime createDateEnd = LocalDateTime.of(year, month, day, 23, 59, 59);
				if (null != createDateStart) {
       				predicate.getExpressions().add(criteriaBuilder.greaterThanOrEqualTo(
             			root.get("createDate"), createDateStart));
				}
				if (null != createDateEnd) {
	 				 predicate.getExpressions().add(criteriaBuilder.lessThanOrEqualTo(
           				 root.get("createDate"), createDateEnd));
				}
				//and 就是 所有条件是"与"的关系,or就是 "或" 关系
				return criteriaBuilder.and(predicate.getExpressions().toArray(new Predicate[predicate.getExpressions().size()]));
            }
        };

//使用分页，并将条件添加进findAll()
		Page<PmAuditDeviceResultEntity> pmAuditDeviceResultEntityPage = pmAuditDeviceResultEntityRepository.findAll(pmAuditSumEntitySpecification, new PageRequest(pageNum - 1, auditFieldNum * pageSize, sort));

		//分页代码  当然，我这边没做非空判断，有需要可以在上面的查询结果下面加if (pmOrderServiceEntityPage.hasContent()) {}else{}并将下面的代码赋值进去
		returnMap.put("content", pmAuditDeviceResultEntityPage.getContent());
        returnMap.put("totalElements", pmAuditDeviceResultEntityPage.getTotalElements() / auditFieldNum);
        returnMap.put("totalPages", pmAuditDeviceResultEntityPage.getTotalElements() / (auditFieldNum * pageSize) + 1);
        returnMap.put("pageSize", pageSize);
        returnMap.put("pageNum", pageNum);
        return returnMap;

因为我上面的代码需要进行稽核项条数的判断，所以findAll()里面用的是 auditFieldNum * pageSize，如果仅仅是一行一行的表格，这边直接是 pageSize。

页面效果是这样的。

2.第二种写法使用Calendar

//根据创建时间查询出数据库中的当天的数据 ，设置创建时间当天的凌晨为开始时间，23时.59分.59秒为结束时间
               if (null == pmAuditDeviceResultEntity.getCreateDate()) {
                    pmAuditDeviceResultEntity.setCreateDate(LocalDateTime.now());
                    LocalDateTime localDate = pmAuditDeviceResultEntity.getCreateDate();
                    Date startDate = Date.from((localDate.atZone(ZoneId.systemDefault()).toInstant()));
                    Calendar calendar = Calendar.getInstance();
                    calendar.setTime(startDate);
                    calendar.set(Calendar.HOUR_OF_DAY, 0);
                    calendar.set(Calendar.MINUTE, 0);
                    calendar.set(Calendar.SECOND, 0);
                    calendar.set(Calendar.MILLISECOND, 0);
                    predicate.getExpressions().add(criteriaBuilder
                            .greaterThanOrEqualTo(root.get("createDate").as(Date.class), calendar.getTime()));
                }
               if (pmAuditDeviceResultEntity.getCreateDate() == null) {
                    pmAuditDeviceResultEntity.setCreateDate(LocalDateTime.now());
                    LocalDateTime localDate = pmAuditDeviceResultEntity.getCreateDate();
                    Date endDate = Date.from((localDate.atZone(ZoneId.systemDefault()).toInstant()));
                    Calendar calendar = Calendar.getInstance();
                    calendar.setTime(endDate);
                    calendar.set(Calendar.HOUR_OF_DAY, 23);
                    calendar.set(Calendar.MINUTE, 59);
                    calendar.set(Calendar.SECOND, 59);
                    calendar.set(Calendar.MILLISECOND, 999);
                    predicate.getExpressions().add(criteriaBuilder
                            .lessThanOrEqualTo(root.get("createDate").as(Date.class), calendar.getTime()));
                }