Spring Data JPA实现复杂的动态查询

最新推荐文章于 2024-04-17 06:09:31 发布

jackob-94

最新推荐文章于 2024-04-17 06:09:31 发布

阅读量752

点赞数

分类专栏： JPA 文章标签：数据库 jpa mysql

本文链接：https://blog.youkuaiyun.com/WuJiangang5112/article/details/109294205

版权

JPA 专栏收录该内容

1 篇文章

订阅专栏

一、JPA介绍

JPA的动态查询通过Specification接口实现，用于当jpa的一些基本CRUD操作的扩展，即spring jpa的复杂查询接口。

1.1、关于Specification用于表单实现多条件动态查询

通过看Specification的源码，能看到可用的方法就一个toPredicate；



	/**
	 * Creates a WHERE clause for a query of the referenced entity in form of a {@link Predicate} for the given
	 * {@link Root} and {@link CriteriaQuery}.
	 *
	 * @param root must not be {@literal null}.
	 * @param query must not be {@literal null}.
	 * @param criteriaBuilder must not be {@literal null}.
	 * @return a {@link Predicate}, may be {@literal null}.
	 */
	@Nullable
	Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder);

正好项目中，有mybatis转为JPA了，需要写一个多查询条件的DAO方法。以下为DAO层代码示例

Page<GatewayAccess> findAll(Specification<GatewayAccess> specification,Pageable pageable);

default  Page<GatewayAccess> findByAppIdAndApiIdAndStatusAndJnlNo(String beginTime,String endTime,String appId,String apiId,String status,String jnlNo,Pageable pageable) {

        Specification<GatewayAccess> specification = new Specification<GatewayAccess>() {
            @SneakyThrows
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
                //增加筛选条件
                Predicate predicate = cb.conjunction();
                //大于等于
                predicate.getExpressions().add(cb.greaterThanOrEqualTo(root.get("tradeTime"), formatter.get().parse(beginTime)));
                //小于
                predicate.getExpressions().add(cb.lessThanOrEqualTo(root.get("tradeTime"),formatter.get().parse(endTime)));
                if (StringUtils.isNotBlank(appId)) {
                    predicate.getExpressions().add(cb.equal(root.get("appId"),appId));
                }
                if (StringUtils.isNotBlank(apiId)) {
                    predicate.getExpressions().add(cb.equal(root.get("apiId"),apiId));
                }
                if (StringUtils.isNotBlank(status)) {
                    predicate.getExpressions().add(cb.equal(root.get("status"),status));
                }
                if (StringUtils.isNotBlank(jnlNo)) {
                    predicate.getExpressions().add(cb.equal(root.get("jnlNo"),jnlNo));
                }
                return predicate;
            }
        };
        return findAll(specification,pageable);
    }

二、Specification除了能使实现单表查询，还能实现多表关联查询，分组查询、排序等

多表查询

**
 * 构建查询条件 三张表内联查询实例
 *
 * @param categoryId 分类id
 * @param name       商品名称
 * @param shopId     店铺id
 * @return 查询条件
 */
public Specification<ProductGroup> buildProductGroupSpec(Integer shopId, String name, Integer categoryId, Integer shopKindId, Boolean health) {
    return (root, query, cb) -> {
        Join<ProductGroup, Shop> shopJoin = root.join("shop", JoinType.INNER);
        Join<Shop, ShopDatum> shopDatumJoin = shopJoin.join("shopDatum", JoinType.INNER);
        Join<Shop, ShopConfig> shopConfigJoin = shopJoin.join("shopConfig", JoinType.INNER);
        List<Predicate> predicates = Lists.newArrayList();
        if (shopId != null) {
            predicates.add(cb.equal(root.get("shop"), shopId));
        }
        if (StringUtils.isNotEmpty(name)) {
            predicates.add(cb.like(root.get("name"), "%" + name.trim() + "%"));
        }
        if (categoryId != null) {
            predicates.add(cb.equal(root.get("category"), categoryId));
        }
        if (shopKindId != null && shopKindId != 0) {
            predicates.add(cb.like(root.get("shopKindIds"), "," + shopKindId + ","));
        }
        predicates.add(cb.equal(root.get("auditState"), AUDIT_STATE_YES.getKey()));
        predicates.add(cb.equal(root.get("offline"), Boolean.FALSE));
        predicates.add(cb.greaterThanOrEqualTo(shopDatumJoin.get("openShopExpire"), new Date()));
        predicates.add(cb.equal(shopConfigJoin.get("openShop"), Boolean.TRUE));
        Predicate p1 = cb.equal(root.get("health"), Boolean.FALSE);
        Predicate p2 = cb.equal(root.get("health"), Boolean.TRUE);
        Predicate p3 = cb.greaterThanOrEqualTo(shopDatumJoin.get("openHealthShopExpire"), new Date());
        if (health == null) {
            predicates.add(cb.or(p1, cb.and(p2, p3)));
        }
        if (health != null) {
            if (health) {
                predicates.add(cb.and(p2, p3));
            } else {
                predicates.add(cb.and(p1));
            }
        }
        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    };
}