使用@Query两表级联查询写法

springdataJpa多表查询，多条件组合查询+分页

注意事项：实体类中的属性不能带有下划线

 

实体类：

1、EduOrg.java

 

 
 

   
   

    
    

   
   
   
   

    
    

     
     package com.lhc.bean;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import java.util.Date;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.Column;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.Entity;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.GeneratedValue;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.GenerationType;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.Id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.SequenceGenerator;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.Table;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     @Entity
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     @Table(name=
     
     "Edu_Org")
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     public 
     
     class EduOrg {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private Integer id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private Integerparent_id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private Stringorgname;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private String is_use;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private Dateupdate_date;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @Id
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @GeneratedValue(generator=
     
     "a",strategy=GenerationType.SEQUENCE)
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @SequenceGenerator(initialValue=
     
     1,allocationSize=
     
     1,name=
     
     "a",sequenceName=
     
     "org_seq1")
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public Integer getId(){
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     return id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public voidsetId(Integer id) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.id =id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public IntegergetParent_id() {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                        returnparent_id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public voidsetParent_id(Integer parent_id) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.parent_id= parent_id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @Column(name=
     
     "org_name")
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public StringgetOrgname() {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                        returnorgname;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public voidsetOrgname(String orgname) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.orgname= orgname;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public StringgetIs_use() {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                        returnis_use;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public void setIs_use(Stringis_use) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.is_use= is_use;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public DategetUpdate_date() {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                        returnupdate_date;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public voidsetUpdate_date(Date update_date) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.update_date= update_date;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @Override
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public StringtoString() {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     return
     
     "EduOrg [id=" + id + 
     
     ", parent_id=" + parent_id + 
     
     ",org_name=" + orgname + 
     
     ", is_use=" + is_use
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                          +
     
     ", update_date=" + update_date + 
     
     "]";
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
        
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     }
    
    
   
   
 
 

 

2、EduTeachers.java

 
 

   
   

    
    

   
   
   
   

    
    

     
     package com.lhc.bean;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import java.util.Date;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.Column;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.Entity;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.FetchType;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.GeneratedValue;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.GenerationType;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.Id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.JoinColumn;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.ManyToOne;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.SequenceGenerator;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import javax.persistence.Table;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     @Entity
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     @Table(name=
     
     "edu_teachers")
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     public 
     
     class EduTeachers {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private Integer id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private String empno;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private String name;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private String sex;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private String degree;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private Dateupdate_date;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private Integerorg_id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
        
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     private EduOrg org;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
        
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public EduTeachers() {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     // TODOAuto-generated constructor stub
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
        
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              publicEduTeachers(String empno, String name, String sex, String degree, Dateupdate_date) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.empno =empno;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.name =name;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.sex =sex;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.degree= degree;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.update_date= update_date;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @Id
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @GeneratedValue(generator=
     
     "b",strategy=GenerationType.SEQUENCE)
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @SequenceGenerator(initialValue=
     
     1,allocationSize=
     
     1,name=
     
     "b",sequenceName=
     
     "tea_seq1")
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public Integer getId(){
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     return id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public voidsetId(Integer id) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.id =id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @Column(name=
     
     "emp_no")
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public StringgetEmpno() {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                        returnempno;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public voidsetEmpno(String empno) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.empno =empno;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public StringgetName() {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     return name;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public voidsetName(String name) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.name =name;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public String getSex(){
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     return sex;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public voidsetSex(String sex) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.sex =sex;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public StringgetDegree() {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                        returndegree;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public voidsetDegree(String degree) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.degree= degree;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public DategetUpdate_date() {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                        returnupdate_date;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public voidsetUpdate_date(Date update_date) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.update_date= update_date;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @Column(name=
     
     "org_id",insertable=
     
     false,updatable=
     
     false)
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public Integer getOrg_id(){
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                        returnorg_id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public voidsetOrg_id(Integer org_id) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.org_id= org_id;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @Override
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public StringtoString() {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     return
     
     "EduTeachers [emp_no=" + empno + 
     
     ", name=" + name + 
     
     ",sex=" + sex + 
     
     ", degree=" + degree
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                          +
     
     ", update_date=" + update_date + 
     
     ", org_id=" + org_id +
     
     "]";
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @ManyToOne(fetch=FetchType.EAGER)
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @JoinColumn(name=
     
     "org_id")
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public EduOrg getOrg(){
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     return org;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     public voidsetOrg(EduOrg org) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     this.org =org;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
        
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     }
    
    
   
   
 
 

 

 

3、使用@Query两表级联查询写法（使用@Query注解， 使用姓名或组织机构名称查询数据）

 
 

   
   

    
    

   
   
   
   

    
    

     
     	
     
     @Query(
     
     "fromEduTeachers t where t.name like ?1 and t.org.orgname like ?2")
    
    
   
   

   
   

    
    

   
   
   
   

    
    
        
     
     publicList<EduTeachers> queryByNameOrOrgName(String tname,String orgName);
    
    
   
   
 
 

4、service层实现多条件查询+分页方法（根据姓名、性别、学历、组织机构名称 几个字段动态模糊查询，要判断是否为空，要加分页，按照组织机构排序）

         

 
 

   
   

    
    

   
   
   
   

    
    

     
     	
     
     @Override
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     @Transactional
    
    
   
   

   
   

    
    

   
   
   
   

    
    
         
     
     publicPage<EduTeachers> query(final String tname,final String sex,final Stringdegree,final String orgname) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                   
     
     //TODO Auto-generated method stub
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                        returnteacherDao.findAll(
     
     new Specification<EduTeachers>() {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                            
     
     @Override
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                            
     
     publicPredicate toPredicate(Root<EduTeachers> root, CriteriaQuery<?>query, CriteriaBuilder cb) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                                     
     
     //TODO Auto-generated method stub
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                          Path<String>namepath = root.get(
     
     "name"); 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                     Path<String> sexpath =root.get(
     
     "sex"); 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                     Path<String> degreepath =root.get(
     
     "degree"); 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                     List<Predicate> list = newArrayList<Predicate>();
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                               
     
     if(tname!=
     
     null&&!
     
     "".equals(tname)) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                              list.add(cb.like(namepath,
     
     "%"+tname+
     
     "%"));
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                     }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                               
     
     if(sex!=
     
     null&&!
     
     "".equals(sex)) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                              list.add(cb.like(sexpath,
     
     "%"+sex+
     
     "%"));
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                     }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                               
     
     if(degree!=
     
     null&&!
     
     "".equals(degree)) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                              list.add(cb.like(degreepath,
     
     "%"+degree+
     
     "%"));
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                     }
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                               
     
     if(orgname!=
     
     null&&!
     
     "".equals(orgname)) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                              Path<String>orgnamepath=root.get(
     
     "org").get(
     
     "orgname");
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                              list.add(cb.like(orgnamepath,
     
     "%"+orgname+
     
     "%"));
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                     }
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                     Predicate[] p = 
     
     new Predicate[list.size()];
    
    
   
   

   
   

    
    

   
   
   
   

    
    
                     
     
     return cb.and(list.toArray(p));
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                 }
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                        },
     
     new PageRequest(
     
     0, 
     
     2, 
     
     new Sort(Direction.DESC, 
     
     "org.orgname")));