您的位置: 首页  >  文章  >  SpringDataJPA中使用EntityManager操作返回多表连接结果集

SpringDataJPA中使用EntityManager操作返回多表连接结果集

分类: 文章 • 2024-07-04 11:45:22

上一篇博客中说过关于SpringDataJPA中多表关联查询，自定义实体类接收返回的结果集：新建Vo类的方式。

单表操作返回实体对象就不必说了，可以用Specification动态查询，也可以直接在接口上定义查询，加非空判断之类的。

但是问题来了，这个返回对象是我们自定义的，在@Query()中是select new XXXX（）返回出来的，那么我们用这种方法拼多条件和分页时怎么处理呢？

第一反应是直接在接口上写sql拼接么？如果是的话你就错了。

首先你应该明白你操作的是实体模型对象(数据库当中并不存在)，并不是数据库持久的实体表类，其次你应该会发现如果直接拼写多条件里面只有nullif()方法，然而这个方法对于拼接多条件并没什么卵用。这个时候可以用entityManager来解决。

示例如下：

  • 多表查询返回Vo类(我定义的TrainingAllListVo) 
    public class TrainingAllListVo {
    private BigInteger projectId;
    private BigInteger scheduleId;
    private Integer flage;
    private String project_name;
    private String school_year;
    private String schedule_name;
    private Timestamp schedule_begin_time;
    private Timestamp schedule_end_time;
    private String training_form;
    private String appraisal_scores;
    private Double actual_credits;
    private String registration_status;
    private String school_name;
    private String organization_name;
    private String course_status;
    private String notice_status;
    private String check_status;
    private String evaluation_status;
    private String study_evaluation_status;
    private String notice_path;

    .....getter setter toString省略......
}

     

  • 接口（接口中定义多条件查询的参数，可以以对象形式也可以单个参数入参，我这里写的单个）
List<TrainingAllListVo> getAllTrainings(@Param("teacherId") long teacherId, SchoolYear schoolYear, RegistrationStatus registrationStatus, EvaluationStatus evaluationStatus,String projectName,Integer page,Integer size);
  • 实现类（示例中连接的表有点多，往后拉拉，查看加非空判断的拼多条件查询）

注意：

首先，引入entitymanager，别忘了加注解。

@PersistenceContext
 EntityManager entityManager;

其次，sql中 WHERE schedulete4_.teacher_id ="+teacherId+" and

非空判断   if('"+schoolYear+"'!='null',project1_.school_year='"+schoolYear+"',1=1)

模糊查询   if('"+projectName+"'!='null',project1_.project_name  LIKE CONCAT('%','"+projectName+"','%'),1=1)

排序分页   ORDER BY schedule0_.id DESC limit "+page+","+size+"";

然后，注意你查询的字段顺序在对应的Vo类中必须有对应参数顺序的构造方法，还有实体类中属性类型和数据库中类型对应java类型，特别需要注意，如：实体类中Long类型，数据库中生成bigInt类型，Vo类必须 BigInteger类型，Integer，Long都不行。

最后，注意以下3行代码，还有TrainingAllListVo.class（这个是你定义返回Vo类的.class，如：XXXVo.class）

Query query=entityManager.createNativeQuery(sql); query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(TrainingAllListVo.class)); query.getResultList();

 @Override
    public List<TrainingAllListVo> getAllTrainings(long teacherId, SchoolYear schoolYear, RegistrationStatus registrationStatus, EvaluationStatus evaluationStatus, String projectName,Integer page,Integer size) {
        String sql="SELECT project1_.id AS projectId,schedule0_.id as scheduleId, project1_.flage,project1_.project_name,project1_.school_year,schedule0_.schedule_name,schedule0_.schedule_begin_time,\n" +
            "schedule0_.schedule_end_time,project1_.training_form,grades6_.appraisal_scores,grades6_.actual_credits,schedulete4_.registration_status,school3_.school_name,organizati2_.organization_name,courses9_.course_status,notice10_.notice_status,leaves7_.check_status,evaluation8_.evaluation_status,evaluation8_.study_evaluation_status,notice10_.notice_path FROM SCHEDULE schedule0_ LEFT OUTER JOIN project project1_ ON schedule0_.project_id = project1_.id LEFT OUTER JOIN organization organizati2_ ON project1_.organization_id = organizati2_.id LEFT OUTER JOIN school school3_ ON project1_.school_id = school3_.id LEFT OUTER JOIN schedule_teacher schedulete4_ ON schedule0_.id = schedulete4_.schedule_id LEFT OUTER JOIN teacher teacher5_ ON schedulete4_.teacher_id = teacher5_.id LEFT OUTER JOIN grade grades6_ ON teacher5_.id = grades6_.teacher_id LEFT OUTER JOIN LEAVES leaves7_ ON teacher5_.id = leaves7_.teacher_id LEFT OUTER JOIN evaluation evaluation8_ ON schedule0_.id = evaluation8_.schedule_id LEFT OUTER JOIN course courses9_ ON schedule0_.id = courses9_.schedule_id LEFT OUTER JOIN notice notice10_ ON schedule0_.notice_id = notice10_.id WHERE schedulete4_.teacher_id ="+teacherId+" and if('"+schoolYear+"'!='null',project1_.school_year='"+schoolYear+"',1=1) and if('"+registrationStatus+"'!='null',schedulete4_.registration_status='"+registrationStatus+"',1=1) and if('"+evaluationStatus+"'!='null',evaluation8_.evaluation_status='"+evaluationStatus+"',1=1) and if('"+projectName+"'!='null',project1_.project_name  LIKE CONCAT('%','"+projectName+"','%'),1=1) ORDER BY schedule0_.id DESC limit "+page+","+size+"";
        Query query=entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(TrainingAllListVo.class));
        return query.getResultList();
    }

3. 控制层

  @GetMapping("/getAllTrainings")
    @PreAuthorize("hasRole(\""+ AuthoritiesConstants.TEACHER +"\")")
    public List<TrainingAllListVo>getAllTrainings(@AuthenticationPrincipal UserDetails userDetails, SchoolYear schoolYear, RegistrationStatus registrationStatus, EvaluationStatus evaluationStatus, String projectName,Integer page,Integer size){
        long teacherId=teacherRepository.getTeacherByUserName(userDetails.getUsername()).getId();
        return teacherService.getAllTrainings(teacherId,schoolYear,registrationStatus,evaluationStatus,projectName,page,size);
    }

效果如下：为null的那些是数据库没有值

 

SpringDataJPA中使用EntityManager操作返回多表连接结果集

相关推荐