Hello,

I'm trying to use the criteria API to find rows without a corresponding 1:1 relation. The native SQL looks like this:



I tried it with:



but this results in something like this:




Any hints?


Oliver

I created a many-to-one between Product and Category and creating a Criteria query like

Criteria crit = session.createCriteria(test.Category.class).createCriteria("products");
crit.add(Restrictions.isNull("category"));

and it did create a query like
Hibernate: select this_.CATEGORY_ID as CATEGORY1_1_, this_.CATEGORY_NAME as CATEGORY2_0_1_, product1_.PRODUCT_ID as PRODUCT1_0_, product1_.PRODUCT_NAME as PRODUCT2_1_0_, product1_.CATEGORY_ID as CATEGORY3_1_0_
from CATEGORY this_ inner join PRODUCT product1_
on this_.CATEGORY_ID=product1_.CATEGORY_ID
where product1_.CATEGORY_ID is null.

This seems to me really senseless. Your query will never return any rows, because if the where clause is true, the join condition cannot be true - due to the inner join no result is ever possible.

You want


but you are getting


because you are coding



how can you get criteria for B if A is what your passing to createCriteria ?

You're right, but if I pass TableB to createCriteria (in addition to TableA - I need criteria there, too) then Hibernate will form an inner join instead of a left join. Due to the inner join no result is possible.