Mapping ARRAY[]::INTEGER[] in hibernate

Question

in postgres we can use the following construct to create a temporary table of type array of integers. For example the singleTestColumn below. Usually this is used during CTE.

String sql_query = "SELECT  ARRAY[]::INTEGER[] AS singleTestColumn";

How to properly map this column in hibernate native sql query.

taking into account the custom integer array mapping described here https://vladmihalcea.com/how-to-map-java-and-sql-arrays-with-jpa-and-hibernate/#comment-26149 i.e. by that i mean the IntArrayType class.

        Query<Object[]> query = session.createNativeQuery(sql_query)
                .addScalar("singleTestColumn", com.vladmihalcea.hibernate.type.array.IntArrayType.INSTANCE)
                ;


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

the question is why is the following not working and giving the error in hibernate 5.3.7.Final and org.hibernate.dialect.PostgreSQL95Dialect.

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

Using Postgres' custom typecast will confuse Hibernate, because it thinks of that as a parameter. You can try cast (array[] as integer[]) or array[]\\:\\:integer[]. — coladict
– coladict, Commented Dec 13, 2018 at 22:15
@coladict you are completely right. The cast operator works with this constellation i.e. "cast (array[] as integer[])" perfectly. Please put that as an answer i will accept it. — Tito
– Tito, Commented Dec 14, 2018 at 13:49

coladict · Accepted Answer · 2018-12-14 13:53:32Z

2

Hibernate considers things starting with : as parameters, so your query is compiled to:

SELECT  ARRAY[]:?[] AS singleTestColumn

You need to either use an escape sequence (I used this in Hibernate 4.3):

String sql_query = "SELECT  ARRAY[]\\:\\:integer[] AS singleTestColumn";

or the standard SQL cast:

String sql_query = "SELECT  CAST(ARRAY[] AS integer[]) AS singleTestColumn";

answered Dec 14, 2018 at 13:53

coladict

5,1151 gold badge20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mapping ARRAY[]::INTEGER[] in hibernate

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related