Fetch all children as a list with Spring Data JPA using projection

Question

I want to use projections to fetch only specific fields from my database with Spring Data JPA.

This is my (shortened) data model:

@Entity
@Table(name = "data")
@Data
public class DataEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    private String name;
    private String description;

    @LazyCollection(LazyCollectionOption.FALSE)
    @Fetch(value = FetchMode.SUBSELECT)
    @OneToMany(mappedBy = "data", fetch = FetchType.LAZY)
    @Builder.Default
    private List<OwnerEntity> owners = new ArrayList<>();
}

@Entity
@Table(name = "owner")
@Data
public class OwnerEntity {
    @EmbeddedId
    public OwnerId id = new OwnerId();

    @Fetch(value = FetchMode.JOIN)
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name="userId", insertable = false, updatable = false)
    private UserEntity user;

    @ManyToOne
    @JoinColumn(name="dataId", insertable = false, updatable = false)
    private InterfaceEntity iface;
}

@Embeddable
@Data
public class OwnerId implements Serializable {
    private Integer dataId;
    private String userId;
}

@Entity
@Table(name = "users")
@Data
public class UserEntity {
    @Id
    private String id;
    private String name;
    private String mail;
}

This is my projection:

public interface DataProjection {
    String getName();
    String getDescription();
    List<UserEntity> getOwners();
}

Finally, this is my DAO:

public interface DataDao extends CrudRepository<DataEntity, Integer> {
@Query("select d.name as name, " +
            "   d.description as description, " +
            "   o.user as owners " +
            "from DataEntity d " +
            "left join d.owners o " +
            "order by d.name")
    List<DataProjection> getData();
}

It generally works but it returns one row for each owner resulting in multiple same DataProjections with a list containing only one of the owners.

A similar problem was mentioned in this question but as mentioned in the solutions comments this would make it an open projection loading all columns. Is there a solution other than mapping the resulting rows programmatically?

*it only returns the first owner for each data entry*. No. it returns a list of rows, and each rows have a name, a description, and one of the owners of the DataEntity. If you have 3 owners for a DataEnity, you'll get three rows for this DataEntity, each with one of the three owners. So the DataProjection interface should have a single owner, not a list. It's up to you to combine the related rows together. — JB Nizet, Jan 30 '18 at 07:43
@JBNizet You are right. Haven't seen this, thanks. I'm adapting my question — YingYang, Jan 30 '18 at 07:53
check this answer https://stackoverflow.com/questions/47542914/spring-repository-projection-get-child-of-one-object/47544712#47544712 — Amr Alaa, Jan 30 '18 at 07:55
Another solution would be to query the `OwnerRepository` instead of querying the `DataEntityRepository`. Fetch a list of `Owners` and its mapping `DataEntity`. — Abdullah Khan, Jan 30 '18 at 08:22

Fetch all children as a list with Spring Data JPA using projection

0 Answers0