Hibernate Community • View topic - Incorrect row count when using Criteria.DISTINCT_ROOT

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Incorrect row count when using Criteria.DISTINCT_ROOT_ENTITY

Page 1 of 1

[ 11 posts ]

Previous topic | Next topic

Author

Message

tfreeman

Post subject: Incorrect row count when using Criteria.DISTINCT_ROOT_ENTITY

Posted: Mon Aug 01, 2005 11:56 am

Newbie

Joined: Mon Aug 01, 2005 10:41 am
Posts: 6

I have two tables (parent/child relationship) where I am retrieving all the SimpleOrders for the SimpleHeaders that have a specific value in the medicalRecordNumber field.
The list I am retrieving has the correct number of items. When I try to do a Projections.rowCount() for the same list, I'm getting an incorrect value. It seems to be ignoring the Criteria.DISTINCT_ROOT_ENTITY part of the query.

Am I coding this incorrectly or is there a problem within Hibernate?
Any assistance would be greatly appreciated. Thanks

Hibernate version:
3.0.5
Mapping documents:

Code:

  <class name="com.fhcp.laboratory.SimpleHeader" table="LABHDR" mutable="false">
       <composite-id name="key" class="com.fhcp.laboratory.LaboratoryHeaderKey"> 
       <key-property name="labTestDate" column="HDDATE"/> 
      <key-property name="seqNo" column="HDKEYNBR"/> 
    </composite-id>
    <property name="medicalRecordNumber" column="PIEMPNO" type="java.lang.String"/> 
    <property name="orderingProvider" column="ORORDPRV" type="java.lang.String"/>
    <set name="obrs" inverse="true" fetch="join">
       <key>
         <column name="obdate"/>
         <column name="obkeynbr"/>
        </key>
      <one-to-many class="com.fhcp.laboratory.SimpleOrder"/>
    </set>       
   </class>

  <class name="com.fhcp.laboratory.SimpleOrder" table="LABOBR" mutable="false">
       <composite-id name="key" class="com.fhcp.laboratory.LaboratoryOrderKey"> 
        <key-property name="labTestDate" column="OBDATE"/> 
        <key-property name="seqNo" column="OBKEYNBR"/> 
        <key-property name="resultsProcessingSeqNo" column="OBSEQ"/>         
     </composite-id> 
    <property name="batteryID" column="OBBATTID" type="java.lang.String"/>
    <property name="batteryDesc" column="OBBATTTXT" type="java.lang.String"/>
    <many-to-one
       name="header" 
      class="com.fhcp.laboratory.SimpleHeader" 
      fetch="join">
      <column name="obdate" />
      <column name="obkeynbr" />
    </many-to-one>
    <set name="obxs" inverse="true" fetch="join">
       <key>
         <column name="oxdate"/>
         <column name="oxkeynbr"/>
         <column name="oxobrseq"/>
        </key>
      <one-to-many class="com.fhcp.laboratory.SimpleResult"/>
    </set> 
  </class>

  <class name="com.fhcp.laboratory.SimpleResult" table="LABOBX" mutable="false">
    <composite-id name="key" class="com.fhcp.laboratory.LaboratoryResultKey"> 
     <key-property name="labTestDate" column="OXDATE"/> 
    <key-property name="seqNo" column="OXKEYNBR"/>
    <key-property name="obrSeqNo" column="OXOBRSEQ"/> 
    <key-property name="obxSeqNo" column="OXSEQ"/>         
  </composite-id>
    <property name="observationID" column="OXOBID" type="java.lang.String"/>
    <property name="observationIDTxt" column="OXOBIDTXT" type="java.lang.String"/>
    <component name="result" class="com.fhcp.laboratory.component.TestResult">
       <property name="actual" column="OXRESULT" type="java.lang.String"/>
       <property name="unitsOfMeasure" column="OXUOM" type="java.lang.String"/>
       <property name="abnormal" column="OXABNFLG" type="java.lang.String"/>
       <property name="normals" column="OXREFRNG" type="java.lang.String"/>
    </component> 
    <many-to-one
       name="obr" 
      class="com.fhcp.laboratory.SimpleOrder" >
      <column name="oxdate" />
      <column name="oxkeynbr" />
      <column name="oxobrseq"/>
    </many-to-one>
  </class>

Code between sessionFactory.openSession() and session.close():

Code:

         System.out.println("Before listCount");
         Integer i = (Integer)sess.createCriteria(SimpleOrder.class)
            .setProjection(Projections.rowCount())
            .createCriteria("header") 
            .add(Restrictions.eq("medicalRecordNumber",member.getMedicalRecordNo()))
            .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
            .uniqueResult();
         System.out.println("Count of items: " + i);
      
         System.out.println("Before list");
         List l = sess.createCriteria(SimpleOrder.class)
            .addOrder(Order.desc("key.labTestDate"))
            .addOrder(Order.asc("batteryDesc"))
         .createCriteria("header") 
         .add(Restrictions.eq("medicalRecordNumber",member.getMedicalRecordNo()))
         .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
         .list();
         System.out.println("Items in list: " + l.size());

Name and version of the database you are using:
DB2/400 V5R3
The generated SQL (show_sql=true):
Before listCount
Hibernate: select count(*) as y0_ from LABOBR this_ inner join LABHDR simplehead1_ on this_.obdate=simplehead1_.HDDATE and this_.obkeynbr=simplehead1_.HDKEYNBR left outer join LABOBR obrs4_ on simplehead1_.HDDATE=obrs4_.obdate and simplehead1_.HDKEYNBR=obrs4_.obkeynbr left outer join LABOBX obxs5_ on obrs4_.OBDATE=obxs5_.oxdate and obrs4_.OBKEYNBR=obxs5_.oxkeynbr and obrs4_.OBSEQ=obxs5_.oxobrseq where simplehead1_.PIEMPNO=?
Count of items: 2940
Before list
Hibernate: select this_.OBDATE as OBDATE3_, this_.OBKEYNBR as OBKEYNBR3_, this_.OBSEQ as OBSEQ3_, this_.OBBATTID as OBBATTID5_3_, this_.OBBATTTXT as OBBATTTXT5_3_, this_.obdate as obdate5_3_, this_.obkeynbr as obkeynbr5_3_, simplehead1_.HDDATE as HDDATE0_, simplehead1_.HDKEYNBR as HDKEYNBR0_, simplehead1_.PIEMPNO as PIEMPNO4_0_, simplehead1_.ORORDPRV as ORORDPRV4_0_, obrs4_.obdate as obdate5_, obrs4_.obkeynbr as obkeynbr5_, obrs4_.OBDATE as OBDATE5_, obrs4_.OBKEYNBR as OBKEYNBR5_, obrs4_.OBSEQ as OBSEQ5_, obrs4_.OBDATE as OBDATE1_, obrs4_.OBKEYNBR as OBKEYNBR1_, obrs4_.OBSEQ as OBSEQ1_, obrs4_.OBBATTID as OBBATTID5_1_, obrs4_.OBBATTTXT as OBBATTTXT5_1_, obrs4_.obdate as obdate5_1_, obrs4_.obkeynbr as obkeynbr5_1_, obxs5_.oxdate as oxdate6_, obxs5_.oxkeynbr as oxkeynbr6_, obxs5_.oxobrseq as oxobrseq6_, obxs5_.OXDATE as OXDATE6_, obxs5_.OXKEYNBR as OXKEYNBR6_, obxs5_.OXOBRSEQ as OXOBRSEQ6_, obxs5_.OXSEQ as OXSEQ6_, obxs5_.OXDATE as OXDATE2_, obxs5_.OXKEYNBR as OXKEYNBR2_, obxs5_.OXOBRSEQ as OXOBRSEQ2_, obxs5_.OXSEQ as OXSEQ2_, obxs5_.OXOBID as OXOBID6_2_, obxs5_.OXOBIDTXT as OXOBIDTXT6_2_, obxs5_.OXRESULT as OXRESULT6_2_, obxs5_.OXUOM as OXUOM6_2_, obxs5_.OXABNFLG as OXABNFLG6_2_, obxs5_.OXREFRNG as OXREFRNG6_2_, obxs5_.oxdate as oxdate6_2_, obxs5_.oxkeynbr as oxkeynbr6_2_, obxs5_.oxobrseq as oxobrseq6_2_ from LABOBR this_ inner join LABHDR simplehead1_ on this_.obdate=simplehead1_.HDDATE and this_.obkeynbr=simplehead1_.HDKEYNBR left outer join LABOBR obrs4_ on simplehead1_.HDDATE=obrs4_.obdate and simplehead1_.HDKEYNBR=obrs4_.obkeynbr left outer join LABOBX obxs5_ on obrs4_.OBDATE=obxs5_.oxdate and obrs4_.OBKEYNBR=obxs5_.oxkeynbr and obrs4_.OBSEQ=obxs5_.oxobrseq where simplehead1_.PIEMPNO=? order by this_.OBDATE desc, this_.OBBATTTXT asc
Items in list: 116

Top

jdl

Post subject:

Posted: Mon Aug 01, 2005 12:08 pm

Regular

Joined: Thu May 26, 2005 2:08 pm
Posts: 99

I don't think you want to mess around with the result transformer like that. Take a look at Projections.countDistinct() and see if that gets you on the right track.

Top

tfreeman

Post subject:

Posted: Mon Aug 01, 2005 2:19 pm

Newbie

Joined: Mon Aug 01, 2005 10:41 am
Posts: 6

I need to use the result transformer because the sql's are doing an inner join. (see generated sql).
All I am trying to accomplish is to generate the following two sql queries using Criteria query:

Code:

Select count(*) from labobr obr left join labhdr hdr
on obdate = hddate and obkeynbr = hdkeynbr
where hdr.piempno = '999999'

and

Code:

Select obr.* from labobr obr left join labhdr hdr
on obdate = hddate and obkeynbr = hdkeynbr
where hdr.piempno = '999999' 
order by obdate desc, obbatttxt

I don't understand how using Projections.countDistinct() would help with that.

Top

jdl

Post subject:

Posted: Mon Aug 01, 2005 2:42 pm

Regular

Joined: Thu May 26, 2005 2:08 pm
Posts: 99

When you call Criteria.setProjection(), you're calling this:

Code:

public Criteria setProjection(Projection projection) {
    this.projection = projection;
    this.projectionCriteria = this;
    setResultTransformer(PROJECTION);
    return this;
}

Notice the part where it sets the result transformer. Changing this seems like a bad idea.

I've used countDistinct() to do something very similar to what you're attempting here. Did it not work? What happened when you tried it?

I'm not sure what having an inner join has to do with needing to set your own result transformer, so I think I'm missing something with what you're trying to do. Maybe you could elaborate on that a bit.

Top

tfreeman

Post subject: Incorrect row count when using Criteria.DISTINCT_ROOT_ENTITY

Posted: Mon Aug 01, 2005 3:23 pm

Newbie

Joined: Mon Aug 01, 2005 10:41 am
Posts: 6

I can not find a reference to countDistinct() in the 3.0.5 manual. When I look at the java code for the method, it needs a propertyName to be passed. This does not apply in my case. I'm trying to get a row count, not count the distinct instances of a particular property name.

If I don't do the setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY), then I will get duplicate SimpleOrders and many more than I actually want.

As I mentioned before, I just want to generate the above sql queries using Criteria queries. If someone can assist me or point me to an example of how I can do this, it would be most helpful.

Top

jdl

Post subject:

Posted: Mon Aug 01, 2005 3:33 pm

Regular

Joined: Thu May 26, 2005 2:08 pm
Posts: 99

I think you're close. I haven't tried this with a composite id before, but you should be able to use your key property in the countDistinct() call, since that seems to be what you're trying to count anyway.

What happens when you run this:

Code:

Integer i = (Integer)sess.createCriteria(SimpleOrder.class)
            .setProjection(Projections.countDistinct("key"))
            .createCriteria("header")
           .add(Restrictions.eq("medicalRecordNumber",member.getMedicalRecordNo()))
            .uniqueResult();
         System.out.println("Count of items: " + i); 

Top

tfreeman

Post subject:

Posted: Mon Aug 01, 2005 4:20 pm

Newbie

Joined: Mon Aug 01, 2005 10:41 am
Posts: 6

I get the following error:
org.hibernate.QueryException: property does not map to a single column: key

Top

tfreeman

Post subject:

Posted: Mon Aug 01, 2005 4:27 pm

Newbie

Joined: Mon Aug 01, 2005 10:41 am
Posts: 6

jdl,

Forget about the rowcount query for now.
Since you seem to be against using the .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY), how would you code this query and not have duplicate results?

Code:

Select obr.* from labobr obr left join labhdr hdr 
on obdate = hddate and obkeynbr = hdkeynbr 
where hdr.piempno = '999999' 
order by obdate desc, obbatttxt

Top

jdl

Post subject:

Posted: Mon Aug 01, 2005 5:14 pm

Regular

Joined: Thu May 26, 2005 2:08 pm
Posts: 99

Well, that's the thing. Using ".setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)" makes sense for your main query. It becomes problematic when you try to add a projection to that.

I'd have to setup some test code to be sure, because I don't have anything with a composite key already setup. However, you still might be able to get somewhere with code like the following. I don't know what's going to happen when it tries to access your key properties, but this would be the direction I'd start with.

Code:

criteria.setProjection(
    Projections.distinct(
        Projections.projectionList()
            .add(Projections.property("key.labTestDate")
            .add(Projections.property("key.seqNo")
            .add(Projections.property("key.resultsProcessingSeqNo")
    )
);

// Obviously, this isn't null safe.  Add checks as appropriate.
List results = criteria.list();
int size = results.size();

On a sidenote, if this isn't legacy data, this exercise should be a pretty good selling point for adding a real ID column to your tables.

Top

tfreeman

Post subject: Incorrect row count when using Criteria.DISTINCT_ROOT_ENTITY

Posted: Tue Aug 02, 2005 10:31 am

Newbie

Joined: Mon Aug 01, 2005 10:41 am
Posts: 6

This has gotten way off topic. The topic is not keys, the topic is getting a row count.
Does anyone have definite answers and/or examples to any of the following questions? (please.... no guesses, no I thinks)
Using criteria queries:
1. Is there a way to do the above count(*) sql query?
2. Is there a way to prevent the inner join from happening?
3. Or is there a problem in Hibernate when trying to do a ".setProjection(Projections.rowCount())" in conjunction with a "setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)"?

Top

jdl

Post subject: Re: Incorrect row count when using Criteria.DISTINCT_ROOT_EN

Posted: Tue Aug 02, 2005 11:28 am

Regular

Joined: Thu May 26, 2005 2:08 pm
Posts: 99

tfreeman wrote:

I apologize for trying to work through your problem with you instead of spoonfeeding you the answer. If you'd like to continue narrowing it down, what error did you run into when you tried using countDistint() with a list of projections? Did you try it? It works for normal properties, but the syntax might be slightly different for composites.

Quote:

1. Is there a way to do the above count(*) sql query?

Yes, but you explicity said that you wanted to use Criteria.

Quote:

2. Is there a way to prevent the inner join from happening?

No. There's a rumor that this is changing in 3.1, but for now if you call createCriteria or createAlias on another criteria, you get an inner join. I can point you to the block of code that does this in Hibernate if you care.

Quote:

3. Or is there a problem in Hibernate when trying to do a ".setProjection(Projections.rowCount())" in conjunction with a "setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)"?

Yes. See above.

Top

Page 1 of 1

[ 11 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum