带有本机查询和数据投影的Spring JPA，将错误的列映射到投影的接口 [英] Spring JPA with native query and data projection mapping the wrong columns into the projected interface

问题描述

我有一个奇怪的问题，我不知道为什么会发生.我确定我做错了，因为这是我第一次使用数据投影，而使用DTO从未遇到过此类问题.

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.

几乎我有一个SELECT政治家，正在返回各种数据类型的某些列.我有一个接口要传递给JPA存储库，以便它可以进行接口映射.但是，它不是根据列名(例如'accountnum'-> getAccountnumber())映射结果，而是按字母顺序映射列.因此，如果"date_of_order"是SELECT语句中的第一个，则其值将由getAccountnumber()返回.

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);
}

当我调用此方法时，查询将返回正确的行.但是，它不是使用列名进行映射(例如，date_of_order到getDateOfOrder())，而是基于SELECT语句中的列顺序映射到接口中按字母顺序排序的方法.

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.

所以:

SELECT date_of_order, accountnumber, tracking_id

结果:

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

它将始终以这种方式返回，因此这不是暂时性的问题.

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

作为临时的解决方法，我对SELECT语句中的列进行了重新排序.但是我宁愿不必这样做，因为这就像遍历结果集并依赖列位置一样，这只是让我抽搐而已..

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....

如何使Spring JPA从结果集中映射到我的界面?我是否需要在投影接口的方法上添加注释，以告诉Spring它指的是什么列名?

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?

^{我的数据库是Postgres.我正在使用Spring 5.0.2.RELEASE和Spring-Boot 2.0.0.M7.如果需要，我可以将其中任何一个调整为较新的版本，但没有较旧的版本.我正在使用C3P0 0.9.5.2进行连接缓冲，并使用postgres-9.2-1002.jdbc4.这个版本的Spring-Boot引入了我所有的其他依赖项(休眠等).}

推荐答案

我遇到了同样的问题，我通过按字母顺序排列查询列来解决了这个问题.

I had the same problem and i solved by odering the query columns alphabetically.

在这种情况下:

public interface OrderTrackingRepository extends JpaRepository<Account, UUID> {
  @Query( nativeQuery = true,
          value = "SELECT a.accountnumber, o.date_of_order, 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);
}

所以您会得到:

getAccountNumber()->帐号 getDateOfOrder()-> date_of_order getTrackingId()-> tracking_id

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

这篇关于带有本机查询和数据投影的Spring JPA，将错误的列映射到投影的接口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！