postgresql - Hibernate、postgres CTE 和原生 SQL 查询映射 ARRAY[]::BYTEA[] 与 addScalar

Question

我正在尝试在休眠中将以下公用表表达式作为本机 SQL 查询执行。虽然这是一个以“WITH”子句开头的 CTE，但我已经看到了其他使用本机 SQL 的示例。我正在一个名为 node 的表上执行此查询，其中包含以下 2 个字段

@Id 
@Type(type = "uuid-binary")
@Column(name="ID", unique = true, nullable=false)
protected UUID id;



@Type(type = "uuid-binary")
@Column(name="PARENT_ID", nullable=true)
private UUID parentId;

此查询取自官方 PostgreSQL wiki from here
并稍作修改，即我已删除限制

 "WHERE parent_id IS NULL".

如果我在 pgAdmin4 中执行这个查询，那么我会成功并且我知道它可以正常工作。即我得到如下所示的预期输出：

请注意，称为祖先的第二列的类型是“bytea[]”（不要与 byte[] 混淆）。

似乎我无法映射这个，即问题在于字节茶数组的映射，即“ARRAY[]::BYTEA[]”和以下 .addScalar("ancestors")

Session session = getSessionFactory().openSession();
session.beginTransaction();

String sql_query = 
                  " WITH RECURSIVE tree AS ("
                + " SELECT id, ARRAY[]::BYTEA[] AS ancestors"
                + " FROM node "

                + " UNION ALL"

                + " SELECT node.id, tree.ancestors || node.parent_id"
                + " FROM node, tree" 
                + " WHERE node.parent_id = tree.id"

                + " ) SELECT * FROM tree WHERE decode(:Argument1,  'hex') = ANY(tree.ancestors)"
                ;

Query<Object[]> query = session.createNativeQuery(sql_query)
        .addScalar("id", org.hibernate.type.StandardBasicTypes.BINARY.INSTANCE)
        .addScalar("ancestors")
        ;



String nodeIdHex = UUIDOperations.uuidToHex(node.getID() );
query.setParameter("Argument1", nodeIdHex, org.hibernate.type.StringType.INSTANCE);

List<Object[]> objectCollection = (List<Object[]>) query.list();


session.getTransaction().commit();
session.close();

return objectCollection;

当我执行此操作时，出现以下错误：

Hibernate:  WITH RECURSIVE tree AS ( SELECT id, ARRAY[]:BYTEA[] AS ancestors FROM node  UNION ALL SELECT node.id, tree.ancestors || node.parent_id FROM node, tree WHERE node.parent_id = tree.id ) SELECT * FROM tree decode(?,  'hex') = ANY(tree.ancestors)
2018-12-11 09:11:38.200  WARN pc --- [           main] o.h.e.j.s.SqlExceptionHelper             : SQL Error: 0, SQLState: 42601
2018-12-11 09:11:38.202 ERROR pc --- [           main] o.h.e.j.s.SqlExceptionHelper             : ERROR: syntax error at or near ":"
  Position: 45



javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1514)
    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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy238.findAllSubNodesRecursively(Unknown Source)
....
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
....
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
....

at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505)
    ... 58 more
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ":"
  Position: 45
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)


org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:230)
    at org.springframework.orm.hibernate5.HibernateExceptionTranslator.convertHibernateAccessException(HibernateExceptionTranslator.java:102)
    at org.springframework.orm.hibernate5.HibernateExceptionTranslator.translateExceptionIfPossible(HibernateExceptionTranslator.java:77)
....
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    ... 49 more
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ":"
  Position: 45
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
    ... 72 more

我怀疑问题出在“ARRAY[]::BYTEA[]”映射中，但我只是不知道如何正确映射它。

我试图创建一个特殊的用户类型来帮助我映射它，但在这种情况下它似乎对我没有帮助，或者我以错误的方式实现它。

Type byteArrayType = new CustomType(new ByteaArrayUserType());
        Query<Object[]> query = session.createNativeQuery(sql_query)
                .addScalar("id", org.hibernate.type.StandardBasicTypes.BINARY.INSTANCE)
                .addScalar("ancestors", byteArrayType)
                ;

这是下面的映射类：

import java.io.Serializable;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;

public class ByteaArrayUserType implements UserType {



    protected static final int[] SQL_TYPES = { Types.ARRAY };



    @Override
    public Object assemble(Serializable cached, Object owner) throws HibernateException {
        return this.deepCopy(cached);
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        return value;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (byte[][]) this.deepCopy(value);
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {

        if (x == null) {
            return y == null;
        }
        return x.equals(y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return x.hashCode();
    }

    @Override
    public boolean isMutable() {
        return true;
    }



    @Override
    public Object replace(Object original, Object target, Object owner) throws HibernateException {
        return original;
    }

    @Override
    public Class<byte[][]> returnedClass() {
        return byte[][].class;
    }

    @Override
    public int[] sqlTypes() {
        return new int[] { Types.ARRAY };
    }

    @Override
    public Object nullSafeGet(
            ResultSet resultSet, 
            String[] names, 
            SharedSessionContractImplementor session, Object owner
            )throws HibernateException, SQLException {
        if (resultSet.wasNull()) {
            return null;
        }
        if (resultSet.getArray(names[0]) == null) {
            return new byte[0];
        }

        Array array = resultSet.getArray(names[0]);
        byte[][] javaArray = (byte[][]) array.getArray();
        return javaArray;
    }

    @Override
    public void nullSafeSet(PreparedStatement statement, 
            Object value, int index, 
            SharedSessionContractImplementor session
            )throws HibernateException, SQLException {

        Connection connection = statement.getConnection();
        if (value == null) {
            statement.setNull(index, SQL_TYPES[0]);
        } else {
            byte[][] castObject = (byte[][]) value;
            Array array = connection.createArrayOf("byte", castObject);
            statement.setArray(index, array);
        }


    }

}

我认为这与 ARRAY[]::BYTEA 有关的原因是因为如果我将查询简化为简单的东西：

          SELECT id, ARRAY[]::BYTEA[] AS ancestors
          FROM node 

我仍然得到同样的错误，但是这次它说

[           main] o.h.e.j.s.SqlExceptionHelper             : SQL Error: 0, SQLState: 42601
[           main] o.h.e.j.s.SqlExceptionHelper             : ERROR: syntax error at or near ":"
  Position: 20
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

那么那个“：”冒号有什么问题。这次只有位置不同，即 20 而不是上一个异常的 45

标签： postgresqlhibernatehibernate-mappingcommon-table-expression