Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 1 post ] 
Author Message
 Post subject: Left outer join/many-to-one -> unexpected generated sql
PostPosted: Mon May 15, 2006 5:44 am 
Newbie

Joined: Tue Jul 12, 2005 5:41 am
Posts: 7
I have three cascade classes (A->B->C) with a many to one relationship. I query the first two by a left outer join but select a field of the third one as well. I expected the third to be left-outer-joint as well but the outcome is different from what I expected and, furthermore, is different according to my selection.

HQL query 1 (select the whole third class):
Code:
select s.string, dst.r from testCaon.JSrc s left outer join s.dst dst

SQL generated query:
Code:
select
   jsrc0_.String as col_0_0_, jdst1_.Resource as col_1_0_, jresource2_.oid as oid1_, jresource2_.String as String1_
from
   JSrc jsrc0_
left outer join
   JDST jdst1_ on jsrc0_.JDST=jdst1_.oid
inner join
   JRESOURCE jresource2_ on jdst1_.Resource=jresource2_.oid


HQL query 2 (select only one fields of the third class):
Code:
select s.string, dst.r.oid from testCaon.JSrc s left outer join s.dst dst

SQL generated query:
Code:
select
   jsrc0_.String as col_0_0_, jresource2_.oid as col_1_0_
from
   JSrc jsrc0_
left outer join
   JDST jdst1_ on jsrc0_.JDST=jdst1_.oid,
   JRESOURCE jresource2_ where jdst1_.Resource=jresource2_.oid


Is it correct?

Hibernate version:

3.1.2

Mapping documents:

Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping auto-import="false" default-cascade="none">
   <!--table="Holder"-->
   <class name="JSrc" table="JSRC">
      <cache usage="read-write" />
      <composite-id>
         <key-property name="oid" type="string">
            <column name="oid" sql-type="char(35)" />
         </key-property>
      </composite-id>
      <property name="string" type="string">
         <column name="String" length="512"/>
      </property>
      <many-to-one class="JDst" name="dst">
         <column name="JDST" sql-type="char(35)" index="I_dst_67d2c"/>
      </many-to-one>
   </class>
   <class name="JResource" table="JRESOURCE">
      <cache usage="read-write"/>
      <composite-id>
         <key-property name="oid" type="string">
            <column name="oid" sql-type="char(35)" />
         </key-property>
      </composite-id>
      <property name="string" type="string">
         <column name="String" length="512"/>
      </property>
   </class>
   <class name="JDst" table="JDST">
      <cache usage="read-write"/>
      <composite-id>
         <key-property name="oid" type="string">
            <column name="oid" sql-type="char(35)"/>
         </key-property>
      </composite-id>
      <many-to-one lazy="false" fetch="join" class="JResource" name="r">
         <column name="Resource" sql-type="char(35)" index="I_r_b7fad"/>
      </many-to-one>
   </class>
</hibernate-mapping>


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

Code:
cfg = new Configuration();
cfg.configure();
cfg.addFile("testJava/testCaon/mapping.xml");
         
// Create schema
SchemaExport se = new SchemaExport(cfg);
se.create(false, true);
         
// Create objects
JSrc src = new JSrc();
src.oid = "1";
src.string = "a";
JDst dst = new JDst();
dst.oid = "1";
src.dst = dst;
         
// Store objects
sf = cfg.buildSessionFactory();
s = sf.openSession();
Transaction t = s.beginTransaction();
s.saveOrUpdate(src);
s.saveOrUpdate(dst);
t.commit();
         
// Retrieve objects
Query q1 = s.createQuery("select s.string, dst.r from testCaon.JSrc s left outer join s.dst dst");
List list1 = q1.list();

Query q2 = s.createQuery("select s.string, dst.r.oid from testCaon.JSrc s left outer join s.dst dst");
List list2 = q2.list();


Full stack trace of any exception that occurs:

Code:
- Hibernate 3.1.2
- hibernate.properties not found
- using CGLIB reflection optimizer
- using JDK 1.4 java.sql.Timestamp handling
- configuring from resource: /hibernate.cfg.xml
- Configuration resource: /hibernate.cfg.xml
- Configured SessionFactory: null
- Reading mappings from file: testJava/testCaon/mapping.xml
- Mapping class: testCaon.JSrc -> JSrc
- Mapping class: testCaon.JResource -> JRESOURCE
- Mapping class: testCaon.JDst -> JDST
- Using dialect: org.hibernate.dialect.HSQLDialect
- Running hbm2ddl schema export
- exporting generated schema to database
- Using Hibernate built-in connection pool (not for production use!)
- Hibernate connection pool size: 20
- autocommit mode: false
- using driver: org.hsqldb.jdbcDriver at URL: jdbc:hsqldb:hsql://localhost/database
- connection properties: {user=sa, password=****}
- schema export complete
- cleaning up connection pool: jdbc:hsqldb:hsql://localhost/database
- composite-id class does not override equals(): testCaon.JResource
- composite-id class does not override hashCode(): testCaon.JResource
- composite-id class does not override equals(): testCaon.JDst
- composite-id class does not override hashCode(): testCaon.JDst
- composite-id class does not override equals(): testCaon.JSrc
- composite-id class does not override hashCode(): testCaon.JSrc
- Using Hibernate built-in connection pool (not for production use!)
- Hibernate connection pool size: 20
- autocommit mode: false
- using driver: org.hsqldb.jdbcDriver at URL: jdbc:hsqldb:hsql://localhost/database
- connection properties: {user=sa, password=****}
- RDBMS: HSQL Database Engine, version: 1.8.0
- JDBC driver: HSQL Database Engine Driver, version: 1.8.0
- Using dialect: org.hibernate.dialect.HSQLDialect
- Using default transaction strategy (direct JDBC transactions)
- No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
- Automatic flush during beforeCompletion(): disabled
- Automatic session close at end of transaction: disabled
- Scrollable result sets: enabled
- JDBC3 getGeneratedKeys(): disabled
- Connection release mode: auto
- Default batch fetch size: 1
- Generate SQL with comments: disabled
- Order SQL updates by primary key: disabled
- Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
- Using ASTQueryTranslatorFactory
- Query language substitutions: {}
- Second-level cache: enabled
- Query cache: disabled
- Cache provider: org.hibernate.cache.EhCacheProvider
- Optimize cache for minimal puts: disabled
- Structured second-level cache entries: disabled
- Echoing all SQL to stdout
- Statistics: disabled
- Deleted entity synthetic identifier rollback: disabled
- Default entity-mode: pojo
- building session factory
- Could not find configuration [testCaon.JResource]; using defaults.
- Could not find configuration [testCaon.JDst]; using defaults.
- Could not find configuration [testCaon.JSrc]; using defaults.
- Not binding factory to JNDI, no JNDI name configured
Hibernate: select jsrc_.oid, jsrc_.String as String0_, jsrc_.JDST as JDST0_ from JSrc jsrc_ where jsrc_.oid=?
Hibernate: select jdst_.oid, jdst_.Resource as Resource2_ from JDST jdst_ where jdst_.oid=?
Hibernate: insert into JSrc (String, JDST, oid) values (?, ?, ?)
Hibernate: insert into JDST (Resource, oid) values (?, ?)
Hibernate: update JSrc set String=?, JDST=? where oid=?

Hibernate: select jsrc0_.String as col_0_0_, jdst1_.Resource as col_1_0_, jresource2_.oid as oid1_, jresource2_.String as String1_ from JSrc jsrc0_ left outer join JDST jdst1_ on jsrc0_.JDST=jdst1_.oid inner join JRESOURCE jresource2_ on jdst1_.Resource=jresource2_.oid

Hibernate: select jsrc0_.String as col_0_0_, jresource2_.oid as col_1_0_ from JSrc jsrc0_ left outer join JDST jdst1_ on jsrc0_.JDST=jdst1_.oid, JRESOURCE jresource2_ where jdst1_.Resource=jresource2_.oid


Name and version of the database you are using:

HSQL Database Engine 1.8.0

The generated SQL (show_sql=true):

Code:
select
   jsrc0_.String as col_0_0_, jdst1_.Resource as col_1_0_, jresource2_.oid as oid1_, jresource2_.String as String1_
from
   JSrc jsrc0_
left outer join
   JDST jdst1_ on jsrc0_.JDST=jdst1_.oid
inner join
   JRESOURCE jresource2_ on jdst1_.Resource=jresource2_.oid

select
   jsrc0_.String as col_0_0_, jresource2_.oid as col_1_0_
from
   JSrc jsrc0_
left outer join
   JDST jdst1_ on jsrc0_.JDST=jdst1_.oid,
   JRESOURCE jresource2_ where jdst1_.Resource=jresource2_.oid


Debug level Hibernate log excerpt:


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

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.