org.postgresql.util.psqlexception:带有jpa的bigdecimal:{1}类型的值不正确

当我尝试使用 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)

我该怎么修？

所以，作为postgresql ARRAY_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类型，这个问题可能会很有用。

org.postgresql.util.psqlexception:带有jpa的bigdecimal:{1}类型的值不正确

1条答案

相关问题

热门标签

最新问答