使用@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")));