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.

Importing Trusted Certificate to all your JVM cacerts

Sometimes when you work in a corporate company there are proxies that require certificates to validate Java applications. Usually your company will provide a valid certificate that will validate any requests coming in and going out from your local machine. In order to use your Java applications successfully with your corporation's certificate you need to import it using keytool to the cacerts file for your installed JVM. 

The problem is however you may have multiple JVMs installed and the location may differ from machine to machine. The following bash script locates all the cacerts on your machine and adds the appropriate certificate to them.

#!/usr/bin/env sh
 
PROXY_CERT="${HOME}/Your_Company_Proxy_CA.cer"
KEYSTORE_ALIAS="proxy-root"

echo "Finding cacerts..."
KEYSTORES=$(find / -name cacerts -type f -print 2>/dev/null)
 
while IFS= read -r KEYSTORE
do
  echo "Finding alias ${KEYSTORE_ALIAS} from JDK Keystore ${KEYSTORE}"
  sudo keytool -list -alias ${KEYSTORE_ALIAS} -keystore "${KEYSTORE}" -storepass changeit -v && {
    echo "Deleting alias ${KEYSTORE_ALIAS} from JDK Keystore ${KEYSTORE}"
    sudo keytool -delete -alias ${KEYSTORE_ALIAS} -storepass changeit -noprompt -keystore "${KEYSTORE}"
  } || echo "Adding cert to JDK Keystore ${KEYSTORE}"
  
  sudo keytool -import -trustcacerts -storepass changeit -noprompt -alias ${KEYSTORE_ALIAS} -keystore "${KEYSTORE}" -file "${PROXY_CERT}"
done <<< "${KEYSTORES}"

Presenting about Quarkus at the Java Meet Up

Quarkus is a framework designed for Kubernetes to improve the startup time and memory footprint of Java apps running in docker containers