时间戳

首页 > 解决方案 > 地图上的 Hibernate 自定义查询会生成不需要的子查询

java - 地图上的 Hibernate 自定义查询会生成不需要的子查询

问题描述

我有一个类购买，里面有一张地图。

@Entity
public class Purchase {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Integer purchaseId;

    @ManyToOne
    @JoinColumn(name = "customer_id")
    private User customer;

    @ManyToOne
    @JoinColumn(name = "item_id")
    private Item item;

    private String customization;

    @ElementCollection
    @MapKeyEnumerated(value = EnumType.STRING)
    @CollectionTable(name = "purchase_status")
    @MapKeyColumn(name = "status")
    @Column(name = "date")
    private Map<PurchaseStatus, Date> statusTransitions = new HashMap<>();

    private Date expectedDeliveryDate;

    @ManyToOne
    @JoinColumn(name = "purchase_cart_id")
    @JsonIgnore
    private PurchaseCart purchaseCart;

    @OneToOne
    @JoinColumn(name = "destination_address_id")
    private DestinationAddress destinationAddress;

    @Transient
    @JsonIgnore
    private Date purchaseDate;

它代表购买。PurchaseStatus 是一个枚举器。statusTransitions 表示此次购买获得的所有状态。特别是它可以是“READY_TO_BE_PAID”和“PAID”。状态有相对日期，即地图的VALUE，状态本身就是地图的KEY，因为一次购买只能处于精确状态一次。我想从数据库中获取具有特定用户（用户名，这是用户的属性）和 statusTransitions HashMap 内的特定（KEY，VALUE）对的所有购买。特别是我想创建一个查询，它返回日期（VALUE）在两个日期（fromDate 和 toDate）之间且 PurchaseStatus（KEY）等于枚举值“READY_TO_BE_PAID”的所有购买。

我创建了这个自定义查询：

@Query("select p from Purchase p JOIN p.customer c JOIN p.statusTransitions s WHERE c.username = :username and " +
            "(KEY(s) = 'READY_TO_BE_PAID' and " +
            "VALUE(s) >= :fromDate and " +
            "VALUE(s) <= :toDate)")
List<Purchase> findByUsernameAndByDate(@Param("fromDate") Date fromDate, @Param("toDate") Date toDate, @Param("username") String username);

唯一的问题是生成的 SQL 查询如下：

select 
*
from 
  purchase purchase0_ 
    inner join user user1_ on purchase0_.customer_id=user1_.user_id 
    inner join purchase_status statustran2_ on purchase0_.purchase_id=statustran2_.purchase_purchase_id 
  where 
    statustran2_.status='READY_TO_BE_PAID' and 
    user1_.username=? and 
      (select 
        statustran2_.date 
      from 
        purchase_status statustran2_ 
      where 
        purchase0_.purchase_id=statustran2_.purchase_purchase_id
        )>=? and 
      (select 
        statustran2_.date 
      from 
        purchase_status statustran2_ 
      where 
        purchase0_.purchase_id=statustran2_.purchase_purchase_id
      )<=?

这不是我想要的。它生成两个子查询，结果是它抛出了这个错误：

java.sql.SQLException: Subquery returns more than 1 row

这是因为当它执行子查询时，它不会过滤 PurchaseStatus 上的行，以这种方式返回不止一行。关键是我不知道如何重写查询以避免这两个子查询或将 this (KEY(s) = 'READY_TO_BE_PAID') 条件放入 WHERE 子句中。我发现其他人也有同样的问题，但我找不到任何解决方案。

标签： javaspringhibernatejpa

解决方案

我找到了解决方案！似乎当您有此查询时

SELECT p 
FROM Purchase p 
    JOIN p.customer c 
    JOIN p.statusTransitions s

statusTransactions Map 的别名“s”已经是值，而不是您被引导相信的对 (KEY, VALUE)。这有点违反直觉，因为 KEY 返回键但 VALUE 不返回值，而是自动生成一个子查询，这不可避免地会导致错误。

java.sql.SQLException: Subquery returns more than 1 row

所以改写这个的正确方法：

SELECT p 
FROM Purchase p 
    JOIN p.customer c 
    JOIN p.statusTransitions s 
WHERE 
    c.username = :username and
    KEY(s) = 'READY_TO_BE_PAID' and
    VALUE(s) >= :fromDate and
    VALUE(s) <= :toDate

是：

SELECT p 
FROM Purchase p 
    JOIN p.customer c 
    JOIN p.statusTransitions s 
WHERE 
    c.username = :username and
    KEY(s) = 'READY_TO_BE_PAID' and
    s >= :fromDate and
    s <= :toDate

或更简洁：

SELECT p 
FROM Purchase p 
    JOIN p.customer c 
    JOIN p.statusTransitions s 
WHERE 
    c.username = :username and
    KEY(s) = 'READY_TO_BE_PAID' and
    s BETWEEN :fromDate and :toDate

正如我已经说过的，别名's'已经是（键，值）对的值！

推荐阅读