Hibernate JPA mapping using @Query done right

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.