org.postgresql.util.PSQLException:具有JPA的BigDecimal:｛1｝类型的值不正

Question

当我尝试使用CeateNativeQuery:启动查询时

String q = "select status.code as status, array_agg(reference.id) as ref  from reference_history
inner join reference on reference.id = reference_history.reference_id
inner join process_info on reference_history.process_info_id = process_info.id
inner join status  on process_info.status_id = status.id
where reference.code = 'ref123456'
and status.code in ('INPROGRESS', 'RESOLVED')
and start_date <= '2020-12-06'
and (end_date >= '2020-12-03' or end_date is null)
group by status";

List<Object[]> result = query.unwrap(org.hibernate.query.NativeQuery.class)
.addScalar("ref", StandardBasicTypes.BIG_INTEGER)
.getResultList();

我得到了这个错误：

org.postgresql.util.PSQLException: Bad value for type BigDecimal : {1}
at org.postgresql.jdbc.PgResultSet.toBigDecimal(PgResultSet.java:2910)
at org.postgresql.jdbc.PgResultSet.toBigDecimal(PgResultSet.java:2919)
at org.postgresql.jdbc.PgResultSet.getNumeric(PgResultSet.java:2378)
at org.postgresql.jdbc.PgResultSet.getBigDecimal(PgResultSet.java:2332)
at org.postgresql.jdbc.PgResultSet.getBigDecimal(PgResultSet.java:390)
at org.postgresql.jdbc.PgResultSet.getBigDecimal(PgResultSet.java:394)
at com.zaxxer.hikari.pool.HikariProxyResultSet.getBigDecimal(HikariProxyResultSet.java)
at org.hibernate.type.descriptor.sql.DecimalTypeDescriptor$2.doExtract(DecimalTypeDescriptor.java:63)
at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:249)
at org.hibernate.loader.custom.ScalarResultColumnProcessor.extract(ScalarResultColumnProcessor.java:54)
at org.hibernate.loader.custom.ResultRowProcessor.buildResultRow(ResultRowProcessor.java:83)
at org.hibernate.loader.custom.ResultRowProcessor.buildResultRow(ResultRowProcessor.java:60)
at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:412)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:775)
at org.hibernate.loader.Loader.getRowsFromResultSet(Loader.java:1044)
at org.hibernate.loader.Loader.processResultSet(Loader.java:995)
at org.hibernate.loader.Loader.doQuery(Loader.java:964)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:350)
at org.hibernate.loader.Loader.doList(Loader.java:2887)
at org.hibernate.loader.Loader.doList(Loader.java:2869)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2701)
at org.hibernate.loader.Loader.list(Loader.java:2696)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2142)
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1163)
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:173)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1533)
at org.hibernate.query.Query.getResultList(Query.java:165)

我该怎么解决？

Answer 1

因此，当PostgreSQLARRAY_AGG函数返回数组时，不能简单地将其强制转换为BigDecimal。要做到这一点，您应该编写自己的hibernate自定义基本类型，或者您可以使用hibernate类型库。

例如，您可以添加以下依赖项：

<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-52</artifactId>
<version>2.10.1</version>
</dependency>

如果您使用hibernate 5.4、5.3或5.2，然后以以下方式编写查询：

import org.hibernate.type.StringType;
import com.vladmihalcea.hibernate.type.array.LongArrayType;
List<Object[]> result = query.unwrap(org.hibernate.query.NativeQuery.class)
.addScalar("status", StringType.INSTANCE)
.addScalar("ref", LongArrayType.INSTANCE)
.getResultList();

如果您决定编写自己的hibernate类型，这个问题可能会很有用。