Spring Data JPA之JpaSpecificationExecutor复杂动态查询实例

简介: Spring Data JPA之JpaSpecificationExecutor复杂动态查询实例

【1】回顾简单查询

简单动态查询实例如下:

 public Page<User> test(){
        Sort sort = new Sort(Sort.Direction.DESC,"id");
        int page = 1;
        int pageSize = 5;
        Pageable pageable = PageRequest.of(page,pageSize,sort);
        //通常使用 Specification 的匿名内部类
        Specification<User> specification = new Specification<User>() {
            /**
             * @param *root: 代表查询的实体类.
             * @param query: 可以从中可到 Root 对象, 即告知 JPA Criteria 查询要查询哪一个实体类. 还可以
             * 来添加查询条件, 还可以结合 EntityManager 对象得到最终查询的 TypedQuery 对象.
             * @param *cb: CriteriaBuilder 对象. 用于创建 Criteria 相关对象的工厂. 当然可以从中获取到 Predicate 对象
             * @return: *Predicate 类型, 代表一个查询条件.
             */
            @Override
            public Predicate toPredicate(Root<User> root,
                                         CriteriaQuery<?> query, CriteriaBuilder cb) {
                Path id = root.get("id");
                Predicate predicateId = cb.gt(id,5);
                return predicateId ;
            }
        };
        Page<User> userPage = userRepository.findAll(specification,  pageable);
        return userPage;
    }

只有一个属性,一个查询条件。

后台打印SQL如下所示:

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_,
 user0_.email as email3_1_, user0_.last_name as last_nam4_1_ from tb_user user0_ 
 where user0_.id>5 order by user0_.id desc limit ?, ?
Hibernate: select count(user0_.id) as col_0_0_ from tb_user user0_ where user0_.id>5

【2】添加多个查询条件

比如这里再添加查询条件:id=5 and email="aa@qq.com"

核心方法如下所示:

Specification<User> specification = new Specification<User>() {
     @Override
     public Predicate toPredicate(Root<User> root,
                                  CriteriaQuery<?> query, CriteriaBuilder cb) {
         Path id = root.get("id");
         Predicate predicateId = cb.gt(id,5);
         Path<User> email = root.get("email");
         Predicate predicateEmail = cb.equal(email, "aa@qq.com");
         Predicate endPredicate = cb.and(predicateId, predicateEmail);
         return endPredicate;
     }
 };

这里需要用到CriteriaBuilder提供的几个方法:

/**
     * Create a conjunction of the given boolean expressions.
     * @param x  boolean expression
     * @param y  boolean expression
     * @return and predicate
     */
    Predicate and(Expression<Boolean> x, Expression<Boolean> y);
    /**
     * Create a conjunction of the given restriction predicates.
     * A conjunction of zero predicates is true.
     * @param restrictions  zero or more restriction predicates
     * @return and predicate
     */
    Predicate and(Predicate... restrictions);
    /**
     * Create a disjunction of the given boolean expressions.
     * @param x  boolean expression
     * @param y  boolean expression
     * @return or predicate
     */
    Predicate or(Expression<Boolean> x, Expression<Boolean> y);
    /**
     * Create a disjunction of the given restriction predicates.
     * A disjunction of zero predicates is false.
     * @param restrictions  zero or more restriction predicates
     * @return or predicate
     */
    Predicate or(Predicate... restrictions);

连词And和OR,其中每种连词又有两种参数。 Predicate and(Predicate... restrictions);表示不定数参数Predicate使用And连接起来,通常你可以传入多个Predicate参数,但是建议传入一个数组。

修改上面核心方法如下:

Specification<User> specification = new Specification<User>() {
    @Override
    public Predicate toPredicate(Root<User> root,
                                 CriteriaQuery<?> query, CriteriaBuilder cb) {
        Path id = root.get("id");
        List<Predicate> predicates=new ArrayList<Predicate>();
        Predicate predicateId = cb.gt(id,5);
        predicates.add(predicateId);
        Path<User> email = root.get("email");
        Predicate predicateEmail = cb.equal(email, "aa@qq.com");
        predicates.add(predicateEmail);
        Predicate endPredicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
//                Predicate endPredicate = cb.and((Predicate[]) predicates.toArray());
        return endPredicate;
    }
};

这里toArray有两种方式:

 Object[] toArray();
 <T> T[] toArray(T[] a);
  * 相同点:都是作为数组和collection之间的桥梁;
  * 不同点:toArray()每次都会分配一个新的数组空间,toArray(T[] a);则在某些情况下节省分配成本。

即完整方法如下:

public Page<User> test17(){
     Sort sort = new Sort(Sort.Direction.DESC,"id");
     int page = 1;
     int pageSize = 5;
     Pageable pageable = PageRequest.of(page,pageSize,sort);
     Specification<User> specification = new Specification<User>() {
         @Override
         public Predicate toPredicate(Root<User> root,
                                      CriteriaQuery<?> query, CriteriaBuilder cb) {
             Path id = root.get("id");
             List<Predicate> predicates=new ArrayList<Predicate>();
             Predicate predicateId = cb.gt(id,5);
             predicates.add(predicateId);
             Path<User> email = root.get("email");
             Predicate predicateEmail = cb.equal(email, "aa@qq.com");
             predicates.add(predicateEmail);
             Predicate endPredicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
//                Predicate endPredicate = cb.and((Predicate[]) predicates.toArray());
             return endPredicate;
         }
     };
     Page<User> userPage = userRepository.findAll(specification,  pageable);
     return userPage;
 }

此时后台SQL打印如下:

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_, 
user0_.email as email3_1_, user0_.last_name as last_nam4_1_ from tb_user user0_ 
where user0_.id>5 and user0_.email=? order by user0_.id desc limit ?, ?
Hibernate: select count(user0_.id) as col_0_0_ from tb_user user0_ where user0_.id>5 and user0_.email=?

【3】CriteriaBuilder.conjunction()和CriteriaBuilder.disjunction()

如果没有查询条件呢?即Predicate为null,CriteriaBuilder同样提供了两个方法conjunction()和disjunction()。

方法说明如下;

/**
 * Create a conjunction (with zero conjuncts).
 * A conjunction with zero conjuncts is true.
 * @return and predicate
 */
Predicate conjunction();
/**
 * Create a disjunction (with zero disjuncts).
 * A disjunction with zero disjuncts is false.
 * @return or predicate
 */
Predicate disjunction();

① CriteriaBuilder.conjunction()

修改方法如下所示:

public Page<User> test(){
    Sort sort = new Sort(Sort.Direction.DESC,"id");
    int page = 1;
    int pageSize = 5;
    Pageable pageable = PageRequest.of(page,pageSize,sort);
    Specification<User> specification = new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root,
                                     CriteriaQuery<?> query, CriteriaBuilder cb) {
            return cb.conjunction();
        }
    };
    Page<User> userPage = userRepository.findAll(specification,  pageable);
    return userPage;
}

后台SQL打印如下:

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_, 
user0_.email as email3_1_, user0_.last_name as last_nam4_1_ from tb_user user0_ 
where 1=1 order by user0_.id desc limit ?, ?
//这里where子句为 where 1=1
Hibernate: select count(user0_.id) as col_0_0_ from tb_user user0_ where 1=1

② CriteriaBuilder.disjunction()

修改方法如下所示:

public Page<User> test18(){
    Sort sort = new Sort(Sort.Direction.DESC,"id");
    int page = 1;
    int pageSize = 5;
    Pageable pageable = PageRequest.of(page,pageSize,sort);
    Specification<User> specification = new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root,
                                     CriteriaQuery<?> query, CriteriaBuilder cb) {
            return cb.disjunction();
//                return cb.conjunction();
        }
    };
    Page<User> userPage = userRepository.findAll(specification,  pageable);
    return userPage;
}

此时SQL打印如下:

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_, 
user0_.email as email3_1_, user0_.last_name as last_nam4_1_ from tb_user user0_ 
where 0=1 order by user0_.id desc limit ?, ?
//这里where子句为 where 0=1,肯定不会返回值了
Hibernate: select count(user0_.id) as col_0_0_ from tb_user user0_ where 0=1

【4】Path应用之对象属性为对象

Path是个什么?有三个关键词:对象引用;属性;路径。

源码如下所示:

/**
 * Represents a simple or compound attribute path from a 
 * bound type or collection, and is a "primitive" expression.
 *表示来自绑定类型或集合的简单或复合属性路径,并且是“原语”表达式。
 * @param <X>  the type referenced by the path
 *
 * @since Java Persistence 2.0
 */
public interface Path<X> extends Expression<X> {
    /** 
     * Return the bindable object that corresponds to the path expression.
     */
    Bindable<X> getModel(); 
    /**
     *  Return the parent "node" in the path or null if no parent.
     */
    Path<?> getParentPath();
    /**
     *  Create a path corresponding to the referenced single-valued attribute.
     */
    <Y> Path<Y> get(SingularAttribute<? super X, Y> attribute);
    /**
     *  Create a path corresponding to the referenced collection-valued attribute.
     */
    <E, C extends java.util.Collection<E>> Expression<C> get(PluralAttribute<X, C, E> collection);
    /**
     *  Create a path corresponding to the referenced map-valued attribute.
     */
    <K, V, M extends java.util.Map<K, V>> Expression<M> get(MapAttribute<X, K, V> map);
    /**
     *  Create an expression corresponding to the type of the path.
     *  @return expression corresponding to the type of the path
     */
    Expression<Class<? extends X>> type();
    //String-based:
    /**
     *  Create a path corresponding to the referenced attribute.
     */
    <Y> Path<Y> get(String attributeName);
}

上面使用的属性都是对象的简单属性,如user.id,user.email。但是如果属性为user.address.id呢?

修改方法如下所示:

public Page<User> test(){
    Sort sort = new Sort(Sort.Direction.DESC,"id");
    int page = 1;
    int pageSize = 5;
    Pageable pageable = PageRequest.of(page,pageSize,sort);
    Specification<User> specification = new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root,
                                     CriteriaQuery<?> query, CriteriaBuilder cb) {
            Path<Object> addressPath = root.get("address");
            //这里再次获取addressPath 中的属性
            Path<Object> id = addressPath.get("id");
            Predicate predicate = cb.equal(id, 1);
            return predicate;
        }
    };
    Page<User> userPage = userRepository.findAll(specification,  pageable);
    return userPage;
}

此时后台SQL打印如下:

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_,
 user0_.email as email3_1_, user0_.last_name as last_nam4_1_ 
 from tb_user user0_ 
 where user0_.address_id=1 order by user0_.id desc limit ?, ?
 //where 子句 条件为user-address的外键列 address_id
Hibernate: select count(user0_.id) as col_0_0_ 
from tb_user user0_ 
where user0_.address_id=1

上面是我们手动处理–先获取addressPath,再获取其idPath,实际应用中通常处理如下:

// nested path translate, 如Task的名为"user.name"的filedName, 转换为Task.user.name属性
 String[] names = StringUtils.split("address.id", ".");
 Path expression = root.get(names[0]);
   for (int i = 1; i < names.length; i++) {
       expression = expression.get(names[i]);
   }

该实例有个特殊之处在于获取user.address时,外键列address_id对应address类的主键id。故而直接使用addressPath查询时后台打印SQL同上。但是如果fieldName为address.city呢?

修改方法如下所示:

public Page<User> test19(){
    Sort sort = new Sort(Sort.Direction.DESC,"id");
    int page = 1;
    int pageSize = 5;
    Pageable pageable = PageRequest.of(page,pageSize,sort);
    Specification<User> specification = new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root,
                                     CriteriaQuery<?> query, CriteriaBuilder cb) {
            Path<Object> addressPath = root.get("address");
            Path<Object> expression = addressPath.get("city");
            Predicate predicate = cb.equal(expression, "beijing");
            return predicate;
        }
    };
    Page<User> userPage = userRepository.findAll(specification,  pageable);
    return userPage;
}

后台SQL打印如下:

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_, 
user0_.email as email3_1_, user0_.last_name as last_nam4_1_
from tb_user user0_ 
cross join tb_address address1_ 
where user0_.address_id=address1_.id and address1_.city=? order by user0_.id desc limit ?, ?
Hibernate: select count(user0_.id) as col_0_0_ 
from tb_user user0_ 
cross join tb_address address1_ 
where user0_.address_id=address1_.id and address1_.city=?

【5】CriteriaQuery实现多条件组合

如下图所示,常见SQL语法CriteriaQuery都提供了实现:


方法实例如下:

public Page<User> test(){
    Sort sort = new Sort(Sort.Direction.DESC,"id");
    int page = 1;
    int pageSize = 5;
    Pageable pageable = PageRequest.of(page,pageSize,sort);
    Specification<User> specification = new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Path id = root.get("id");
            List<Predicate> predicates=new ArrayList<Predicate>();
            Predicate predicateId = cb.gt(id,5);
            predicates.add(predicateId);
            Path<User> email = root.get("email");
            Predicate predicateEmail = cb.equal(email, "aa@qq.com");
            predicates.add(predicateEmail);
            Predicate endPredicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
//                Predicate endPredicate = cb.and((Predicate[]) predicates.toArray());
            //添加where条件
            query.where(endPredicate);
            // //指定查询项,select后面的东西
            query.multiselect(id,email,cb.count(id));
            //分组
            query.groupBy(id);
            //排序
            query.orderBy(cb.asc(id));
            //筛选
            query.having(cb.greaterThan(id,0));
            //获取最终的Predicate
            Predicate restriction = query.getRestriction();
            return restriction;
        }
    };
    Page<User> userPage = userRepository.findAll(specification,  pageable);
    return userPage;
}

后台SQL打印如下:

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_, 
user0_.email as email3_1_, user0_.last_name as last_nam4_1_ from tb_user user0_
 where user0_.id>5 and user0_.email=? 
 group by user0_.id 
 having user0_.id>0 
 order by user0_.id desc limit ?, ?
// 这里需要注意,排序根据pageable
Hibernate: select count(user0_.id) as col_0_0_ from tb_user user0_ 
where user0_.id>5 and user0_.email=? group by user0_.id having user0_.id>0

【6】CriteriaQuery与entityManager整合

前面提到过CriteriaQuery可以结合 EntityManager 对象得到最终查询的 TypedQuery 对象。

实例如下:

@PersistenceContext
private EntityManager entityManager;
public List<User> test22(){
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        //User指定了查询结果返回至自定义对象
        CriteriaQuery<User> query = cb.createQuery(User.class);
        Root<User> root = query.from(User.class);
        Path id = root.get("id");
        List<Predicate> predicates=new ArrayList<Predicate>();
        Predicate predicateId = cb.equal(id,1);
        predicates.add(predicateId);
        Path<User> email = root.get("email");
        Predicate predicateEmail = cb.equal(email, "aa@qq.com");
        predicates.add(predicateEmail);
        Predicate endPredicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
        //添加where条件
        query.where(endPredicate);
         //指定查询项,select后面的东西
//        query.multiselect(id,email);
        //分组
        query.groupBy(id);
        //排序
        query.orderBy(cb.asc(id));
        //筛选
        query.having(cb.greaterThan(id,0));
        TypedQuery<User> q = entityManager.createQuery(query);
        List<User> result = q.getResultList();
        for (User user : result) {
            //打印查询结果
            System.out.println(user.toString());
        }
        return result;
    }

后台SQL打印如下:

Hibernate: select user0_.id as id1_1_, user0_.address_id as address_5_1_, user0_.add_id as add_id2_1_,
 user0_.email as email3_1_, user0_.last_name as last_nam4_1_ from tb_user user0_ 
 where user0_.id=1 and user0_.email=? 
 group by user0_.id 
 having user0_.id>0 
 order by user0_.id asc
//关联查询Address 
Hibernate: select address0_.id as id1_0_0_, address0_.city as city2_0_0_, address0_.province as province3_0_0_
from tb_address address0_ where address0_.id=?

博文项目代码下载地址:GitHub项目地址


目录
相关文章
存储 JSON Java
217 0
|
1月前
|
SQL Java 数据库连接
Spring Data JPA 技术深度解析与应用指南
本文档全面介绍 Spring Data JPA 的核心概念、技术原理和实际应用。作为 Spring 生态系统中数据访问层的关键组件,Spring Data JPA 极大简化了 Java 持久层开发。本文将深入探讨其架构设计、核心接口、查询派生机制、事务管理以及与 Spring 框架的集成方式,并通过实际示例展示如何高效地使用这一技术。本文档约1500字,适合有一定 Spring 和 JPA 基础的开发者阅读。
145 0
|
3月前
|
NoSQL Java Redis
Redis基本数据类型及Spring Data Redis应用
Redis 是开源高性能键值对数据库,支持 String、Hash、List、Set、Sorted Set 等数据结构,适用于缓存、消息队列、排行榜等场景。具备高性能、原子操作及丰富功能,是分布式系统核心组件。
401 2
|
4月前
|
Java API 数据库
JPA简介:Spring Boot环境下的实践指南
上述内容仅是JPA在Spring Boot环境下使用的冰山一角,实际的实践中你会发现更深更广的应用。总而言之,只要掌握了JPA的规则,你就可以借助Spring Boot无比丰富的功能,娴熟地驾驶这台高性能的跑车,在属于你的程序世界里驰骋。
159 15
|
4月前
|
SQL Java 数据库
解决Java Spring Boot应用中MyBatis-Plus查询问题的策略。
保持技能更新是侦探的重要素质。定期回顾最佳实践和新技术。比如,定期查看MyBatis-Plus的更新和社区的最佳做法,这样才能不断提升查询效率和性能。
171 1
|
5月前
|
消息中间件 缓存 NoSQL
基于Spring Data Redis与RabbitMQ实现字符串缓存和计数功能(数据同步)
总的来说,借助Spring Data Redis和RabbitMQ,我们可以轻松实现字符串缓存和计数的功能。而关键的部分不过是一些"厨房的套路",一旦你掌握了这些套路,那么你就像厨师一样可以准备出一道道饕餮美食了。通过这种方式促进数据处理效率无疑将大大提高我们的生产力。
195 32
|
6月前
|
NoSQL 安全 Java
深入理解 RedisConnectionFactory:Spring Data Redis 的核心组件
在 Spring Data Redis 中,`RedisConnectionFactory` 是核心组件,负责创建和管理与 Redis 的连接。它支持单机、集群及哨兵等多种模式,为上层组件(如 `RedisTemplate`)提供连接抽象。Spring 提供了 Lettuce 和 Jedis 两种主要实现,其中 Lettuce 因其线程安全和高性能特性被广泛推荐。通过手动配置或 Spring Boot 自动化配置,开发者可轻松集成 Redis,提升应用性能与扩展性。本文深入解析其作用、实现方式及常见问题解决方法,助你高效使用 Redis。
588 4
|
6月前
|
SQL Java 编译器
深入理解 Spring Data JPA 的导入与使用:以 UserRepository为例
本文深入解析了 Spring Data JPA 中 `UserRepository` 的导入与使用。通过示例代码,详细说明了为何需要导入 `User` 实体类、`JpaRepository` 接口及 `@Repository` 注解。这些导入语句分别用于定义操作实体、提供数据库交互方法和标识数据访问组件。文章还探讨了未导入时的编译问题,并展示了实际应用场景,如用户保存、查询与删除操作。合理使用导入语句,可让代码更简洁高效,充分发挥 Spring Data JPA 的优势。
363 0
|
7月前
|
Java 微服务 Spring
微服务——SpringBoot使用归纳——Spring Boot中使用拦截器——拦截器使用实例
本文主要讲解了Spring Boot中拦截器的使用实例,包括判断用户是否登录和取消特定拦截操作两大场景。通过token验证实现登录状态检查,未登录则拦截请求;定义自定义注解@UnInterception实现灵活取消拦截功能。最后总结了拦截器的创建、配置及对静态资源的影响,并提供两种配置方式供选择,帮助读者掌握拦截器的实际应用。
224 0
|
9月前
|
存储 NoSQL Java
使用Java和Spring Data构建数据访问层
本文介绍了如何使用 Java 和 Spring Data 构建数据访问层的完整过程。通过创建实体类、存储库接口、服务类和控制器类,实现了对数据库的基本操作。这种方法不仅简化了数据访问层的开发,还提高了代码的可维护性和可读性。通过合理使用 Spring Data 提供的功能,可以大幅提升开发效率。
195 21