文章目录
1 SpringDataJpa认识
1.1 SpringDataJpa结构
Spring Data JPA的七个Repository接口:
Repository(org.springframework.data.repository.Repository)
CrudRepository(org.springframework.data.repository.CrudRepository)
PagingAndSortingRepository(org.springframework.data.repository.PagingAndSortingRepository)
JpaRepository (org.springframework.data.jpa.repository.JpaRepository)
QueryByExampleExecutor(org.springframework.data.repository.query.QueryByExampleExecutor)
JpaSpecificationExecutor (org.springframework.data.jpa.repository.JpaSpecificationExecutor)
QueryDslPredicateExecutor (org.springframework.data.querydsl.QueryDslPredicateExecutor)
两大Repository实现类:
SimpleJpaRepository(org.springframework.data.jpa.repository.support.SimpleJpaRepository)
QueryDslJpaRepository(org.springframework.data.jpa.repository.support.QueryDslJpaRepository)
结构图:
说明:
- 几个查询、及批量保存方法,和 CrudRepository 接口相比,返回的是 List,使用起来更方便。
- 增加了 InBatch 删除, 实际执行时,后台生成一条sql语句,效率更高些。相比较而言,CrudRepository 接口的删除方法,都是一条一条删除的,即便是 deleteAll 也是一条一条删除的。
- 增加了 getOne() 方法,切记,该方法返回的是对象引用,当查询的对象不存在时,它的值不是Null
2 SpringDataJpa用法
2.1 普通CRUD
//查询所有
@Test
public void testFindAll()throws Exception{
List<Employee> list = employeeRepository.findAll();
for (Employee employee : list) {
System.out.println(employee);
}
}
//批量查询
@Test
public void testFindAll1()throws Exception{
List lists = Arrays.asList(1L,2L,3L);
List<Employee> list = employeeRepository.findAll(lists);
for (Employee employee : list) {
System.out.println(employee);
}
}
//查询一条数据
@Test
public void testFindOne()throws Exception{
Employee one = employeeRepository.findOne(1L);
System.out.println(one);
}
//保存
@Test
public void testSave()throws Exception{
Employee employee = new Employee();
employee.setUsername("皮皮虾");
employee.setPassword("123");
employee.setEmail("123@qq.com");
employee.setAge(18);
employeeRepository.save(employee);
}
//修改
@Test
public void testUpdate()throws Exception{
Employee employee = new Employee();
employee.setId(274L);
employee.setUsername("皮皮怪");
employee.setPassword("123");
employee.setEmail("123@qq.com");
employee.setAge(18);
employeeRepository.save(employee);
}
//删除
@Test
public void testDel()throws Exception{
employeeRepository.delete(274L);
}
//批量删除
@Test
public void testDel1()throws Exception{
List lists = Arrays.asList(272L,273L);
List all = employeeRepository.findAll(lists);
employeeRepository.deleteInBatch(all);
}
//总条数
@Test
public void testCount()throws Exception{
System.out.println(employeeRepository.count());
}
2.2 排序和分页
//进行分页功能
@Test
public void testPage() {
//1.需要先创建一个page对象(注意:页数是从0开始计算【0就是第1页】)
Pageable pageable = new PageRequest(0, 10);
//2.进行查询
Page<Employee> page = employeeRepository.findAll(pageable);
System.out.println(page.getTotalElements()); //总条数
System.out.println(page.getTotalPages()); //总页数
System.out.println(page.getContent()); //当前页数据
System.out.println(page.getNumber()); //第几页
System.out.println(page.getNumberOfElements()); //当前页有多少个数据
System.out.println(page.getSize()); //每页条数
}
//进行排序功能
@Test
public void testSort() {
//排序 :第一个参数是排序的规则(DESC/ASC) 后面参数是排序的字符
Sort sort = new Sort(Sort.Direction.DESC,"username");
List<Employee> emps = employeeRepository.findAll(sort);
for (Employee emp : emps) {
System.out.println(emp);
}
}
//分页与排序的集成
@Test
public void testPageAndSort() {
//排序 :第一个参数是排序的规则(DESC/ASC) 后面参数是排序的字符
Sort sort = new Sort(Sort.Direction.DESC,"username");
Pageable pageable = new PageRequest(0, 10,sort);
//2.进行查询
Page<Employee> page = employeeRepository.findAll(pageable);
for (Employee employee : page) {
System.out.println(employee);
}
}
2.3 根据条件进行查询
//通过员工名称来查询员工
List<Employee> findEmployeeByUsername(String username);
List<Employee> findEmployeeByUsernameLike(String username);
2.4 @Query注解查询
//查询数据--query注解查询
@Query("select o from Employee o where o.username like ?1")
List<Employee> findEmployee(String username);
//根据顺序查询
@Query("select o from Employee o where o.username like ?1 and o.email like ?2")
List<Employee> query02(String username,String email);
根据参数名称
@Query("select o from cn.itsource.pss.domain.Employee o where o.username like :username and o.email like :email")
List<Employee> query03(@Param("username") String username,@Param("email") String email);
//对原生sql语句
@Query(value="select count(*) from employee ",nativeQuery=true)
Long query04();
2.5 JPA规则(动态sql)的查询
步骤:
- 继承该JpaSpecificationExecutor 接口
- 测试
2.5.1 原生写法
@Test
public void testSpec()throws Exception{
List<Employee> list = employeeRepository1.findAll(new Specification<Employee>() {
@Override
public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
Path path = root.get("username");
Predicate predicate = cb.like(path, "%1%");
return predicate;
}
});
for (Employee employee : list) {
System.out.println(employee);
}
}
@Test
public void testSpec1()throws Exception{
List<Employee> list = employeeRepository1.findAll(new Specification<Employee>() {
@Override
public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
Path path1 = root.get("username");
Path path2 = root.get("age");
Predicate predicate1 = cb.like(path1, "%1%");
Predicate predicate2 = cb.gt(path2, 25);
CriteriaQuery<?> where = cq.where(predicate1, predicate2);
return where.getRestriction();
}
});
for (Employee employee : list) {
System.out.println(employee);
}
}
//条件+分页排序
@Test
public void testSpec2()throws Exception{
Sort sort = new Sort(Sort.Direction.DESC, "age");
Pageable pageable = new PageRequest(0, 5,sort);
Specification<Employee> specification = new Specification<Employee>() {
@Override
public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
Path path1 = root.get("username");
Predicate predicate1 = cb.like(path1, "%1%");
return predicate1;
}
};
Page<Employee> page = employeeRepository1.findAll(specification, pageable);
for (Employee employee : page) {
System.out.println(employee);
}
}
2.5.2 springdatajpa的查询sql的插件
- 引入第三方jar包
- 继承 JpaSpecificationExecutor 接口
Specification<Employee> specification = Specifications.<Employee>and()
.eq(StringUtils.isNotBlank(employee.getUsername()),"username", employee.getUsername())
.gt(employee.getAge() !=null, "age", employee.getAge())
.build();
2.6 BaseQuery和EmployeeQuery这些抽取
BaseQuery类
/**
* BaseQuery:公共查询对象 完成分页查询
*/
public abstract class BaseQuery {
//当前页
private Integer currentPage = 1;
//每页条数
private Integer pageSize = 10;
//排序字段
private String orderByName;
//排序类型
private String orderByType = "DESC";
public Integer getJpaPage(){
return currentPage -1;
}
//排序
public Sort createSort() {
Sort sort = null;
if (getOrderByName() != null) {
String orderByName = getOrderByName();
Sort.Direction direction = null;
if (getOrderByType().equals("DESC")) {
direction = Sort.Direction.DESC;
} else {
direction = Sort.Direction.ASC;
}
sort = new Sort(direction, orderByName);
}
return sort;
}
//分页
public abstract Specification createSpecification();
}
EmployeeQuery类
/**
* 单独查询对象
*/
public class EmployeeQuery extends BaseQuery{
//自身条件
private String username;
private String email;
private Integer age;
//抽取查询
@Override
public Specification createSpecification() {
Specification<Employee> specification = Specifications.<Employee>and()
.like(StringUtils.isNotBlank(username), "username","%"+username+"%")
.gt(Objects.nonNull(age), "age", age)
.build();
return specification;
}
}
实现方法
@Test
public void testSpec1()throws Exception{
EmployeeQuery employeeQuery = new EmployeeQuery();
employeeQuery.setUsername("1");
employeeQuery.setAge(18);
employeeQuery.setOrderByName("age");
employeeQuery.setOrderByType("ASC");
Specification specification = employeeQuery.createSpecification();
Sort sort = employeeQuery.createSort();
//分页
Pageable pageable = new PageRequest(employeeQuery.getJpaPage(), employeeQuery.getPageSize(),sort);
Page<Employee> page = employeeRepository1.findAll(specification, pageable);
for (Employee employee : page) {
System.out.println(employee);
}
}