JPA关联实体使用IN操作符查询时的参数与属性解析问题

问题场景 #

现有UserStoryMapping实体类，通过story_id字段与Story实体关联，实体代码如下：

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "user_story_mapping")
public class UserStoryMapping {
 
     @ManyToOne
     @JoinColumn(name = "story_id")
     private Story story;
 
     @Column(name = "user_id")
     private String userId;
 
     @Column(name = "seen_at")
     private Date seenAt;
  
     @Transient
     private String storyId;
}

需要实现类似以下原生SQL的查询逻辑：

select usm.story_id as storyId from user_story_mapping usm where usm.user_id='Naman' and usm.story_id IN ("8a9f90858871dc9f018abc3fc3dc4e86","ffhy8a9f9abc3fc3dc4e09") and usm.status = 1;

错误尝试1：直接关联实体匹配 #

编写JPQL如下：

public static final String GET_ACTIVE_USER_STORY_MAPPINGS = """
             from UserStoryMapping usm 
             where ((usm.userId = :userId) and (usm.story IN :storyIds) and (usm.status = 1))
             """;

执行时抛出类型不匹配异常：

…nested exception is java.lang.IllegalArgumentException:
Parameter value element [8a9f90858871dc9f018abc3fc3dc4e86] did not match expected type [entity.core.Story (n/a)]

错误尝试2：直接使用数据库字段名 #

修改JPQL为直接引用数据库字段：

public static final String GET_ACTIVE_USER_STORY_MAPPINGS = """
             select usm.story_id as storyId 
             from UserStoryMapping usm 
             where ((usm.userId = :userId) and (usm.story_id IN :storyIds) and (usm.status = 1))
             """;

执行时抛出属性解析异常：

org.hibernate.QueryException: could not resolve property: story_id of: entity.core.UserStoryMapping [select usm.story_id as storyId
from entity.core.UserStoryMapping usm where ((usm.userId = :userId) and (usm.story_id IN :storyIds) and (usm.status = 1))
];

正确解决方案 #

方案1：面向实体属性的JPQL查询（推荐） #

JPQL基于实体类属性而非数据库字段编写，需通过关联实体story的id属性进行匹配，同时指定查询返回story.id作为结果：

public static final String GET_ACTIVE_USER_STORY_MAPPINGS = """
             select usm.story.id as storyId 
             from UserStoryMapping usm 
             where usm.userId = :userId 
               and usm.story.id IN :storyIds 
               and usm.status = 1
             """;

执行时传入参数示例：

List<String> storyIdList = Arrays.asList("8a9f90858871dc9f018abc3fc3dc4e86", "ffhy8a9f9abc3fc3dc4e09");
Query query = entityManager.createQuery(GET_ACTIVE_USER_STORY_MAPPINGS);
query.setParameter("userId", "Naman");
query.setParameter("storyIds", storyIdList);
List<String> result = query.getResultList();

方案2：使用原生SQL查询 #

如果倾向直接复用原生SQL语法，可使用createNativeQuery执行原生查询：

String nativeSql = """
             select usm.story_id as storyId from user_story_mapping usm 
             where usm.user_id=?1 and usm.story_id IN (?2) and usm.status = 1
             """;
List<String> storyIdList = Arrays.asList("8a9f90858871dc9f018abc3fc3dc4e86", "ffhy8a9f9abc3fc3dc4e09");
Query query = entityManager.createNativeQuery(nativeSql);
query.setParameter(1, "Naman");
query.setParameter(2, storyIdList);
List<String> result = query.getResultList();

错误原因说明 #

• 错误尝试1：usm.story IN :storyIds期望传入Story实体对象的集合，但实际传入的是字符串ID，导致类型不匹配。
• 错误尝试2：JPQL仅支持使用实体类定义的属性名（如story.id），不能直接使用数据库字段名story_id，因此触发属性解析失败。

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