级联映射关系的分页查询ManyToMany的notIn子查询subquery实现。notMember 、notExist

查询分页过滤Spring的东西写起来太麻烦。

要做到一个简单的事情，就是根据type参数决定查询sysuer表，如果type存在则用type过滤，否则的话查询所有的sysuer（不包含patient）。

service层分页查询调用的地方如下：

@Override
    public Page<SysUser> findBySpec(String type, Pageable pageable) {
        SysUserSpecification spec = new SysUserSpecification(type);
        Page<SysUser> sysUsers = userRepository.findAll(spec, pageable);      
        return sysUsers;
    }

那么分页查询的条件实现就是在SysUserSpecification的toPredicate方法实现的。

public class SysUserSpecification implements Specification<SysUser> {

    private String roleType;

    public SysUserSpecification(String roleType) {
        super();
        this.roleType = roleType;
    }

    @Override
        public Predicate toPredicate(Root<SysUser> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        List<Predicate> list = new ArrayList<Predicate>(1);
        if (StringUtils.isNotBlank(roleType)) {
            ListJoin<SysUser, SysRole> orgHospReportJoin = root.join(root.getModel().getDeclaredList("sysRoles", SysRole.class), JoinType.LEFT);
            Predicate predicate =cb.equal(orgHospReportJoin.get("roleCode").as(String.class), roleType);
            list.add(predicate);
        }else{
            Subquery<Patient> subquery = query.subquery(Patient.class);
            Root<Patient> subRoot = subquery.from(Patient.class);
            subquery.select(subRoot);

            Predicate p = cb.equal(subRoot.get("id"), root);
            subquery.where(p);
            Predicate predicate = cb.not(cb.exists(subquery));
            list.add(predicate);
        }
        Predicate[] p = new Predicate[list.size()];
        return cb.and(list.toArray(p));
    }

    public String getRoleType() {
        return roleType;
    }

    public void setRoleType(String roleType) {
        this.roleType = roleType;
    }
}

其实加粗的部分查询本来很简单的东西。用SQL写：

select * from sysuser where id not in (
        select id from patient
)

外层再包裹上分页查询就好。我们这里怎么写的呢？

 Subquery<Patient> subquery = query.subquery(Patient.class);
            Root<Patient> subRoot = subquery.from(Patient.class);
            subquery.select(subRoot);

            Predicate p = cb.equal(subRoot.get("id"), root);
            subquery.where(p);
            Predicate predicate = cb.not(cb.exists(subquery));
            list.add(predicate);

POJO对象SysUser

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@Table(name = "SysUser")
public class SysUser extends SuperEntity {
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        @Column(name = "ID")
        private Long id;

        /**用户、角色关联关系*/
	@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
	@JoinTable(name = "SysUser_SysRole",
			joinColumns = {@JoinColumn(name = "SYSUSER_ID")},
			inverseJoinColumns = {@JoinColumn(name = "SYSROLE_ID")})
	private List<sysrole> sysRoles = new ArrayList<sysrole>();
}

POJO对象Patient

@Entity
@Table(name = "Patient")
public class Patient extends SysUser {
        @Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "ID")
	private Long id;
        /**身份证号*/
        @NotBlank@Column(length = 20, unique = true)
        @Length(max = 20, message = "身份证长度不能大于20")
        private String idNo;/**备注*/

        @Column(length = 200)
        private String remark;<pre name="code" class="java">
}

POJO对象Sysrole

@Entity
@Table(name = "SysRole")
public class SysRole extends SuperEntity {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "ID")
	private Long id;

        /**角色代码*/
       @NotBlank(message = "角色代码不能为空")
       @Column(length = 50, unique = true)
        private String roleCode;

	/**角色、用户关联关系*/
	@ManyToMany(mappedBy = "sysRoles")
	private List<SysUser> sysUsers = new ArrayList<SysUser>();
}