-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
bbb
 Post subject: Example query - bad result set
PostPosted: Tue Jul 26, 2005 10:53 am 
Newbie

Joined: Tue Jul 26, 2005 10:12 am
Posts: 4
Hello :)

Hibernate version:
3.0.2

Mapping documents:

Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<!--
        Auto-generated mapping file from
        the hibernate.org cfg2hbm engine
-->
    <class name="ca.medavie.pmp.dao.mappings.pmp_pat.PatientDemographicV1" table="PATIENT_DEMOGRAPHIC_V1" schema="PMP_PAT">
        <composite-id name="id" class="ca.medavie.pmp.dao.mappings.pmp_pat.PatientDemographicV1Id">
            <key-property name="patientIid" type="string">
                <column name="PATIENT_IID" length="10" />
            </key-property>
            <key-property name="programId" type="long">
                <column name="PROGRAM_ID" precision="15" scale="0" />
            </key-property>
            <key-property name="cardHolderTypeId" type="long">
                <column name="CARD_HOLDER_TYPE_ID" precision="15" scale="0" />
            </key-property>
            <key-property name="healthCardNumber" type="string">
                <column name="HEALTH_CARD_NUMBER" length="20" />
            </key-property>
            <key-property name="firstName" type="string">
                <column name="FIRST_NAME" length="50" />
            </key-property>
            <key-property name="lastName" type="string">
                <column name="LAST_NAME" length="50" />
            </key-property>
            <key-property name="otherName" type="string">
                <column name="OTHER_NAME" length="50" />
            </key-property>
            <key-property name="dateOfBirth" type="timestamp">
                <column name="DATE_OF_BIRTH" length="7" />
            </key-property>
            <key-property name="gender" type="string">
                <column name="GENDER" length="1" />
            </key-property>
            <key-property name="effDate" type="timestamp">
                <column name="EFF_DATE" length="7" />
            </key-property>
            <key-property name="termDate" type="timestamp">
                <column name="TERM_DATE" length="7" />
            </key-property>
            <key-property name="createdDate" type="timestamp">
                <column name="CREATED_DATE" length="7" />
            </key-property>
            <key-property name="modifiedBy" type="string">
                <column name="MODIFIED_BY" length="30" />
            </key-property>
            <key-property name="modifiedActionCode" type="string">
                <column name="MODIFIED_ACTION_CODE" length="15" />
            </key-property>
            <key-property name="modifiedDate" type="timestamp">
                <column name="MODIFIED_DATE" length="7" />
            </key-property>
            <key-property name="activeFlag" type="string">
                <column name="ACTIVE_FLAG" length="1" />
            </key-property>
            <key-property name="loadDate" type="timestamp">
                <column name="LOAD_DATE" length="7" />
            </key-property>
        </composite-id>
    </class>
</hibernate-mapping>


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

Code:
PatientDemographicV1 query = new PatientDemographicV1( new PatientDemographicV1Id() );

query.getId().setActiveFlag( "Y" );


Example queryByExample = Example.create( query );

List queryResults = currentSession.createCriteria( query.getClass() )
                                                 .add( queryByExample )
                                                 .list();



Full stack trace of any exception that occurs:

No exceptions occur.

Name and version of the database you are using:

Oracle 10g

The generated SQL (show_sql=true):

Code:
Hibernate:
select this_.PATIENT_IID as PATIENT1_0_,
this_.PROGRAM_ID as PROGRAM2_0_,
this_.CARD_HOLDER_TYPE_ID as CARD3_0_, this_.HEALTH_CARD_NUMBER as HEALTH4_0_,
this_.FIRST_NAME as FIRST5_0_,
this_.LAST_NAME as LAST6_0_,
this_.OTHER_NAME as OTHER7_0_,
this_.DATE_OF_BIRTH as DATE8_0_,
this_.GENDER as GENDER0_,
this_.EFF_DATE as EFF10_0_,
this_.TERM_DATE as TERM11_0_,
this_.CREATED_DATE as CREATED12_0_,
this_.MODIFIED_BY as MODIFIED13_0_,
this_.MODIFIED_ACTION_CODE as MODIFIED14_0_, this_.MODIFIED_DATE as MODIFIED15_0_,
this_.ACTIVE_FLAG as ACTIVE16_0_, this_.LOAD_DATE as LOAD17_0_ from PMP_PAT.PATIENT_DEMOGRAPHIC_V1 this_ where (1=1)


Debug level Hibernate log excerpt:

N/A

Problem:

The table currently has 3 rows in it with only 1 row with activeFlag='Y'. The result list after the query returns with 3 entries:
1. null
2. PatientDemographicV1 Object with activeFlag='N'
3. The other PatientDemographicV1 with activeFlag='N'

I was expecting only to get 1 result back with the row/PatientDemographicV1 Object that has activeFlag='Y'.

The generated sql looks suspicious since the where clause is "where (1=1)" not sure what that means.

I'm fairly new to hibernate and querying by example has worked for other objects, just not sure why this one returns unexpected results. Is it because it is using a composite-id or is it because this table is a view?

Thanks for your time
Brad
Top
 Profile  
 
jbarnum
 Post subject:
PostPosted: Wed Jul 27, 2005 4:35 pm 
Regular
Regular

Joined: Wed May 11, 2005 11:57 pm
Posts: 80
This part is confusing:

Quote:
PatientDemographicV1 query = new PatientDemographicV1( new PatientDemographicV1Id() );

query.getId().setActiveFlag( "Y" );


What is a "PatientDemographicV1Id()"? If activeFlag is a field in PatientDemographicV1 , maybe the next line should just be:

Code:
query.setActiveFlag("Y")

instead?
Top
 Profile  
 
bbb
 Post subject:
PostPosted: Thu Jul 28, 2005 11:45 am 
Newbie

Joined: Tue Jul 26, 2005 10:12 am
Posts: 4
Yeah, it is confusing...but I seem to have gotten around the problem which was two-fold...

1. That I used the Auto-generated mapping tools from hibernate.org blindly instead doing the mapping myself

2. My lack of database knowledge ;)

Don't get me wrong, those auto-generating tools are awesome, it was more a problem of #2 than anything.

The view that I mapped had no primary key set, so the tool created a composite key for me out of all the columns in the view. Technically correct, but not in my case. I did the remapping manually for the composite key for 2 columns that make a row unique and mapped the rest of the fields normally. Everything worked.

So chalk this one up to a newbie not knowing what he is doing. Thank you though for your post jbarnum. :)

Cheers
Brad
Top
 Profile  
 
jbarnum
 Post subject:
PostPosted: Thu Jul 28, 2005 12:17 pm 
Regular
Regular

Joined: Wed May 11, 2005 11:57 pm
Posts: 80
Don't forget to rate ;-)
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 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

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.