I've got two tables, 'Players' and 'Items'. Players have a list of items. I want to retrieve the players, and all of their items, using pagination. I want to paginate based on the players and without regard to how many items there are.
So I do something like this:
Criteria c = session.createCriteria(Players.class).setFetchMode("items", FetchMode.JOIN);
c.addOrder(Order.asc("playerID"));
c.setFirstResult(0);
c.setMaxResults(25);
List<Player> players = c.list();
Is this going to give me the first 25 players, or will it give me the first 25 items (grouped by players)? I'm wondering if this behavior is undefined like it would be for JPA queries, or if perhaps there's a guaranteed answer.
Regardless, what are Criteria queries that would give me the first 25 players or the first 25 player-item combinations (ordered by player ID, then item ID)?
Quite sure, but not 100%, it will do the following:
It will join the players and the items, order by playerID and take the first 25 results, all in one SQL query. From this data, it creates the players and items, which will result in an arbitrary amount of players (less or equal then 25) with a total of 25 items. It may happen that the last player doesn't get all items.
To get 25 players, avoid FetchMode.JOIN (to avoid the N+1 problem, use batch-size in the mapping file):
List<Player> first25Players = session
.createCriteria(Players.class)
.addOrder(Order.asc("playerID"))
.setMaxResults(25)
.list();
To get 25 items, start the query by Item, not Player.
List<Item> first25Items = session
.createCriteria(Item.class)
.addOrder(Order.asc("player")) // assuming that player is available
.setMaxResults(25)
.list();
If there is no navigation from the item to the player, you may add one.