I am trying to do the Criteria equivalent of the following query.
Code:
from MatchSet m
where m.memberships.subscription.client.id=:clientId
I have tried the following, but it doesn't handle the implicit joins, though it does detect the correct field name for client.id at the end of the path:
Code:
Criteria matchSetCriteria = session.createCriteria(MatchSet.class);
matchSetCriteria.createAlias("memberships.subscription.client", "client")
.add(Restrictions.eq("client.id", clientId));
The stack trace and SQL are at the end of this post.
I tried (and was successful) to use createCriteria() for each of the path components, but it was fetching memberships, subscription, and client eventhough I also used setFetchMode(FetchMode.SELECT) at each level. There are also some relationships that are lazy="false" off of Subscription, so I really don't want to pull anything but MatchSet. The relationship matchSet.memberships is lazy="true". How can I accomplish the above HQL without eager fetching all these unwanted relationships?
Hibernate version: 3.0.5
Full stack trace of any exception that occurs:Code:
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [select this_.pers_MatchSet_Id as pers1_0_, this_.RowVersion as RowVersion0_0_, this_.AddressLine1 as AddressL3_0_0_, this_.AddressLine1MK as AddressL4_0_0_, this_.BirthDate as BirthDate0_0_, this_.BirthSq as BirthSq0_0_, this_.FirstName as FirstName0_0_, this_.FirstNameMK as FirstNam8_0_0_, this_.GeneratingHash as Generati9_0_0_, this_.Generating_Gid as Generating10_0_0_, this_.Generating_meta_Table_Id as Generating11_0_0_, this_.KeyValue1 as KeyValue12_0_0_, this_.elig_MSXRefSource_Id1 as elig13_0_0_, this_.KeyValue2 as KeyValue14_0_0_, this_.elig_MSXRefSource_Id2 as elig15_0_0_, this_.LastName as LastName0_0_, this_.LastNameMK as LastNameMK0_0_, this_.MiddleName as MiddleName0_0_, this_.Prefix as Prefix0_0_, this_.Suffix as Suffix0_0_, this_.pers_Person_Id as pers21_0_0_ from pers_MatchSet this_ where client1_.csm_Clt_Id=? and this_.BirthDate=? and this_.FirstName=? and this_.LastName=?]; nested exception is java.sql.SQLException: ORA-00904: "CLIENT1_"."CSM_CLT_ID": invalid identifier
java.sql.SQLException: ORA-00904: "CLIENT1_"."CSM_CLT_ID": invalid identifier
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:420)
at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896)
at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:452)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1322)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:300)
at com.shps.da.person.dao.hibernate.MatchSetDAOImpl$3.doInHibernate(MatchSetDAOImpl.java:122)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:365)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:333)
at com.shps.da.person.dao.hibernate.MatchSetDAOImpl.findByNearMatch1(MatchSetDAOImpl.java:101)
at com.shps.da.person.dao.MatchSetDAOTest.testFindByNearMatch1_NoAddress(MatchSetDAOTest.java:51)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Name and version of the database you are using: Oracle 10g
The generated SQL (show_sql=true):Code:
14:01:26,250 DEBUG org.hibernate.SQL:324 - select this_.pers_MatchSet_Id as pers1_0_, this_.RowVersion as RowVersion0_0_, this_.AddressLine1 as AddressL3_0_0_, this_.AddressLine1MK as AddressL4_0_0_, this_.BirthDate as BirthDate0_0_, this_.BirthSq as BirthSq0_0_, this_.FirstName as FirstName0_0_, this_.FirstNameMK as FirstNam8_0_0_, this_.GeneratingHash as Generati9_0_0_, this_.Generating_Gid as Generating10_0_0_, this_.Generating_meta_Table_Id as Generating11_0_0_, this_.KeyValue1 as KeyValue12_0_0_, this_.elig_MSXRefSource_Id1 as elig13_0_0_, this_.KeyValue2 as KeyValue14_0_0_, this_.elig_MSXRefSource_Id2 as elig15_0_0_, this_.LastName as LastName0_0_, this_.LastNameMK as LastNameMK0_0_, this_.MiddleName as MiddleName0_0_, this_.Prefix as Prefix0_0_, this_.Suffix as Suffix0_0_, this_.pers_Person_Id as pers21_0_0_ from pers_MatchSet this_ where client1_.csm_Clt_Id=? and this_.BirthDate=? and this_.FirstName=? and this_.AddressLine1=?
Login
FAQ
Search
