Spring Data JPA Projection从DB中选择了字段 [英] Spring Data JPA Projection selected fields from the DB

问题描述

我正在测试Spring Data 1.10.4.RELEASE，遵循Spring Data Docs中的示例 http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections

I was testing Spring Data 1.10.4.RELEASE, following the example in Spring Data Docs http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections

我注意到一些问题，我有2个问题。

and I noticed some issues for which I have 2 questions.

首先让我假设我有这两个实体：

First let's suppose I have these 2 entities:

@Entity
public class Person {

  @Id @GeneratedValue
  private Long id;
  private String firstName, lastName;

  @OneToOne
  private Address address;
}

@Entity
public class Address {

  @Id @GeneratedValue
  private Long id;
  private String street, state, country;
}

• 问题1：


• Question 1:

以下预测：

interface PersonLimited {  

  String getFirstName(); 

  AddressLimited getAddress();
}

interface AddressLimited {  

  String getCountry(); 
}

当我运行 findPersonByFirstNameProjectedForLimitedData

interface PersonRepository extends CrudRepository<Person, Long> {

  @Query("select p from Person p where p.firstName = ?1")
  PersonLimited findPersonByFirstNameProjectedForLimitedData(String firstName);
}

它完全符合预期：

{
    firstName: 'Homer',
    address: {
        country: 'USA'
    }
}

现在，如果我查看生成的SQL，这就是我所拥有的：

now if I look into the generated SQL, this is what I have:

SELECT person0_.firstName      AS col_0_0_, 
       address1_.id            AS id1_13_, 
       address1_.street        AS street2_13_, 
       address1_.state         AS state3_13_, 
       address1_.country       AS country4_13_
FROM   person person0_ 
       LEFT OUTER JOIN address address1_ 
                    ON person0_.addressId = address1_.id 
WHERE  person0_.firstName = ?  

人实体的预测仅选择fistName，这是100％正确，因为在PersonLimited接口中我只定义了getFirstName。

The projection for the "Person" entity is selecting only "fistName", which is 100% correct because in the PersonLimited interface I've only defined "getFirstName".

但对于Address实体，它选择了所有字段，这是错误的，因为在AddressLimited接口中我只定义了getCountry，它应该只选择country。

But for the "Address" entity, it selects all the fields, which is wrong because in the AddressLimited interface I've only defined "getCountry", It should only select "country".

生成的查询应该类似于：

SELECT person0_.firstName      AS col_0_0_, 
       address1_.country       AS country4_13_
FROM   person person0_ 
       LEFT OUTER JOIN address address1_ 
                    ON person0_.addressId = address1_.id 
WHERE  person0_.firstName = ?  

所以问题是，为什么它不选择地址实体的国家/地区字段？为什么需要选择所有字段？这是春天的错误吗？

so the question is, why it is not selecting only the "country" field for the Address "entity"? why it needs to select all the fields? is it a bug in Spring?



• 问题2：


• Question 2:

与上述相同的投影，

当我运行 findAllPersonsProjectedForLimitedData

interface PersonRepository extends CrudRepository<Person, Long> {

  @Query("select p from Person p")
  List<PersonLimited> findAllPersonsProjectedForLimitedData();
}

它完全符合预期：

[
     {
        firstName: 'Homer',
        address: {
            country: 'USA'
        }
     },
     {
        firstName: 'Maggie',
        address: {
            country: 'USA'
        }
     }
]

现在如果我查看生成的SQL，这就是我所拥有的：

now if I look into the generated SQL, this is what I have:

SELECT person0_.id                 AS id1_18_, 
       person0_.firstName          AS firstName2_18_, 
       person0_.lastName           AS lastName3_18_, 
       person0_.addressid          AS company4_18_
FROM   person person0_ 

SELECT address0_.id         AS id1_13_0_, 
       address0_.street     AS street2_13_0_, 
       address0_.state      AS state3_13_0_, 
       address0_.country    AS country4_13_0_
FROM   address address0_ 
WHERE  address0_.id = ? 

这里，Person和Address实体的预测是选择所有错误的字段，它应该只选择firstName和country。

here, the projection for both the Person and the Address entities is selecting all the fields which is wrong, it should only select "firstName" and "country".

生成的查询应该类似于：

SELECT person0_.firstName        AS firstName2_18_
FROM   person person0_ 

SELECT address0_.country    AS country4_13_0_
FROM   address address0_ 
WHERE  address0_.id = ? 

这是正常行为，不应该只选择我们需要的字段吗？

is this the normal behavior, shouldn't select only the fields that we need?

谢谢，

推荐答案

如果你想使用注释 @Query 使用Spring Data Projections，您必须使用字段别名，并且需要确保为与投影字段匹配的项目设置别名。以下代码适用于问题1：

If you want use the annotation @Query with Spring Data Projections you have to use field alias and you need to make sure you alias the projects matching the projection fields. The following code should work for question 1:

interface PersonRepository extends CrudRepository<Person, Long> {

  @Query("select p.firstName as firstname, p.address as address from Person p where p.firstName = ?1")
  PersonLimited findPersonByFirstNameProjectedForLimitedData(String firstName);
}

您可以使用的另一种方法是使用 Property Expressions 。只要可能：

Another alternative that you can use is define your queries with Property Expressions. whenever is possible:

interface PersonRepository extends CrudRepository<Person, Long> {

  List<PersonLimited> findByFirstName(String firstName);
}

这篇关于Spring Data JPA Projection从DB中选择了字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！