JPA注解解决Hibernate的N+1问题
JPA注解解决Hibernate的N+1问题
数据准备
映射关系
一个班级对应多个学生 一对多
一个学生对应多个角色 一对多
一个学生对应多项权限，一个权限对应多个学生（例如收作业，语文课代表可以，数学课代表也可以） 多对多
数据实体类
/**
* 班级实体类
*/
@Data
@Entity
@Table(name = "class")
public class StudentClass {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String className;
@OneToMany(mappedBy = "studentClass", cascade = {CascadeType.ALL}, orphanRemoval = true)
@OrderBy("id asc")
private List<Student> students;
}
/**
* 学生实体类
*/
@Entity
@Table(name = "student")
@Data
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
private Integer age;
@ManyToOne
@JoinColumn(name = "class_id")
@JsonIgnore
private StudentClass studentClass;
@OneToMany(mappedBy = "student", cascade = {CascadeType.ALL})
private List<Role> roles = new ArrayList<>();
@ManyToMany
@JoinTable(
name = "student_permission",
joinColumns = @JoinColumn(name = "student_id"),
inverseJoinColumns = @JoinColumn(name = "permission_id")
)
private List<Permission> permissions = new ArrayList<>();
}
/**
* 角色实体类
*/
@Data
@Entity
@Table(name = "role")
public class Role {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String roleName;
@ManyToOne
@JoinColumn(name = "student_id")
@JsonIgnore
private Student student;
}
/**
* 权限实体类
*/
@Data
@Entity
@Table(name = "perminssion")
public class Permission {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
private Integer sort;
}
测试数据
{
"id": 3,
"className": "一班",
"students": [
{
"id": 11,
"name": "张三",
"age": 18,
"roles": [
{
"id": 8,
"roleName": "管理员"
},
{
"id": 9,
"roleName": "技术员"
},
{
"id": 11,
"roleName": "1"
}
],
"permissions": [
{
"id": 1,
"name": "发作业",
"sort": 2
},
{
"id": 2,
"name": "收作业",
"sort": 1
}
]
},
{
"id": 12,
"name": "李四",
"age": 18,
"roles": [
{
"id": 10,
"roleName": "岗位员"
}
],
"permissions": [
{
"id": 3,
"name": "测试",
"sort": 1
},
{
"id": 4,
"name": "111",
"sort": 2
}
]
},
{
"id": 13,
"name": "王五",
"age": 18,
"roles": [],
"permissions": []
}
]
}
N+1问题复现
- 调用查询方法
classRepository.findById(id).orElseGet(null);
- 查询结果
一个班有三个学生，每个学生都单独去查询了一次role与permission表，共8条sql语句
N+1解决办法
结合JPA使用@NamedEntityGraph注解
- 在StudentClass上面添加对应注解
@NamedEntityGraph(name = "calss.one", attributeNodes = {
@NamedAttributeNode(value = "students", subgraph = "students")
}, subgraphs = {
@NamedSubgraph(name = "students", attributeNodes = {
@NamedAttributeNode(value = "roles", subgraph = "roles"),
@NamedAttributeNode(value = "permissions", subgraph = "permissions")
})
})
- 在Repository重写findById方法
@Override
@EntityGraph(value="calss.one", type = EntityGraph.EntityGraphType.FETCH)
Optional<StudentClass> findById(Integer id);
- 调用
classRepository.findById(id).orElseGet(null);
- 查询结果
只有一条sql - 存在问题
使用了左外连接数据重复 - 数据重复问题解决
将实体类中的List改为Set接收
