"NOT IN" in HQL

Question

I´m starting with Hibernate.

I have 2 tables in MySQL DB.

1)Unit

2)type: has as foreign key Unit_IdUnit I have also in type a column name "Period" VARCHAR

I want to create this query on HQL:

SELECT * 
FROM unit 
WHERE IdUnit not in(SELECT Unit_IdUnit 
                    FROM type 
                    WHERE Period='2016-09');

If I run this query in Mysql it´s working. But I can´t make it works on HQL.

How could I create this query to return a list of Unit?

This is my first question, so please let me know if you need further information

Possible duplicate of [In hibernate how to use not in clause](http://stackoverflow.com/questions/5694620/in-hibernate-how-to-use-not-in-clause) — Ankush soni, Sep 07 '16 at 17:46
Please post your mappings and the error you get with your current solution. — Dragan Bozanovic, Sep 07 '16 at 18:57

score -1 · Answer 1 · answered Sep 07 '16 at 17:45

-1

You can use below HQL:

session.createQuery(
        "from unit where IdUnit not in (SELECT Unit_IdUnit 
                    FROM type 
                    WHERE Period='2016-09')"
    ).list();

answered Sep 07 '16 at 17:45

Ankush soni

1,439
1
15
30

score -1 · Answer 2 · answered Sep 07 '16 at 20:55

This is how I fix it.

Query query= session.createQuery("SELECT e1 FROM Unit e1 "
                               + "WHERE e1.idUnit NOT IN ("
                                                       + "SELECT e2.unit "
                                                       + "FROM type e2 "
                                                       + "WHERE e2.period=:period)");

query.setParameter("period", "2016-09");
list= query.list();

"NOT IN" in HQL

2 Answers2