jpa之org.springframework.data.jpa.domain.Specification

 学习官方文档: https://docs.spring.io/spring-data/jpa/docs/2.3.3.RELEASE/reference/html

 

 

Specification

复制代码
interface TaskRepository extends JpaRepository<Task, Integer>, JpaSpecificationExecutor<Task> 
/**
org.springframework.data.jpa.domain.Specification
    Predicate toPredicate(Root<T> var1, CriteriaQuery<?> var2, CriteriaBuilder var3);
        #Root : 实体类引用, 获取任何实体的属性
        #CriteriaQuery: 顶层查询条件,自定义查询 (where)(order by)  过滤掉关键字:from, where, order by等
        #CriteriaBuilder: 可构建底层查询条件,也可构建顶层查询条件 (like,equal,and,in,greaterThanOrEqualTo,lessThanOrEqualTo)  构建条件查询的语句

criteria 规则
criteria query 查询规则
criteria builder 构建起规则
restriction  限制
**/
复制代码

Specification五个核心方法

复制代码
    Optional<T> findOne(@Nullable Specification<T> var1);

    List<T> findAll(@Nullable Specification<T> var1);

    Page<T> findAll(@Nullable Specification<T> var1, Pageable var2);

    List<T> findAll(@Nullable Specification<T> var1, Sort var2);

    long count(@Nullable Specification<T> var1);
复制代码

 

无lambda表达式

复制代码
@Test
    public void test4(){
        Specification<Task> spec = new Specification<>() {
            @Override
            public Predicate toPredicate(Root<Task> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
                Predicate p1 = builder.equal(root.get("status"), TaskStatusEnum.CREATED);
                return query.where(p1).getRestriction();
            }
        };
        this.taskRepository.findAll(spec);
    }
复制代码

有lambda表达式

@Test
    public void test4(){
        Specification<Task> spec = (root, query, builder) -> {
            Predicate p1 = builder.equal(root.get("status"), TaskStatusEnum.CREATED);
            return query.where(p1).getRestriction();
        };
        this.taskRepository.findAll(spec);
    }

分页查询

复制代码
@Transactional
    @Test
    public void test5(){
        Specification<Task> spec = (root, query, builder) -> query.where().getRestriction();
        Page<Task> pageResult = this.taskRepository.findAll(spec, PageRequest.of(0, 10));
        pageResult.getContent().forEach(System.out::println);
        System.out.println("总页数:" + pageResult.getTotalPages());
        System.out.println("页面记录数:" + pageResult.getSize());
        System.out.println("当前页:" + pageResult.getNumber() + 1);
        System.out.println("总记录数:" + pageResult.getTotalElements());
    }
复制代码

简单order by

复制代码
@Test
    public void test6(){
        Specification<Task> spec = (root, query, builder) -> {
            root.fetch("owner");    //inner join user
            root.fetch("creator");  //inner join user
            root.fetch("model");    //inner join model
            root.fetch("client");   //inner join client

            Predicate p1 = builder.equal(root.get("name"), "上海福新");   //name=?
            Predicate p2 = builder.in(root.get("type")).value(TaskTypeEnum.BLACKOUT_APPLICATION).value(TaskTypeEnum.HIGH_USER_CHECK); //type in (?,?)
            Predicate p3 = builder.like(root.get("name"), "%" + "上海福新" + "%"); //name like %上海福新%

            return query.where(p1, p2, p3).getRestriction();
        };
        this.taskRepository.findAll(spec, Sort.by("name").descending()); //简单的order by
    }
复制代码

复杂order by

复制代码
@Test
    public void test7(){
        Specification<Task> spec = (root, query, builder) -> {

            root.fetch("owner");    //inner join user
            root.fetch("creator");  //inner join user
            root.fetch("model");    //inner join model
            root.fetch("client");   //inner join client

            //复杂自定义排序
            List<Order> orders = new ArrayList<>();
            orders.add(builder.asc(
                    builder.selectCase()
                            .when(builder.equal(root.get("status").as(TaskStatusEnum.class), TaskStatusEnum.CREATED), 1)
                            .when(builder.equal(root.get("status").as(TaskStatusEnum.class), TaskStatusEnum.FINISHED), 2)
                            .otherwise(3)
            ));
            query.orderBy(orders);


            Predicate p1 = builder.equal(root.get("name"), "上海福新");   //name=?
            Predicate p2 = builder.in(root.get("type")).value(TaskTypeEnum.BLACKOUT_APPLICATION).value(TaskTypeEnum.HIGH_USER_CHECK); //type in (?,?)
            Predicate p3 = builder.like(root.get("name"), "%" + "上海福新" + "%"); //name like %上海福新%

            return query.where(p1, p2, p3).getRestriction();
        };
        //this.taskRepository.findAll(spec, Sort.by("name").descending()); //简单的order by  query的排序和外面的排序同时存在时, 外面的会覆盖里面的排序
        this.taskRepository.findAll(spec);
    }
复制代码

 

CriteriaBuilder构建顶层查询条件写法

复制代码
@Test
    public void test8(){
        Specification<Task> spec = (root, query, builder) -> {
            Predicate p1 = builder.equal(root.get("name"), "上海福新");   //name=?
            Predicate p2 = builder.in(root.get("type")).value(TaskTypeEnum.BLACKOUT_APPLICATION).value(TaskTypeEnum.HIGH_USER_CHECK); //type in (?,?)
            Predicate p3 = builder.like(root.get("name"), "%" + "上海福新" + "%"); //name like %上海福新%

            //return query.where(p1, p2, p3).getRestriction();
            return builder.and(p1, p2, p3); //and
        };
        this.taskRepository.findAll(spec); 
    }
复制代码

 

左连接,内连接

复制代码
 @Test
    public void test9(){
        Specification<Task> spec = (root, query, builder) -> {
            root.fetch("owner");    //inner join user  决定返回内容有owner
            root.fetch("creator");  //inner join user  决定返回内容有creator
            root.fetch("model");    //inner join model  决定返回内容有model
            root.fetch("client");   //inner join client  决定返回内容有client

            Predicate p1 = builder.equal(root.get("name"), "上海福新");   //name=?
            Predicate p2 = builder.in(root.get("type")).value(TaskTypeEnum.BLACKOUT_APPLICATION).value(TaskTypeEnum.HIGH_USER_CHECK); //type in (?,?)
            Predicate p3 = builder.equal(root.join("client", JoinType.LEFT).get("clientId"), 12); //左连接 left outer join  内连接 inner join  (会覆盖上面fetch的inner join)

            return builder.and(p1, p2, p3);
        };
        this.taskRepository.findAll(spec);
    }
复制代码

区别fetch和join:

fetch决定返回结果有哪些字段,  此时如果没有join就默认inner join;

join决定查询条件是left outer join还是inner join, 不考虑返回的内容;

join: client

select t1.* 
from task t1
inner join client t2 on t1.client_id=t2.client_id
where 1=1

fetch: client

select t1.* , t2.*
from task t1
inner join client t2 on t1.client_id=t2.client_id
where 1=1

 



posted @   Peter.Jones  阅读(1275)  评论(0编辑  收藏  举报
编辑推荐:
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
阅读排行:
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
历史上的今天:
2018-09-01 CentOS6.5安装RabbitMQ
点击右上角即可分享
微信分享提示