Aurora Mysql 5.7无效长度(4)的类型为LONG

Question

我有一个Spring Boot应用程序，使用Hibernate和AWS Aurora Mysql 5.7版本。

我的存储库中有这个查询:

@Cacheable(cacheNames = "documents#chartprofitbyday")
@Transactional(readOnly = true)
@Query("SELECT new org.javatuples.Quartet(COALESCE(SUM(d.grossProfit),0),COALESCE(CAST(AVG(d.grossMargin) as big_decimal),0),MONTH(d.date),DAY(d.date)) FROM Document d WHERE d.type IN (:types) AND d.status<>'CANCELED' AND d.status<>'DECLINED' AND d.store.id=:storeId AND d.date>=:from AND d.date<=:until GROUP BY MONTH(d.date),DAY(d.date) ORDER BY MONTH(d.date),DAY(d.date)")
List<Quartet<BigDecimal, BigDecimal, Integer, Integer>> chartProfitByDay(@Param("types") List<DocumentType> types, @Param("from") LocalDate from, @Param("until") LocalDate until, @Param("storeId") Long storeId);

有时(不是所有时候)，我有这样的例外:

Invalid length (4) for type LONG
at com.mysql.cj.protocol.a.MysqlBinaryValueDecoder.decodeInt8(MysqlBinaryValueDecoder.java:220)
at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:113)
at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:243)
at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91)
at com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1316)
at com.mysql.cj.jdbc.result.ResultSetImpl.getInt(ResultSetImpl.java:822)
at com.mysql.cj.jdbc.result.ResultSetImpl.getInt(ResultSetImpl.java:843)
at com.zaxxer.hikari.pool.HikariProxyResultSet.getInt(HikariProxyResultSet.java)
at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:62)
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:243)
at org.hibernate.loader.hql.QueryLoader.getResultRow(QueryLoader.java:457)
at org.hibernate.loader.hql.QueryLoader.getResultColumnOrRow(QueryLoader.java:440)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:770)
at org.hibernate.loader.Loader.getRowsFromResultSet(Loader.java:1039)
at org.hibernate.loader.Loader.processResultSet(Loader.java:990)
at org.hibernate.loader.Loader.doQuery(Loader.java:959)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
at org.hibernate.loader.Loader.doList(Loader.java:2850)
at org.hibernate.loader.Loader.doList(Loader.java:2832)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2664)
at org.hibernate.loader.Loader.list(Loader.java:2659)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1414)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1625)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1593)
at org.hibernate.query.Query.getResultList(Query.java:165)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:126)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)

我不知道在我的查询中哪里有long,grossProfit和grossMargin在我的DB中被声明为DECIMAL(12,2)。

你对如何理解和解决这个问题有什么建议吗?

Answer 1

YourstoreId是龙的一种。
@Param("storeId") Long storeId

List<Quartet<BigDecimal, BigDecimal, Integer, Integer>> chartProfitByDay(@Param("types") List<DocumentType> types, @Param("from") LocalDate from, @Param("until") LocalDate until, @Param("storeId") Long storeId);

我认为这个错误是由于您的storeId是Long类型，而您提供的是Integer。

List<Quartet<BigDecimal, BigDecimal, Integer, Long>>