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 :
candidate : id_candidate, lastName, firstName, ... Example : 1, Smith, John
candidateLanguage : id_language, id_level, id_candidate Example : English, Native, 1
Language, id_language, Example : English
Level, id_level, 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!
Login
FAQ
Search