在休眠状态下映射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屋!