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