Spring JPA with native query and data projection mapping the wrong columns into the projected interface

Question

I've got a bit of a bizarre problem that I can't figure out why it's happening. I'm sure I did something wrong, because this is my first time using a data projection and I've never had such problems using DTOs.

Pretty much I have a SELECT statemen that is returning certain columns of various data types. And I have an interface that I'm passing to the JPA Repository so it can do the interface mapping. But instead of mapping the results based on the column name (eg. 'accountnum' -> getAccountnumber()), it's mapping the columns in alphabetical order. So if 'date_of_order' is the first in the SELECT statement, its value will be returned by getAccountnumber().

I have a projected interface that looks something like this:

public interface FlatSearchResult {
    String getAccountnumber();
    UUID getTrackingId;
    Date getDateOfOrder;
}

My model has three tables something like this:

ACCOUNT
  - account_id : uuid (pkey)
  - accountnumber : string
ORDERS
  - order_id : uuid (pkey)
  - date_of_order : timestamp
  - account_id : uuid (fkey)
TRACKING
  - tracking_id : uuid (pkey)
  - order_id : uuid (fkey)

There's other columns in each of those tables, but they're not relevant.

I have a repository defined with a simple query:

public interface OrderTrackingRepository extends JpaRepository<Account, UUID> {
  @Query( nativeQuery = true,
          value = "SELECT o.date_of_order, a.accountnumber, t.tracking_id " +
                  "FROM account as a " +
                  "INNER JOIN orders as o USING (account_id) " +
                  "INNER JOIN tracking as t USING (tracking_id) " +
                  "WHERE a.accountnumber = :acctnum")
  <T> Collection<T> findOrderInfoForAccount(@Param("acctnum") acctNumber, Class<T> type);
}

When I call this method, the correct rows are returned by the query. But instead of mapping using the column name (eg. date_of_order to getDateOfOrder()), it is mapping based on the order of the columns in the SELECT statement to the alphabetically-ordered methods in the interface.

So:

SELECT date_of_order, accountnumber, tracking_id

Results in:

getAccountNumber() -> date_of_order
getDateOfOrder() -> accountnumber
getTrackingId() -> tracking_id

It will consistently return in this fashion, so it's not a transient issue.

As a temporary workaround, I've reordered the columns in my SELECT statement. But I would rather not have to do this since it's like iterating through a result set and relying on column position, which just makes me twitchy....

How can I get Spring JPA to map from the result set to my interface? Do I need to annotate my projection interface's methods with something to tell Spring what column name it's referring to?

^{My database is Postgres. I'm using Spring 5.0.2.RELEASE and Spring-Boot 2.0.0.M7. I can adjust either of those to newer versions if needed, but nothing older. I'm using C3P0 0.9.5.2 for my connection pooling, and postgres-9.2-1002.jdbc4. All my other dependencies (hibernate, etc) are what is pulled in by this version of Spring-Boot.}

Answer 1

Not sure if this is the correct solution because it only fits 80% of the description. But it is too long for a comment. So here we go.

I think you misunderstood @osamayaccoub or the documentation. Your property name is fine. But the columns in your select should match the java convention.

So the first attempt to fix that would be

value = "SELECT o.date_of_order as dateOfOrder, a.accountnumber as accountNumber, t.tracking_id as trackingId "

Note: This might actually work, but might break later, so read on, even if it does work

But Postgres converts everything that isn't double quoted into lower case (Oracle and MySql do similar stuff though details vary, don't know about other DBs yet). So you really should use:

value = "SELECT o.date_of_order as \"dateOfOrder\", a.accountnumber as \"accountNumber\", t.tracking_id as \"trackingId\" "

This probably doesn't work, because the Hibernate version you are using has a bug in that it converted everything to lower case.

So you should upgrade to the latest Hibernate version 5.3.13 which has the issue fixed.

This bug fix interestingly might break the version without the double quotes. But it should work again with this PR for this Spring Data JPA issue.

The part I don't understand is, why stuff gets assigned using the column order.