利用Specification实现分页、多条件模糊、精确查询(复杂查询)、过滤、筛选(or、in、like、equal)

        对于持久层API Spring Data JPA而言,利用它进行基本的CRUD操作,开发者一般不需要写SQL语句,当然它也支持SQL,具体本文不再赘述。本文主要阐述利用Spring Data JPA进行复杂查询,它支持JPA2.0的Criteria查询,相应的接口是JpaSpecificationExecutor。Criteria 查询:是一种类型安全和更面向对象的查询 。这个接口基本是围绕着Specification接口来定义的, Specification接口中仅包含如下一个方法:

public interface Specification<T> {

       Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb);

}

   Service层

private static Specification<UserEntity> getUserSpec(String userId, String userName, String nickName) {
    return (root, query, cb) -> {
        List<Predicate> predicates = new ArrayList<>();

        /**
         * 1. 精确查询条件:userId
         */

        predicates.add(cb.equal(root.get("userId").as(String.class), userId));

        /**
         * 2. 模糊查询条件1:userName
         */

        if (!StringUtils.isEmpty(userName)) {
            predicates.add(cb.like(root.get("userName").as(String.class), "%" + userName.trim() + "%"));
        }

        /**
         * 3. 模糊查询条件2:nickName
         */

        if (!StringUtils.isEmpty(nickName)) {
            predicates.add(cb.like(root.get("nickName").as(String.class), "%" + nickName.trim() + "%"));
        }

        /**
         * 4. 只要两种角色的数据:LANDLORD、FARMER
         */

        predicates.add(cb.or(
                    cb.equal(root.get("role").as(String.class), User.ROLE.LANDLORD),
                    cb.equal(root.get("role").as(String.class), User.ROLE.FARMER)
            ));


        /**
         * 5. 筛选两种角色的数据:LANDLORD、FARMER
         */

        List<String> roles = Arrays.asList(User.ROLE.LANDLORD,User.ROLE.FARMER);
        predicates.add(root.get("role").in(roles));


        //可以根据需求添加过滤条件

        Predicate[] p = new Predicate[predicates.size()];
        return cb.and(predicates.toArray(p));
    };
}
public Page<UserEntity> findAllDevices(String userId, String userName, String nickName, PageRequest request) {  
        Page<Device> page = deviceDao.findAll(getUserSpec(kitchenId, productName, deviceName), request);
        List<Device> list = page.getContent();
        return new PageImpl<>(list, request, page.getTotalElements());
}

DAO层

 

public interface UserDao extends JpaRepository<UserEntity, String> ,JpaSpecificationExecutor<UserEntity> {

    Page<Device> findAll(Specification<UserEntity> specification, Pageable request);
}
 //Entity


@Data
@Entity
@EqualsAndHashCode(callSuper = true)
@Table(name = "User", schema = "MomoDB", catalog = "")
public class UserEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id = 0;

    @Column(name = "gmt_create", nullable = false, updatable = false)
    private long gmtCreate = 0;

    @Column(name = "gmt_update", nullable = false)
    private long gmtUpdate = 0;

    @PrePersist
    public void onCreate()
    {
        this.gmtCreate = this.gmtUpdate = new Date().getTime();
    }

    @PreUpdate
    public void onUpdate()
    {
        this.gmtUpdate = new Date().getTime();
    }
    /**
     * 用户名称
     */
    @Column(name = "user_Id")
    private String userId;

    /**
     * 用户名称
     */
    @Column(name = "user_name")
    private String userName;

    /**
     * 用户昵称
     */
    @Column(name = "nick_name")
    private String nickName;


    /**
     * 城市
     */
    @Column(name = "city")
    private String city;

    /**
     * 角色
     */
    @Column(name = "role")
    private String role;

    public interface ROLE{
        String LONG_TERM_WORK = "LONG_TERM_WORK";

        String SHORT_TERM_WORK = "SHORT_TERM_WORK";

        String LAND_LORD = "LAND_LORD";
        
        String SLAVE = "SLAVE";
        
        String FARMER = "FARMER";
    }

}
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页