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
'{}'::bytea[]. It probably won't help when it comes down to queries with actual data. Also in thecreateArrayOfcall you're usingbyte, when you want an array ofbytea.bytea[]type, but you can't extract it. It throws an exception with the message "not yet implemented".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 readbytea[]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.