java - 如何连接两个条件查询

Question

我有一个查询想要进行条件查询

select u.email, st.total_amount, st.company_total  from users u
join (select user_id, SUM(balance) as total_amount,SUM(company_count) as company_total from subscription s
      where s.is_active = 0
group by user_id) st on u.id = st.user_id 
where u.is_active = 0
order by st.company_total 

我已经做了 1 个条件查询

CriteriaQuery<UserImpl> innerQuery = builder.createQuery(UserImpl.class);
            Root<Subscription> subscriptionRoot = innerQuery.from(Subscription.class);
            innerQuery.multiselect(subscriptionRoot.get("user").get("id"), builder.sum(subscriptionRoot.get("balance")),
                    builder.sum(subscriptionRoot.get("companyCount")));

我不知道如何在 Spring Hibernate JPA 中进行外部查询。可以帮忙吗。

Answer 1

定义 JOIN 子句非常简单。您首先调用 CriteriaQuery 对象的 from 方法来获取 Root 对象。在下一步中，您可以调用 join 方法来定义 JOIN 子句。

以下是文档示例，您可以按照您的示例操作它。

您可以引用 Hibernate 文档来获取连接示例。以下是示例代码。

CriteriaQuery<String> q = cb.createQuery(String.class);
Root<Order> order = q.from(Order.class);
q.select(order.get("shippingAddress").<String>get("state"));

CriteriaQuery<Product> q2 = cb.createQuery(Product.class);
q2.select(q2.from(Order.class)
                 .join("items")
                 .<Item,Product>join("product"));

Docs to refer

下面列出了我发现的另一个简单示例:

<Y> ListJoin<X, Y> join(ListAttribute<? super X, Y> list);

简单示例(假设员工有具有多对多关系的任务列表):

CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
Root<Employee> employee = query.from(Employee.class);
ListJoin<Employee, Task> tasks = employee.join(Employee_.tasks);
query.select(employee)
        .where(criteriaBuilder.equal(tasks.get(Task_.supervisor), 
employee.get(Employee_.name)));
TypedQuery<Employee> typedQuery = entityManager.createQuery(query);
List<Employee> employees = typedQuery.getResultList();

引用:here