Spring data JPA 的分页功能及复杂语句查询(修改第二次)

测试的数据表

 

import com.example.demo.entity.DemoUser;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface DemoUserRepository extends JpaRepository<DemoUser,Integer>,JpaSpecificationExecutor<DemoUser> {

}

import com.example.demo.entity.DemoUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;

import javax.persistence.criteria.*;

@Service
public class JPAService {

    @Autowired
    DemoUserRepository demoUserRepository;

    public Page<DemoUser> pageFindAll(int page ,int size){
        Sort sort = new Sort(Sort.Direction.DESC, "id");        //根据id降序排序
        Pageable pageable = PageRequest.of(page, size, sort);   //page当前页，size一页大小
        Page<DemoUser> demoUserPage = demoUserRepository.findAll(pageable);
        return demoUserPage;
    }

}

测试代码

    @Test
    public void jpat(){
        Page<DemoUser> demoUserPage = jpaService.pageFindAll(0,2);
        System.out.println("总记录数："+demoUserPage.getTotalElements());
        System.out.println("总页数："+demoUserPage.getTotalPages());
        System.out.println("一页有多少记录："+demoUserPage.getSize());
        System.out.println("当前页数："+demoUserPage.getNumber());
        System.out.println("记录："+demoUserPage.getContent());
    }

上面这个pageFindAll()方法运行结果

page在 pageFindAll() 是一个参数，表示当前页索引，但是在计算sql语句的limit需要开始和结束位置时是 page * size 和 (page+1) * size，才能得到我们需要那一页的数据，所以这里面我们传入的page都是从0开始，在得出结果或者到达前端时再加1就好了。

Repository继承JpaSpecificationExecutor可以构建复杂的查询语句

/**
     * 使用JPA分页功能并有条件查询，单个条件
     * 模糊查询所有记录username like %字符%，降序id排序
     * 分页，一页2条记录
     */
    public Page<User> findByUsernameLike(String username) {
        Specification<User> specification = new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                Path<String> _username = root.get("username");
                Predicate _key = criteriaBuilder.like(_username, "%" + username + "%");
                return criteriaBuilder.and(_key);
            }
        };
        Sort sort = new Sort(Sort.Direction.DESC, "id");
        Pageable pageable = PageRequest.of(0, 2, sort);
        Page<User> demoPage = userRepository.findAll(specification, pageable);
        return demoPage;
    }

toPredicate()方法在生成SQL语句时会添加  类似 “ where username like ? ” 的条件，可能 ‘ username ’ 会发生变化，因为hibernate在生成SQL语句时会为表生成别名。

测试代码

@Test
public void jpat2(){
    Page<DemoUser> demoUserPage = jpaService.findByUsernameLike("11");
    System.out.println("总记录数："+demoUserPage.getTotalElements());
    System.out.println("总页数："+demoUserPage.getTotalPages());
    System.out.println("一页有多少记录："+demoUserPage.getSize());
    System.out.println("当前页数："+demoUserPage.getNumber());
    System.out.println("记录："+demoUserPage.getContent());
}

运行结果