Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring Data JPA Projection selected fields from the DB

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,

like image 672
arammal Avatar asked Oct 22 '16 16:10

arammal


People also ask

What is Spring JPA projection?

Overview. When using Spring Data JPA to implement the persistence layer, the repository typically returns one or more instances of the root class. However, more often than not, we don't need all the properties of the returned objects. In such cases, we might want to retrieve data as objects of customized types.

How do you fetch a one to many DTO projection with JPA and Hibernate?

Fetching a one-to-many DTO projection with JPA and Hibernate. The postDTOMap is where we are going to store all PostDTO entities that, in the end, will be returned by the query execution. The reason we are using the postDTOMap is that the parent rows are duplicated in the SQL query result set for each child record.

What is DTO projection?

You define a DTO projection in a CriteriaQuery in a pretty similar way as you do in JPQL. But instead of using the new keyword to specify the constructor call in a query String, you call the construct method on the CriteriaBuilder with a reference to the DTO class and a List of constructor parameters.


1 Answers

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); } 
like image 125
Andrés Cuadros Avatar answered Oct 04 '22 15:10

Andrés Cuadros