Criteria API JPA Hibernate: Distinct select on foreign key

Question

Given is the following entity structure:

@Entity
public class Item {
    @Id @GeneratedValue
    private Long id;
    @OneToMany
    private Set<ItemEntry> itemEntries;

    public Item() {
        itemEntries = new HashSet<>();
    }

    // Getters and setters
}

@Entity
public class ItemEntry {
    @Id @GeneratedValue
    private Long id;
    private String stringValue;
    @ManyToOne
    private Item item;

    public Item() {}

    // Getters and setters
}

This resolves to a database table ItemEntry as follows:

| id | stringValue | item_id |

I need to query this database table using the Criteria API in a JPA environment (persistence provider is Hibernate) and the canonical metamodel.

The query is to retrieve all distinct Item objects where stringValue is like %my%. Please note that a stringValue that matches %my% might be assigned to the Item multiple times.

What I have so far is the following code:

final CriteriaQuery<ItemEntry> itemEntryQuery = criteriaBuilder.createQuery(ItemEntry.class);
final Root<ItemEntry> itemEntryRoot = criteriaQuery.from(ItemEntry.class);
final Path<Item> selection = itemEntryRoot.get(ItemEntry_.item);
itemEntryQuery.select(selection).where(...).distinct(true);

The compiler issues an error stating

The method select(Selection<? extends ItemEntry>) in the type CriteriaQuery<ItemEntry> is not applicable for the arguments (Path<Item>)

Is there another possibility to achieve what I am looking for? At this point, I cannot use a SetJoin<Item, ItemEntry> because I need to order the results by the stringValue which is not possible as order by items need to appear in the select list for a distinct query. In case I use a SetJoin<Item, ItemEntry>, only the the Item's fields appear in the select clause.

score 1 · Answer 1 · answered Jul 08 '16 at 11:38

1

In CriteriaQuery<T>, T is the type of the result of the query. You want your query to return Items, not ItemEntries, so it should be a CriteriaQuery<Item>.

Note that I would really avoid criteria queries for queries that aren't based on several criteria and don't need to be built dynamicall. A simple JPQL is much more straightforward and readable:

select distinct item from ItemEntry entry 
join entry.item item
where entry.stringValue like :param

answered Jul 08 '16 at 11:38

JB Nizet

678,734
91
1,224
1,255

Thanks for your answer. The where condition is built dynamically, hence it seems legitimate to use the Criteria API. Basically, I agree that I need to deal with `Item` objects. The main reason why I need it to work "the other way round" is stated in my question: "At this point, I cannot use a SetJoin because I need to **order the results by the stringValue which is not possible as order by items need to appear in the select list for a distinct query.**". – fatdevelops Jul 08 '16 at 11:59
That is why I thought it would be a good idea to do a distinct query on the `item_id` in the `ItemEntry` class and fetch the `Item` objects in another query. – fatdevelops Jul 08 '16 at 11:59
That doesn't make much sense to me. How could you order Items by a field that doesn't exist in Item? But anyway, that doesn't have much to do with your problem. If you want a query that returns Item, you need a CriteriaQuery, not a CriteriaQuery. – JB Nizet Jul 08 '16 at 12:08
In simple terms, the requirement says to have the `Item` objects sorted by `stringValue` of `ItemEntry` and I am trying to find a solution for that. Anyhow, if you have any idea I would be grateful if you could share that idea with me. – fatdevelops Jul 08 '16 at 12:15
1

So, if item1 has "a", "b", item2 has "b" and "c", and item3 has "a" and "a" and "c", what should be the order? – JB Nizet Jul 08 '16 at 12:21
Shoot, I think I see the point... Thanks for hinting at that, I have obviously really never thought about that. – fatdevelops Jul 08 '16 at 12:31

Criteria API JPA Hibernate: Distinct select on foreign key

1 Answers1