A paged query for cascading mappings Manytomany the Notin subquery subquery implementation. Notmember, Notexist

Query paging filtering spring's stuff is too cumbersome to write.

The simple thing to do is to query the Sysuer table based on the type parameter, and if type exists, filter by type, otherwise query all Sysuer (not including patient).

The service layer paged query is called in the following places:

@Override public
    page<sysuser> findbyspec (String type, pageable pageable) {
        Sysuserspecification spec = New Sysuserspecification (type);
        page<sysuser> sysUsers = Userrepository.findall (spec, pageable);      
        return sysUsers;
    }

Then the conditional implementation of paged query is implemented in the Sysuserspecification Topredicate method.

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, Criteriabuil
        Der CB) {list<predicate> List = new arraylist<predicate> (1); if (Stringutils.isnotblank (Roletype)) {listjoin<sysuser, sysrole> orghospreportjoin = Root.join (root.ge
            TModel (). 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; }
}

In fact, bold part of the query originally very simple things. Write with sql:

SELECT * from Sysuser where ID not in (
        Select ID from patient
)

The outer layer of the package on the paging query is good. What do we write here?

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 Object 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;

        /** user, role affinity/*
	@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 Object Patient

@Entity
@Table (name = "Patient") public
class Patient extends Sysuser {
        @Id
	@GeneratedValue (strategy = Generationtype.auto)
	@Column (name = "id")
	private Long ID;
        /** ID Number */
        @NotBlank @column (length =, unique = True)
        @Length (max =, message = "ID cannot be greater than")
        private STR ing idno;/** notes * *

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

Pojo Object Sysrole

@Entity
@Table (name = "Sysrole") public
class Sysrole extends Superentity {
	@Id
	@GeneratedValue ( Strategy = Generationtype.auto)
	@Column (name = "id")
	private Long ID;

        /** Role Code *
       /@NotBlank (message = "Role code cannot be empty")
       @Column (length = number, unique = true)
        private String rolecode;< c23/>/** role, User affinity *
	/@ManyToMany (Mappedby = "Sysroles")
	private list<sysuser> sysUsers = new ArrayList <SysUser> ();
}