基于方法名的查询,尽管很简单,但是满足不了复杂查询,可以使用JpaSpecificationExecutor来进行复杂查询
一.数据库中创建user、user_comment表
user_comment.user_id = user.id
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
`email` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `test`.`user`(`id`, `name`, `email`) VALUES (1, 'admin', '张三@李四.com');
CREATE TABLE `user_comment` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`comment` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `test`.`user_comment`(`id`, `user_id`, `comment`) VALUES (1, 1, 'comment1');
二.生成entity
生成entity步骤参见 SpringBoot 从入门到入门(五 ORM-JPA-1)
三.修改IUserRepository,继承 JpaSpecificationExecutor
package com.example.demo.repository;
import com.example.demo.entity.UserEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
@Repository
public interface IUserRepository extends JpaRepository<UserEntity,Integer>, JpaSpecificationExecutor<UserEntity> {
}
JpaSpecificationExecutor接口方法中接收Specification作为参数
三.编写Specification
为了演示方便,在Demo1Application文件中创建Specification
在类中新增implements Specification的子类
private class MySpec implements Specification<UserEntity>{
@Override
public Predicate toPredicate(Root<UserEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
return null;
}
}
在toPredicate方法中,编写查询内容
private class MySpec implements Specification<UserEntity>{
@Override
public Predicate toPredicate(Root<UserEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Path<Integer> id = root.get("id");
Path<String> name = root.get("name");
Path<String> email = root.get("email");
Predicate predicate = criteriaBuilder.or(criteriaBuilder.like(name,"%ad%"),criteriaBuilder.equal(email,"张三@李四.com"));
return criteriaBuilder.and(predicate,criteriaBuilder.equal(id,1));
}
}
上述例子SQL为
select userentity0_.id as id1_0_, userentity0_.email as email2_0_, userentity0_.name as name3_0_ from user userentity0_ where (userentity0_.name like ? or userentity0_.email=?) and userentity0_.id=1
四.创建路由并访问
@GetMapping("/user/all")
public List<UserEntity> userAll() {
List<UserEntity> list = userRepository.findAll(new MySpec());
return list;
}
五.重复第三步,可实现复杂查询
1.JOIN
- 修改UserEntity,增加字段,配置关联关系
编写toPredicateprivate Set<UserCommentEntity> userCommentEntity; @OneToMany @JoinColumn(name = "user_id",referencedColumnName = "id") public Set<UserCommentEntity> getUserCommentEntity(){ return userCommentEntity; } public void setUserCommentEntity(Set<UserCommentEntity> userCommentEntity){ this.userCommentEntity = userCommentEntity; }
private class MySpec implements Specification<UserEntity>{ @Override public Predicate toPredicate(Root<UserEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { Path<Integer> id = root.get("id"); Path<String> name = root.get("name"); Path<String> email = root.get("email"); Predicate predicate = criteriaBuilder.or(criteriaBuilder.like(name,"%ad%"),criteriaBuilder.equal(email,"张三@李四.com")); Join<UserEntity, UserCommentEntity> join = root.join("userCommentEntity",JoinType.LEFT); return criteriaBuilder.and(predicate,criteriaBuilder.equal(id,1)); } }
等同于
select userentity0_.id as id1_0_, userentity0_.email as email2_0_, userentity0_.name as name3_0_ from user userentity0_ left outer join user_comment usercommen1_ on userentity0_.id=usercommen1_.user_id where (userentity0_.name like ? or userentity0_.email=?) and userentity0_.id=1