spring data jpa为我们提供了JpaSpecificationExecutor接口,只要简单实现toPredicate方法就可以实现复杂的查询。JpaSpecification查询的关键在于怎么构建Predicates。
下面通过示例对其进行学习。
由运动员表(player)和助手表(assistant)表,它们的关系未一对多,即一个运动员可以有多个助手,一个助手只能服务一个运动员。
![bb53604ce8588930c09de64c583cc6a6.png](https://i-blog.csdnimg.cn/blog_migrate/41dba67936b92cf33825c58a5dbcbce5.png)
对应实体类如下:
PlayerEntity:
@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "player")
public class PlayerEntity implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
// 国籍
private String country;
// 运动员姓名
private String name;
// 运动种类
private String profession;
// 获得金牌次数
private int goldenNum;
// 是否决定参加奥运会
private boolean attend;
@OneToMany(targetEntity = AssistantEntity.class)
@org.hibernate.annotations.ForeignKey(name = "none")
@JoinColumn(name = "player_assistant", referencedColumnName = "name")
private List<AssistantEntity> list;
}
AssistantEntity:
@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "assistant")
public class AssistantEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
// 助理名称
private String name;
// 助理年龄
private int age;
}
数据库已有数据情况如下:
![9af56cb67e521c639b001baaf73e54b0.png](https://i-blog.csdnimg.cn/blog_migrate/0ef0fc2ccf52df8aabbbcd5748c896e5.png)
![0603debab6366f1cf3f352a077242408.png](https://i-blog.csdnimg.cn/blog_migrate/931e3dbf6d53fe8a4a0a8eea48880cb2.png)
① 使用and和or的简单复合查询
多个and, equal查询
我们此处查询player,故有PlayerRepo
PlayerRepo:
@Repository
public interface PlayerRepo extends JpaRepository<PlayerEntity, Long>, JpaSpecificationExecutor {
}
Controller代码(按照代码规范,不应在Controller层写业务逻辑代码,此处为了方便展示,将业务逻辑代码写在Controller方法中)
@GetMapping("/jpatest7")
public List<PlayerEntity> jpatest7() {
Specification spec = (Specification) (root, criteriaQuery, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
Predicate countryPredicate = criteriaBuilder.equal(root.get("country"), "中国");
Predicate professionPredicate = criteriaBuilder.equal(root.get("profession"), "篮球");
Predicate goldenPredicate = criteriaBuilder.equal(root.get("goldenNum"), 5);
predicates.add(countryPredicate);
predicates.add(professionPredicate);
predicates.add(goldenPredicate);
return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
};
List<PlayerEntity> list = playerRepo.findAll(spec);
return list;
}
记得注入playerRepo。
root.get("country"),对应着实体类的相关属性country,root.get属性对应的实体类,我认为从使用该spec的Repository对应的实体类相对应,比如此处的playerRepo对应的实体类PlayerEntity,实际上如果属性对不上,运行时会报错。root.get取得相应实体的操作字段。
而criteriaBuilder.equal和criteriaBuilder.and,则是用来构建复杂查询
criteriaBuilder.add 或者 criteriaBuilder.or等方法的返回值也为Predicate对象。
此处示例对应的sql语句为:
select * from player where country = "中国" and profession = "篮球" and golden_num = 5;
Specification查询中用实体类属性,sql语句中用数据库字段。
访问接口结果如下:
![a59682eebf6dc01a2fef4f3be5fa4d30.png](https://i-blog.csdnimg.cn/blog_migrate/9e204e3b1ab4dbb5e21986f9eebd2f02.png)
修改
return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
为
return criteriaBuilder.or(predicates.toArray(new Predicate[predicates.size()]));
此时对应的sql语句为select * from player where country = "中国" or profession = "篮球" or golden_num = 5;
表中所有数据都复合条件,都查出来,访问结果为:
![3bc16bb0105ca169e87c49f3e0429aa9.png](https://i-blog.csdnimg.cn/blog_migrate/f11fb6becedb4db151c967b19f4b1758.png)
现在使用and和or进行混合查询:
controller代码:
@GetMapping("/jpatest7")
public List<PlayerEntity> jpatest7() {
Specification spec = (Specification) (root, criteriaQuery, criteriaBuilder) -> {
Predicate p1 = criteriaBuilder.equal(root.get("country"), "中国");
Predicate p2 = criteriaBuilder.equal(root.get("goldenNum"), 5);
Predicate p3 = criteriaBuilder.equal(root.get("name"), "郭艾伦");
Predicate p = criteriaBuilder.and(p1);
p = criteriaBuilder.and(p, p2);
p = criteriaBuilder.or(p, p3);
return p;
};
List<PlayerEntity> list = playerRepo.findAll(spec);
return list;
}
对应的sql语句为:
select * from player where country = "中国" and golden_num = 5 or name = "郭艾伦";
数据库数据如下:
![cbd1ff07803e47d3d284df5d57d7dbef.png](https://i-blog.csdnimg.cn/blog_migrate/c9f3841146df2ef08d5c651c158b803c.png)
![32c12502814541c78c8c87533b51edf5.png](https://i-blog.csdnimg.cn/blog_migrate/01b1fd7dcbacdef95b6db25178bb1974.png)
访问接口的结果如下:
![baec7fd9e119bca14ba83b1ec051a6b6.png](https://i-blog.csdnimg.cn/blog_migrate/dab9fc5afb6213293edaad2dc9aa8a08.png)
注意:springboot设置了表关系,会自动将player的对象的assistant信息注入,故等价的sql语句运行时也会查出相应的assistant信息。
结合liked的查询条件
controller代码:
@GetMapping("/jpatest8")
public List<PlayerEntity> jpatest8() {
Specification spec = (Specification) (root, criteriaQuery, criteriaBuilder) -> {
Predicate p = criteriaBuilder.like(root.get("name"), "%" + "詹姆斯" + "%");
return p;
};
List<PlayerEntity> list = playerRepo.findAll(spec);
return list;
}
相当于sql语句:
select * from player where name like "%詹姆斯%";
数据库数据如下:
![f927056491de92bda8b41944804c59d8.png](https://i-blog.csdnimg.cn/blog_migrate/3ef0ee7eba03ce758f73dc0667add1fb.png)
![1ae8f6943d86c14431dd5a6961317f60.png](https://i-blog.csdnimg.cn/blog_migrate/dcf13cf2861b6cc49f9a7a941816aa08.png)
访问接口的结果如下:
![beacc110b89dc09e19e65c1fb08be258.png](https://i-blog.csdnimg.cn/blog_migrate/7ab26896f140b1e62bbfba0221a7e833.png)
大于的查询条件(greaterThan)
controller代码:
@GetMapping("/jpatest9")
public List<PlayerEntity> jpatest9() {
Specification spec = (Specification) (root, criteriaQuery, criteriaBuilder) -> {
Predicate p = criteriaBuilder.greaterThan(root.get("goldenNum"), 3);
return p;
};
List<PlayerEntity> list = playerRepo.findAll(spec);
return list;
}
相当于SQL语句:
select * from player where golden_num > 3;
再次提醒root.get()方法的参数为实体类的属性
数据库数据如下:
![a98d1dc0af40864f147129d4372ff1b9.png](https://i-blog.csdnimg.cn/blog_migrate/a3a810a9fad26f0211d4cd7effa4e862.png)
![471e2d7320e0113f56ad71ad7be8de5a.png](https://i-blog.csdnimg.cn/blog_migrate/c1195e4615648bd3cf0eb811e6f2e409.png)
访问接口结果:
![566e25505c2472542b5f3db58cc88e06.png](https://i-blog.csdnimg.cn/blog_migrate/0df96a554e7033b3560fabc20eeb33f3.png)
其余的大小关系总结如下:
大于:criteriaBuilder.greaterThan
小于:criteriaBuilder.lessThan
大于等于:criteriaBuilder.greaterThanOrEqualTo
小于等于:criteriaBuilder.lessThanOrEqualTo
用法类似,不再赘述
区间查询(between and)
controller代码:
@GetMapping("/jpatest10")
public List<PlayerEntity> jpatest10() {
Specification spec = (Specification) (root, criteriaQuery, criteriaBuilder) -> {
Predicate p = criteriaBuilder.between(root.get("goldenNum"), 2, 4);
return p;
};
List<PlayerEntity> list = playerRepo.findAll(spec);
return list;
}
对criteriaBuilder.between(root.get("goldenNum"), 2, 4),其中2为左边界,4为右边界。
查询出的结果既包含左边界,也包含右边界。
相当于sql语句:
select * from player where golden_num between 2 and 4;
数据库情况如上,不再赘述。
访问接口的结果如下:
![8665a2f72113886ee0459be222df2825.png](https://i-blog.csdnimg.cn/blog_migrate/b8540e4ce6a730d388da71883a702ecd.png)
联表查询
个人的水平有限,下述记录如有错误,烦请说明错误点和原因:
重新给定两个Entity,StudentEntity和TeacherEntity,其中考虑“一对一的家教模式”,即一个学生可以有很多的辅导老师,一个老师只能辅导一个学生。即StudentEntity 和 TeacherEntity 的关系为 “一对多”关系。
StudentEntity:
@Entity
@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Log4j
@Table(name = "student")
public class StudentEntity implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private int age;
@OneToMany(targetEntity = TeacherEntity.class, mappedBy = "student")
@org.hibernate.annotations.ForeignKey(name = "none")
private List<TeacherEntity> teacherList;
}
TeacherEntity:
@Entity
@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Log4j
@Table(name = "teacher")
public class TeacherEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String subject;
@ManyToOne(targetEntity = StudentEntity.class)
@org.hibernate.annotations.ForeignKey(name = "none")
@JoinColumn(name = "student", referencedColumnName = "name")
private StudentEntity student;
}
对应的Repository:
StudentRepo:
@Repository
public interface StudentRepo extends JpaRepository<StudentEntity, Long>, JpaSpecificationExecutor {
}
TeacherRepo:
@Repository
public interface TeacherRepo extends JpaRepository<TeacherEntity, Long>, JpaSpecificationExecutor {
}
Controller代码:
@GetMapping("/jpatest11")
public List<TeacherDTO> jpatest11() {
Specification spec = (Specification) (root, criteriaQuery, criteriaBuilder) -> {
Join<TeacherEntity, StudentEntity> join = root.join("student", JoinType.INNER);
Predicate p = criteriaBuilder.equal(join.get("age"), "20");
return p;
};
List<TeacherEntity> list = teacherRepo.findAll(spec);
List<TeacherDTO> rList = new ArrayList<>();
for (TeacherEntity entity : list) {
TeacherDTO teacherDTO = new TeacherDTO(entity.getId(), entity.getName(), entity.getSubject());
StudentDTO dto = new StudentDTO(entity.getStudent().getId(), entity.getStudent().getName(), entity.getStudent().getAge());
teacherDTO.setStudent(dto);
rList.add(teacherDTO);
}
return rList;
}
这里有几个注意点:
①Join<TeacherEntity, StudentEntity> join = root.join("student", JoinType.INNER);
其中Join<TeacherEntity, StudentEntity> 中的TeacherEntity,我理解为主表,而StudentEntity对应的表为被联接的表。因此,后面的root.join("student", JoinType.INNER)中的“student”, 为TeacherEntity中持有的StudentEntity的属性。
②Join<TeacherEntity, StudentEntity> join = root.join("student", JoinType.INNER);
中的JoinType有INNER,LEFT,RIGHT三个值,但指定为RIGHT时会报错RIGHT METHOD不支持,目前还不知道原因。
③ Predicate p = criteriaBuilder.equal(join.get("age"), "20");
在获得join对象后,通过join.get("age")来得到相应的属性值,此处注意,join.get() 中的参数只能是被连接表(此处为Studentity)对应实体的属性,若传入主表的属性如“subject”,会报错:
Unable to locate Attribute with the the given name [subject] on this ManagedType [com.example.tplink.entity.jpa.entity.StudentEntity]
④ 由于③中的限制,我自己觉得这样的话,那么JoinType.LEFT 的实际作用不是和JoinType.INNER 一样了吗,因为mysql的左连接会保留左表,与右表拼成一个整表,当右表不符合条件时,对应字段为空,示意图如下:
![49694c569f19426ab7fe5ac43197a1fc.png](https://i-blog.csdnimg.cn/blog_migrate/11d039382f1548cb9120d5476688baf8.png)
select * from company c left join user u on c.id = u.id;
左连接的结果为:
![c424312d499d19642b91c3d630e8e242.png](https://i-blog.csdnimg.cn/blog_migrate/4842d39addeef71955ceeeade858aa22.png)
又只能查询被连接表的字段,那么表连接表的字段不为空,实际也就是内连接。
⑤ 解决相互持有关系两表查询的栈溢出异常
像StudentEntiy和TeacherEntity这样,相互持有对方。在查询方法中,如果返回的是SudentEntity或者TeacherEntity实体类,那么就会导致循环查找,最后发生栈溢出异常。
以上述代码为例:
若返回的是List<TeacherEntity> 那么对List中的每一个Entity,会去找到它持有的StudentEntity 对象student,而在StudentEntity中,又会反过来查找List<TeacherEntity>因为StudentEntity中也有属List<TeacherEntity> teacherList ,这样就会导致循环查找,最后栈溢出。正确的方法是返回包装类,而不是直接返回Entity类。这也是上述代码中返回DTO的原因。
数据库情况:
![2310ba5427768405429758ed34c170f9.png](https://i-blog.csdnimg.cn/blog_migrate/edd7be74dc5bece9d6c1645a8eacc2dc.png)
![4aa7bce086c716cbc09ea1f785377b7d.png](https://i-blog.csdnimg.cn/blog_migrate/1c4499131049709f60149a8d524fcb86.png)
访问接口的结果如下:
![65e70d922554c131450aed55bf2077cb.png](https://i-blog.csdnimg.cn/blog_migrate/072de57c7f2dd3c3bc9d72408fde5935.png)
当以StudentEntity为主表,TeacherEntity为被连接表时,
Controller代码如下:
@GetMapping("/jpatest12")
public List<StudentDTO> jpatest12() {
Specification spec = (Specification) (root, criteriaQuery, criteriaBuilder) -> {
Join<StudentEntity, TeacherEntity> join = root.join("teacherList", JoinType.INNER);
Predicate p = criteriaBuilder.equal(join.get("subject"), "数学");
return p;
};
List<StudentEntity> list = studentRepo.findAll(spec);
List<StudentDTO> rList = new ArrayList<>();
for (StudentEntity entity : list) {
StudentDTO dto = new StudentDTO(entity.getId(), entity.getName(), entity.getAge());
List<TeacherDTO> tList = new ArrayList<>();
for (TeacherEntity tentity : entity.getTeacherList()) {
TeacherDTO tdto = new TeacherDTO(tentity.getId(), tentity.getName(), tentity.getSubject());
tList.add(tdto);
}
dto.setTeacherList(tList);
rList.add(dto);
}
return rList;
}
访问接口的结果为:
![2ae8b17960c56bbd62b4955ca92ec98f.png](https://i-blog.csdnimg.cn/blog_migrate/f6ded6a3ec801084491a1e08c9ab4885.png)