如何在JPQL WHERE子句中使用@ElementCollection Map的value值

我有一个实体类Config，包含标注@ElementCollection的Map<String,String>类型字段settings，想要筛选出该map的value匹配指定前缀的实体，但在H2数据库运行时报错。

实体类代码 #

@Entity
@Table(name = "config")
public class Config {

    private String id;

    private String name;

    @ElementCollection
    @CollectionTable(name = "setting",
            joinColumns = @JoinColumn(name = "config_id", referencedColumnName = "id"))
    @MapKeyColumn(name = "name", columnDefinition = "varchar(1024)")
    @Column(name = "value", columnDefinition = "TEXT")
    Map<String, String> settings;

}

错误的JPA查询方法 #

@Query("select config.id, config.name from Config config " +
        "join config.settings setting " +
        "where value(setting) like concat(:prefix, '%')")
List<List<String>> findConfigBySettingPrefix(@Param("prefix") String prefix);

报错信息 #

Caused by: org.h2.jdbc.JdbcSQLDataException: Scalar subquery contains more than one row;

生成的错误SQL #

select config."id", config."name" from "config" config 
inner join "setting" setting on config."id"=setting."config_id" 
where (select setting."value" from "setting" setting where config."id"=setting."config_id") like (?||'%')

期望的正确SQL #

select config."id", config."name" from "config" config
inner join "setting" setting on config."id"=setting."config_id"
where setting."value" like (?||'%')

尝试过的其他无效JPQL变体 #

@Query("select config.id, config.name from Config config " +
        "join config.settings setting on value(setting) like concat(:prefix, '%')")

@Query("select config.id, config.name from Config config " +
        "where value(config.settings) like concat(:prefix, '%')")

可行的实现方式 #

方法1：修正JPQL写法，直接访问map的value属性 #

大部分JPA实现（比如Hibernate）支持在join Map类型的ElementCollection后，直接通过setting.value访问关联表的value字段，同时加上distinct避免重复返回同一Config记录：

@Query("select distinct config.id, config.name from Config config " +
        "join config.settings setting " +
        "where setting.value like concat(:prefix, '%')")
List<List<String>> findConfigBySettingPrefix(@Param("prefix") String prefix);

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

如果JPQL的解析仍然有问题，直接用原生SQL完全按照你期望的逻辑编写：

@Query(value = "select distinct config.id, config.name from config config " +
        "inner join setting setting on config.id=setting.config_id " +
        "where setting.value like concat(?1, '%')", nativeQuery = true)
List<List<String>> findConfigBySettingPrefix(String prefix);

方法3：使用JPA Criteria API构建查询 #

如果需要动态调整查询条件，用Criteria API可以更精准控制SQL生成：

@Autowired
private EntityManager entityManager;

public List<List<String>> findConfigBySettingPrefix(String prefix) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> query = cb.createTupleQuery();
    Root<Config> configRoot = query.from(Config.class);
    Join<Config, Map.Entry<String, String>> settingJoin = configRoot.join("settings");
    
    query.select(cb.tuple(configRoot.get("id"), configRoot.get("name")))
         .where(cb.like(settingJoin.get("value"), prefix + "%"))
         .distinct(true);
    
    return entityManager.createQuery(query)
                        .getResultList()
                        .stream()
                        .map(tuple -> Arrays.asList(tuple.get(0).toString(), tuple.get(1).toString()))
                        .collect(Collectors.toList());
}

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