Hi,

I am using the following SQL query in Restrictions.sqlRestriction argument for Criteria API upon whose execution, I get jdbc exception in Hibernate.

TABLE

Property (id,details)
Feature(property_id,desc)

property_id is a foreign key in Feature corresponding to Property

The query is:

My objective is to find all properties in Property Table which contain 'Gym' and 'Wifi' as features in Feature Table. So, I first create inner join of PropertyXFeature and select rows with desc field of the joined table matching 'Gym' or 'Wifi'. Now, property which will have both feature will appear twice in the Inner Joined Table. So for that, I select count(*) and later check if freq is 2.

Query works fine but throws exception when Inner Join Table contains no rows. In other words, when no property contains any of 'Gym' or 'Wifi' as feature, then I get an exception saying Column 'tid' can't be null. I guess this is probably because the temporary table created doesn't contain any rows/records and when I am performing another query(outer select) on it, it finds that tid field is not non-null any where and treats it as Primary key.

IS there any work around for such scenario? I need to use only Criteria API and any hql through Restriction only as I can add it to Criteria API. Can this scenario be achieved? I guess it should be as this is quite common scenario.

Looking for guidance.

Thanks,
Satyendra Mishra

Sorry, I din't post the code and mapping.



This results in two table
one is Property(id,features)
and other one is Property_Features(Property_Id,element)

For a property with 6 features, Property_Features table will have 6 rows with same Property_Id and different element corresponding to 6 different features.