JPA命名原生查询中传递列表到IN子句

Question

我知道在JPA中可以将列表传递给命名查询，但是如何在NamedNativeQuery中传递呢？我尝试了许多方法，但仍然无法将列表直接传递给NamedNativeQuery。有人知道如何将列表传递给NamedNativeQuery的in子句吗？非常感谢！

以下是NamedNativeQuery：

@NamedNativeQuery(
   name="User.findByUserIdList", 
   query="select u.user_id, u.dob, u.name, u.sex, u.address from user u "+
         "where u.user_id in (?userIdList)"
)

并且它是这样调用的：

List<Object[]> userList = em.createNamedQuery("User.findByUserIdList").setParameter("userIdList", list).getResultList();

然而结果并不如我所期望。

System.out.println(userList.size());  //output 1

Object[] user = userList.get(0);
System.out.println(user.length);   //expected 5 but result is 3
System.out.println(user[0]);       //output MDAVERSION which is not a user_id
System.out.println(user[1]);       //output 5
System.out.println(user[2]);       //output 7

- Li Ho Yin

---

你尝试过使用数据库客户端完全执行相同的查询吗？ - Art Licis

你不应该使用括号（?userIdList），只需删除括号即可正常工作。 - prashant thakre

Answer 1

以上被接受的答案是不正确的，导致我跑了很多天的偏

JPA和Hibernate都可以使用Query在本地查询中接受集合。

你只需要这样做：

String nativeQuery = "Select * from A where name in :names"; //use (:names) for older versions of hibernate
Query q = em.createNativeQuery(nativeQuery);
q.setParameter("names", l);

请参考这里的答案，它们提出了相同的建议（我从其中之一选择了上面的例子）

1. 参考1
2. 参考2中提到了在将列表作为参数时括号起作用的情况

*注意这些参考是关于jpql查询的，但集合的使用也适用于本地查询。

- HopeKing

---

那些是JPA/JPQL查询，不是本地SQL查询，对吗？问题是关于“本地查询”的，而且被接受的答案甚至提到了这种差异，或者我理解错了什么？ - Sander Verhagen

对我来说可以工作，但只有当列表至少有1个条目时。 - Klaus

这是错误的，也不可行。你可以在JPA/JPQL查询中执行此操作，而不能像@SanderVerhagen所说的那样在本地SQL查询中执行。 - rMonteiro

这对于我的本地查询根本不起作用，答案错误。 - Gina De Beukelaer

答案是正确的。如果不是，则尝试将生成的SQL显示在控制台中。在“in”子句之后不要使用括号，例如“select * from A where name in (:names)”。这不起作用！ - Rudy Vissers

Answer 2

一个列表不是本地SQL查询的有效参数，因为它不能在JDBC中被绑定。你需要为列表中的每个参数创建一个参数。

例如：where u.user_id in (?id1, ?id2)

这可以通过JPQL来支持，但不支持SQL，所以你可以使用JPQL代替本地查询。

一些JPA提供商可能会支持此功能，因此你可以向提供商报告错误。

- James

---

注意：这个答案是不正确的。请看下面我的回答和参考文献。 - HopeKing

Answer 3

这可以非常简单：

@Query(nativeQuery =true,value = "SELECT * FROM Employee as e WHERE e.employeeName IN (:names)")  
 List<Employee> findByEmployeeName(@Param("names") List<String> names);

- Wei Wang

Answer 4

根据您的数据库/提供程序/驱动程序等，实际上可以将列表作为绑定参数传递给JPA本地查询。

例如，对于Postgres和EclipseLink，以下内容有效（返回true），演示了多维数组以及如何获取双精度数组。 （使用SELECT pg_type.* FROM pg_catalog.pg_type获取其他类型；可能是带有_的那些类型，但在使用之前请将其删除。）

Array test = entityManager.unwrap(Connection.class).createArrayOf("float8", new Double[][] { { 1.0, 2.5 }, { 4.1, 5.0 } });
Object result = entityManager.createNativeQuery("SELECT ARRAY[[CAST(1.0 as double precision), 2.5],[4.1, 5.0]] = ?").setParameter(1, test).getSingleResult();

这里的“cast”是指将数组中的元素转换为双精度浮点数而不是数字。

更加关键的问题是 - 我不知道是否可以使用命名查询；可能会有所不同。但我认为以下内容适用于数组操作。

Array list = entityManager.unwrap(Connection.class).createArrayOf("int8", arrayOfUserIds);
List<Object[]> userList = entityManager.createNativeQuery("select u.* from user u "+
     "where u.user_id = ANY(?)")
     .setParameter(1, list)
     .getResultList();

我没有和OP相同的模式，所以我没有确切地检查过这个问题，但我认为它应该可以工作 - 至少在Postgres & EclipseLink上。

此外，关键字可以在以下网址找到：http://tonaconsulting.com/postgres-and-multi-dimensions-arrays-in-jdbc/

- Erhannis

Answer 5

使用Hibernate、JPA 2.1和Deltaspike Data，我可以在查询中将包含IN子句的列表作为参数传递。我的查询如下所示。

@Query(value = "SELECT DISTINCT r.* FROM EVENT AS r JOIN EVENT AS t on r.COR_UUID = t.COR_UUID where " +
        "r.eventType='Creation' and t.eventType = 'Reception' and r.EVENT_UUID in ?1", isNative = true)
public List<EventT> findDeliveredCreatedEvents(List<String> eventIds);

- IsidIoan

Answer 6

在JPA中，它对我起作用了。

@Query(nativeQuery =true,value = "SELECT * FROM Employee as e WHERE e.employeeName IN (:names)")  
List<Employee> findByEmployeeName(@Param("names") List<String> names);

- Edgar Nir

Answer 7

目前我使用的是带有Hibernate的JPA 2.1版本

我还使用原生查询中的IN条件。以下是我的查询示例：

SELECT ... WHERE table_name.id IN (?1)

我注意到由于James描述的限制，无法传递类似"id_1，id_2，id_3"这样的字符串。

但是，当您使用jpa 2.1 + hibernate时，可以传递字符串值列表。 对于我的情况，下面的代码是有效的：

    List<String> idList = new ArrayList<>();
    idList.add("344710");
    idList.add("574477");
    idList.add("508290");

    query.setParameter(1, idList);

- musicccMan

Answer 8

在我的情况下（EclipseLink，PostGreSQL），这样做就可以：

最初的回答

    ServerSession serverSession = this.entityManager.unwrap(ServerSession.class);
    Accessor accessor = serverSession.getAccessor();
    accessor.reestablishConnection(serverSession);
    BigDecimal result;
    try {
        Array jiraIssues = accessor.getConnection().createArrayOf("numeric", mandayWorkLogQueryModel.getJiraIssues().toArray());
        Query nativeQuery = this.entityManager.createNativeQuery(projectMandayWorkLogQueryProvider.provide(mandayWorkLogQueryModel));
        nativeQuery.setParameter(1,mandayWorkLogQueryModel.getPsymbol());
        nativeQuery.setParameter(2,jiraIssues);
        nativeQuery.setParameter(3,mandayWorkLogQueryModel.getFrom());
        nativeQuery.setParameter(4,mandayWorkLogQueryModel.getTo());
        result = (BigDecimal) nativeQuery.getSingleResult();
    } catch (Exception e) {
        throw new DataAccessException(e);
    }

    return result;

也不能在查询中使用IN(?)，否则会出现错误，如下所示：

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: numeric = numeric[]

'IN(?)'必须替换为'= ANY(?)'

我的解决方案基于Erhannis的概念。

- Marcin Dąbrowski

Answer 9

您应该这样做：

String userIds ="1,2,3,4,5"; List<String> userIdList= Stream.of(userIds.split(",")).collect(Collectors.toList());

然后，将其作为参数传递到查询中，像这样：

@NamedNativeQuery(name="User.findByUserIdList", query="select u.user_id, u.dob, u.name, u.sex, u.address from user u where u.user_id in (?userIdList)")

- Gino Chavez

Answer 10

尝试使用Hibernate作为提供程序在JPA2中进行操作，似乎Hibernate支持将列表用于“IN”并且可以正常工作（至少对于命名查询而言，我认为命名NATIVE查询也会类似）。Hibernate在内部生成与传入列表中元素数量相同的动态参数。

因此，在您上面的示例中，

List<Object[]> userList = em.createNamedQuery("User.findByUserIdList").setParameter("userIdList", list).getResultList();

如果列表有2个元素，查询将如下所示：

select u.user_id, u.dob, u.name, u.sex, u.address from user u "+
         "where u.user_id in (?, ?)

如果它有3个元素，则看起来像：

select u.user_id, u.dob, u.name, u.sex, u.address from user u "+
         "where u.user_id in (?, ?, ?)

- Ravi Sanwal