Spring Data JPA Projection 从数据库中选择的字段 [英] 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

我注意到一些问题，我有两个问题.

首先假设我有这两个实体:

@Entity公共类人{@Id @GeneratedValue私人长ID；私人字符串名字，姓氏；@OneToOne私人地址地址；}@实体公共类地址{@Id @GeneratedValue私人长ID；私人字符串街，州，国家；}

• 问题 1:

对于以下预测:

interface PersonLimited {字符串 getFirstName();AddressLimited getAddress();}接口地址限制{字符串 getCountry();}

当我运行 findPersonByFirstNameProjectedForLimitedData

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

它完全返回预期:

<代码>{firstName: '荷马',地址: {国家:'美国'}}

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

SELECT person0_.firstName AS col_0_0_,地址1_.id AS id1_13_,地址1_.street AS street2_13_，address1_.state AS state3_13_,address1_.country AS country4_13_发件人 person0_LEFT OUTER JOIN 地址 address1_ON person0_.addressId = address1_.id哪里 person0_.firstName = ?

Person"实体的投影仅选择fistName"，这是 100% 正确的，因为在 PersonLimited 接口中我只定义了getFirstName".

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

生成的查询应该类似于:

SELECT person0_.firstName AS col_0_0_,address1_.country AS country4_13_发件人 person0_LEFT OUTER JOIN地址地址1_ON person0_.addressId = address1_.id哪里 person0_.firstName = ?

所以问题是，为什么它不只为地址实体"选择国家/地区"字段?为什么它需要选择所有字段?是 Spring 的 bug 吗?

• 问题 2:

对于与上面相同的投影，

当我运行 findAllPersonsProjectedForLimitedData

interface PersonRepository extends CrudRepository{@Query("从人 p 中选择 p")列表findAllPersonsProjectedForLimitedData();}

它完全返回预期:

<预><代码>[{firstName: '荷马',地址: {国家:'美国'}},{firstName: 'Maggie',地址: {国家:'美国'}}]

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

SELECT person0_.id AS id1_18_,person0_.firstName AS firstName2_18_,person0_.lastName AS lastName3_18_,person0_.addressid AS company4_18_发件人 person0_选择地址0_.id AS id1_13_0_，地址0_.street AS street2_13_0_，address0_.state AS state3_13_0_,address0_.country AS country4_13_0_发件人地址 address0_哪里地址0_.id =?

这里，Person 和 Address 实体的投影选择了所有错误的字段，它应该只选择firstName"和country".

生成的查询应该类似于:

SELECT person0_.firstName AS firstName2_18_发件人 person0_SELECT address0_.country AS country4_13_0_发件人地址 address0_哪里地址0_.id =?

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

谢谢，

解决方案

如果你想在 Spring Data Projections 中使用注解 @Query 你必须使用字段别名并且你需要确保你的别名与投影字段匹配的项目.以下代码应该适用于问题 1:

interface PersonRepository extends CrudRepository{@Query("选择 p.firstName 作为名字，p.address 作为来自 Person p 的地址，其中 p.firstName = ?1")PersonLimited findPersonByFirstNameProjectedForLimitedData(String firstName);}

您可以使用的另一种替代方法是使用 属性表达式.只要有可能:

interface PersonRepository extends CrudRepository{列表findByFirstName(String firstName);}

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

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

• Question 1:

for the following projections:

interface PersonLimited {  

  String getFirstName(); 

  AddressLimited getAddress();
}

interface AddressLimited {  

  String getCountry(); 
}

when I run findPersonByFirstNameProjectedForLimitedData

interface PersonRepository extends CrudRepository<Person, Long> {

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

it returns exactly what expected :

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

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 = ?  

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

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

The generated query should be something like:

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?

• Question 2:

for the same projection as above,

when I run findAllPersonsProjectedForLimitedData

interface PersonRepository extends CrudRepository<Person, Long> {

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

it returns exactly what expected :

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

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 = ? 

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

The generated query should be something like:

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?

Thanks,

解决方案

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

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 从数据库中选择的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！