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_ci

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

CREATE 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 {

    @Id
    private Integer id;
    private String name;

    /**
     * 多对多关系配置
     */
    @ManyToMany(fetch = FetchType.LAZY, mappedBy = "schoolList")
    private List<Student> studentList;

}

• Student.java

@Entity
public class Student {

    @Id
    private 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关联查询：

select
        sch.id ,
        sch.name 
    from
        school sch
    inner join
        school_student ss 
            on sch.id = ss.school_id 
    inner join
        student stu 
            on ss.student_id = stu.id 
    where
        stu.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 {

    @Autowired
    private 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);

}

调用结果

- End - - 个人学习笔记 - - 仅供参考 -