关于SpringDataJPA中多表关联查询，自定义实体类接收返回的结果集

Java光头强 • 2023年2月20日下午7:52 • 技术随笔 • 阅读 159

在项目中使用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( )括号里面写你查得东西，注意这里面返回数据顺序要和你实体类中构造方法参数顺序一致，否则会出错。

运行结果如下：