Hibernate for getting a list of primitive integers for subselect

Question

Isn't there a way with Hibernate to return a list of (primitive) values from one column in a table? I need this for a subselect where I only want rows where a particular field is not in a list of ids from another table.

Answer 1

Can you use a Hibernate raw SQLQuery?

SQLQuery q = getSession().createSQLQuery("select int_column from table");
List<Integer> list = (List<Integer>) q.list();

Answer 2

Dont know about using the hibernate engine itself, I think this will depend on how youve mapped your model objects, short of seeing the mappings, you can go via the hibernate session object and use standard SQL;

session.createSQLQuery("select idCol from someTable where someId not in (
select someId from anotherTable)");

Then use the list() method on the query, and use autoboxing for the primtive int array.

Answer 3

well it turned to be as simple as something such as the following, from the URL https://www.hibernate.org/hib_docs/nhibernate/html/queryhql.html which was actually one of the first results I found when googling but I was concerned it might be NHibernate specific

from Eg.DomesticCat as cat where cat.Name not in ( 
select name.NickName from Eg.Name as name )