IDisposable Thoughts

A few days at work we were in the middle of a refactoring of a sort of big code base, we are using Spring Boot 2.0 and Hibernate/JPA for database access. Because the database we connect to is a little complicated (don’t ask why), to speed up and simplify some queries, instead of using JPQL or JPA generated methods we decided to go and write the native SQL.

Currently, our JPA Repositories look something like this (of course this is a fictional domain):

interface FruitRepository: JpaRepository<Fruit, Long> {
    @Query("SELECT f.id, CONCAT('delicious ', f.name), f.color FROM fruit WHERE f.name = :name", 
        nativeQuery = true)
    fun getDeliciousFruit(name: String): Fruit
}

There is nothing wrong with this query, it works, but just for a moment imagine such query with 10 or 15 lines length! immediately your eyes will roll and see a lot of noise around the method (I personally don’t like mixing both, especially when the native SQL is really big), immediately the following question was raised:

Can we place the native SQL outside in a resource file or something like that?

The answer, of course, is yes, and it is very easy if you are using Spring Boot and JPA!

The first step is create a new file named orm.xml in your resources/META-INF directory (the full path would be resources/META-INF/orml.xml), this is very important. Inside this file we place the native query in the following format (notice the CDATA to allow line breaks and long queries):

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings
        xmlns="http://java.sun.com/xml/ns/persistence/orm"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"
        version="2.0">
    <named-native-query name="Fruit.getDeliciousFruit" result-class="com.example.Fruit">
        <query>
            <![CDATA[
                SELECT f.id, CONCAT('delicious ', f.name), f.color
                FROM FRUIT
                WHERE f.name = :name
            ]]>
        </query>
    </named-native-query>
</entity-mappings>

Now you should change your JPA repository to something thinner:

// NOTE: native queries in orm.xml
interface FruitRepository: JpaRepository<Fruit, Long> {
    @Query(nativeQuery = true)
    fun getDeliciousFruit(name: String): Fruit
}

Voila! it works!

Something important to notice is the name of the query. To allow Spring JPA to automatically detect the query name the format should be Entity.queryMethodName and NOT Repository.queryMethodName and with Entity it refers to the return class specified by the repository method AND the result-class in your orm.xml, in native queries we have to specify the return type explicitly because it is impossible for your JPA engine to know what type will it return.

I am pretty sure this question has been raised before but for some reason my Google abilities were failing or I found a way using a third party library, in my heart I knew there was a way to do it in JPA ;)