如何使用Postgres any子句与JPA/HiberNate本机查询（数组参数）

Question

所以我们有很多Postgres SQL查询存储在文件中，并从PHP中使用。任务是用Java取代PHP。我们希望“按原样”重用尽可能多的查询，以缩短迁移路径。我无法让数组参数工作。

下面是一个查询示例：

update user_devices
set some_date = now()
where some_id in (
    select distinct some_id from user_devices 
    where user_id = any(:userIDs) and device_id = any(:deviceIDs)
    and exists (select 1 from users where user_id = any(:userIDs) and customer_id = :customerID)
);

请注意导致问题的“any”子句，因为它们需要数组类型。下面是我们从PHP中使用它们的方式：

$this->allValues['userIDs'] = '{' . implode ( ",", $userIdNodes ) . '}';
$this->allValues['deviceIDs'] = '{' . implode ( ",", $deviceIdNodes ) . '}';
$this->allValues['customerID'] = customerID;
$this->db->runQuery ( $this->getQuery ( 'my_query' ), $this->allValues );

因此，作为参数，数组类型看起来像“{111222}”。

这就是我在Java中尝试的：

    Integer customerID = 1;
    int[] userIDs  = new int[]{111,222};
    int[] deviceIDs= new int[]{333,444};
    //List<Integer> userIDs  = Arrays.asList(111,222);
    //List<Integer> deviceIDs= Arrays.asList(333,444);
    //java.sql.Array userIDs  = toArray("integer", new int[]{111,222}));
    //java.sql.Array deviceIDs= toArray("integer", new int[]{333,444}));
    //java.sql.Array userIDs  = toArray("integer", Arrays.asList(111,222)));
    //java.sql.Array deviceIDs= toArray("integer", Arrays.asList(333,444)));
    //String userIDs  = "{111,222}";
    //String deviceIDs= "{333,444}";
    //String userIDs  = "ARRAY[111,222]";
    //String deviceIDs= "ARRAY[333,444]";

    Query nativeQuery = em.createNativeQuery(queryString);
    nativeQuery.setParameter("userIDs", userIDs);
    nativeQuery.setParameter("deviceIDs", deviceIDs);
    nativeQuery.setParameter("customerID", customerID);
    //nativeQuery.setParameter(createParameter("userIDs",java.sql.Array.class), userIDs);
    //nativeQuery.setParameter(createParameter("userIDs",java.sql.Array.class), deviceIDs);
    //nativeQuery.setParameter(createParameter("customerID", Integer.class), customerID);
    query.executeUpdate();

//[...]
private Array toArray(String typeName, Object... elements) {
    Session session = em.unwrap(Session.class); // ATTENTION! This is Hibernate-specific!
    final AtomicReference<Array> aRef = new AtomicReference<>();
    session.doWork((c) -> {
        aRef.set(c.createArrayOf(typeName, elements));
    });
    return aRef.get();
}

private <T> Parameter<T> createParameter(final String name, final Class<?> clazz) {
    return new Parameter<T>() {
        @Override
        public String getName() {
            return name;
        }
        @Override
        public Integer getPosition() {
            return null; // not used
        }
        @Override
        public Class<T> getParameterType() {
            return (Class<T>) clazz;
        }
    };
}

所有这些都不起作用，我将得到以下例外之一：使用“toArray”方法时：

Caused by: org.hibernate.HibernateException: Could not determine a type for class: org.postgresql.jdbc4.Jdbc4Array
    at org.hibernate.internal.AbstractQueryImpl.guessType(AbstractQueryImpl.java:550)
    at org.hibernate.internal.AbstractQueryImpl.guessType(AbstractQueryImpl.java:534)
    at org.hibernate.internal.AbstractQueryImpl.determineType(AbstractQueryImpl.java:519)
    at org.hibernate.internal.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:487)
    at org.hibernate.jpa.internal.QueryImpl$ParameterRegistrationImpl.bindValue(QueryImpl.java:247)
    at org.hibernate.

或者当使用int[]或Strings时，我会得到：

Caused by: org.postgresql.util.PSQLException: ERROR: op ANY/ALL (array) requires array on right side
  Position: 137
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:366)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:453)
    at com.sun.proxy.$Proxy274.executeUpdate(Unknown Source)
    at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:125)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
    jpa.spi.BaseQueryImpl.setParameter(BaseQueryImpl.java:582)

使用Wireshark，我在两个API与数据库对话时发现：

图：数据库调用与Wireshark的比较

select oid, typname from pg_type where oid in (0, 23, 1043) order by oid;

oid   |typname
------+-------
23    |int4
1043  |varchar

有没有人设法使用Hibernate作为JPA EntityManager的后端，在本机查询中使用数组参数？如果是：如何？

Answer 1

我可以通过从EntityManager中解包Hibernate会话并使用JDBC PreparedStatement来解决这个问题，该语句会吃掉java。sql。阵列参数没有任何投诉。

下面的示例中使用的NamedParameter语句在这里进行了描述（我根据自己的需要对其进行了修改）。它代表一份事先准备好的声明。

代码的其余部分是这样的：

public int executeUpdate(...){
    //....
    Integer customerID = 1;
    java.sql.Array userIDs  = toArray("integer", new int[]{111,222}));
    java.sql.Array deviceIDs= toArray("integer", new int[]{333,444}));

    final AtomicInteger rowsModifiedRef = new AtomicInteger();
    final Session session = em.unwrap(Session.class); // ATTENTION! This is Hibernate-specific!
    session.doWork((c) -> {
        try (final NamedParameterStatement statement = new NamedParameterStatement(c, queryString)) {
            statement.setObject("deviceIDs", userIDs);
            statement.setObject("userIDs", userIDs);
            statement.setObject("customerID", userIDs);
            rowsModifiedRef.set(statement.executeUpdate());
        }
    });
    return rowsModifiedRef.get();
}

private Array toArray(String typeName, Object... elements) {
    Session session = em.unwrap(Session.class); // ATTENTION! This is Hibernate-specific!
    final AtomicReference<Array> aRef = new AtomicReference<>();
    session.doWork((c) -> {
        aRef.set(c.createArrayOf(typeName, elements));
    });
    return aRef.get();
}

Answer 2

将查询从where user_id=any（：userid）更改为where user_id IN（：userid），并将userid数组更改为集合，例如列表