关于SpringDataJPA中多表关联查询,自定义实体类接收返回的结果集
在项目中使用jpa进行多表连接查询,基本上返回的都是多张表中的数据。那么问题来了,这关联返回的数据那么多,还是不同表当中的数据,数据库中也没有表和它对应啊,怎么处理呢?返回Object吗?
其实可以用新建Vo的方法解决,话不多说,直接看代码示例:
示例实体类如下:
1.教师类
- public class Teacher implements Serializable {
-
- private static final long serialVersionUID = 1L;
-
- @Id
- @GeneratedValue(strategy = GenerationType.IDENTITY)
- private Long id;
-
- @Column(name = "teacher_name")
- private String teacherName;
-
- @Column(name = "teacher_id_card")
- private String teacherIdCard;
-
- @Enumerated(EnumType.STRING)
- @Column(name = "sex")
- private Sex sex;
-
- @Enumerated(EnumType.STRING)
- @Column(name = "teacher_status")
- private TeacherStatus teacherStatus;
-
- @Enumerated(EnumType.STRING)
- @Column(name = "highest_degree")
- private HighestDegree highestDegree;
-
- @Enumerated(EnumType.STRING)
- @Column(name = "specialized_technical_job")
- private SpecializedTechnicalJob specializedTechnicalJob;
-
- // getter setter 构造 tostring省略了,自己加哈........
- }
2.学校类
- public class School implements Serializable {
-
- private static final long serialVersionUID = 1L;
-
- @Id
- @GeneratedValue(strategy = GenerationType.IDENTITY)
- private Long id;
-
- @Column(name = "school_name")
- private String schoolName;
-
- @NotNull
- @Column(name = "school_principal", nullable = false)
- private String schoolPrincipal;
-
- // getter setter 构造 tostring省略了,自己加哈........
-
- }
接收多表关联返回结集,新建Vo类:该类中放你返回的数据,需要什么放什么(我这里放的是school和teacher两张表的定义)
-
- public class SchoolTeacherVo {
- private String teacherIdCard;
- private String teacherName;
- private Sex sex;
- private HighestDegree highestDegree;
- private SpecializedTechnicalJob specializedTechnicalJob;
- private TeacherStatus teacherStatus;
- private String schoolName;
-
- // getter setter 构造 tostring省略了,自己加哈........
-
- }
接口中方法:返回你定义的Vo泛型的list就行
- @Query(value = "SELECT new com.mycompany.myapp.vo.SchoolTeacherVo( b.teacherIdCard,b.teacherName,b.sex,b.highestDegree,b.specializedTechnicalJob,b.teacherStatus,c.schoolName) FROM ScheduleTeacher a inner JOIN Teacher b on a.teacher.id=b.id LEFT JOIN School c on b.school.id=c.id WHERE a.schedule.id= :scheduleId")
- List<SchoolTeacherVo>getTeachers(@Param("scheduleId") Long scheduleId);
注意:写sql时@Query(value = "SELECT new com.mycompany.myapp.vo.XXXVo( a.xxx,b.xxx)FROM XXX a inner JOIN YYY b on a.id=b.xxid WHERE XXXX= :XXX")
再次强调 new 绝对不能少,后面最好跟全限定类名(不写有可能找不到这个类),还有new com.mycompany.myapp.vo.XXXVo( )括号里面写你查得东西,注意这里面返回数据顺序要和你实体类中构造方法参数顺序一致,否则会出错。
运行结果如下: