Spring Data JPA Projection with select distinct

Question

I have a database table which holds Metadata for documents. My task now is to get a list with documenttypes. The documenttypes are not unique in the database table but of course I want them to be in my list. The sql is very simple:

SELECT DISTINCT groupname, group_displayorder
FROM t_doc_metadata
ORDER BY group_displayorder;

I have learned that I can use projections to get a subset of fields from my entity DocMetadata. I solved this as follows. My Entity:

@Entity
@Table(name="T_DOC_METADATA")
@Data
public class DocMetadata {
..............

    @Column(nullable=false)
    private String displayname;

    @Column(nullable=false)
    private Integer displayorder;

    @Column(nullable=false)
    private String groupname;

    @Column(name="GROUP_DISPLAYORDER",
        nullable=false)
    private Integer groupDisplayorder;

    @Column(name="METADATA_CHANGED_TS",
        nullable=false,
        columnDefinition="char")
    private String metadataChangedTimestamp;
..........
}

My inteface for projection:

public interface GroupnameAndOrder {

    String getGroupname();
    Integer getGroupDisplayorder();
    void setGroupname(String name);
    void setGroupDisplayorder(int order);
}

Now I thought I'd be extraordinary clever by adding these lines to my repository:

@Query("select distinct d.groupname, d.groupDisplayorder from DocMetadata d order by d.groupDisplayorder")  
public List<GroupnameAndOrder> findSortedGroupnames();

Sadly, when iterating over the result list and calling getGroupname() the result is null.

So I changed the lines in my repository according to the documentation:

public List<GroupnameAndOrder> findBy();

Now I get the groupnames but of course they are not unique now. So it doesn't solve my problem.

Is there any way to receive a ordered list with unique groupnames?

Answer 1

You are trying to be too clever. Instead just write the proper find method and return the GroupnameAndOrder. Spring Data JPA will then only retrieve what is needed for the projection.

Something like this should do the trick.

List<GroupnameAndOrder> findDistinctByOrderByGroupDisplayorder();