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.
