How to correctly externalise native SQL query with Spring Data JPA

Question

I'm trying to get rid of my jpa-named-queries.properties as I find annoying to have to explicitely state the line breaks.

Therefore I'd like to externalise my native SQL query, but I can't manage to do it and need help.

GenericRecordRepository.java

public interface GenericRecordRepository extends JpaRepository<GenericRecord, Long> {

    ...

    @Query(nativeQuery = true)
    List<TransactionView> getTransactionListIncludingNull();

    ...

}

orm.xml

<named-query name="GenericRecord.getTransactionListIncludingNull">
    <query>
        SELECT ALL_DATES_CATEGORIES_MATRIX.CATEGORY, ALL_DATES_CATEGORIES_MATRIX.DATE, EXPENSES_BY_CATEGORY.AMOUNT
        FROM
        (
        -- Transaction list with expenses in € grouped by month and category
        SELECT CATEGORY, DATE_FORMAT(CAST(local_date as DATE), '%m-%Y') AS DATE, SUM(AMOUNT) AS AMOUNT
        FROM GENERIC_RECORD
        GROUP BY CATEGORY, DATE
        ) AS EXPENSES_BY_CATEGORY
        RIGHT JOIN
        (
        -- Categories and dates matrix
        SELECT *
        FROM
        (
        -- Dates where there was at least one expense
        SELECT DISTINCT DATE_FORMAT(CAST(local_date as DATE), '%m-%Y') AS DATE
        FROM GENERIC_RECORD
        ) AS DISTINCT_DATES
        CROSS JOIN
        (
        -- All categories
        SELECT DISTINCT CATEGORY
        FROM GENERIC_RECORD
        ) AS DISTINCT_CATEGORIES
        ) AS ALL_DATES_CATEGORIES_MATRIX
        ON
        EXPENSES_BY_CATEGORY.DATE = ALL_DATES_CATEGORIES_MATRIX.DATE AND
        EXPENSES_BY_CATEGORY.CATEGORY = ALL_DATES_CATEGORIES_MATRIX.CATEGORY
        ORDER BY CATEGORY, DATE ASC
    </query>
</named-query>

I get this error tho org.springframework.data.mapping.PropertyReferenceException: No property getTransactionListIncluding found for type GenericRecord!

I also tried to change the name of the method as it seems that the "Null" part in the name is cut off, but it makes no difference

Answer 1

You should fix few moments:

• file orm.xml should be located in META-INF folder, you can read about that by link https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.named-queries
• change the content of that file, content should start from entity-mappings tag:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd"
                 version="2.1">
                 ...
</entity-mappings>

• check type of query: named-query tag is for HQL, named-native-query tag is for native sql, for second one be should provided result-class attribute:

Examples:

For HQL query:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd"
                 version="2.1">
    <named-query name="Customer.bauerUsers">
        <query>
            <![CDATA[
            select u from Customer u where u.lastName = 'Bauer'
            ]]>
        </query>
    </named-query>
</entity-mappings>

For native query:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd"
                 version="2.1">
    <named-native-query name="Customer.bauerUsers" result-class="com.datajpa.entity.Customer">
        <query>
            <![CDATA[
            SELECT * FROM CUSTOMER WHERE LAST_NAME = 'Bauer'
            ]]>
        </query>
    </named-native-query>
</entity-mappings>

• The name of the repository method should be the same as in orm.xml, @Query annotation is not necessary:

public interface CustomerRepository extends CrudRepository<Customer, Long> {

    List<Customer> bauerUsers();
}

Your working version should be as follows:

---

public interface GenericRecordRepository extends JpaRepository<GenericRecord, Long> {

    List<TransactionView> getTransactionListIncludingNull();

}

File orm.xml:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd"
                 version="2.1">
    <named-native-query name="GenericRecord.getTransactionListIncludingNull" result-class="<path_to_your_class>.TransactionView">
        <query>
            <![CDATA[
            SELECT ALL_DATES_CATEGORIES_MATRIX.CATEGORY, ALL_DATES_CATEGORIES_MATRIX.DATE, EXPENSES_BY_CATEGORY.AMOUNT
        FROM
        (
        -- Transaction list with expenses in € grouped by month and category
        SELECT CATEGORY, DATE_FORMAT(CAST(local_date as DATE), '%m-%Y') AS DATE, SUM(AMOUNT) AS AMOUNT
        FROM GENERIC_RECORD
        GROUP BY CATEGORY, DATE
        ) AS EXPENSES_BY_CATEGORY
        RIGHT JOIN
        (
        -- Categories and dates matrix
        SELECT *
        FROM
        (
        -- Dates where there was at least one expense
        SELECT DISTINCT DATE_FORMAT(CAST(local_date as DATE), '%m-%Y') AS DATE
        FROM GENERIC_RECORD
        ) AS DISTINCT_DATES
        CROSS JOIN
        (
        -- All categories
        SELECT DISTINCT CATEGORY
        FROM GENERIC_RECORD
        ) AS DISTINCT_CATEGORIES
        ) AS ALL_DATES_CATEGORIES_MATRIX
        ON
        EXPENSES_BY_CATEGORY.DATE = ALL_DATES_CATEGORIES_MATRIX.DATE AND
        EXPENSES_BY_CATEGORY.CATEGORY = ALL_DATES_CATEGORIES_MATRIX.CATEGORY
        ORDER BY CATEGORY, DATE ASC
            ]]>
        </query>
    </named-native-query>
</entity-mappings>

Answer 2

You didn't use standard Spring Data methods such as findById or deleteByName in your JpaRepository. (Which in these cases, id and name must be a property in your GenericRecord type.)

In your case, getTransactionListIncludingNull() method, is a custom method that you defined in your JpaRepository. To use custom methods in JpaRepository, you should define a new interface and implementation of it. I think you didn't consider this point.

There are so many articles about it that can help you. (e.g., https://www.baeldung.com/spring-data-jpa-method-in-all-repositories, Add Custom Method to JPARepository with Generics)