So I have the following situation: two joint entities, Offer and Item, where I want to fetch parts and put them into a tuple object like that:
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
Metamodel m = entityManager.getMetamodel();
EntityType<Offer> offerMeta = m.entity(Offer.class);
CriteriaQuery<MyTupleContainer> criteriaQuery = criteriaBuilder.createQuery(MyTupleContainer.class);
Root<Offer> offer = criteriaQuery.from(Offer.class);
Join<Offer, Item> item = offer.join(offerMeta.getSingularAttribute("item", Item.class));
criteriaQuery.multiselect(offer.get("id"), item.get("name"), offer.get("offerValidUntil"),
item.get("regularPrice"), offer.get("discountInPercent"));
Afterwards, filtering, sorting and pagination gets applied (the data is presented in a table).
This works fine as it is, however I ALSO want to fetch the index of each element when default sorting and no filtering / pagination is applied! Specifically, default order is by offer.get("offerValidUntil"), ascending. So if my database table has 100 entries, I want them to dynamically get indices 1 to 100 at query execution, and custom sorting / filtering / pagination should be applied afterwards (because obviously it would mess up the correct mapping if done afterwards).
I have a working SQL query that does what it should with the help of the ROW_NUMBER Oracle window function:
SELECT Offer.id AS id,
Item.name AS name,
Offer.offer_valid_until AS offerValidUntil,
Item.regular_price AS regularPrice,
Offer.discount_in_percent AS discountInPercent
ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY Offer.offer_valid_until ASC) AS listIndex
FROM Offer LEFT JOIN Item ON Item.id = Offer.item_id
What's the best way to do it with the Criteria API?
Thanks in advance!