Hibernate Map自定义查询生成多余子查询问题求助

我完全明白你遇到的困扰——Hibernate生成的SQL里出现了多余的无过滤子查询，导致返回多行结果触发java.sql.SQLException: Subquery returns more than 1 row错误。咱们来一步步修复这个问题。

问题根源 #

你原来的HQL写法中，虽然通过JOIN p.statusTransitions s关联了状态变更表，但Hibernate在解析VALUE(s)时没有正确绑定到当前JOIN的条目，反而生成了一个未过滤状态的子查询，去查询该Purchase的所有状态变更日期。当一个Purchase存在多个状态记录时，这个子查询自然会返回多行，触发报错。

修复方案 #

方案一：面向对象的HQL写法（推荐） #

调整HQL，直接利用JOIN后的别名s来过滤Map的键和值，避免生成不必要的子查询：

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

关键细节：

• 使用枚举的全限定类名代替字符串，既避免拼写错误，也能让Hibernate自动适配你配置的@MapKeyEnumerated(EnumType.STRING)规则，转换成对应的数据库字符串值。
• 用BETWEEN简化日期范围判断，逻辑和>=+<=完全一致，但写法更简洁。
• 此时s代表的是statusTransitions中的单个键值对，KEY(s)和VALUE(s)会直接映射到关联表的对应列，Hibernate会生成正确的JOIN过滤SQL，不再生成多余子查询。

方案二：直接关联数据库列名（备选） #

如果方案一仍有解析问题，可以直接使用关联表的列名来编写查询（依赖你配置的@CollectionTable和列注解）：

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

这种写法直接指定关联表的列名（status和date），绕过HQL对Map元素的解析，适合特殊场景下的兼容处理。

修复后的SQL效果 #

修改后的HQL会生成类似这样的简洁SQL，完全没有多余子查询：

select p.* from purchase p 
inner join user c on p.customer_id = c.user_id 
inner join purchase_status s on p.purchase_id = s.purchase_purchase_id 
where c.username = ? 
and s.status = 'READY_TO_BE_PAID' 
and s.date between ? and ?

这样就能精准过滤出符合条件的Purchase记录，不会再触发子查询返回多行的错误。

内容的提问来源于stack exchange，提问作者Stefano Sambruna