为了学习JPA，我尝试探索各种执行查询的方法。我有原生SQL和JPQL查询，它们都能按预期工作。现在我尝试通过条件查询方法实现相同的效果。我的实体：
@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;
}任务是获取所有订单中使用最广泛且成本最高的用户的标签
工作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工作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不起作用的条件查询：
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();Hibernate根据条件查询生成的SQL：
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以下SQL块存在问题：
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))这将导致org. postgresql. util.错误：用作表达式的子查询返回了多行
问题是如何使条件查询生成与JPQL查询相同的SQL：
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条答案
按热度按时间rn0zuynd1#
“JPQL”（实际上是HQL）只能在Hibernate 6+中使用，因为在此之前，Hibernate不支持from子句中的子查询。要使用JPA Criteria中的该特性，必须使用Hibernate JPA Criteria扩展。