Specification的基本使用
Specification的基本使用
Specification是啥,相信你都需要使用了,我就不过多介绍了,直接上代码
1.基础表结构
create database jpa;
use jpa;
CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`age` int(4) NOT NULL,
`teacher_no` int(8) NOT NULL,
`status` char(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO jpa.teacher (name,age,teacher_no,status) VALUES
('joshuay',20,1001,'Y'),
('felixf',30,1002,'Y'),
('stanh',25,1003,'Y');
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`teacher_no` int(8) NOT NULL,
`name` varchar(16) NOT NULL,
`status` char(1) NOT NULL,
`age` int(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO jpa.student (teacher_no,name,status,age) VALUES
(1001,'张三','Y',15),
(1001,'李四','Y',20),
(1002,'王五','Y',25),
(1002,'赵六','Y',30),
(1003,'金七','Y',35);
2.建立实体类
Teacher类
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(catalog = "jpa", name = "teacher")
public class Teacher implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Integer id;
@Column(name = "name")
private String name;
@Column(name = "age")
private Integer age;
@Column(name = "teacher_no")
private Integer teacherNo;
@Column(name = "status")
private String status;
// @JsonIgnore
@OneToMany(cascade = {CascadeType.ALL}, fetch = FetchType.EAGER)
@JoinColumn(name = "teacher_no", referencedColumnName = "teacher_no", insertable = false, updatable = false)
private List<Student> studentList;
}
Student类
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(catalog = "jpa", name = "student")
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Integer id;
@Column(name = "name")
private String name;
@Column(name = "age")
private Integer age;
@Column(name = "teacher_no")
private Integer teacherNo;
@Column(name = "status")
private String status;
}
3.建立对应的Repository接口并继承相应的接口
public interface TeacherRepository extends JpaRepository<Teacher, Integer>, JpaSpecificationExecutor<Teacher> {
}
4.编写相应的Specification代码并进行单元测试
编写Specification实现逻辑
public class TeacherRepositorySpec {
private TeacherRepositorySpec() {
}
public static Specification<Teacher> byTeacherName(@NonNull String name) {
return (root, query, builder) -> {
Predicate conjunction = builder.conjunction();
List<Expression<Boolean>> expressions = conjunction.getExpressions();
expressions.add(builder.equal(root.get("name"), name));
return conjunction;
};
}
public static Specification<Teacher> byReportForm(Map<String, Object> reportForm) {
return (root, query, builder) -> {
List<Predicate> sqlList = new ArrayList<>();
sqlList.add(builder.equal(root.get("status"), "Y"));
if (!ObjectUtils.isEmpty(reportForm.get("name"))) {
String name = (String) reportForm.get("name");
sqlList.add(builder.like(root.get("name"), "%" + name + "%"));
}
// join table student
Join<Teacher, Student> studentJoin = root.join("studentList");
studentJoin = studentJoin.on(builder.equal(studentJoin.get("status"), "Y"));
if (!ObjectUtils.isEmpty(reportForm.get("age"))) {
Integer age = (Integer) reportForm.get("age");
sqlList.add(builder.gt(studentJoin.get("age"), age));
}
query.where(sqlList.toArray(new Predicate[0]));
return null;
};
}
public static Specification<Teacher> byAgeRange(Integer min,Integer max) {
return (root, query, builder) -> {
Assert.isTrue(Integer.compare(max,min) == 1,"max can not more than min");
List<Predicate> sqlList = new ArrayList<>();
sqlList.add(builder.equal(root.get("status"), "Y"));
// join table student
Join<Teacher, Student> studentJoin = root.join("studentList");
studentJoin = studentJoin.on(builder.equal(studentJoin.get("status"), "Y"));
sqlList.add(builder.between(root.get("age"),min,max));
return builder.and(sqlList.toArray(new Predicate[0]));
};
}
}
单元测试
@SpringBootTest
class DemoSpringApplicationTests {
@Autowired
private TeacherRepository teacherRepository;
@Test
void test_spec() {
Specification<Teacher> spec1 = TeacherRepositorySpec.byAgeRange(19, 26);
List<Teacher> rangeTeacherList = teacherRepository.findAll(spec1);
rangeTeacherList.forEach(System.out::println);
System.out.println("================================================");
Specification<Teacher> spec2 = TeacherRepositorySpec.byTeacherName("felixf");
List<Teacher> nameTeacherList = teacherRepository.findAll(spec2);
nameTeacherList.forEach(System.out::println);
System.out.println("================================================");
HashMap<String,Object> reportForm = new HashMap<String,Object>(){
{
put("name","h");
put("age",20);
}
};
Specification<Teacher> spec3 = TeacherRepositorySpec.byReportForm(reportForm);
List<Teacher> formTeacherList = teacherRepository.findAll(spec3);
formTeacherList.forEach(System.out::println);
/* 打印日志以及输出结果如下:
select
teacher0_.id as id1_8_,
teacher0_.age as age2_8_,
teacher0_.name as name3_8_,
teacher0_.status as status4_8_,
teacher0_.teacher_no as teacher_5_8_
from
jpa.teacher teacher0_
inner join
jpa.student studentlis1_
on teacher0_.teacher_no=studentlis1_.teacher_no
and (
studentlis1_.status=?
)
where
teacher0_.status=?
and (
teacher0_.age between 19 and 26
)
Teacher(id=1, name=joshuay, age=20, teacherNo=1001, status=Y, studentList=[Student(id=1, name=张三, age=15, teacherNo=1001, status=Y), Student(id=2, name=李四, age=20, teacherNo=1001, status=Y)])
Teacher(id=1, name=joshuay, age=20, teacherNo=1001, status=Y, studentList=[Student(id=1, name=张三, age=15, teacherNo=1001, status=Y), Student(id=2, name=李四, age=20, teacherNo=1001, status=Y)])
Teacher(id=3, name=stanh, age=25, teacherNo=1003, status=Y, studentList=[Student(id=5, name=金七, age=35, teacherNo=1003, status=Y)])
================================================
select
teacher0_.id as id1_8_,
teacher0_.age as age2_8_,
teacher0_.name as name3_8_,
teacher0_.status as status4_8_,
teacher0_.teacher_no as teacher_5_8_
from
jpa.teacher teacher0_
where
teacher0_.name=?
Teacher(id=2, name=felixf, age=30, teacherNo=1002, status=Y, studentList=[Student(id=3, name=王五, age=25, teacherNo=1002, status=Y), Student(id=4, name=赵六, age=30, teacherNo=1002, status=Y)])
================================================
select
teacher0_.id as id1_8_,
teacher0_.age as age2_8_,
teacher0_.name as name3_8_,
teacher0_.status as status4_8_,
teacher0_.teacher_no as teacher_5_8_
from
jpa.teacher teacher0_
inner join
jpa.student studentlis1_
on teacher0_.teacher_no=studentlis1_.teacher_no
and (
studentlis1_.status=?
)
where
teacher0_.status=?
and (
teacher0_.name like ?
)
and studentlis1_.age>20
Teacher(id=3, name=stanh, age=25, teacherNo=1003, status=Y, studentList=[Student(id=5, name=金七, age=35, teacherNo=1003, status=Y)])
*/
}
}
