Criteria API interlinked subqueries

Stewbidoo · **Posted:** Mon Oct 23, 2006 8:27 am

Hello all,

I have a problem with interlinked Subqueries.propertyIn. I am using Spring framework, hibernate 3, and MySql 5.0. I have 4 tables :

Example : 1, Smith, John

Example : English, Native, 1

Example : English

Example : Native

I would like to retrieve every candidate who speak certain languages and have certain levels in them. For example, I would like to retrieve candidates who are native in English and speak French fluently. My query in SQL would be :

Code:

SELECT * from candidateLanguage cl, candidate c where cl.id_candidate= c.id_candidate and cl.id_candidate in (SELECT id_candidate from candidateLanguage where id_language= "french" AND id_level="fluent") AND id_language="English" AND id_level="native"

Spring hibernate code :

Code:

DetachedCriteria criteria = DetachedCriteria.forClass(Candidate.class, "c");

criteria.setFetchMode("candidateLanguages", FetchMode.JOIN)
                    .createAlias("candidateLanguages", "pl");

DetachedCriteria criteria1 = DetachedCriteria.forClass(CandidateLanguage.class);
            criteria1.setProjection(Projections.property("candidate"))
            .add(Restrictions.eq("pl.language", frenchLanguage))
            .add(Restrictions.eq("pl.level", frenchLevel));
criteria.add(Subqueries.propertyIn("id", criteria1))
            .add(Restrictions.eq("pl.language", englishLanguage))
            .add(Restrictions.eq("pl.level", englishLevel));

It is working great but if now I add another subqueries for another language such as basic Italian :

SQL query :

Code:

SELECT * from candidateLanguage cl, candidate c where cl.id_candidate= c.id_candidate and cl.id_candidate in (SELECT id_candidate from candidateLanguage where id_candidate in (SELECT id_candidate from candidateLanguage where id_language="Italian" AND id_level="basic") AND id_language= "french" AND id_level="fluent") AND id_language="English" AND id_level="native"

Spring hibernate code :

Code:

DetachedCriteria criteria = DetachedCriteria.forClass(Candidate.class, "c");

criteria.setFetchMode("candidateLanguages", FetchMode.JOIN)
                    .createAlias("candidateLanguages", "pl");

DetachedCriteria criteria2 = DetachedCriteria.forClass(CandidateLanguage.class);
            criteria2.setProjection(Projections.property("candidate"))
            .add(Restrictions.eq("pl.language", italianLanguage))
            .add(Restrictions.eq("pl.level", italianLevel));


DetachedCriteria criteria1 = DetachedCriteria.forClass(CandidateLanguage.class);
            criteria1.setProjection(Projections.property("candidate"))
            .add(Restrictions.eq("pl.language", frenchLanguage))
            .add(Restrictions.eq("pl.level", frenchLevel))
            .add(Subqueries.propertyIn("candidate", criteria2));

criteria.add(Subqueries.propertyIn("id", criteria1))
            .add(Restrictions.eq("pl.language", englishLanguage))
            .add(Restrictions.eq("pl.level", englishLevel));

I got this error :

Code:

testAdvancedSearch(qual.test.domain.dao.CompanyTestDAO)java.lang.NullPointerException
        at org.hibernate.criterion.SubqueryExpression.getTypedValues(SubqueryExpression.java:80)
        at org.hibernate.loader.criteria.CriteriaQueryTranslator.getQueryParameters(CriteriaQueryTranslator.java:251)
        at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:55)
        at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:334)
        at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:82)
        at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:68)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1550)
        at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
        at org.springframework.orm.hibernate3.HibernateTemplate$37.doInHibernate(HibernateTemplate.java:988)
        at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:366)
        at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:978)
        at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:971)
        at qual.domain.dao.hibernate3.CompanyDAOHibernate.getAdvancedSearch(CompanyDAOHibernate.java:247)
        at qual.test.domain.dao.CompanyTestDAO.testAdvancedSearch(CompanyTestDAO.java:119)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

I don't know what mistake I made. When this issue is resolved I would like to create a recursive method to add to 7 subqueries (for 7 languages max).

Thank your for your time!