在休眠状态下映射ARRAY [] :: INTEGER [] [英] Mapping ARRAY[]::INTEGER[] in hibernate

问题描述

在postgres中，我们可以使用以下结构创建一个整数数组类型的临时表.例如下面的singleTestColumn.通常在CTE期间使用.

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

如何在休眠本机sql查询中正确映射此列.

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

问题是为什么以下内容不起作用并在hibernate 5.3.7.Final和org.hibernate.dialect.PostgreSQL95Dialect中给出错误.

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

推荐答案

Hibernate将以:开头的内容视为参数，因此您的查询被编译为:

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

SELECT  ARRAY[]:?[] AS singleTestColumn

您需要使用转义序列(我在Hibernate 4.3中使用过此序列):

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

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

或标准SQL强制转换:

or the standard SQL cast:

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

这篇关于在休眠状态下映射ARRAY [] :: INTEGER []的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！