How to write select queries for paginating output from ManyToMany attributes

Question

I have a Group table with ManyToMany relationship with the User table via a join table. A Group could contain a lot of users or could contain sub groups containing more users.

I am using JPA, Hibernate 3.3

1. How do I paginate the results returned by Group.getUsers() which could be either users obtained via Group.getUsers OR could be all users from the subgroups in Group.getGroups

Note: Since I have expressed this as a ManyToMany relationship, I don't have an entity table (for the join table) and not sure how to perform any queries using JPA on the join table.

@Entity
@Table(name = "group")
public class Group {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false)
    private Integer id;

    @Column(name = "name", length = "255", nullable = false)
    private String name;

    @ManyToMany(cascade = {CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH}, fetch = FetchType.LAZY)
    @JoinTable(name = "group_user", joinColumns = @JoinColumn(name = "group_id"), inverseJoinColumns = @JoinColumn(name = "user_id"))
    private List<User> users = new ArrayList<User>(0);

@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinTable(name = "group_subgroup", joinColumns = @JoinColumn(name = "src_group_id"), inverseJoinColumns = @JoinColumn(name = "target_group_id"))
private List<Group> groups = new ArrayList<Group>(0);

Answer 1

You can add private List<Group> groups (lazy) in the user entity. And have, for the groups:

`SELECT u FROM User u JOIN u.groups g WHERE g=:group`

and for the subgroups:

`SELECT u FROM User u JOIN u.groups WHERE g IN (:groups)`

(and call query.setParameter("groups", group.getGroups()))