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

Answer 1

在我的例子（ 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(?) 因为你会得到如下错误：

由以下原因引起：org.postgresql.util.PSQLException：错误：运算符不存在：numeric = numeric[]

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

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

Answer 2

在 JPA2 中尝试使用 Hibernate 作为提供者，看来 hibernate 确实支持接受“IN”列表并且它可以工作。 （至少对于命名查询来说，我相信它与命名本机查询类似）
hibernate 内部所做的是生成动态参数，在 IN 内部与传入列表中的元素数量相同。

因此，在上面的示例中，

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 (?, ?, ?)

Answer 3

你应该这样做：

String userIds ="1,2,3,4,5";
列表<字符串> userIdList= Stream.of(userIds.split(",")).collect(Collectors.toList());

然后，在查询中传递 like 参数，如下所示：

@NamedNativeQuery(name=" User.findByUserIdList", query="从用户 u 中选择 u.user_id、u.dob、u.name、u.sex、u.address，其中 u.user_id in (?userIdList)")

Answer 4

标准 JPA 是不可能的。 Hibernate 提供了专有方法 setParameterList()，但它仅适用于 Hibernate 会话，并且在 JPA 的 EntityManager 中不可用。

我为 Hibernate 想出了以下解决方法，这并不理想，但几乎是标准的 JPA 代码，并且具有一些很好的属性。

对于初学者，您可以将命名的本机查询很好地分隔在 orm.xml 文件中：

<named-native-query name="Item.FIND_BY_COLORS" result-class="com.example.Item">
    <query>
        SELECT i.*
        FROM item i
        WHERE i.color IN ('blue',':colors')
        AND i.shape = :shape
    </query>
</named-native-query>

占位符用单引号括起来，因此它是有效的本机 JPA 查询。它在不设置参数列表的情况下运行，并且当在其周围设置其他匹配的颜色参数时仍然会返回正确的结果。

在 DAO 或存储库类中设置参数列表：

@SuppressWarnings("unchecked")
public List<Item> findByColors(List<String> colors) {
    String sql = getQueryString(Item.FIND_BY_COLORS, Item.class);
    sql = setParameterList(sql, "colors", colors);

    return entityManager
            .createNativeQuery(sql, Item.class)
            .setParameter("shape", 'BOX')
            .getResultList();
}

无需手动构造查询字符串。您可以像平常一样设置任何其他参数。

辅助方法：

String setParameterList(String sql, String name, Collection<String> values) {
    return sql.replaceFirst(":" + name, String.join("','", values));
}

String getQueryString(String queryName, Class<?> resultClass) {
    return entityManager
            .createNamedQuery(queryName, resultClass)
            .unwrap(org.hibernate.query.Query.class) // Provider specific
            .getQueryString();
}

基本上，我们从 orm.xml 读取查询字符串，手动设置参数列表，然后创建本机 JPA 查询。不幸的是，createNativeQuery().getResultList() 会返回一个非类型化查询和非类型化列表，即使我们向它传递了一个结果类。因此，@SuppressWarnings("unchecked")。

缺点：对于 Hibernate 以外的 JPA 提供者来说，在不执行查询的情况下展开查询可能会更复杂或不可能。例如，以下内容可能适用于 EclipseLink（未经测试，取自 我可以从 JPA 查询对象获取 SQL 字符串吗？）：

Session session = em.unwrap(JpaEntityManager.class).getActiveSession();
DatabaseQuery databaseQuery =     query.unwrap(EJBQueryImpl.class).getDatabaseQuery();
databaseQuery.prepareCall(session, new DatabaseRecord());
Record r = databaseQuery.getTranslationRow();
String bound = databaseQuery.getTranslatedSQLString(session, r);
String sqlString = databaseQuery.getSQLString();

另一种方法可能是将查询存储在文本文件中，并添加代码以从那里读取它。

Answer 5

您可以传入列表作为参数，但是：

• 如果您创建 @NamedNativeQuery 并使用 .createNamedQuery()，则不使用命名参数，您使用了 ?1（位置参数）。它从 1 开始，而不是 0。

• 如果您使用 .createNativeQuery(String)，则可以使用命名参数。

Answer 6

您可以尝试使用 :userIdList 而不是 (?userIdList)

@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"
)

Answer 7

上述接受的答案不正确，导致我偏离轨道很多天！！

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 查询的，尽管如此，集合的使用也适用于本机查询。

Answer 8

列表不是本机 SQL 查询的有效参数，因为它无法在 JDBC 中绑定。列表中的每个参数都需要有一个参数。

where u.user_id in (?id1, ?id2)

这是通过 JPQL 支持的，但不支持 SQL，因此您可以使用 JPQL 而不是本机查询。

某些 JPA 提供商可能支持此功能，因此您可能需要向提供商记录错误。

Answer 9

可以很简单：

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

Answer 10

事实上，您可以将列表作为绑定参数传递给 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();

强制转换就在那里，因此文字数组是双精度数而不是数字。

更重要的是 - 我不知道如何或是否可以进行命名查询；我认为这可能取决于情况。但我认为以下内容适用于数组。

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/

Answer 11

使用 hibernate、JPA 2.1 和 deltaspike 数据，我可以将列表作为包含 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);

Answer 12

在jpa，它对我有用

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

Answer 13

目前我将 JPA 2.1 与 Hibernate 一起使用，

我还在本机查询中使用 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);