-->
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
  [ 7 posts ] 
  Previous topic | Next topic 
Author Message
dimulescu
 Post subject: empty result from left join fetch
PostPosted: Wed Jun 30, 2004 11:02 am 
Newbie

Joined: Wed Aug 27, 2003 2:43 am
Posts: 19
Location: Paris
Hello,

I have 2 classes (Partner and PartnerDetails) connected by a one-to-one association; I run an HQL using LEFT JOIN FETCH in order to retrieve a Partner with its associated PartnerDetail using only one SELECT.

The strange behaviour is that the HQL returns an empty result while the generated SQL returns good data.

Here is the HQL:

Code:
FROM com.kertel.kercash.core.types.Partner AS partner
LEFT JOIN FETCH partner.details
WHERE partner.id =7


Strangely enough, the generated SQL returns 1 row. Here's the mappings of the two classes and Hiberantes trace logs.

I am using Hibernate 2.1.4 on a Sybase.

Thanks for any ideas!


Code:
   <class
      name="com.kertel.kercash.core.types.Partner"
      table="partenaire"
   >

      <id name="id" column="part_id" type="long" unsaved-value="0">
         <generator class="com.kertel.kercash.serialization.MaxIdGenerator" >
            <param name="table-name">partenaire</param>
            <param name="table-id">part_id</param>
            <param name="counter-table-name">compteur</param>
         </generator>
      </id>

      <property
         name="name"
         type="string"
         column="part_nom" />

      <set
         name="sites"
         table="site"
         lazy="true"
         inverse="true">

         <key column="part_id" />
         <one-to-many
            class="com.kertel.kercash.core.types.Site" />
      </set>
      
      <one-to-one
         name="details"
         class="com.kertel.kercash.core.types.PartnerDetails"
         outer-join="true"
         cascade="none"
      />

   </class>


Code:
    <class
       name="com.kertel.kercash.core.types.PartnerDetails"
       table="partenaire_detail">

      <id name="id" type="long" column="part_id" unsaved-value="0">
         <generator class="foreign">
             <param name="property">partner</param>
         </generator>

         <!--<generator class="assigned"></generator>-->
      </id>
      
      <one-to-one
         name="partner"
         class="com.kertel.kercash.core.types.Partner"
         constrained="true"
         outer-join="true" />

        <property name="signatureDate"       column="pard_datesignature"    type="java.sql.Date"/>
        <property name="siretNumber"       column="pard_siret"          type="string"/>
        <property name="extranetLogin"       column="pard_extranet_login"    type="string"/>
        <property name="extranetPassword"    column="pard_extranet_password" type="string"/>

        <property name="address1"          column="pard_adresse1"          type="string" />
        <property name="address2"          column="pard_adresse2"          type="string"/>
        <property name="address3"          column="pard_adresse3"          type="string"/>
        <property name="city"             column="pard_ville"          type="string" />
        <property name="state"             column="pard_etat"             type="string"/>
        <property name="country"          column="pard_pays"             type="string" />
        <property name="phone"             column="pard_tel"             type="string"/>
        <property name="postCode"          column="pard_codepostal"       type="string"/>
        <property name="fax"             column="pard_fax"             type="string"/>

        <property name="invoiceAddress1"    column="pard_adresse1_F"       type="string"/>
        <property name="invoiceAddress2"    column="pard_adresse2_F"       type="string"/>
        <property name="invoiceAddress3"    column="pard_adresse3_F"       type="string"/>
        <property name="invoiceCity"        column="pard_ville_F"          type="string"/>
        <property name="invoiceCountry"     column="pard_pays_F"          type="string"/>
        <property name="invoicePostCode"    column="pard_codepostal_F"       type="string"/>
        <property name="invoiceState"       column="pard_etat_F"          type="string"/>
    </class>



Hibernate's trace logs while executing the HQL query:

[code]
1047246 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - opened session
1047406 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - closing session
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - opened session
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - find: FROM com.kertel.kercash.core.types.Partner AS partner
LEFT JOIN FETCH partner.details
WHERE partner.id =7
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.engine.QueryParameters - named parameters: {}
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - flushing session
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - Flushing entities and processing referenced collections
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - Processing unreferenced collections
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - Scheduling collection removes/(re)creates/updates
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - Dont need to execute flush
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.hql.QueryTranslator - HQL: FROM com.kertel.kercash.core.types.Partner AS partner
LEFT JOIN FETCH partner.details
WHERE partner.id =7
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.hql.QueryTranslator - SQL: select partner0_.part_id as part_id0_, partnerdet1_.part_id as part_id1_, partner0_.part_nom as part_nom0_, partnerdet1_.pard_datesignature as pard_dat2_1_, partnerdet1_.pard_siret as pard_siret1_, partnerdet1_.pard_extranet_login as pard_ext4_1_, partnerdet1_.pard_extranet_password as pard_ext5_1_, partnerdet1_.pard_adresse1 as pard_adr6_1_, partnerdet1_.pard_adresse2 as pard_adr7_1_, partnerdet1_.pard_adresse3 as pard_adr8_1_, partnerdet1_.pard_ville as pard_ville1_, partnerdet1_.pard_etat as pard_etat1_, partnerdet1_.pard_pays as pard_pays1_, partnerdet1_.pard_tel as pard_tel1_, partnerdet1_.pard_codepostal as pard_co13_1_, partnerdet1_.pard_fax as pard_fax1_, partnerdet1_.pard_adresse1_F as pard_ad15_1_, partnerdet1_.pard_adresse2_F as pard_ad16_1_, partnerdet1_.pard_adresse3_F as pard_ad17_1_, partnerdet1_.pard_ville_F as pard_vi18_1_, partnerdet1_.pard_pays_F as pard_pa19_1_, partnerdet1_.pard_codepostal_F as pard_co20_1_, partnerdet1_.pard_etat_F as pard_et21_1_ from partenaire partner0_ left outer join partenaire_detail partnerdet1_ on partner0_.part_id=partnerdet1_.part_id where (partner0_.part_id=7 )
1051763 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.BatcherImpl - about to open: 0 open PreparedStatements, 0 open ResultSets
1051803 [AWT-EventQueue-0] DEBUG net.sf.hibernate.SQL - select partner0_.part_id as part_id0_, partnerdet1_.part_id as part_id1_, partner0_.part_nom as part_nom0_, partnerdet1_.pard_datesignature as pard_dat2_1_, partnerdet1_.pard_siret as pard_siret1_, partnerdet1_.pard_extranet_login as pard_ext4_1_, partnerdet1_.pard_extranet_password as pard_ext5_1_, partnerdet1_.pard_adresse1 as pard_adr6_1_, partnerdet1_.pard_adresse2 as pard_adr7_1_, partnerdet1_.pard_adresse3 as pard_adr8_1_, partnerdet1_.pard_ville as pard_ville1_, partnerdet1_.pard_etat as pard_etat1_, partnerdet1_.pard_pays as pard_pays1_, partnerdet1_.pard_tel as pard_tel1_, partnerdet1_.pard_codepostal as pard_co13_1_, partnerdet1_.pard_fax as pard_fax1_, partnerdet1_.pard_adresse1_F as pard_ad15_1_, partnerdet1_.pard_adresse2_F as pard_ad16_1_, partnerdet1_.pard_adresse3_F as pard_ad17_1_, partnerdet1_.pard_ville_F as pard_vi18_1_, partnerdet1_.pard_pays_F as pard_pa19_1_, partnerdet1_.pard_codepostal_F as pard_co20_1_, partnerdet1_.pard_etat_F as pard_et21_1_ from partenaire partner0_ left outer join partenaire_detail partnerdet1_ on partner0_.part_id=partnerdet1_.part_id where (partner0_.part_id=7 )
Hibernate: select partner0_.part_id as part_id0_, partnerdet1_.part_id as part_id1_, partner0_.part_nom as part_nom0_, partnerdet1_.pard_datesignature as pard_dat2_1_, partnerdet1_.pard_siret as pard_siret1_, partnerdet1_.pard_extranet_login as pard_ext4_1_, partnerdet1_.pard_extranet_password as pard_ext5_1_, partnerdet1_.pard_adresse1 as pard_adr6_1_, partnerdet1_.pard_adresse2 as pard_adr7_1_, partnerdet1_.pard_adresse3 as pard_adr8_1_, partnerdet1_.pard_ville as pard_ville1_, partnerdet1_.pard_etat as pard_etat1_, partnerdet1_.pard_pays as pard_pays1_, partnerdet1_.pard_tel as pard_tel1_, partnerdet1_.pard_codepostal as pard_co13_1_, partnerdet1_.pard_fax as pard_fax1_, partnerdet1_.pard_adresse1_F as pard_ad15_1_, partnerdet1_.pard_adresse2_F as pard_ad16_1_, partnerdet1_.pard_adresse3_F as pard_ad17_1_, partnerdet1_.pard_ville_F as pard_vi18_1_, partnerdet1_.pard_pays_F as pard_pa19_1_, partnerdet1_.pard_codepostal_F as pard_co20_1_, partnerdet1_.pard_etat_F as pard_et21_1_ from partenaire partner0_ left outer join partenaire_detail partnerdet1_ on partner0_.part_id=partnerdet1_.part_id where (partner0_.part_id=7 )
1051803 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.BatcherImpl - preparing statement
1051823 [AWT-EventQueue-0] DEBUG net.sf.hibernate.loader.Loader - processing result set
1051823 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.LongType - returning '7' as column: part_id0_
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.LongType - returning '7' as column: part_id1_
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.loader.Loader - result row: 7, 7
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.loader.Loader - Initializing object from ResultSet: 7
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.loader.Loader - Hydrating entity: com.kertel.kercash.core.types.Partner#7
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning 'ORT' as column: part_nom0_
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.loader.Loader - Initializing object from ResultSet: 7
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.loader.Loader - Hydrating entity: com.kertel.kercash.core.types.PartnerDetails#7
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.DateType - returning '01 janvier 1900' as column: pard_dat2_1_
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_siret1_
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning 'ORT' as column: pard_ext4_1_
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning 'brsn2635' as column: pard_ext5_1_
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning '12, VILLA DE LOURCINE / 16-24, RUE CABANIS' as column: pard_adr6_1_
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_adr7_1_
1052003 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_adr8_1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning 'PARIS' as column: pard_ville1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_etat1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning 'FRANCE' as column: pard_pays1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_tel1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning '75014' as column: pard_co13_1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_fax1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_ad15_1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_ad16_1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_ad17_1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_vi18_1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_pa19_1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_co20_1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.type.StringType - returning null as column: pard_et21_1_
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.loader.Loader - done processing result set (1 rows)
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.BatcherImpl - done closing: 0 open PreparedStatements, 0 open ResultSets
1052023 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.BatcherImpl - closing statement
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.loader.Loader - total objects hydrated: 2
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - resolving associations for [com.kertel.kercash.core.types.Partner#7]
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - creating collection wrapper:[com.kertel.kercash.core.types.Partner.sites#7]
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - loading [com.kertel.kercash.core.types.PartnerDetails#7]
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - attempting to resolve [com.kertel.kercash.core.types.PartnerDetails#7]
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - resolved object in session cache [com.kertel.kercash.core.types.PartnerDetails#7]
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - done materializing entity [com.kertel.kercash.core.types.Partner#7]
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - resolving associations for [com.kertel.kercash.core.types.PartnerDetails#7]
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - loading [com.kertel.kercash.core.types.Partner#7]
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - attempting to resolve [com.kertel.kercash.core.types.Partner#7]
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - resolved object in session cache [com.kertel.kercash.core.types.Partner#7]
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - done materializing entity [com.kertel.kercash.core.types.PartnerDetails#7]
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - initializing non-lazy collections
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.hql.QueryTranslator - compiling query
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - closing session
1052033 [AWT-EventQueue-0] DEBUG net.sf.hibernate.impl.SessionImpl - disconnecting session
1052073 [AWT-EventQueue-0] WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Warning: 0, SQLState: 010SK
1052073 [AWT-EventQueue-0] WARN net.sf.hibernate.util.JDBCExceptionReporter - 010SK: La base de donn
Top
 Profile  
 
dimulescu
 Post subject:
PostPosted: Thu Jul 01, 2004 4:44 am 
Newbie

Joined: Wed Aug 27, 2003 2:43 am
Posts: 19
Location: Paris
...this wouldn't be a bug by any chance, would it ?
Top
 Profile  
 
christian
 Post subject:
PostPosted: Thu Jul 01, 2004 4:49 am 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
Think about it: It would mean that no one can use Hibernate. So, no, its certainly not a bug.

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.

Top
 Profile  
 
dimulescu
 Post subject:
PostPosted: Thu Jul 01, 2004 5:01 am 
Newbie

Joined: Wed Aug 27, 2003 2:43 am
Posts: 19
Location: Paris
Christian,

I am aware that my question is not very complex, even close to basically stupid. My real problem is actually a bit more complex than that but the example I put here was simplified on purpose for a faster comprehension.

Anyway, the fact is that it's stopping me and I couldn't find an answer while rebrowsing Hibernate docs & forums.

If the problem is visible please don't hesitate to point it out.

christian wrote:
Think about it: It would mean that no one can use Hibernate. So, no, its certainly not a bug.
Top
 Profile  
 
anthony
 Post subject:
PostPosted: Thu Jul 01, 2004 5:05 am 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
when you copy paste the generated sql into a db client what happened?
if the resultset is wrong, that simply mean your mapping is wrong, or maybe your data corrupted

_________________
Anthony,
Get value thanks to your skills: http://www.redhat.com/certification

Top
 Profile  
 
dimulescu
 Post subject:
PostPosted: Thu Jul 01, 2004 8:41 am 
Newbie

Joined: Wed Aug 27, 2003 2:43 am
Posts: 19
Location: Paris
Hi Anthony, trying to answer your question in detail, I found out what was wrong :)

I think the problem was that while using this named query:

Code:
FROM com.kertel.kercash.core.types.Partner AS partner
LEFT JOIN FETCH partner.details
WHERE LOWER(partner.name) LIKE :name


I was replacing :name with:

Code:
'%something%'

(note the bordering single quotes)

Looks like while that is syntactically correct while inside an SQL client, but not good for Hibernate. That's why inside the SQL client everything worked OK while from Hibernate nothing was returned.

Best regards,
Adrian.

anthony wrote:
when you copy paste the generated sql into a db client what happened?
if the resultset is wrong, that simply mean your mapping is wrong, or maybe your data corrupted
Top
 Profile  
 
anthony
 Post subject:
PostPosted: Thu Jul 01, 2004 8:44 am 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
in the future you should debug a little more before posting things like this.... please

peace

_________________
Anthony,
Get value thanks to your skills: http://www.redhat.com/certification

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
  [ 7 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.