hibernate中的分页查询（转）

hibernate是支持分页查询的，网上资料不多，收集了一些，以备不时之需。

 

jpa ExampleMatcher Example

参考：

https://www.cnblogs.com/HiLzd/p/14533982.html

https://www.cnblogs.com/lshan/p/12018313.html

https://www.cnblogs.com/foshuo-cv/p/12036795.html

https://www.cnblogs.com/lshan/p/12970706.html

官网文档：https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example.usage

 

Person person = new Person();                          
person.setFirstname("Dave");                           

ExampleMatcher matcher = ExampleMatcher.matching()     
  .withIgnorePaths("lastname")                         
  .withIncludeNullValues()                             
  .withStringMatcherEnding();                          

Example<Person> example = Example.of(person, matcher)

 

 

Example 103. Configuring matcher options

Example 103. Configuring matcher options
ExampleMatcher matcher = ExampleMatcher.matching()
  .withMatcher("firstname", endsWith())
  .withMatcher("lastname", startsWith().ignoreCase());
}

 

Example 104. Configuring matcher options with lambdas

ExampleMatcher matcher = ExampleMatcher.matching()
  .withMatcher("firstname", match -> match.endsWith())
  .withMatcher("firstname", match -> match.startsWith());
}

 

example构建对象时 ,如果有默认值 比如int类型 的 都会生成sql语句,在使用的时候要特别注意设置忽略查询的属性

ExampleMatcher 设置查询规则

案例:

  @Test
    public  void  testExampleQuery(){
        SysMenu menu= SysMenu.builder().menuName("测试").build();

        ExampleMatcher matcher=ExampleMatcher.matching()
                //设置默认的字符串查询为 like
                .withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING)
                //设置忽略大小写 也可以设置负略的字段 withIgnoreCase(String... propertyPaths)
                .withIgnoreCase(true)
                //设置为开头模糊查询 如 : like %asas
                .withMatcher("menuName",ExampleMatcher.GenericPropertyMatchers.startsWith())
                //设置为结尾模糊模糊查询 如 : like asas%
                .withMatcher("menuIcon",ExampleMatcher.GenericPropertyMatchers.endsWith())
                //设置为全模糊 如 like %asas%
//                .withMatcher("menuName",ExampleMatcher.GenericPropertyMatchers.contains())
                // 設置查询忽略的属性
                .withIgnorePaths("orderNum","menuId","parentId");


        Example<SysMenu> example=Example.of(menu,matcher);
        //设置分页 排序
        Pageable pageable=PageRequest.of(0,1,Sort.by(Sort.Direction.DESC,"menuId"));
        Page<SysMenu> all = dao.findAll(example, pageable);
        System.out.println(all.getContent());
        System.out.println(all.getTotalPages());
        System.out.println(all.getTotalElements());

    }

jpa使用Specification动态查询和分页

 

Specification核心方法

Predicate toPredicate(Root var1, CriteriaQuery<?> var2, CriteriaBuilder var3);

• Root:代表查询的根对象 即实体
• CriteriaQuery : 顶层查询对象****,用于自定义查询方式 基本不用
• CriteriaBuilder:查询构造器,封装了很多的查询条件

案例:

@Test
    public  void testSpecification(){
        //查询菜单名包含 测试 和菜单id=1212的数据


        Specification<SysMenu> specification=(root,query,buidler)->{
            //获取比较对象 从root中获取
            Path<String> menuName = root.get("menuName");
            //比较值
            Predicate pre1 = buidler.like(menuName, "测试%");

            //获取比较对象 从root中获取
            Path<Long> id = root.get("menuId");
            //比较值
            Predicate pre2 = buidler.equal(id, 1212l);


            //组装对象 使用buidler

            Predicate and = buidler.or(pre1,pre2);
            return and;
        };

        //构建排序
        Sort sort=Sort.by("menuId").descending();
        List<SysMenu> menuList=   dao.findAll(specification,sort);
        menuList.forEach(p-> System.out.println(p));
        //分页
        Pageable pageable=PageRequest.of(1,1,sort);
        Page<SysMenu> all = dao.findAll(specification, pageable);
        System.out.println(all.getTotalElements());
        System.out.println(all.getTotalPages());
        System.out.println(all.getContent());


    }

 

组合多条件复杂查询

public void test(){
       //第一个Specification定义了两个or的equal组合
      Specification<Dept> s1 = (root, criteriaQuery, criteriaBuilder) -> {
            Predicate p1 = criteriaBuilder.equal(root.get("deptno"), "20");
           Predicate p2 = criteriaBuilder.equal(root.get("deptno"), "30");
           return criteriaBuilder.or(p1, p2);
      };
        //第二个Specification定义了两个or的like组合
       Specification<Dept> s2 = (root, criteriaQuery, criteriaBuilder) -> {
          Predicate p1 = criteriaBuilder.like(root.get("dname"), "%S%");
            Predicate p2 = criteriaBuilder.like(root.get("loc"), "AS%");
           return criteriaBuilder.or(p1, p2);
     };
      //通过Specification将两个Specification连接起来，第一个条件加where，第二个是and
      List<Dept> depts = deptDao.findAll(Specification.where(s1).and(s2));
       depts.forEach(System.out::println);

}