hi, guys, I have two tables with many-to-many relationships which called Position and Content.

Mapping is collect, because insert and and other queries all can work properly.


and I use

Criteria c = sessionFactory.getCurrentSession().createCriteria(Content.class);
c.add(Restrictions.isEmpty("position"));

to get the dataset which means all the Contents are not taken by any Position.

It works properly.

And then, I add:

Criteria c = sessionFactory.getCurrentSession().createCriteria(Content.class);
c.add(Restrictions.isEmpty("position"));
c.createAlias("position", "p");
c.add(Restrictions.eq("p.id", 100));

which means that to get all the Contents are not taken by Position 100.

I get a empty return.

May I know what I was wrong in the case please?

Up and thank you!! Please help~

Please, write SQL-query, that you want to get.

I think, you need this:


(deleted first restriction and added "not" to last)

Thanks a lot! It works!!

The problem is so interesting, that the code just work once time correctly, now it can only get result which equals

Restrictions.eq("p.id", 100)

but Restrictions.not() looks like no function anymore.

Any ideas?


Here is the SQL I need to query:

SELECT * FROM `content` WHERE `content`.id not in (select content_id from `position_has_content` where position_id = 62)


but I think

Criteria c = sessionFactory.getCurrentSession().createCriteria(Content.class);
c.createAlias("position", "p");
c.add(Restrictions.not(Restrictions.eq("p.id", 62)));


results:

SELECT * FROM `position_has_content` WHERE content_id not in (select content_id from `position_has_content` where position_id = 62)


it is quite different.

The problem is that you get select from wrong table, right? Write, please, mapping of your classes and query, that Hibernate writes in log (with "this_" and etc)

thank you for helping
but I think I already select from the right target I want:

Criteria c = sessionFactory.getCurrentSession().createCriteria(Content.class);
c.add(Restrictions.isEmpty("position"));
c.createAlias("position", "p");
c.add(Restrictions.not(Restrictions.eq("p.id", 100)));

The thing many be that firstly I used EAGER to search from join-table to find which already exists, and the final result actually comes from the single table Content which should use LAZY to get result.

Criteria c = sessionFactory.getCurrentSession().createCriteria(Content.class);
c.add(Restrictions.isEmpty("position"));
c.createAlias("position", "p");
c.add(Restrictions.not(Restrictions.eq("p.id", 100)));

works like below:

SELECT * FROM `position_has_content` WHERE content_id not in (select content_id from `position_has_content` where position_id = 100)



but the result I want should like this:
SELECT * FROM `content` WHERE `content`.id not in (select content_id from `position_has_content` where position_id = 100)

[quote="xiaonvren"]The problem is that you get select from wrong table

I tried many way, the final solution looks like below:

Criteria c = sessionFactory.getCurrentSession().createCriteria(Content.class);
c.createAlias("position", "p");
c.add(Restrictions.eq("p.id", 100));

List<Content> nc = c.list();
List<Integer> idList = new ArrayList<Integer>();

for(Content cont:nc)
{
idList.add(cont.getId());
}

Criteria c2 = sessionFactory.getCurrentSession().createCriteria(Content.class).setFetchMode("position", FetchMode.SELECT);

if(nc.size()!=0) c2.add(Restrictions.not(Restrictions.in("id",idList)));

return c2.list();

I know it is not efficient and effective, so any ideas to help? thank you.