problem with subqueries

siempre · **Joined:** Tue Jan 16, 2007 11:22 am **Posts:** 4

This is my original HQL query which works just fine.

Code:

protected static final String HQL_FIND_LATEST_DOCS = "from " + DOC_REFERENCE_TABLE_NAME
         + " as docRef1 where docRef1.recipientAccount in (:accounts) and docRef1.ticketNumber = ("
         + " select max(docRef2.ticketNumber) from " + DOC_REFERENCE_TABLE_NAME
         + " as docRef2 where docRef1.recipientAccount = docRef2.recipientAccount and docRef2.transactionDate = ("
         + " select max(docRef3.transactionDate) from " + DOC_REFERENCE_TABLE_NAME
         + " as docRef3 where docRef3.recipientAccount = docRef2.recipientAccount))";

Now I am rewriting all HQL to criteria.
This is what I made of it.

Code:

   public List getLatestDocumentReferences(final IBANAccountNumber[] accountNumbers) {
      DetachedCriteria maxTrans = DetachedCriteria.forClass(DocumentReference.class, "doc3")
      .setProjection(Projections.projectionList().add(Projections.max("doc3.transactionDate")))
      .add(Restrictions.eqProperty("doc3.recipientAccount", "doc2.recipientAccount"));
      
      final DetachedCriteria maxTick = DetachedCriteria.forClass(DocumentReference.class, "doc2")
      .setProjection(Projections.projectionList().add(Projections.max("doc2.recipientAccount")))
      .add(Restrictions.eqProperty("doc1.recipientAccount", "doc2.recipientAccount"))
      .add(Property.forName("doc2.transactionDate").eq(maxTrans));
      
      return (List) getHibernateTemplate().execute(new HibernateCallback() {
         public Object doInHibernate(Session session) throws HibernateException {
            return session.createCriteria(DocumentReference.class.getName(), "doc1")
            .add(Restrictions.in("doc1.recipientAccount", accountNumbers ))
            //.add(Subqueries.in("doc1.ticketNumber", maxTick))
            .add(Property.forName("doc1.ticketNumber").eq(maxTick))
            .list();
         }
      });
   }

The problem is I keep getting nullpointer-exceptions on my return.
Is there something I am missing (btw: i am fairly new to writing criteria)

andresgr · **Posted:** Tue Jan 16, 2007 2:51 pm

I think you are using DetachedCriteria wrongly:

From de DetachedCriteria javadoc:

Some applications need to create criteria queries in "detached mode", where the Hibernate session is not available. This class may be instantiated anywhere, and then a Criteria may be obtained by passing a session to getExecutableCriteria(). All methods have the same semantics and behavior as the corresponding methods of the Criteria interface.

Regards.

siempre · **Joined:** Tue Jan 16, 2007 11:22 am **Posts:** 4

Detached queries are used for subqueries no? At least, that is what I read everywhere on the net. If there is another way to achieve my statement with two subqueries...

andresgr · **Posted:** Wed Jan 17, 2007 5:21 am

Quote:

Detached queries are used for subqueries no

Sorry, you were right... O:)

Please post stack trace of exception...

siempre · **Joined:** Tue Jan 16, 2007 11:22 am **Posts:** 4

I changed the query to this:

Code:

public List getLatestDocumentReferences(final IBANAccountNumber[] accountNumbers) {
      return (List) getHibernateTemplate().execute(new HibernateCallback() {
         public Object doInHibernate(Session session) throws HibernateException {
            
            DetachedCriteria maxTrans = DetachedCriteria.forClass(DocumentReference.class, "doc3")
            .add(Restrictions.eqProperty("doc3.recipientAccount", "doc2.recipientAccount"))
            .setProjection(Property.forName("doc3.transactionDate").max());
            
            DetachedCriteria maxTick = DetachedCriteria.forClass(DocumentReference.class, "doc2")
            .add(Restrictions.eqProperty("doc1.recipientAccount", "doc2.recipientAccount"))
            //.add(Property.forName("doc2.transactionDate").eq(maxTrans))
            .setProjection(Property.forName("doc2.ticketNumber").max());
            
            return session.createCriteria(DocumentReference.class.getName(), "doc1")
            .add(Restrictions.in("doc1.recipientAccount", accountNumbers ))
            .add(Property.forName("doc1.ticketNumber").eq(maxTick))
            .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
            .list();
         }
      });
   }

If I execute the query with only the maxtick subquery, everything runs fine but when I add the line to use the maxTrans subquery then I get the nullpointer.

Code:

.add(Property.forName("doc2.transactionDate").eq(maxTrans))

Code:

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 be.isabel.pdg.bank.persistance.dao.DocumentReferenceDAO$3.doInHibernate(DocumentReferenceDAO.java:169)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:362)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:328)
   at be.isabel.pdg.bank.persistance.dao.DocumentReferenceDAO.getLatestDocumentReferences(DocumentReferenceDAO.java:153)
   at be.isabel.pdg.bank.persistance.dao.TestDocumentReferenceDAO.testGetLatestDocumentReferences(TestDocumentReferenceDAO.java:152)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:324)
   at junit.framework.TestCase.runTest(TestCase.java:154)
   at junit.framework.TestCase.runBare(TestCase.java:127)
   at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:69)
   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 org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
   at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

[/code]

andresgr · **Posted:** Wed Jan 17, 2007 5:56 am

Mmmmm, looking at the source code i see:

public abstract class SubqueryExpression implements Criterion {

private CriteriaImpl criteriaImpl;
private String quantifier;
private String op;
private QueryParameters params;
private Type[] types;

(...)

Looking for usages of the "params" instance variable i see it is initialized only if the toSqlString() method is called previously (strange design)...

Maybe this is a bug. I think the NPE is the result of calling

SubqueryExpression.getTypedValues()

with "params" not initialized...

Anyone?

siempre · **Joined:** Tue Jan 16, 2007 11:22 am **Posts:** 4

nothing?