Spring Date JPA的条件查询方法（带分页）

直接实例化Specification接口

• 通过实例化Specification接口，省去了在业务层中写sql，拼接sql的繁琐；使业务层能够只关注于业务实现，将sql语句处理放到dao层处理。

一、普通写法

@Override
public Page<ShopRegister> findShopRegisterByWheres(String shopId, String shopName, String phone, Date startTime, long brandId, String preUserPhone, Pageable pageable) {
    return shopRegisterRepository.findAll(wheres(shopId, shopName, phone, startTime, brandId, preUserPhone), pageable);
}

/**
* 实例化Specification接口
* ShopRegister商户注册实体类
*/
private Specification<ShopRegister> wheres(final String shopId, final String shopName, final String phone, final Date startTime, final long brandId, final String preUserPhone) {
    return new Specification<ShopRegister>() {
        public Predicate toPredicate(Root<ShopRegister> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
            List<Predicate> predicates = new ArrayList<Predicate>();
            if (isNotBlank(shopId)) {
                predicates.add(cb.equal(root.<String>get("shopId"), shopId));
            }
            if (isNotBlank(shopName)) {
                predicates.add(cb.like(root.<String>get("shopName"), "%" + shopName + "%"));
            }
            if (isNotBlank(phone)) {
                predicates.add(cb.like(root.<String>get("mobilePhone"), "%" + phone + "%"));
            }
            if (startTime != null) {
                predicates.add(cb.greaterThan(root.<Date>get("createTime"), DateUtil.getYYMMHHMMSSDateFromStr(startTime)));
            }
            if (isNotBlank(endTime)) {
                predicates.add(cb.lessThan(root.<Date>get("createTime"), DateUtil.getYYMMHHMMSSDateFromStr(endTime)));
            }
            if (brandId != -1) {
                predicates.add(cb.equal(root.<String>get("brandId"), brandId));
            }
            if (isNotBlank(preUserPhone)) {
                predicates.add(cb.equal(root.<String>get("agentPhone"), preUserPhone));
            }
            return criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
        }
    };
}
复制代码

二、JDK8之后写法

@Override
public Page<ShopRegister> findShopRegisterByWheres(String shopId, String shopName, String phone, Date startTime, long brandId, String preUserPhone, Pageable pageable) {
    return shopRegisterRepository.findAll(wheres(shopId, shopName, phone, startTime, brandId, preUserPhone), pageable);
}

private Specification<ShopRegister> wheres(final String shopId, final String shopName, final String phone, final Date startTime, final long brandId, final String preUserPhone) {
    return (Root<ShopRegister> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) -> {
        List<Predicate> predicates = new ArrayList<Predicate>();
        if (isNotBlank(shopId)) {
            predicates.add(cb.equal(root.<String>get("shopId"), shopId));
        }
        if (isNotBlank(shopName)) {
            predicates.add(cb.like(root.<String>get("shopName"), "%" + shopName + "%"));
        }
        if (isNotBlank(phone)) {
            predicates.add(cb.like(root.<String>get("mobilePhone"), "%" + phone + "%"));
        }
        if (startTime != null) {
            predicates.add(cb.greaterThan(root.<Date>get("createTime"), DateUtil.getYYMMHHMMSSDateFromStr(startTime)));
        }
        if (isNotBlank(endTime)) {
            predicates.add(cb.lessThan(root.<Date>get("createTime"), DateUtil.getYYMMHHMMSSDateFromStr(endTime)));
        }
        if (brandId != -1) {
            predicates.add(cb.equal(root.<String>get("brandId"), brandId));
        }
        if (isNotBlank(preUserPhone)) {
            predicates.add(cb.equal(root.<String>get("agentPhone"), preUserPhone));
        }
        return criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
    };
}
复制代码

三、更简洁写法

@Override
public Page<ShopRegister> findShopRegisterByWheres(String shopId, String shopName, String phone, Date startTime, long brandId, String preUserPhone, Pageable pageable) {
    return shopRegisterRepository.findAll(wheres(shopId, shopName, phone, startTime, brandId, preUserPhone), pageable);
}

private Specification<ShopRegister> wheres(final String shopId, final String shopName, final String phone, final Date startTime, final long brandId, final String preUserPhone) {
    return (root, criteriaQuery, cb) -> {
        List<Predicate> predicates = new ArrayList<Predicate>();
        if (isNotBlank(shopId)) {
            predicates.add(cb.equal(root.<String>get("shopId"), shopId));
        }
        if (isNotBlank(shopName)) {
            predicates.add(cb.like(root.<String>get("shopName"), "%" + shopName + "%"));
        }
        if (isNotBlank(phone)) {
            predicates.add(cb.like(root.<String>get("mobilePhone"), "%" + phone + "%"));
        }
        if (startTime != null) {
            predicates.add(cb.greaterThan(root.<Date>get("createTime"), DateUtil.getYYMMHHMMSSDateFromStr(startTime)));
        }
        if (isNotBlank(endTime)) {
            predicates.add(cb.lessThan(root.<Date>get("createTime"), DateUtil.getYYMMHHMMSSDateFromStr(endTime)));
        }
        if (brandId != -1) {
            predicates.add(cb.equal(root.<String>get("brandId"), brandId));
        }
        if (isNotBlank(preUserPhone)) {
            predicates.add(cb.equal(root.<String>get("agentPhone"), preUserPhone));
        }
        return criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
    };
}
复制代码

转载于:https://juejin.im/post/5baa3f78f265da0a9a3971ba