JPA Projection: select only some items and an entire entity of @OneToMany relationship

Question

I have these two entities:

@Entity
@Table(name = "ORGANIZATION")
public class Organization implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @OneToMany(mappedBy = "organization", fetch = FetchType.LAZY)
    private Set<OrganizationMeta> metas;

    public Organization() {
        super();
    }

    public Organization(Long id, String name, Set<OrganizationMeta> metas) {
        super();
        this.id = id;
        this.name = name;
        this.metas = metas;
    }

    // ... others fields ... getters and setters
}

@Entity
@Table(name = "ORGANIZATION_META")
public class OrganizationMeta implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Enumerated(EnumType.STRING)
    @Column(name = "meta_key", length = 200, nullable = false)
    private OrganizationMetaKeyEnum metaKey;

    private String metaValue;

    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn(nullable = false)
    private Organization organization;

    // ... getters and setters ...
}

So an Organization can have one or more OrganizationMeta, it's a simple situation.

In the ORGANIZATION_META table there is the foreign key, so it is the owner of the relationship.

In the Organization entity I have the set of metas loaded by @OneToMany.

I want to write a query using a projection, because I want only some fields of Organization entity, but at the same time I want the entire OrganizationMeta entity.

This is the JPQL query, but I have always different error

@Query("select new net.feed.feedentity.domain.organization.Organization(organization.id, organization.name, organization.metas) from Organization organization left join fetch organization.metas where organization.id = ?1")

It seem impossible to select in the projection, a set or list of a @OneToMany field.

Is it possible to do this? Anyone encountered this problem ?

Answer 1

I suggest that you receive the data by doing closed projection technique with the Organization entity since what you need is just a subset of Organization class.

In that case; you need to define an a projection interface which will require to include the getter methods of the properties of your interest. That is the rule you have to follow. Names should be matched.

The interface will look like this:

public interface OrganizationView {
    String getId();
    String getName();
    Set<OrganizationMeta> getMetas();
}

You can give any name to your interface, I used OrganizationView.

You will use this interface as the return type. You can define the method in the Organization Repository like in the below.

public interface OrganizationRepository extends JpaRepository<Organization, Long> {

    @Query("select organization.id, organization.name, organization.metas from Organization organization left join fetch organization.metas where organization.id = ?1")
    List<OrganizationView> findSubsetById(Long id);
}

Finally, you will get the data by calling the methods of OrganizationView interface.

Edit: You can have a look at this page about the explanation of projection with samples.