Hibernate criteria with collections and "not contains"

Question

public class Game{
@Id
    private int id;

    @ManyToMany
    @JoinTable(
        name="game_community"
        , joinColumns={
            @JoinColumn(name="game_id", nullable=false)
            }
        , inverseJoinColumns={
            @JoinColumn(name="community_id", nullable=false)
            }
        )
    private Set<Community> communities;

    @ManyToMany
    @JoinTable(
        name="user_game"
        , joinColumns={
            @JoinColumn(name="game_id", nullable=false)
            }
        , inverseJoinColumns={
            @JoinColumn(name="user_id", nullable=false)
            }
        )
    private Set<User> users;

Help me to create Hibernate criteria to get Games, where community.id = 1

and game.users are null or not contains user with id=5

hmrojas.p · Answer 1 · 2015-09-09T22:07:19.620

0

I supposed that the name of your id variables for users and communities is "id" and you have a UserGame class, then I think you can use the following code:

DetachedCriteria critSubQuery = DetachedCriteria.forClass(UserGame.class, "ug");
critSubQuery.add(Restrictions.eq("ug.user.id", 5));
critSubQuery.add(Restrictions.eqProperty("ug.game.id","g.id"));
critSubQuery.setProjection(Projections.property("ug.game.id"));

Criteria crit = sess.createCriteria(Game.class, "g");
crit.createAlias("communities", "com");
crit.add(Restrictions.eq("g.com.id", 1));
crit.add(Subqueries.notExists("g.id", critSubQuery));

You need to do a subquery to get all records from User-Game relation with id_user = 5 and use that subquery in your main query.

I hope this code works very well.

Good luck.

edited Sep 09 '15 at 22:07

answered Aug 20 '15 at 16:53

hmrojas.p

562
1
5
15

This code return me every game where us.id !=5, but this game can have user with id == 5. – Denis Glusskyi Aug 31 '15 at 13:32
I don't understand you, what do you mean with "but this game can have user with id == 5"? Because with this restriction `crit.add(Restrictions.ne("us.id", 5)))`, you get every record with users with id different from 5. At least I can see it so. – hmrojas.p Aug 31 '15 at 15:24
For example: if game contains user with id == 5 and user with id == 7, in table user_game will be 2 records. And this restiction crit.add(Restrictions.ne("us.id", 5))) will return game because condition with second record is satisfied, us.id != 5 – Denis Glusskyi Sep 02 '15 at 08:23
Then, do you want to your query doesn't return a game which has an user with id=5 at least? is it right? – hmrojas.p Sep 04 '15 at 22:32
Yes, all games that not contains user with id=5. – Denis Glusskyi Sep 07 '15 at 09:53

Hibernate criteria with collections and "not contains"

1 Answers1