英文:
Converting JPQL query to Criteria Query
问题
以下是你要翻译的内容：
Working SQL:
SELECT *
FROM tag
JOIN (SELECT tag_id,
Count (gift_certificate_tag.gift_certificate_id) AS
cert_count
FROM gift_certificate_tag
JOIN orders
ON gift_certificate_tag.gift_certificate_id =
orders.gift_certificate_id
JOIN (SELECT user_id,
Sum (order_cost) AS all_orders_cost
FROM orders
GROUP BY user_id
ORDER BY all_orders_cost DESC
LIMIT 1) AS orders_sum
ON orders_sum.user_id = orders.user_id
GROUP BY tag_id
ORDER BY cert_count DESC
LIMIT 1) AS result
ON result.tag_id = tag.id
Working JPQL:
SELECT new Tag(t.id, t.name)
FROM GiftCertificate gc
JOIN gc.tags t
JOIN Order o ON o.giftCertificate = gc
JOIN o.user u
WHERE u = (
SELECT ou
FROM Order o2
JOIN o2.user ou
GROUP BY ou
HAVING SUM(o2.orderCost) = (
SELECT MAX(totalOrderCost)
FROM (
SELECT SUM(o3.orderCost) AS totalOrderCost
FROM Order o3
GROUP BY o3.user
) subquery
)
)
GROUP BY t.id, t.name
ORDER BY COUNT(gc) DESC
Not working Criteria Query:
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tag> cq = cb.createQuery(Tag.class);
Root<Order> order = cq.from(Order.class);
Join<Order, GiftCertificate> giftCertificate = order.join("giftCertificate");
Join<GiftCertificate, Tag> tag = giftCertificate.join("tags");
Join<Order, User> user = order.join("user");
//sub-query to find user's orders sums
Subquery<BigDecimal> querySumOfOrders = cq.subquery(BigDecimal.class);
Root<Order> subOrderSumOfOrders = querySumOfOrders.from(Order.class);
Join<Order, User> subUserSumOfOrders = subOrderSumOfOrders.join("user");
querySumOfOrders.select(cb.sum(subOrderSumOfOrders.get("orderCost")))
.groupBy(subUserSumOfOrders);
//sub-query to find max cost of the user's orders sum
Subquery<BigDecimal> queryMaxOrdersSum = cq.subquery(BigDecimal.class);
queryMaxOrdersSum.from(Order.class);
queryMaxOrdersSum.select(cb.max(querySumOfOrders));
//sub-query to find user with most cost of all orders
Subquery<User> queryMostSpentUser = cq.subquery(User.class);
Root<Order> subOrderMostSpentUser = queryMostSpentUser.from(Order.class);
Join<Order, User> subUserMostSpentUser = subOrderMostSpentUser.join("user");
queryMostSpentUser.select(subUserMostSpentUser)
.having(cb.equal(cb.sum(subOrderMostSpentUser.get("orderCost")), cb.max(queryMaxOrdersSum)))
.groupBy(subUserMostSpentUser);
//main query
cq.select(tag)
.where(cb.equal(user, queryMostSpentUser))
.orderBy(cb.desc(cb.count(giftCertificate)))
.groupBy(tag);
List<Tag> tagList = entityManager.createQuery(cq)
.setMaxResults(1)
.getResultList();
The SQL generated by Hibernate from criteria query:
select
t1_1.id,
t1_1.name
from
public.orders o1_0
join
public.gift_certificate g1_0
on g1_0.id=o1_0.gift_certificate_id
join
(public.gift_certificate_tag t1_0
join
public.tag t1_1
on t1_1.id=t1_0.tag_id)
on g1_0.id=t1_0.gift_certificate_id
join
public.users u1_0
on u1_0.id=o1_0.user_id
where
u1_0.id=(
select
u2_0.id
from
public.orders o2_0
join
public.users u2_0
on u2_0.id=o2_0.user_id
group by
1
having
sum(o2_0.order_cost)=(
select
max((select
sum(o4_0.order_cost)
from
public.orders o4_0
join
public.users u3_0
on u3_0.id=o4_0.user_id
group by
o4_0.user_id))
from
public.orders o3_0)
)
group by
1,
2
order by
count(o1_0.gift_certificate_id) desc fetch first ? rows only
The problem is with the following SQL block:
select max((select
sum(o4_0.order_cost)
from
public.orders o4_0
join
public.users u3_0
on u3_0.id=o4_0.user_id
group by
o4_0.user_id))
this causes org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression
The question is how to make criteria query instead generate SQL same as generated in case of JPQL query:
select max(subquery1_0.totalOrderCost)
from
(select
sum(o3_0.order_cost)
from
public.orders o3_0
group by
o3_0.user_id) subquery1_0(totalOrderCost)
英文:
In order to learn JPA I'm trying to explore various ways to execute queries. I have native SQL and JPQL queries which work as expected. Now I'm trying to achieve the same by Criteria Query approach.
My entities:
@Entity
public class GiftCertificate {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String description;
@Column(precision = 10, scale = 2)
private BigDecimal price;
private Short duration;
private LocalDateTime createDate;
private LocalDateTime lastUpdateDate;
@ManyToMany
@JoinTable(
name = "gift_certificate_tag",
joinColumns = @JoinColumn(name = "gift_certificate_id"),
inverseJoinColumns = @JoinColumn(name = "tag_id")
)
private Set<Tag> tags = new HashSet<>();
public Set<Tag> getTags() {
return Collections.unmodifiableSet(tags);
}
}
@Entity
@Table(name = "ORDERS")
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(fetch = FetchType.LAZY, optional = false)
private User user;
@ManyToOne(fetch = FetchType.LAZY, optional = false)
private GiftCertificate giftCertificate;
private LocalDateTime orderDate;
private BigDecimal orderCost;
}
@Entity
@Table(name = "tag", schema = "public")
public class Tag {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
//@Basic(optional = false)
//@NotNull
@Column(nullable = false)
private String name;
}
@Entity
@Table(name = "users", schema = "public")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String userName;
}
The task is to get the most widely used tag of a user with the highest cost of all orders
Working SQL:
SELECT *
FROM tag
JOIN (SELECT tag_id,
Count (gift_certificate_tag.gift_certificate_id) AS
cert_count
FROM gift_certificate_tag
JOIN orders
ON gift_certificate_tag.gift_certificate_id =
orders.gift_certificate_id
JOIN (SELECT user_id,
Sum (order_cost) AS all_orders_cost
FROM orders
GROUP BY user_id
ORDER BY all_orders_cost DESC
LIMIT 1) AS orders_sum
ON orders_sum.user_id = orders.user_id
GROUP BY tag_id
ORDER BY cert_count DESC
LIMIT 1) AS result
ON result.tag_id = tag.id
Working JPQL:
SELECT new Tag(t.id, t.name)
FROM GiftCertificate gc
JOIN gc.tags t
JOIN Order o ON o.giftCertificate = gc
JOIN o.user u
WHERE u = (
SELECT ou
FROM Order o2
JOIN o2.user ou
GROUP BY ou
HAVING SUM(o2.orderCost) = (
SELECT MAX(totalOrderCost)
FROM (
SELECT SUM(o3.orderCost) AS totalOrderCost
FROM Order o3
GROUP BY o3.user
) subquery
)
)
GROUP BY t.id, t.name
ORDER BY COUNT(gc) DESC
Not working Criteria Query:
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tag> cq = cb.createQuery(Tag.class);
Root<Order> order = cq.from(Order.class);
Join<Order, GiftCertificate> giftCertificate = order.join("giftCertificate");
Join<GiftCertificate, Tag> tag = giftCertificate.join("tags");
Join<Order, User> user = order.join("user");
//sub-query to find user's orders sums
Subquery<BigDecimal> querySumOfOrders = cq.subquery(BigDecimal.class);
Root<Order> subOrderSumOfOrders = querySumOfOrders.from(Order.class);
Join<Order, User> subUserSumOfOrders = subOrderSumOfOrders.join("user");
querySumOfOrders.select(cb.sum(subOrderSumOfOrders.get("orderCost")))
.groupBy(subUserSumOfOrders);
//sub-query to find max cost of the user's orders sum
Subquery<BigDecimal> queryMaxOrdersSum = cq.subquery(BigDecimal.class);
queryMaxOrdersSum.from(Order.class);
queryMaxOrdersSum.select(cb.max(querySumOfOrders));
//sub-query to find user with most cost of all orders
Subquery<User> queryMostSpentUser = cq.subquery(User.class);
Root<Order> subOrderMostSpentUser = queryMostSpentUser.from(Order.class);
Join<Order, User> subUserMostSpentUser = subOrderMostSpentUser.join("user");
queryMostSpentUser.select(subUserMostSpentUser)
.having(cb.equal(cb.sum(subOrderMostSpentUser.get("orderCost")), cb.max(queryMaxOrdersSum)))
.groupBy(subUserMostSpentUser);
//main query
cq.select(tag)
.where(cb.equal(user, queryMostSpentUser))
.orderBy(cb.desc(cb.count(giftCertificate)))
.groupBy(tag);
List<Tag> tagList = entityManager.createQuery(cq)
.setMaxResults(1)
.getResultList();
The SQL generated by Hibernate from criteria query:
select
t1_1.id,
t1_1.name
from
public.orders o1_0
join
public.gift_certificate g1_0
on g1_0.id=o1_0.gift_certificate_id
join
(public.gift_certificate_tag t1_0
join
public.tag t1_1
on t1_1.id=t1_0.tag_id)
on g1_0.id=t1_0.gift_certificate_id
join
public.users u1_0
on u1_0.id=o1_0.user_id
where
u1_0.id=(
select
u2_0.id
from
public.orders o2_0
join
public.users u2_0
on u2_0.id=o2_0.user_id
group by
1
having
sum(o2_0.order_cost)=(
select
max((select
sum(o4_0.order_cost)
from
public.orders o4_0
join
public.users u3_0
on u3_0.id=o4_0.user_id
group by
o4_0.user_id))
from
public.orders o3_0)
)
group by
1,
2
order by
count(o1_0.gift_certificate_id) desc fetch first ? rows only
The problem is with the following SQL block:
select max((select
sum(o4_0.order_cost)
from
public.orders o4_0
join
public.users u3_0
on u3_0.id=o4_0.user_id
group by
o4_0.user_id))
this causes org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression
The question is how to make criteria query instead generate SQL same as generated in case of JPQL query:
select max(subquery1_0.totalOrderCost)
from
(select
sum(o3_0.order_cost)
from
public.orders o3_0
group by
o3_0.user_id) subquery1_0(totalOrderCost)
答案1
得分: 1
以下是翻译好的代码部分：
"The "JPQL" (actually HQL) can only work with Hibernate 6+, because before that, Hibernate did not support subqueries in the from clause. To use that feature from JPA Criteria, you have to use the Hibernate JPA Criteria extensions.
HibernateCriteriaBuilder cb = entityManager.unwrap(Session.class).getCriteriaBuilder();
JpaCriteriaQuery<Tag> cq = cb.createQuery(Tag.class);
JpaRoot<Order> order = cq.from(Order.class);
JpaJoin<Order, GiftCertificate> giftCertificate = order.join("giftCertificate");
JpaJoin<GiftCertificate, Tag> tag = giftCertificate.join("tags");
JpaJoin<Order, User> user = order.join("user");
//sub-query to find user's orders sums
JpaSubquery<Tuple> querySumOfOrders = cq.subquery(Tuple.class);
JpaRoot<Order> subOrderSumOfOrders = querySumOfOrders.from(Order.class);
querySumOfOrders.multiselect(cb.sum(subOrderSumOfOrders.get("orderCost")).alias("theSum"))
.groupBy(subOrderSumOfOrders.get("user"));
//sub-query to find max cost of the user's orders sum
JpaSubquery<BigDecimal> queryMaxOrdersSum = cq.subquery(BigDecimal.class);
JpaRoot<Tuple> queryMaxOrders = queryMaxOrdersSum.from(querySumOfOrders);
queryMaxOrdersSum.select(cb.max(queryMaxOrders.get("theSum")));
//sub-query to find user with most cost of all orders
JpaSubquery<User> queryMostSpentUser = cq.subquery(User.class);
JpaRoot<Order> subOrderMostSpentUser = queryMostSpentUser.from(Order.class);
JpaJoin<Order, User> subUserMostSpentUser = subOrderMostSpentUser.join("user");
queryMostSpentUser.select(subUserMostSpentUser)
.having(cb.equal(cb.sum(subOrderMostSpentUser.get("orderCost")), cb.max(queryMaxOrdersSum)))
.groupBy(subUserMostSpentUser);
//main query
cq.select(tag)
.where(cb.equal(user, queryMostSpentUser))
.orderBy(cb.desc(cb.count(giftCertificate)))
.groupBy(tag);
List<Tag> tagList = entityManager.createQuery(cq)
.setMaxResults(1)
.getResultList();
希望这对你有所帮助。如果有任何其他问题，请随时提出。
英文:
The "JPQL" (actually HQL) can only work with Hibernate 6+, because before that, Hibernate did not support subqueries in the from clause. To use that feature from JPA Criteria, you have to use the Hibernate JPA Criteria extensions.
HibernateCriteriaBuilder cb = entityManager.unwrap(Session.class).getCriteriaBuilder();
JpaCriteriaQuery<Tag> cq = cb.createQuery(Tag.class);
JpaRoot<Order> order = cq.from(Order.class);
JpaJoin<Order, GiftCertificate> giftCertificate = order.join("giftCertificate");
JpaJoin<GiftCertificate, Tag> tag = giftCertificate.join("tags");
JpaJoin<Order, User> user = order.join("user");
//sub-query to find user's orders sums
JpaSubquery<Tuple> querySumOfOrders = cq.subquery(Tuple.class);
JpaRoot<Order> subOrderSumOfOrders = querySumOfOrders.from(Order.class);
querySumOfOrders.multiselect(cb.sum(subOrderSumOfOrders.get("orderCost")).alias("theSum"))
.groupBy(subOrderSumOfOrders.get("user"));
//sub-query to find max cost of the user's orders sum
JpaSubquery<BigDecimal> queryMaxOrdersSum = cq.subquery(BigDecimal.class);
JpaRoot<Tuple> queryMaxOrders = queryMaxOrdersSum.from(querySumOfOrders);
queryMaxOrdersSum.select(cb.max(queryMaxOrders.get("theSum")));
//sub-query to find user with most cost of all orders
JpaSubquery<User> queryMostSpentUser = cq.subquery(User.class);
JpaRoot<Order> subOrderMostSpentUser = queryMostSpentUser.from(Order.class);
JpaJoin<Order, User> subUserMostSpentUser = subOrderMostSpentUser.join("user");
queryMostSpentUser.select(subUserMostSpentUser)
.having(cb.equal(cb.sum(subOrderMostSpentUser.get("orderCost")), cb.max(queryMaxOrdersSum)))
.groupBy(subUserMostSpentUser);
//main query
cq.select(tag)
.where(cb.equal(user, queryMostSpentUser))
.orderBy(cb.desc(cb.count(giftCertificate)))
.groupBy(tag);
List<Tag> tagList = entityManager.createQuery(cq)
.setMaxResults(1)
.getResultList();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论