jpa 分页查询几种demo Example分页/ExampleMatcher/Specification分页/SQL分页

以下都是基于spring-data-jpa2.1.8

1.单纯分页，没有条件

/***
     * 直接用dao调用分页
     */
    @Test
    public void findPageDao1() {
        try {
            System.out.println("=============== findPage ================" );

            //jpa 1.*写法
            //Pageable pageable = new PageRequest(0,2, Sort.Direction.DESC,"userId");//sprint-data-common 2.0以下用这个
            //jpa 0.*写法 1
            //Sort sort=Sort.by(Sort.Direction.DESC,"userId");
            //Pageable pageable = PageRequest.of(0, 2,sort);
            //jpa 0.*写法 2
            Pageable pageable = PageRequest.of(0, 2, Sort.Direction.DESC,"userId");//一定要是model中的字段注意大小写,sprint-data-common 2.0以上用这个
            Page<SysUser> pageList=sysUserDao.findAll(pageable);
            if(pageList!=null && pageList.getTotalPages()>0){
                System.out.println("page list:" );
                for(SysUser item:pageList){
                    System.out.println("userid: "+item.getUserId()+"__userAccount:"+item.getAccount());
                }
            }else {
                System.out.println("page is null:" );
            }
        } catch (Exception e) {
            System.out.println("error:" +e.getMessage());
            // e.printStackTrace();
        }
    }

2.(不推荐)使用findAll(example,pageable)Example/ExampleMatcher表达式分页 不支持   a=1 and (b=2 or c=3)这样的逻辑 https://stackoverflow.com/questions/42584191/spring-query-by-example-using-or

/***
     * (不推荐)直接用dao调用分页带条件  Example<S>  不支持   a=1 and (b=2 or c=3)这样的逻辑 https://stackoverflow.com/questions/42584191/spring-query-by-example-using-or
     */
    @Test
    public void findPageDao2() {
        try {
            System.out.println("=============== findPage ================" );
            Sort sort = new Sort(Sort.Direction.DESC, "userId");
            //分页
            int page=1;//第几页
            int pageSize=2;//每页条数
            //Pageable pageable = new PageRequest(page - 1, pageSize, sort);//已过时
            Pageable pageable = PageRequest.of(page - 1, pageSize, sort);

            //多条件  account='test' and username='zw'
            SysUser entity=new SysUser();
            entity.setAccount("test");
            entity.setUserName("zw");
            ExampleMatcher matcher = ExampleMatcher.matching()
                    .withMatcher("account", ExampleMatcher.GenericPropertyMatchers.contains())
                    .withMatcher("userName", ExampleMatcher.GenericPropertyMatchers.contains());//like
            Example<SysUser> example = Example.of(entity,matcher);

            //Example<SysUser> example = Example.of(entity);

            Page<SysUser> pageList= sysUserDao.findAll(example,pageable);



            if(pageList!=null && pageList.getTotalPages()>0){
                System.out.println("page list:" );
                for(SysUser item:pageList){
                    System.out.println("userid: "+item.getUserId()+"__userAccount:"+item.getAccount());
                }
            }else {
                System.out.println("page is null:" );
            }
        } catch (Exception e) {
            System.out.println("error:" +e.getMessage());
            // e.printStackTrace();
        }
    }

3.使用Specification，功能比较强大，可以实现常用的条件功能  Specification使用说明请参考：https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/domain/Specification.html

/***
     * 直接用dao调用分页带条件  Specification<S>
     */
    @Test
    public void findPageDao3() {
        try {
            System.out.println("=============== findPage ================" );
            Sort sort = new Sort(Sort.Direction.DESC, "userId");
            //分页
            int page=1;//第几页
            int pageSize=2;//每页条数
            //Pageable pageable = new PageRequest(page - 1, pageSize, sort);//已过时
            Pageable pageable = PageRequest.of(page - 1, pageSize, sort);

            //多条件
            SysUser entity=new SysUser();
            entity.setAccount("test");
            entity.setUserName("zw1");

            Specification<SysUser> spec=new Specification<SysUser>(){
                @Override
                public Predicate toPredicate(Root<SysUser> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                    Predicate p1 = cb.equal(root.get("account"), entity.getAccount());
                    Predicate p2 = cb.equal(root.get("userName"), entity.getUserName());
                    return cb.or(p1,p2);
                }
            };
            Page<SysUser> pageList= sysUserDao.findAll(spec,pageable);

            if(pageList!=null && pageList.getTotalPages()>0){
                System.out.println("page list:" );
                for(SysUser item:pageList){
                    System.out.println("userid: "+item.getUserId()+"__userAccount:"+item.getAccount());
                }
            }else {
                System.out.println("page is null:" );
            }
        } catch (Exception e) {
            System.out.println("error:" +e.getMessage());
            // e.printStackTrace();
        }
    }

4.自己写SQL条件返回List<Model>，这个最灵活可以随意写自己的SQL

/***
     * 直接用dao调用分页带条件返回List<Model>  直接用SQL
     */
    @Test
    public void findPageDao4() {
        try {
            System.out.println("=============== findPage ================" );
            //分页
            int page=1;//第几页
            int pageSize=2;//每页条数

            //多条件
            SysUser entity=new SysUser();
            entity.setAccount("test");
            entity.setUserName("zw1");
            //查总数sql
            String sqlCount="select count(*) from SYS_User where 1=1  and (account=:account or userName=:userName) Order by UserID desc ";
            //查分页的数据
            String sql="select * from SYS_User where 1=1   and (account=:account or userName=:userName) Order by UserID desc ";

            Query queryCount = sysUserService.getEntityManager().createNativeQuery(sqlCount);
            queryCount.setParameter("account", entity.getAccount());
            queryCount.setParameter("userName", entity.getUserName());
            Object count=queryCount.getSingleResult();
            Integer listCount = count != null ? Integer.parseInt(count.toString()) : 0;//记录总数

            Query query = sysUserService.getEntityManager().createNativeQuery(sql,SysUser.class);//指定Model
            query.setParameter("account", entity.getAccount());
            query.setParameter("userName", entity.getUserName());
            int offSet = PageUtil.getOffset(page, pageSize);
            query.setFirstResult(offSet).setMaxResults(pageSize);
            List<SysUser> pageList=query.getResultList();

            if(pageList!=null && pageList.size()>0){
                System.out.println("page list:" +pageList.size());
                System.out.println("page Index:" +page+" item size :" +pageSize+" item Total :" +listCount);
                for(SysUser item:pageList){
                    System.out.println("userid: "+item.getUserId()+"__userAccount:"+item.getAccount());
                }
            }else {
                System.out.println("page is null:" );
            }
        } catch (Exception e) {
            System.out.println("error:" +e.getMessage());
            // e.printStackTrace();
        }
    }

4.自己写SQL条件返回List<Map>，这个最灵活可以随意写自己的SQL

/***
     * 直接用dao调用分页带条件返回List<Map>  直接用SQL
     */
    @Test
    public void findPageDao5() {
        try {
            System.out.println("=============== findPage ================" );
            //分页
            int page=1;//第几页
            int pageSize=2;//每页条数

            //多条件
            SysUser entity=new SysUser();
            entity.setAccount("test");
            entity.setUserName("zw1");
            //查总数sql
            String sqlCount="select count(*) from SYS_User where 1=1  and (account=:account or userName=:userName) Order by UserID desc ";
            //查分页的数据
            String sql="select * from SYS_User where 1=1   and (account=:account or userName=:userName) Order by UserID desc ";

            Query queryCount = sysUserService.getEntityManager().createNativeQuery(sqlCount);
            queryCount.setParameter("account", entity.getAccount());
            queryCount.setParameter("userName", entity.getUserName());
            Object count=queryCount.getSingleResult();
            Integer listCount = count != null ? Integer.parseInt(count.toString()) : 0;//记录总数

            Query query = sysUserService.getEntityManager().createNativeQuery(sql);//这时只有sql
            query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);//添加这一行才能返回List<Map>
            query.setParameter("account", entity.getAccount());
            query.setParameter("userName", entity.getUserName());
            int offSet = PageUtil.getOffset(page, pageSize);
            query.setFirstResult(offSet).setMaxResults(pageSize);
            List<Map> pageList=query.getResultList();

            if(pageList!=null && pageList.size()>0){
                System.out.println("page list:" +pageList.size());
                System.out.println("page Index:" +page+" item size :" +pageSize+" item Total :" +listCount);
                for(Map item:pageList){
                    System.out.println("userid: "+item.get("UserID").toString()+"__userAccount:"+item.get("Account").toString());
                }
            }else {
                System.out.println("page is null:" );
            }
        } catch (Exception e) {
            System.out.println("error:" +e.getMessage());
            // e.printStackTrace();
        }
    }

 

sysUserService代码

@PersistenceContext
    protected EntityManager em;// 类似hibernate session

    //给子类用的
    public EntityManager getEntityManager() {
        return em;
    }

SysUserDao代码

@Repository
public interface SysUserDao<SysUser,Integer> extends JpaRepository <T,ID>, JpaSpecificationExecutor<T> {

}