-->
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
  [ 1 post ] 
  Previous topic | Next topic 
Author Message
jpearson
 Post subject: Syntax help for Load-Collection Query with Composite Key
PostPosted: Thu Aug 12, 2010 12:45 pm 
Newbie

Joined: Thu Aug 12, 2010 12:41 pm
Posts: 1
I'm working with an existing database (that cannot be changed) that uses composite keys. I trying to create a native SQL join for a collection however Hibernate is trying to retrieve a value from the result set that isn't there ("PID1_0_"). I'm almost certain I'm missing something in my "load-collection" element.

Can you please help?

Parent.hbm.xml
Code:
<hibernate-mapping ...>
  <class name="Parent" table="TBLPARENT">
    <id name="id" type="double">
      <column name="ID" sql-type="decimal" scale="9" percision="0" />
    </id>

    <set name="children">
      <key column="PID" />
      <one-to-many class="Child" />
      <loader query-ref="children" />
    </set>
  </class>
</hibernate-mapping>


Child.hbm.xml
Code:
<hibernate-mapping ...>
  <class name="Child" table="TBLCHILD">
    <composite-id name="id" class="ChildID">
      <key-property name="parentID" type="double">
        <column name="PID" sql-type="decimal" scale="9" percision="0" />
      </key-property>
      <key-property name="childID" type="double">
        <column name="SEQ" sql-type="decimal" scale="9" percision="0" />
      </key-property>
    </composite-id>
    <property name="name" type="string">
      <column name="NAME" length="40" sql-type="char" not-null="true"/>
    </property>
  </class>

  <sql-query name="children">
    <load-collection alias="c" role="Parent.children">
      <return-property name="key" column="PID" />
      <return-property name="element">
        <return-column name="PID" />
        <return-column name="SEQ" />
      </return-property>
      <return-property name="element.name" column="NAME" />
    </load-collection>

    select 
      c.PID,
      c.SEQ,
      c.NAME
    from
      TBLCHILD c
    WHERE
      c.PID=?
  </sql-query>
</hibernate-mapping>


create.sql
Use to create a really simple testing database
Code:
CREATE SCHEMA SCHEMA1;

CREATE TABLE SCHEMA1.TBLPARENT(
  ID DECIMAL(9,0) NOT NULL
);

CREATE TABLE SCHEMA1.TBLCHILD(
  PID DECIMAL(9,0) NOT NULL,
  SEQ DECIMAL(9,0) NOT NULL,
  NAME CHAR(40)
);

INSERT INTO SCHEMA1.TBLPARENT VALUES (1);
INSERT INTO SCHEMA1.TBLCHILD VALUES (1,1,'Richard');
INSERT INTO SCHEMA1.TBLCHILD VALUES (1,2,'Sally');
INSERT INTO SCHEMA1.TBLCHILD VALUES (1,3,'Thomas');

INSERT INTO SCHEMA1.TBLPARENT VALUES (2);
INSERT INTO SCHEMA1.TBLCHILD VALUES (2,4,'Harry');
INSERT INTO SCHEMA1.TBLCHILD VALUES (2,5,'Crystal');

INSERT INTO SCHEMA1.TBLPARENT VALUES (3);
INSERT INTO SCHEMA1.TBLCHILD VALUES (3,6,'Jim');

INSERT INTO SCHEMA1.TBLPARENT VALUES (4);

INSERT INTO SCHEMA1.TBLPARENT VALUES (5);
INSERT INTO SCHEMA1.TBLCHILD VALUES (5,7,'George');


Log
Code:
2010-08-12 12:19:18,866 TRACE [org.hibernate.engine.query.QueryPlanCache] - <located native-sql query plan in cache (select 
      c.PID,
      c.SEQ,
      c.NAME
    from
      TBLCHILD c
    WHERE
      c.PID=?)>
2010-08-12 12:19:18,866 TRACE [org.hibernate.impl.SessionImpl] - <SQL query: select 
      c.PID,
      c.SEQ,
      c.NAME
    from
      TBLCHILD c
    WHERE
      c.PID=?>
2010-08-12 12:19:18,881 DEBUG [org.hibernate.jdbc.AbstractBatcher] - <about to open PreparedStatement (open PreparedStatements: 0, globally: 0)>
2010-08-12 12:19:18,881 DEBUG [org.hibernate.SQL] - <select 
      c.PID,
      c.SEQ,
      c.NAME
    from
      TBLCHILD c
    WHERE
      c.PID=?>
2010-08-12 12:19:18,881 TRACE [org.hibernate.jdbc.AbstractBatcher] - <preparing statement>
2010-08-12 12:19:18,881 TRACE [org.hibernate.type.DoubleType] - <binding '1.0' to parameter: 1>
2010-08-12 12:19:18,881 TRACE [org.hibernate.loader.Loader] - <Bound [2] parameters total>
2010-08-12 12:19:18,881 DEBUG [org.hibernate.jdbc.AbstractBatcher] - <about to open ResultSet (open ResultSets: 0, globally: 0)>
2010-08-12 12:19:18,881 DEBUG [org.hibernate.loader.Loader] - <result set contains (possibly empty) collection: [forumexample.Parent.children#1.0]>
2010-08-12 12:19:18,881 TRACE [org.hibernate.engine.loading.LoadContexts] - <constructing collection load context for result set [org.apache.derby.client.net.NetResultSet40@f37a62]>
2010-08-12 12:19:18,897 TRACE [org.hibernate.engine.loading.CollectionLoadContext] - <starting attempt to find loading collection [[forumexample.Parent.children#1.0]]>
2010-08-12 12:19:18,897 TRACE [org.hibernate.engine.loading.CollectionLoadContext] - <collection not yet initialized; initializing>
2010-08-12 12:19:18,897 TRACE [org.hibernate.loader.Loader] - <processing result set>
2010-08-12 12:19:18,897 DEBUG [org.hibernate.loader.Loader] - <result set row: 0>
2010-08-12 12:19:18,897 INFO [org.hibernate.type.DoubleType] - <could not read column value from result set: PID1_0_; There is no column named: PID1_0_.  >
2010-08-12 12:19:18,897 DEBUG [org.hibernate.jdbc.AbstractBatcher] - <about to close ResultSet (open ResultSets: 1, globally: 1)>
2010-08-12 12:19:18,897 DEBUG [org.hibernate.jdbc.AbstractBatcher] - <about to close PreparedStatement (open PreparedStatements: 1, globally: 1)>
2010-08-12 12:19:18,897 TRACE [org.hibernate.jdbc.AbstractBatcher] - <closing statement>
2010-08-12 12:19:18,897 DEBUG [org.hibernate.util.JDBCExceptionReporter] - <could not execute query [select 
      c.PID,
      c.SEQ,
      c.NAME
    from
      TBLCHILD c
    WHERE
      c.PID=?]>
java.sql.SQLException: There is no column named: PID1_0_.   
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.ResultSet.getDouble(Unknown Source)
at org.hibernate.type.DoubleType.get(DoubleType.java:46)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:186)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:175)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:105)
at org.hibernate.type.ComponentType.hydrate(ComponentType.java:588)
at org.hibernate.type.ComponentType.nullSafeGet(ComponentType.java:303)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1142)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:604)
at org.hibernate.loader.Loader.doQuery(Loader.java:745)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
at org.hibernate.loader.Loader.doList(Loader.java:2294)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
at org.hibernate.loader.Loader.list(Loader.java:2167)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1832)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:179)
at org.hibernate.persister.collection.NamedQueryCollectionInitializer.initialize(NamedQueryCollectionInitializer.java:77)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:628)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:83)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1853)
at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:366)
at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:108)
at org.hibernate.collection.AbstractPersistentCollection.readSize(AbstractPersistentCollection.java:131)
at org.hibernate.collection.PersistentSet.size(PersistentSet.java:162)
at forumexample.App.main(App.java:25)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.codehaus.mojo.exec.ExecJavaMojo$1.run(ExecJavaMojo.java:291)
at java.lang.Thread.run(Thread.java:619)
Caused by: org.apache.derby.client.am.SqlException: There is no column named: PID1_0_.   
at org.apache.derby.client.am.ColumnMetaData.findColumnX(Unknown Source)
at org.apache.derby.client.am.ResultSet.findColumnX(Unknown Source)
... 33 more


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
  [ 1 post ] 

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.