JPA - JPQL完成复杂的多表查询

前言

JPA中可以通过设置实体的导航属性 + JPQL完成复杂的多表查询，简化SQL的编写。

示例

建表语句

Table

CREATE TABLE `school` (`id` int(11) DEFAULT NULL,`name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ciCREATE TABLE `student` (`id` int(11) DEFAULT NULL,`name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ciCREATE TABLE `school_student` (`id` int(11) DEFAULT NULL,`school_id` int(11) DEFAULT NULL,`student_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

上述表中Studen和School呈现多对多关系

Entity实体

School.java

@Entity
@Setter
@Getter
public class School {@Idprivate Integer id;private String name;/*** 多对多关系配置*/@ManyToMany(fetch = FetchType.LAZY, mappedBy = "schoolList")private List<Student> studentList;}

Student.java

@Entity
public class Student {@Idprivate Integer id;private String name;/*** 多对多关系配置*/@ManyToMany(fetch = FetchType.LAZY)@JoinTable(name = "school_student",joinColumns = @JoinColumn(name = "student_id"),inverseJoinColumns = @JoinColumn(name = "school_id"))private List<School> schoolList;}

JPQL多表查询

在这里，通过Studen ID 查询出关联的 School
常规SQL关联查询：

selectsch.id ,sch.name fromschool schinner joinschool_student ss on sch.id = ss.school_id inner joinstudent stu on ss.student_id = stu.id wherestu.id = 1

JPQL多表查询：

@Query("select s from School s " +"join s.studentList ss " +"where ss.id = :id ")

完整代码

TestController.java

@RestController
@RequestMapping("/test")
public class TestController {@Autowiredprivate StudentRepository studentRepository;@GetMapping(value = "/test")public List<Student> test(@RequestParam Integer id){return studentRepository.findStudenBySchool(id);}}

StudentRepository.java

@Repository
public interface StudentRepository extends JpaRepository<School, Long> {@Query("select s from School s " +"join s.studentList ss " +"where ss.id = :id ")List<Student> findStudenBySchool(Integer id);}