java - 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 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)