Specfiy columns which should be considered for distinct calculation

Question

I'm using javax.persistence.criteria.CriteriaBuilder and javax.persistence.criteria.CriteriaQuery to select some entities.

I now want to select only the entities that are unique which should be specified by a certain column.

There is the method javax.persistence.criteria.CriteriaQuery#distinct which only returns unique entities.

I would rather need something like

CriteriaQuery<T> distinct(String... columnNames)

Do you know how I can bake such a distinct in my JPA CriteriaQuery?

It seems to be possible with Hibernate.

score 3 · Accepted Answer · answered Mar 25 '16 at 12:05

The following statement has no sense:

I now want to select only the entities that are unique which should be specified by a certain column.

The result sets are filtered by 'distinct' if they are 'exactly the same'. The entities are not the same if only some fields are the same.

You can make distinct clause on resultset in the following manner:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery query = builder.createQuery();
Root<Friend> c = query.from(Friend.class);
query.multiselect(c.get(Friend_.firstName),c.get(Friend_.lastName)).distinct(true);

then you will get unique combination of firstName and lastName from Friend entities.

So for instance... 'Give me all unique combinations from my Friends where the firstName and lastName of the friend is unique.' But it doesn't mean give me unique friends.

Simon Martinelli · Answer 2 · 2016-03-25T17:11:35.850

2

distinct takes a boolean as parameter. You can use multiselect to select more than one column like in this example:

CriteriaQuery<Country> q = cb.createQuery();
Root<Country> c = q.from(Country.class);
q.multiselect(c.get("currency"), c.get("countryCode")).distinct(true);

edited Mar 25 '16 at 17:11

answered Mar 22 '16 at 16:04

Simon Martinelli

34,053
5
48
82

When I'm adding your code I'm getting this error: `Unable to locate appropriate constructor on class`. Removing your snippet makes it work again. Why should I need a different constructor with the `multiselect`? I currently have none. – Harold L. Brown Mar 22 '16 at 16:41
I think I haven't been clear in expressing my wish. I have hence rephrased the title of my question. – Harold L. Brown Mar 22 '16 at 16:56
to get you right: you want a distinct on columns that you don't select? That's not possible – Simon Martinelli Mar 22 '16 at 18:26
No. I want to select all entities of `House` which are distinct by the property `House#colour`. I want to select the whole `House` entity. – Harold L. Brown Mar 23 '16 at 08:58
1

try to add a group by house – Simon Martinelli Mar 23 '16 at 13:24
@simas_ch your example doesn't work because you said cb.createQuery(Country.class) and you do multiselect on currency and country code. cb.createQuery() instead fixes the problem. – Łukasz Rzeszotarski Mar 25 '16 at 12:10

Specfiy columns which should be considered for distinct calculation

2 Answers2

Linked