JPA EclipseLink结合PostgreSQL时List作为查询参数失效问题

我使用JPA（EclipseLink实现）搭配PostgreSQL数据库，在原生查询中使用List类型的命名参数（比如in (#ids)）时出现报错。

假设有一张cars表，包含id和name列，我想查询id在集合ids中的所有汽车名称，编写的JPA原生查询如下：

final String sql = """
  select c.name
  from cars c
  where c.id = (#ids)""";
final List<String> result = em.createNativeQuery(sql)
.setParameter("ids", new ArrayList<>(ids))
.getResultList();

执行时抛出错误：

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.12.v20230209-e5c4074ef3): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: 无法推断java.util.ArrayList实例在SQL中应使用的数据类型，请使用setObject()指定显式类型。
Error Code: 0
Call:          select c.name
            from cars c
            where c.id = (?)
    bind => [[1]]
Query: DataReadQuery(sql="         select c.name
            from cars c
            where c.id = (?)")
    at app//org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:342)
    at app//org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:700)
    at app//org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:567)
    at app//org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2105)
    at app//org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:313)
    at app//org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:275)
    at app//org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:261)
    at app//org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:345)
    at app//org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelect(DatasourceCallQueryMechanism.java:327)
    at app//org.eclipse.persistence.queries.DataReadQuery.executeNonCursor(DataReadQuery.java:199)
    at app//org.eclipse.persistence.queries.DataReadQuery.executeDatabaseQuery(DataReadQuery.java:154)
    at app//org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:913)
    at app//org.eclipse.persistence.queries.DataReadQuery.execute(DataReadQuery.java:139)
    at app//org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:812)
    at app//org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3004)
    at app//org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1898)
    at app//org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1880)
    at app//org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1845)
    at app//org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:262)
    ... 162 more
Caused by: org.postgresql.util.PSQLException: 无法推断java.util.ArrayList实例在SQL中应使用的数据类型，请使用setObject()指定显式类型。
    at app//org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1051)
    at app//org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:2703)
    at app//org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:791)
    at app//org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:628)
    ... 179 more

注：拼接字符串的方式不可行（存在SQL注入风险），目前正在研究Criteria API和Spring Data作为解决方案。

版本信息：

• org.eclipse.persistence:org.eclipse.persistence.jpa:2.7.12
• org.postgresql:postgresql:42.6.0

1. 修正原生查询写法（适配PostgreSQL + EclipseLink） #

方式一：使用PostgreSQL数组语法 + 指定参数类型 #

PostgreSQL支持数组类型查询，可将List转为数组并显式指定参数类型：

final String sql = """
  select c.name
  from cars c
  where c.id = any (?1)""";
final List<String> result = em.createNativeQuery(sql)
    .setParameter(1, ids.toArray(), Integer.class) // 根据id实际类型调整，比如Long.class
    .getResultList();

方式二：使用EclipseLink原生查询集合参数语法 #

EclipseLink对原生查询的集合参数有特殊处理，使用?作为占位符即可，框架会自动展开集合为多个参数：

final String sql = """
  select c.name
  from cars c
  where c.id in (?)""";
final List<String> result = em.createNativeQuery(sql)
    .setParameter(1, ids)
    .getResultList();

2. 使用Criteria API实现 #

通过Criteria API构建类型安全的查询，无需处理原生查询的参数适配问题：

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<String> query = cb.createQuery(String.class);
Root<Car> root = query.from(Car.class);

query.select(root.get("name"))
     .where(root.get("id").in(ids));

List<String> result = em.createQuery(query).getResultList();

（需确保存在与cars表对应的实体类Car）

3. 使用Spring Data JPA简化实现 #

若引入Spring Data JPA，只需在Repository接口中定义方法即可，框架自动生成安全查询：

public interface CarRepository extends JpaRepository<Car, Integer> {
    List<String> findNamesByIdIn(List<Integer> ids);
}

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