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:
Code:
Restrictions.sqlRestriction("{alias}.id in (select DISTINCT temp.tid from (select p.id as tid, COUNT(*) as freqfrom Property p, Feature f where p.id=f.property_id and (f.desc='Gym' or f.desc='Wifi')) as temp where temp.freq=2)");
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