Distinct on specific column using JPA Specification

Question

I'm trying to write the following query using JPA Specification. select distinct name from hcp where area = 'Dhaka';

select distinct name from hcp where area = 'Dhaka';

The hcp entity looks as following

@Entity
public class HCP implements Serializable {

    @Id
    @Column
    private String id;

    @Column
    private String name;

    @Column
    private String area;
}

The table would look like this

I tried to convert the above query using jpa-specification as following. It's selecting 'name' field and I've set distinct as true

List<HCP> result = hcpRepository.findAll(new Specification<HCP>() {
            @Override
            public Predicate toPredicate(Root<HCP> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                query.select(root.get("name")).distinct(true);
                return criteriaBuilder.equal(root.get("area"), "Dhaka");
            }
        });

But it's not working. It's only applying district on the field that has @Id annotation. I want to apply distinct on area field.

Generated Hibernate Query looks like this:

select
        distinct hcp0_.id as id1_0_,
        hcp0_.area as area2_0_,
        hcp0_.name as name3_0_ 
    from
        hcp hcp0_ 
    where
        hcp0_.area=?

How can I get generated query like the following?

select
        distinct hcp0_.name as name3_0_
        from
            hcp hcp0_
        where
            hcp0_.area= 'Dhaka'

The desired result will list distinct names based on area. How can I apply distinct on a specific field using JPA Specification?

Desired query and output:

But you want HCP as a result. So you cannot use distinct like this. What should be the result of your query? — Simon Martinelli
@SimonMartinelli Hello, Thank you for pointing it out. I've updated the question with desired output and more information. Hope it'll clarify your question. — Hafizur Rahman Arfin

Simon Martinelli Simon Martinelli · Accepted Answer · 2021-08-09T13:59:05

You can't use Specifications because you want to return a List of Strings.

So you could use JPQL

@Query("select distinct h.name from Hcp h where area = 'Dhaka'")
List<String> findDistinctName();

Distinct on specific column using JPA Specification

1 Answers