Spring Data JPA Select Distinct

Question

I have a situation where I need to build a select distinct a.address from Person a (where address is a Address entity inside the Person) kind of query.

I am using Specification to dynamically build my where clause and using the findAll(Specification<T>) function to get the result. The problem is I can not use specification to build my select clause and hence can not use findAll(Spcification) function.

What would be the best way to do something like this?

score 10 · Answer 1 · answered Apr 27 '15 at 09:07

As it's the top question in google, I'll post the answer here.

In the specification you have access to query, so you can do

query.distinct(true);

Full example, which results in such SQL emitted:

2015-04-27 12:03:39 EEST [7766-759] postgres@sales LOG: execute : SELECT DISTINCT t1.ID, t1.NAME, t1.WEBNAME, t1.WEBORDER, t1.PVGROUPPARENT_ID, t1.SITE_ID FROM PRODUCTVARIANT t0, PVGROUP t1 WHERE ((t0.PRODUCTTYPE_ID = $1) AND (t0.PVGROUP_ID = t1.ID)) 2015-04-27 12:03:39 EEST [7766-760] postgres@sales DETAIL: parameters: $1 = '4608bdc9-d0f2-4230-82fd-b0f776dc2cfd'

public static Specification<PVGroup> byProductType(final ProductType pt) {
        return (final Root<PVGroup> root, final CriteriaQuery<?> query, final CriteriaBuilder builder) -> {

            query.distinct(true);
            final CollectionJoin<PVGroup, ProductVariant> jPV = root.join(PVGroup_.productVariant);

            final Path<ProductType> ptPath = jPV.get(ProductVariant_.productType);

            return builder.equal(ptPath, pt);
        };
    }
}

score 6 · Answer 2 · answered Jan 06 '15 at 14:35

I came across the same issue, so in case it would help someone, this is what I did:

The Specification is being translated to the where clause, and the findAll(Specification<T>) function is creating its own select clause. So there is no way we can fix this by somehow using the findAll(Specification<T>) function. I already had custom repository which extends SimpleJpaRepository, so I've added a new method:

@Override
    @Transactional(readOnly = true)
    public List<Object> findDistinctValues(Specifications<T> spec, String columnName) {
        return getQuery(spec, columnName).getResultList();
    }

    protected TypedQuery<Object> getQuery(Specification<T> spec, final String distinctColumnName) {

        CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<Object> query = builder.createQuery(Object.class);
        Root<T> root = applySpecificationToCriteria(spec, query);

        if (null != distinctColumnName) {
            query.distinct(true);
            query.multiselect(root.get(distinctColumnName));
        }

        // We order by the distinct column, Asc
        query.orderBy(builder.asc(root.get(distinctColumnName)));

        return em.createQuery(query);
    }

applySpecificationToCriteria is in the SimpleJpaRepository class.

Now you can use the findDistinctValues method.

`applySpecificationToCriteria` is a private method in the SimpleJpaRepository class. You can't use it. — Neeraj Singh, Jul 13 '21 at 13:20
As far as I remember, I copied the method to my custom repository. — yishaiz, Jul 13 '21 at 18:23

score 4 · Answer 3 · edited May 23 '17 at 12:07

4

Good! You can use distinct in JPQL and also for specific column. It is already here. Reference Using DISTINCT in JPA

edited May 23 '17 at 12:07

Community

1
1

answered Oct 18 '12 at 07:57

Zaw Than oo

9,651
13
83
131

Anders R. Bystrup · Answer 4 · 2012-10-18T08:18:48.753

3

A quick and dirty to solution is to filter the result using a Set:

Set<...> set = new HashSet<...>( findAll( ... ) )

and be sure that equals() and hashCode() are relevantly implemented on the domain class :-)

Cheers,

edited Oct 18 '12 at 08:18

answered Oct 18 '12 at 07:37

Anders R. Bystrup

15,729
10
59
55

1

If you use pageable this will mess up the size and offsets – Tonino Apr 17 '20 at 15:15

score -2 · Answer 5 · answered Jun 01 '15 at 14:29

-2

Will this work ?

List<Person> findDistinctPeopleByAddress(String lastname, String firstname);

followed by iterating thru the list and using Person.getAddress() ?

answered Jun 01 '15 at 14:29

Ankit

1,250
16
23

Spring Data JPA Select Distinct

5 Answers5

Linked