Hibernate is a very powerful ORM. Its been around for a while, but sometimes the magic under the hood can leave you for hours scratching your head trying to figure out why a perfectly normal sql query isn't mapping correctly to your POJO.
Consider the following query.
@Query(nativeQuery = false, value = """ select t.abn, t.accountNumber, count(1) transactionCount, sum(t.balance) totalAmount from AccountTransaction t where t.reportDate = :reportDate group by t.abn, t.accountNumber """ ) fun findTotalAmountByDate( @Param("reportDate") reportDate: LocalDate ): List<AccountTransactionEntity>The Entity that should be mapped to the results is as follows.
@Entity data class AccountTransactionEntity( val abn: String, val accountNumber: String, val transactionCount: Int, val totalAmount: BigDecimal )At first glance it seems to be perfectly fine. However looking at the logs, the following error occurs.
org.springframework.core.convert.ConversionFailedException: Failed to convert from type [java.lang.Object[]] to type [AccountTransactionEntity] for value '{67220345566, 200300809, 8, -2459.25}'; nested exception is org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [String] to type [AccountTransactionEntity]
The reason for this is that it doesn't understand how to bind the query result set implicitly with a strongly typed object using inference. You'll need to help hibernate a little by making an explicit instantiation within the non-native query as follows.
@Query(nativeQuery = false, value = """
select new AccountTransactionEntity(
t.abn,
t.accountNumber,
count(1) transactionCount,
sum(t.balance) totalAmount
) from AccountTransaction t
where t.reportDate = :reportDate
group by t.abn, t.accountNumber
"""
)
fun findTotalAmountByDate(
@Param("reportDate") reportDate: LocalDate
): List<AccountTransactionEntity>
That will compile fine and won't need any further transformation downstream.