Hibernate, postgres CTE and native SQL query mapping ARRAY[]::BYTEA[] with addScalar

Question

I am trying to execute the following common table expression as a native SQL query in hibernate. Although this is a CTE starting with "WITH" clause, I have seen other examples where this was working with native SQL. I am executing this query on a table called node with the following 2 field

@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;

This query has been taken from the official PostgreSQL wiki from here
and just modified a little bit i.e. i have removed the restriction

 "WHERE parent_id IS NULL".

If I execute this query in pgAdmin4, then I get a success and I know it works as it should. i.e. I get the expected output that look like this:

Notice the type of the second column called ancestors is "bytea[]" (Not to be confused with byte[]).

It seems that I am not able to map this i.e. i thing that the problem is the mapping of the array of bytea i.e. " ARRAY[]::BYTEA[]" and the following .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;

When i execute this i get the following error :

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

I suspect that the problem is in the " ARRAY[]::BYTEA[]" mapping but i just have no idea how to map that correctly.

I have tried to create a special user type that will help me map that but it does not seem help me in this case or i have implemented it the wrong way.

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

here is the mapping class below:

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);
        }


    }

}

The reason i believe this has to do with the ARRAY[]::BYTEA is because if i simplify the query to something simple as :

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

I still get the same error , however this time it says

[           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

so what is wrong with that ":" colon. This time only the position is different i.e. 20 instead of 45 on the previous exception

Try using the string version of the array type '{}'::bytea[]. It probably won't help when it comes down to queries with actual data. Also in the createArrayOf call you're using byte, when you want an array of bytea. — coladict
– coladict, Commented Dec 13, 2018 at 13:26
@coladict thanks for the feedback. I simplified the query first to "SELECT ARRAY[]::BYTEA[] AS ancestors". Then i tired what you suggested i.e. "SELECT {}::BYTEA[] AS ancestors" but still not luck. I even tried to use using a type which is based on sql descriptors i.e. not "UserType" i.e. see the second question related to this query. Do you know if the double column is reserved in and can be used at all in SQL? The reason i am asking this is because i opened another question regarding the ARRAY keywordi.e. here stackoverflow.com/questions/53747733/… — Tito
– Tito, Commented Dec 13, 2018 at 16:11
I did some experimentation on my own, and it turns out that with the newest Postgres JDBC drivers you can set a column of bytea[] type, but you can't extract it. It throws an exception with the message "not yet implemented". — coladict
– coladict, Commented Dec 13, 2018 at 22:11
@coladict i am afraid you are right. Can you elaborate a little on the test you made. Since i just tested the Integer[] and Long[] with the cast operator that you suggested in the other case and that worked perfectly. Many thanks for that. Currently i am trying to understand if that is a shortcoming of hibernate or anything else that might be involved i.e. maybe the dialect. I.e. we can create array of type: bytea[] but can not extract it . The only thing that i can thing of why this could be the case is length of each bytea object within that array, but this is just a pure guess. — Tito
– Tito, Commented Dec 14, 2018 at 14:00
I have a small, but complex library that allows usage of the most common array types. I simply added byte[][] to the unit test for it, and it crashed not on inserting the data, but on trying to read it. The PostgreSQL JDBC driver doesn't know how to read bytea[] data type yet, and it's probably not a priority. You could implement it in their driver and submit a pull request github.com/pgjdbc/pgjdbc. However a fix for the UUID[] use-case would need changes in all 3 projects. — coladict
– coladict, Commented Dec 14, 2018 at 14:23

Collectives™ on Stack Overflow

Hibernate, postgres CTE and native SQL query mapping ARRAY[]::BYTEA[] with addScalar

0

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

0

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.

Your Answer

Sign up or log in

Post as a guest

Linked