如何书写Jpa的动态sql

最新推荐文章于 2024-09-04 20:28:11 发布

莫论

最新推荐文章于 2024-09-04 20:28:11 发布

阅读量1.2k

点赞数 1

CC 4.0 BY-SA版权

文章标签： java

本文链接：https://blog.csdn.net/qq_41581858/article/details/108475262

本文介绍了如何在Java中利用Jpa实现动态SQL查询，包括注入Jpa对象，设置查询参数，执行查询并获取结果。同时，文章还提到了自定义查询方式，支持分页操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

步骤

1. 注入

@PersistenceContext
private EntityManager em;

2. 获取对象

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
//cost去接收
CriteriaQuery<Cost> criteriaQuery = criteriaBuilder.createQuery(Cost.class);

3. 为对象赋予各个参数

select

// 代表select *
criteriaQuery.select(root);

或

criteriaQuery.multiselect(
                root.get("name"),
                root.get("ty"),
                root.get("qu"),
                root.get("us"),
                criteriaBuilder.count(root.get("na")).alias("count"),
                criteriaBuilder.max(criteriaBuilder.diff(root.get("finTime"), root.get("stTime"))).alias("maxDuration"),
                criteriaBuilder.min(criteriaBuilder.diff(root.get("finTime"), root.get("stTime"))).alias("minDuration"),
                criteriaBuilder.avg(criteriaBuilder.diff(root.get("finTime"), root.get("stTime"))).alias("avgDuration")
        );

from

Root<Cost> root = criteriaQuery.from(Cost.class);

where

List<Predicate> where = new ArrayList<>();//用来封装条件
where.add(criteriaBuilder.equal(root.get("clus"), costDto.getCluster()));
//表达式finishTime-submitTime<duration
//diff求差，concat字符串拼接
where.add(criteriaBuilder.greaterThanOrEqualTo(criteriaBuilder.diff(root.get("finDate"), root.get("suDate")), costDto.getDuration() * 60 * 60 * 1000L));
Predicate[] predicates = where.toArray(new Predicate[]{});
criteriaQuery.where(predicates);
// in
Expression<Long> parentExpression = root.get("name");
predicate = parentExpression.in(users);
andList.add(predicate);
// in
if (!CollectionUtils.isEmpty(featureIds)) {
                    Path<Object> path = root.get("id");
                    CriteriaBuilder.In<Object> in = criteriaBuilder.in(path);
                    for (Long featureId : featureIds) {
                        in.value(featureId);
                    }
                    predicateList.add(criteriaBuilder.and(in));
                }

order by

List<Order> orderList = new ArrayList<>();
orderList.add(criteriaBuilder.desc(root.get("pri")));
criteriaQuery.orderBy(orderList);

4. 执行，获取结果

获取全部

List<Cost> cost = em.createQuery(criteriaQuery).getResultList();

或

limit

List<Cost> cost = em.createQuery(criteriaQuery).setMaxResults(costDto.getTopN()).getResultList();

自定义方式，可分页

Page<Audit> page = auditDao.findAll(getSpecification(authDto), pageable);
private Specification<Audit> getSpecification(AuthDto authDto) {
        Specification<Audit> specification = new Specification<Audit>() {
            @Override
            public Predicate toPredicate(Root<Audit> root, CriteriaQuery<?> criteriaQuery,
                                         CriteriaBuilder criteriaBuilder) {
                                         return predicate;
            }
        };
        return specification;
    }