-->
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
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
Jan Herman
 Post subject: Inconsistent fetching of object by primary key
PostPosted: Wed Aug 19, 2009 3:36 am 
Newbie

Joined: Tue Aug 18, 2009 5:57 am
Posts: 3
I have the following problem:
The foreign key of a child class is not always filled with the same value as the primary key of the parent class.
I have two domain objects which also exist as MySQL tables namely Onderzoek and Monster. With a 0>n relationship
OND_Nummer is the primary key of Onderzoek
MON_Nummer_OND is the foreign key of Monster
Fetching of Monster rows is done with the join where OND_Nummer = MON_Nummer_OND

Fetching monster rows based on several onderzoek values results in inconsistent results. See for example a log4j comment:

Code:
18-aug-2009 11:51:04 
INFO: 2009-08-18 11:51:04,668 DEBUG [nl.indrisoftware.cbwextranet.web.LablistsAction] - <nl.indrisoftware.cbwextranet.business.domain.OnderzoekWithMonsters Object {
OND_Nummer: 124325
}>

18-aug-2009 11:51:04 
INFO: 2009-08-18 11:51:04,668 DEBUG [nl.indrisoftware.cbwextranet.web.LablistsAction] - <nl.indrisoftware.cbwextranet.business.domain.Monster Object {
MON_Nummer_OND: 124263
}>


As you can see OND_Nummer is not equal to MON_Nummer_OND

The hbm.xml is as follows:
Code:
   <class name="OnderzoekWithMonsters" table="Onderzoek" mutable="false">
      <id name="nummer" column="OND_Nummer" type="java.lang.Integer" >
         <generator class="native" />
      </id>
      <property name="datum" column="OND_Datum" type="java.lang.Integer" />
      <property name="status" column="OND_Status" type="java.lang.String" />
      <property name="labOk" column="OND_LabOk" type="java.lang.Integer" />
      <property name="toestand" column="OND_Toestand" type="java.lang.String" />
      <property name="naam_REL" column="OND_Naam_REL" type="java.lang.String" />
      <property name="nummer_REL" column="OND_Nummer_REL" type="java.lang.Integer" />
      <set name="monsters" >
         <key column="MON_Nummer_OND" not-null="true"/>
         <one-to-many class="Monster" />
         <filter name="normGroep" condition="MON_Normgroep NOT LIKE :exclude_910 AND MON_Normgroep NOT LIKE :exclude_Checklist"/>
      </set>
   </class>

   <class name="Monster" table="Monster" mutable="false">
      <id name="nummer" column="MON_Nummer" type="java.lang.Integer">
         <generator class="native" />
      </id>
      <property name="datum" column="MON_Datum" type="java.lang.Integer" />
      <property name="onderzoekNummer" column="MON_Nummer_OND" type="java.lang.Integer" insert="false" update="false" not-null="true"/>
      <property name="prodOms" column="MON_ProdOms" type="java.lang.String" />
      <property name="omschrijving" column="MON_Omschrijving" type="java.lang.String" />
      <property name="leverancier" column="MON_Leverancier" type="java.lang.String" />
      <property name="temp" column="MON_Temp" type="java.lang.String" />
      <property name="beoordeling" column="MON_Beoordeling" type="java.lang.String" />
      <property name="normgroep" column="MON_Normgroep" type="java.lang.String" />
   </class>


With the java code as follows:
Code:
   public Collection<OnderzoekWithMonsters> getOnderzoekenByRels(Collection<Integer> relatieNummers, int dateFrom,
         int dateUntil) throws DataAccessException {
      // do not select monsters where MON_Normgroep contains 910 or Checklist
      String exclude_910 = "910%";
      String exclude_Checklist = "Checklist";
      Session session = sessionFactory.getCurrentSession();
      session.enableFilter("normGroep").setParameter("exclude_910", exclude_910).setParameter("exclude_Checklist",
            exclude_Checklist);
      Query q = session
            .createQuery("from OnderzoekWithMonsters onderzoek where onderzoek.nummer_REL IN (:rels) and onderzoek.datum >= :dateFrom and onderzoek.datum <= :dateUntil order by onderzoek.datum desc");
      q.setParameterList("rels", relatieNummers).setInteger("dateFrom", dateFrom).setInteger("dateUntil", dateUntil);
      List<OnderzoekWithMonsters> onderzoeken = q.list();
      return onderzoeken;
   }


Resulting in the following SQL hibernate statements:

Code:
18-aug-2009 11:51:03 
INFO: Hibernate: select onderzoekw0_.OND_Nummer as OND1_18_, onderzoekw0_.OND_Datum as OND2_18_, onderzoekw0_.OND_Status as OND3_18_, onderzoekw0_.OND_LabOk as OND4_18_, onderzoekw0_.OND_Toestand as OND5_18_, onderzoekw0_.OND_Naam_REL as OND6_18_, onderzoekw0_.OND_Nummer_REL as OND7_18_ from Onderzoek onderzoekw0_ where (onderzoekw0_.OND_Nummer_REL in (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)) and onderzoekw0_.OND_Datum>=? and onderzoekw0_.OND_Datum<=? order by onderzoekw0_.OND_Datum desc
18-aug-2009 11:51:03 
INFO: Hibernate: select monsters0_.MON_Nummer_OND as MON3_1_, monsters0_.MON_Nummer as MON1_1_, monsters0_.MON_Nummer as MON1_19_0_, monsters0_.MON_Datum as MON2_19_0_, monsters0_.MON_Nummer_OND as MON3_19_0_, monsters0_.MON_ProdOms as MON4_19_0_, monsters0_.MON_Omschrijving as MON5_19_0_, monsters0_.MON_Leverancier as MON6_19_0_, monsters0_.MON_Temp as MON7_19_0_, monsters0_.MON_Beoordeling as MON8_19_0_, monsters0_.MON_Normgroep as MON9_19_0_ from Monster monsters0_ where  monsters0_.MON_Normgroep NOT LIKE ? AND monsters0_.MON_Normgroep NOT LIKE ? and monsters0_.MON_Nummer_OND=?
…….


As I minimize the number of results by decreasing the date period the results are more consistent.
I already tried, among other options, for example:

Code:
   <class name="OnderzoekWithMonsters" table="Onderzoek" mutable="false">
      <id name="nummer" column="OND_Nummer" type="java.lang.Integer" >
         <generator class="native" />
      </id>
      <property name="datum" column="OND_Datum" type="java.lang.Integer" />
      <property name="status" column="OND_Status" type="java.lang.String" />
      <property name="labOk" column="OND_LabOk" type="java.lang.Integer" />
      <property name="toestand" column="OND_Toestand" type="java.lang.String" />
      <property name="naam_REL" column="OND_Naam_REL" type="java.lang.String" />
      <property name="nummer_REL" column="OND_Nummer_REL" type="java.lang.Integer" />
      <set name="monsters" inverse="true" cascade="all-delete-orphan">
         <key column="MON_Nummer_OND" />
         <one-to-many class="Monster" />
         <filter name="normGroep" condition="MON_Normgroep NOT LIKE :exclude_910 AND MON_Normgroep NOT LIKE :exclude_Checklist"/>
      </set>
   </class>

   <class name="Monster" table="Monster" mutable="false">
      <id name="nummer" column="MON_Nummer" type="java.lang.Integer">
         <generator class="native" />
      </id>
      <property name="datum" column="MON_Datum" type="java.lang.Integer" />
      <property name="onderzoekNummer" column="MON_Nummer_OND" type="java.lang.Integer" insert="false" update="false" not-null="true"/>
      <property name="prodOms" column="MON_ProdOms" type="java.lang.String" />
      <property name="omschrijving" column="MON_Omschrijving" type="java.lang.String" />
      <property name="leverancier" column="MON_Leverancier" type="java.lang.String" />
      <property name="temp" column="MON_Temp" type="java.lang.String" />
      <property name="beoordeling" column="MON_Beoordeling" type="java.lang.String" />
      <property name="normgroep" column="MON_Normgroep" type="java.lang.String" />
      <many-to-one name="onderzoek" column="MON_Nummer_OND" not-null="true"/>
   </class>


With the same inconsistent results.

How can I fix this problem?
Top
 Profile  
 
latha1119
 Post subject: Re: Inconsistent fetching of object by primary key
PostPosted: Wed Aug 19, 2009 4:42 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
Hi,
I did not look at the generated SQL's but looking at your mapping files, Isn't it that the Monster.hbm.xml file needs to have this entry?
<many-to-one name="OnderzoekWithMonsters" column="MON_Nummer_OND" class="...OnderzoekWithMonsters" outer-join="auto"/>
Instead of
<property name="onderzoekNummer" column="MON_Nummer_OND" type="java.lang.Integer" insert="false" update="false" not-null="true"/>

-Srilatha.
Top
 Profile  
 
Jan Herman
 Post subject: Re: Inconsistent fetching of object by primary key
PostPosted: Fri Aug 21, 2009 5:31 am 
Newbie

Joined: Tue Aug 18, 2009 5:57 am
Posts: 3
Hi,

I tried serveral versions implementing your advice see code:

Code:
   <class name="OnderzoekWithMonsters" table="Onderzoek" mutable="false">
      <id name="nummer" column="OND_Nummer" type="java.lang.Integer">
         <generator class="native" />
      </id>
      <property name="datum" column="OND_Datum" type="java.lang.Integer" />
      <property name="status" column="OND_Status" type="java.lang.String" />
      <property name="labOk" column="OND_LabOk" type="java.lang.Integer" />
      <property name="toestand" column="OND_Toestand" type="java.lang.String" />
      <property name="naam_REL" column="OND_Naam_REL" type="java.lang.String" />
      <property name="nummer_REL" column="OND_Nummer_REL" type="java.lang.Integer" />
      <set name="monsters" mutable="false" fetch="join">
         <key column="MON_Nummer_OND" not-null="true" />
         <one-to-many class="Monster" />
         <filter name="normGroep" condition="MON_Normgroep NOT LIKE :exclude_910 AND MON_Normgroep NOT LIKE :exclude_Checklist"/>
      </set>
   </class>

   <class name="Monster" table="Monster" mutable="false">
      <id name="nummer" column="MON_Nummer" type="java.lang.Integer">
         <generator class="native" />
      </id>
      <property name="datum" column="MON_Datum" type="java.lang.Integer" />
      <many-to-one name="onderzoekWithMonsters" column="MON_Nummer_OND" class="OnderzoekWithMonsters" fetch="join" insert="false" update="false" not-null="true"/>
      <property name="prodOms" column="MON_ProdOms" type="java.lang.String" />
      <property name="omschrijving" column="MON_Omschrijving" type="java.lang.String" />
      <property name="leverancier" column="MON_Leverancier" type="java.lang.String" />
      <property name="temp" column="MON_Temp" type="java.lang.String" />
      <property name="beoordeling" column="MON_Beoordeling" type="java.lang.String" />
      <property name="normgroep" column="MON_Normgroep" type="java.lang.String" />
   </class>


and

Code:
   <class name="OnderzoekWithMonsters" table="Onderzoek" mutable="false">
      <id name="nummer" column="OND_Nummer" type="java.lang.Integer">
         <generator class="native" />
      </id>
      <property name="datum" column="OND_Datum" type="java.lang.Integer" />
      <property name="status" column="OND_Status" type="java.lang.String" />
      <property name="labOk" column="OND_LabOk" type="java.lang.Integer" />
      <property name="toestand" column="OND_Toestand" type="java.lang.String" />
      <property name="naam_REL" column="OND_Naam_REL" type="java.lang.String" />
      <property name="nummer_REL" column="OND_Nummer_REL" type="java.lang.Integer" />
      <set name="monsters" mutable="false">
         <key column="MON_Nummer_OND" not-null="true" />
         <one-to-many class="Monster" />
         <filter name="normGroep" condition="MON_Normgroep NOT LIKE :exclude_910 AND MON_Normgroep NOT LIKE :exclude_Checklist"/>
      </set>
   </class>

   <class name="Monster" table="Monster" mutable="false">
      <id name="nummer" column="MON_Nummer" type="java.lang.Integer">
         <generator class="native" />
      </id>
      <property name="datum" column="MON_Datum" type="java.lang.Integer" />
      <many-to-one name="onderzoekWithMonsters" column="MON_Nummer_OND" class="OnderzoekWithMonsters" outer-join="auto" insert="false" update="false"/>
      <property name="prodOms" column="MON_ProdOms" type="java.lang.String" />
      <property name="omschrijving" column="MON_Omschrijving" type="java.lang.String" />
      <property name="leverancier" column="MON_Leverancier" type="java.lang.String" />
      <property name="temp" column="MON_Temp" type="java.lang.String" />
      <property name="beoordeling" column="MON_Beoordeling" type="java.lang.String" />
      <property name="normgroep" column="MON_Normgroep" type="java.lang.String" />
   </class>


but still several of the fetched monsters were fetched with a wrong value of the primary key of the parent class onderzoek.

see log4j comment:

Code:

21-aug-2009 11:10:25 
INFO: 2009-08-21 11:10:25,638 DEBUG [nl.indrisoftware.cbwextranet.web.LablistsAction] - <nl.indrisoftware.cbwextranet.business.domain.OnderzoekWithMonsters Object {
OND_Nummer: 124325
}>

21-aug-2009 11:10:25 
INFO: 2009-08-21 11:10:25,638 DEBUG [nl.indrisoftware.cbwextranet.web.LablistsAction] - <nl.indrisoftware.cbwextranet.business.domain.Monster Object {
MON_Nummer_OND: 124182
}>


where MON_Nummer_OND is in MOnster is
Code:
MON_Nummer_OND: " + onderzoekWithMonsters.getNummer()
Top
 Profile  
 
latha1119
 Post subject: Re: Inconsistent fetching of object by primary key
PostPosted: Fri Aug 21, 2009 11:42 am 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
Ok I'm kind of getting confused.


It would have been easier if the foreign key column is named same as the primary key column.
Code:
<class name="OnderzoekWithMonsters" table="Onderzoek" mutable="false">
      <id name="nummer" column="OND_Nummer" type="java.lang.Integer">
         <generator class="native" />
      </id>
      <property name="datum" column="OND_Datum" type="java.lang.Integer" />
      <property name="status" column="OND_Status" type="java.lang.String" />
      <property name="labOk" column="OND_LabOk" type="java.lang.Integer" />
      <property name="toestand" column="OND_Toestand" type="java.lang.String" />
      <property name="naam_REL" column="OND_Naam_REL" type="java.lang.String" />
      <property name="nummer_REL" column="OND_Nummer_REL" type="java.lang.Integer" />
      <set name="monsters" mutable="false" table="Monster" fetch="join">
         <key column="OND_Nummer" not-null="true" />
         <one-to-many class="Monster" />
         <filter name="normGroep" condition="MON_Normgroep NOT LIKE :exclude_910 AND MON_Normgroep NOT LIKE :exclude_Checklist"/>
      </set>
   </class>

   <class name="Monster" table="Monster" mutable="false">
      <id name="nummer" column="MON_Nummer" type="java.lang.Integer">
         <generator class="native" />
      </id>
      <property name="datum" column="MON_Datum" type="java.lang.Integer" />
      <many-to-one name="onderzoekWithMonsters"  column="OND_Nummer" class="OnderzoekWithMonsters" fetch="join" insert="false" update="false" not-null="true"/>
      <property name="prodOms" column="MON_ProdOms" type="java.lang.String" />
      <property name="omschrijving" column="MON_Omschrijving" type="java.lang.String" />
      <property name="leverancier" column="MON_Leverancier" type="java.lang.String" />
      <property name="temp" column="MON_Temp" type="java.lang.String" />
      <property name="beoordeling" column="MON_Beoordeling" type="java.lang.String" />
      <property name="normgroep" column="MON_Normgroep" type="java.lang.String" />
   </class>
Top
 Profile  
 
Jan Herman
 Post subject: Re: Inconsistent fetching of object by primary key
PostPosted: Sun Aug 23, 2009 8:31 am 
Newbie

Joined: Tue Aug 18, 2009 5:57 am
Posts: 3
Hi latha1119

The primary key of table onderzoek OND_Nummer should match the foreign key of the table monster MON_Nummer_OND, but what ever I try I keep getting inconsistent rows of monsters, being fetched with another primary key value.

I did not make the MySQL database, so the names are to be taken.

Do you have another suggestion?
Top
 Profile  
 
latha1119
 Post subject: Re: Inconsistent fetching of object by primary key
PostPosted: Mon Aug 24, 2009 12:10 am 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
Hi Jan Herman,
If this is still not resolved then lets do this:-
1)Keeping the mapping files as below disable the filter for now.
OnderzoekWithMonsters.hbm.xml
<set name="monsters" mutable="false" table="Monster" fetch="join">
<key column="MON_Nummer_OND" not-null="true" />
<one-to-many class="Monster" />
</set>
in the Monster.hbm.xml
<many-to-one name="onderzoekWithMonsters" column="MON_Nummer_OND" class="OnderzoekWithMonsters" fetch="join" insert="false" update="false" not-null="true"/>

2) Write and execute a straight forward SQL query.
select * from Onderzoek onderzoek
join monster monster on (onderzoek.OND_Nummer = monster.MON_Nummer_OND)
--put some restrictions on the onderzoek table (NOT on the monster table) using a where clause to narrow down the results

3)Use the same restrictions at the DAO end and check if the results are the same.

4)If you got lucky with step 3, then repeat step two to get a larger dataset and repeat step 3 to see if there are any inconsistent results. always check the SQL data output/results with the DAO layer.

If inconsistent results are found at this point Please post the SQL query, results and the Hibernate generated query and the inconsistent result set.

5)If you can come to this step then My guess is true. Filter might be the issue.
before enabling the filter repeat step 2 and step 3 but put restrictions on the monster table columns (same columns as your filter was doing) and make sure the dataset results are accurate both at SQL and at DAO layer.

6)enable the filter on the onderzoek.hbm.xml.
use the the same restrictions(on the monster table columns) in the Step 5 to figure out why the set of data is in-consistent.
Please post the SQL query, data returned and the Hibernate generated query and the in-consistent data.

Hope this will help to find out the actual issue. Sorry if i could not be of any help!
-Srilatha.
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
  [ 6 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.