Spring Data Projection with OneToMany returns too many results

Question

I have a JPA entity (Person) with onetomany relation (ContactInfo).

@Entity
public class Person {
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
    private String lastname;
    private String sshKey;
    @OneToMany(mappedBy = "personId")
    private List<ContactInfo> contactInfoList;
}

@Entity
public class ContactInfo {
    @Id
    @GeneratedValue
    private Integer id;
    private Integer personId;
    private String description;
}

I've defined a projection interface that includes this onetomany relation as described here.

public interface PersonProjection {
    Integer getId();
    String getName();
    String getLastname();
    List<ContactInfo> getContactInfoList();
}

public interface PersonRepository extends JpaRepository<Person,Integer> {
    List<PersonProjection> findAllProjectedBy();
}

When I retrieve the data with findAllProjectedBy the result contains too many rows. It looks like the returned data is the result of a join query similar to:

select p.id, p.name, p.lastname, ci.id, ci.person_id, ci.description 
from person p 
join contact_info ci on ci.person_id = p.id

For example for this data set:

insert into person (id,name,lastname,ssh_key) values (1,'John','Wayne','SSH:KEY');

insert into contact_info (id, person_id, description) values (1,1,'[phone]'), (2,1,'[email protected]');

The findAllProjectedBy method returns 2 objects (incorrectly) and the standard findAll returns 1 object (correctly).

Full project is here

I've done some debugging and it seems that the problem is with the jpa query. The findAll method uses this query:

select generatedAlias0 from Person as generatedAlias0

The findAllProjectedBy uses this query:

select contactInfoList, generatedAlias0.id, generatedAlias0.name, generatedAlias0.lastname from Person as generatedAlias0 
left join generatedAlias0.contactInfoList as contactInfoList

Does anyone know how to fix this invalid behaviour?

Penury

It's normal behavior. What do you exactly want to achieve with projection? Maybe you just need to load a list of Persons contained ContactInfos?.. – Dupe

'Normal behaviour' - is documented anywhere? The general use case I'm implementing is an api with view parameter. View can be either full (findAll) or limited (findAllProjectedBy). I want to be able to limit the data retrieved from DB. The example above is very simplified. – Penury

Looks like a bug/missing feature to me. Can you open an issue? jira.spring.io/browse/DATAJPA/… – Electrophilic

Reported in Spring jira: jira.spring.io/browse/DATAJPA-1173 – Penury

I am using Spring Boot v2.0.0 and I will I am getting whole object as such.. not just those fields written in Projection – Spider

Answer 1

A quick fix for this problem is described here: https://jira.spring.io/browse/DATAJPA-1173

You need to describe one of the single projection attributes with a @Value annotation. For the example posted above you will end up with:

import java.util.List;
import org.springframework.beans.factory.annotation.Value;

public interface PersonProjection {
    @Value("#{target.id}")
    Integer getId();
    String getName();
    String getLastname();
    List<ContactInfo> getContactInfoList();
}

Penury

This annotation makes this projection open rather than closed. They fixed this bug by assuming a projection to be open by default in case one of the methods returns a collection or a map. – Margitmargo

It's not working when you are using Dto object instand of interface! I'm getting Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting OPEN, found ',' near line 1, column 157 – Arable

@Margitmargo Good point. A consequence of this is that when returning a subset of entities column in a projection hibernate selects all the columns. – Penury

@Arable I believe you're supposed to use interfaces instead of dto pojos for projections. Anyways I've found Spring Data really usefull for prototyping and rnd but as the scenarios get more complex the lib shows it's limitations (performance, dynamic queries with variable params/result cols). IMO this is a good alternative worth checking out: link. – Penury

@Penury therr is also graphql that even lets to define such projections at runtime and the sql actually only selects what needed. Also, for complex compile-time view of an entity one should create a dto class and use @Query using the dto's fully qualified constructor as the select argument. This feature is on the jpa level. Google best way to map dto hibernate and will see Vlad's post – Margitmargo

@Ibd01 - Can u pls guide here: #68589297? – Spider