spring data jpa 多表 分页 多条件 查询

 package com.xxxx.xxxxxx.repository.custom.impl;

    
  
    
 import com.querydsl.core.QueryResults;
    
 import com.querydsl.core.Tuple;
    
 import com.querydsl.core.types.Predicate;
    
 import com.querydsl.core.types.Projections;
    
 import com.querydsl.jpa.impl.JPAQuery;
    
 import com.querydsl.jpa.impl.JPAQueryFactory;
    
 import freemarker.template.utility.DateUtil;
    
 import org.apache.commons.lang3.StringUtils;
    
 import org.hibernate.SQLQuery;
    
 import org.hibernate.query.NativeQuery;
    
 import org.hibernate.transform.Transformers;
    
 import org.springframework.data.domain.Page;
    
 import org.springframework.data.domain.PageImpl;
    
 import org.springframework.data.domain.PageRequest;
    
 import org.springframework.data.domain.Pageable;
    
 import org.springframework.data.jpa.domain.Specification;
    
 import org.springframework.data.jpa.repository.support.Querydsl;
    
  
    
 import javax.annotation.PostConstruct;
    
 import javax.persistence.EntityManager;
    
 import javax.persistence.PersistenceContext;
    
 import javax.persistence.Query;
    
 import javax.persistence.criteria.CriteriaBuilder;
    
 import javax.persistence.criteria.CriteriaQuery;
    
 import javax.persistence.criteria.Root;
    
 import java.math.BigInteger;
    
 import java.util.ArrayList;
    
 import java.util.HashMap;
    
 import java.util.List;
    
 import java.util.Map;
    
  
    
 public class DemoStudentRepositoryImpl implements DemoStudentRepositoryCustom {
    
  
    
     @PersistenceContext
    
     private EntityManager entityManager;
    
  
    
     //查询工厂实体
    
     private JPAQueryFactory queryFactory;
    
  
    
     @PostConstruct
    
     public void init(){
    
     queryFactory = new JPAQueryFactory(entityManager);
    
     }
    
  
    
     /** * 多表查询，采用dsl的方式,返回单个实体的page列表，单实体中有其他实体
    
      * @return
    
      */
    
     @Override
    
     public Page<DemoStudent> pageStudentClassListPredicate(Predicate predicate, Pageable pageable){
    
     QDemoStudent qDemoStudent = QDemoStudent.demoStudent;
    
     QDemoClass qDemoClass = QDemoClass.demoClass;
    
     JPAQuery<Tuple> jpaQuery = queryFactory.select(qDemoStudent,qDemoClass)
    
             .from(qDemoStudent).leftJoin(qDemoClass).on(qDemoStudent.classGuid.eq(qDemoClass.classGuid))
    
             .where(predicate)
    
             .orderBy(qDemoStudent.age.desc())
    
             .offset(pageable.getOffset())
    
             .limit(pageable.getPageSize());
    
  
    
     QueryResults<Tuple> result=jpaQuery.fetchResults();
    
     List<DemoStudent> lsStudent=new ArrayList <DemoStudent>();
    
     for (Tuple row : result.getResults()) {
    
         DemoStudent student= row.get(qDemoStudent);
    
         student.setDemoClass(row.get(qDemoClass));
    
         lsStudent.add(student);
    
     }
    
     Page<DemoStudent> pageDemoStudent=new PageImpl < DemoStudent >(lsStudent,pageable,result.getTotal());
    
     return pageDemoStudent;
    
     }
    
  
    
  
    
     /** * 多表查询，采用dsl的方式,返回自定义对象
    
      * @return
    
      */
    
     @Override
    
     public List < DemoStudentVO > findStudentClassDslDTO(){
    
     QDemoStudent qDemoStudent = QDemoStudent.demoStudent;
    
     QDemoClass qDemoClass = QDemoClass.demoClass;
    
  
    
     //用下面的办法或者直接返回Tuple 对象，一个个set,例如：dto.setPrice(tuple.get(_Q_good.price));
    
     return queryFactory
    
             .select(
    
                     Projections.bean(
    
                             DemoStudentVO.class,//返回自定义实体的类型 这种方式，dto对象不要用构造函数，会报错
    
                             qDemoStudent.studentGuid,
    
                             qDemoStudent.name,
    
                             qDemoStudent.age,
    
                             qDemoStudent.birthday,
    
                             qDemoClass.className,
    
                             qDemoClass.remark.as("classRemark")//使用别名对应dto内的typeId
    
                     )
    
             )
    
             .from(qDemoStudent,qDemoClass)//构建两表笛卡尔集
    
             .where(qDemoStudent.classGuid.eq(qDemoClass.classGuid))//关联两表
    
             .orderBy(qDemoStudent.age.desc())//倒序
    
             .fetch();
    
  
    
     }
    
  
    
  
    
     /** * 多表查询，原始查询的方式（native sql），并且按以前hibernate方式返回
    
      * @return
    
      */
    
     @Override
    
     public Page<DemoStudent> pageStudentClassSqlMapResult(Pageable pageable, String name, String age) {
    
     StringBuilder countSelectSql = new StringBuilder();
    
     countSelectSql.append("select count(1)  from demo_student s "+
    
             " left join  demo_class c on s.class_guid=c.class_guid where 1=1 ");
    
  
    
     StringBuilder selectSql = new StringBuilder();
    
  
    
     selectSql.append("select {s.*},{c.*}  from demo_student s "+
    
             " left join  demo_class c on s.class_guid=c.class_guid where 1=1 ");
    
  
    
     Map<String,Object> params = new HashMap<>();
    
     StringBuilder whereSql = new StringBuilder();
    
     if(StringUtils.isNotBlank(name)){
    
         whereSql.append(" and name=:name ");
    
         params.put("name",name);
    
     }
    
     if(StringUtils.isNotBlank(age)){
    
         whereSql.append(" and age=:age ");
    
         params.put("age",age);
    
     }
    
  
    
     String countSql = new StringBuilder().append(countSelectSql).append(whereSql).toString();
    
     Query countQuery = entityManager.createNativeQuery(countSql);
    
  
    
  
    
     for(Map.Entry<String,Object> entry:params.entrySet()){
    
         countQuery.setParameter(entry.getKey(),entry.getValue());
    
     }
    
     BigInteger totalCount = (BigInteger)countQuery.getSingleResult();
    
  
    
     String querySql = new StringBuilder().append(selectSql).append(whereSql).toString();
    
  
    
     // select s.*,c.* 这种，两个表有相同字段的，因为第二个表的对应字段会用第一个表的对应字段，数据信息不对。
    
     //Query query = this.entityManager.createNativeQuery(querySql,"StudentResults");
    
     Query query = this.entityManager.createNativeQuery(querySql);
    
     for(Map.Entry<String,Object> entry:params.entrySet()){
    
         query.setParameter(entry.getKey(),entry.getValue());
    
     }
    
     query.setFirstResult((int)pageable.getOffset());
    
     query.setMaxResults(pageable.getPageSize());
    
  
    
     //query.unwrap(SQLQuery.class).addEntity("s",DemoStudent.class).addEntity("c",DemoClass.class);
    
     query.unwrap(NativeQuery.class).addEntity("s",DemoStudent.class).addEntity("c",DemoClass.class);
    
     List result =query.getResultList();//是object[]数组，第一个元素是demo_student对象，第二个元素是demo_class对象
    
     List<DemoStudent> lsStudent=new ArrayList <DemoStudent>();
    
     for (Object row : result) {
    
         DemoStudent demoStudent=(DemoStudent)((Object[])row)[0];
    
         DemoClass demoClass=(DemoClass)((Object[])row)[1];
    
         demoStudent.setDemoClass(demoClass);
    
         lsStudent.add(demoStudent);
    
     }
    
     Page<DemoStudent> pageDemoStudent=new PageImpl < DemoStudent >(lsStudent,pageable,totalCount.longValue());
    
     return pageDemoStudent;
    
     }
    
  
    
  
    
 }

  
    
 @SqlResultSetMapping (
    
     name="StudentResults",
    
     entities = {
    
             @EntityResult(entityClass = DemoStudent.class,fields={
    
                     @FieldResult(name="studentGuid",column = "student_guid"),
    
                     @FieldResult(name="name",column = "name"),
    
                     @FieldResult(name="age",column = "age"),
    
               @FieldResult(name="sex",column = "sex"),
    
               @FieldResult(name="birthday",column = "birthday"),
    
               @FieldResult(name="classGuid",column = "class_guid"),
    
               @FieldResult(name="isDeleted",column = "is_deleted")
    
               /*@FieldResult(name="className",column = "class_name") 直接想写在这里，直接把班级表的名称映射到这个实体类的transient字段中，是不行的，必须是数据库的，不报错，但是也不会填充值*/

             })

    
     },
    
     columns = {
    
             @ColumnResult(name="class_name")
    
     }
    
 )
    
 @Entity
    
 @DynamicInsert (true)
    
 @DynamicUpdate (true)
    
 @Table (name = "Demo_Student")
    
 @Where (clause="Is_Deleted=0")
    
 public class DemoStudent implements java.io.Serializable{
    
  
    
    private static final long serialVersionUID = -1L;
    
  
    
    private String studentGuid;
    
  
    
    private String name;
    
  
    
    private String age;
    
  
    
    private String sex;
    
  
    
    private Date birthday;
    
  
    
 /* private DemoClass demoClass;*/
    
  
    
  
    
    private String classGuid;
    
  
    
    private String className;
    
  
    
    private Boolean isDeleted;
    
  
    
  
    
    public DemoStudent(){
    
    }
    
  
    
    public DemoStudent(
    
       String studentGuid
    
    ){
    
       this.studentGuid = studentGuid;
    
    }
    
  
    
    @Id
    
    @GeneratedValue (generator = "pk")
    
    @GenericGenerator (name = "pk", strategy = "uuid2")
    
    @Column (name = "student_guid")
    
    public String getStudentGuid() {
    
       return this.studentGuid;
    
    }
    
    
    
    public void setStudentGuid(String value) {
    
       this.studentGuid = value;
    
    }
    
  
    
    @Column (name = "name")
    
    public String getName() {
    
       return name;
    
    }
    
  
    
    public void setName(String name) {
    
       this.name = name;
    
    }
    
  
    
    @Column (name = "age")
    
    public String getAge() {
    
       return age;
    
    }
    
  
    
    public void setAge(String age) {
    
       this.age = age;
    
    }
    
  
    
    @Column (name = "sex")
    
    public String getSex() {
    
       return sex;
    
    }
    
  
    
    public void setSex(String sex) {
    
       this.sex = sex;
    
    }
    
  
    
    @Column (name = "birthday")
    
    public Date getBirthday() {
    
       return birthday;
    
    }
    
  
    
    public void setBirthday(Date birthday) {
    
       this.birthday = birthday;
    
    }
    
  
    
 /* @ManyToOne
    
    @JoinColumn(name="class_guid",insertable = false,updatable = false)*/
    
 /* @Transient
    
    public DemoClass getDemoClass() {
    
       return demoClass;
    
    }
    
  
    
    public void setDemoClass(DemoClass demoClass) {
    
       this.demoClass = demoClass;
    
    }*/
    
  
    
    @Column (name = "class_guid")
    
    public String getClassGuid() {
    
       return classGuid;
    
    }
    
  
    
    public void setClassGuid(String classGuid) {
    
       this.classGuid = classGuid;
    
    }
    
  
    
    @Column (name = "is_deleted")
    
    public Boolean getIsDeleted() {
    
       return isDeleted;
    
    }
    
  
    
    public void setIsDeleted(Boolean isDeleted) {
    
       this.isDeleted = isDeleted;
    
    }
    
  
    
    @Transient
    
    public String getClassName() {
    
       return className;
    
    }
    
  
    
    public void setClassName(String className) {
    
       this.className = className;
    
    }
    
 }

  
    
 @Repository
    
 public class DemoStudentDaoImpl implements DemoStudentDao{
    
     //实体管理
    
     @Autowired
    
     private EntityManager entityManager;
    
  
    
     //查询工厂
    
     private JPAQueryFactory queryFactory;
    
  
    
     public DemoStudentDaoImpl(){
    
     queryFactory=new  JPAQueryFactory(entityManager);
    
     }
    
  
    
     @Override
    
     public void pageStudentClassListDsl(PageRequest page) {
    
     Query q = entityManager.createNativeQuery(
    
             "select s.student_guid,s.name,s.age,s.birthday,s.is_deleted,s.sex,s.class_guid,c.class_name  from demo_student s " +
    
                     "left join  demo_class c on s.class_guid=c.class_guid ",
    
             "StudentResults");
    
  
    
     List resultList =q.getResultList();
    
   //这里的结果就是object数组的集合，每个object数组两个元素，第一个元素是学生实体，第二个就是classname对应的字符串
    
  
    
  
    
       return null;
    
     }
    
 }

    query.unwrap(SQLQuery.class).addEntity("s",DemoStudent.class).addEntity("c",DemoClass.class);