I have three related classes
Address.java -> one to many relationship with AddressPreference
AddressPreference.java -> has attribute type AddressPreferenceID id
AddressPreferenceID.java

When trying to use custom SQL for a named query the mapping is not working because I don't know how to alias the columns correctly. the difficulty is due to the component foreign key from AddressPreference.

With Hibernate using the criteria
List l = session.createCriteria(Address.class).add( Restrictions.idEq(304717)).list();

Code generated looks like this.
select
this_.ADDRESS_ID as ADDRESS1_1_1_,
this_.FIRST_NAME as FIRST2_1_1_,
this_.MIDDLE_NAME as MIDDLE3_1_1_,
this_.LAST_NAME as LAST4_1_1_,
this_.ADDR1 as ADDR5_1_1_,
this_.ADDR2 as ADDR6_1_1_,
this_.ADDR3 as ADDR7_1_1_,
this_.CITY as CITY1_1_,
this_.STATE as STATE1_1_,
this_.ZIP as ZIP1_1_,
this_.COUNTRY as COUNTRY1_1_,
this_.CREATED_DTM as CREATED12_1_1_,
addresspre2_.ADDRESS_ID as ADDRESS1_3_,
addresspre2_.PREFERENCE_TYPE_ID as PREFERENCE2_3_,
addresspre2_.ADDRESS_ID as ADDRESS1_2_0_,
addresspre2_.PREFERENCE_TYPE_ID as PREFERENCE2_2_0_,
addresspre2_.SOURCE_ID as SOURCE3_2_0_,
addresspre2_.CURRENT_STATUS as CURRENT4_2_0_,
addresspre2_.STATUS_CHANGE_DTM as STATUS5_2_0_,
addresspre2_.SUB_TYPE as SUB6_2_0_,
addresspre2_.CREATED_BY as CREATED7_2_0_,
addresspre2_.CREATED_DTM as CREATED8_2_0_,
addresspre2_.MODIFIED_BY as MODIFIED9_2_0_,
addresspre2_.MODIFIED_DTM as MODIFIED10_2_0_
from
clm.wcp_address this_
left outer join
clm.wcp_address_preference addresspre2_
on this_.ADDRESS_ID=addresspre2_.ADDRESS_ID
where
this_.ADDRESS_ID = ?

I tried to copy this as best as possible but the "as xxx" in the child table mappings is hard to understand how to map.

Hibernate version:
3.4

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Mar 25, 2009 4:02:23 PM by Hibernate Tools 3.2.4.CR1 -->
<hibernate-mapping package="com.test.dataobj">
<class name="Address" table="wcp_address" catalog="clm">
<id name="addressId" type="java.lang.Integer">
<column name="ADDRESS_ID" />
<!-- <generator class="org.hibernate.id.TableHiLoGenerator">-->
<generator class="hilo">
<param name="table">unq_aid</param>
<param name="column">next_high_val</param>
<param name="max_lo">100</param>
</generator>
</id>
<property name="firstName" type="string">
<column name="FIRST_NAME" length="50" not-null="true" />
</property>
<property name="middleName" type="string">
<column name="MIDDLE_NAME" length="50" />
</property>
<property name="lastName" type="string">
<column name="LAST_NAME" length="50" not-null="true" />
</property>
<property name="addr1" type="string">
<column name="ADDR1" length="100" not-null="true" />
</property>
<property name="addr2" type="string">
<column name="ADDR2" length="100" />
</property>
<property name="addr3" type="string">
<column name="ADDR3" length="100" />
</property>
<property name="city" type="string">
<column name="CITY" length="100" not-null="true" />
</property>
<property name="state" type="string">
<column name="STATE" length="10" not-null="true" />
</property>
<property name="zip" type="string">
<column name="ZIP" length="20" not-null="true" />
</property>
<property name="country" type="string">
<column name="COUNTRY" length="100" not-null="true" />
</property>
<property name="createdDtm" type="timestamp">
<column name="CREATED_DTM" length="19" not-null="true" />
</property>
<map name="addressPreferences" inverse="true" fetch="join" lazy="false">
<key column="ADDRESS_ID" not-null="true" />
<map-key type="long" column="PREFERENCE_TYPE_ID"/>
<one-to-many class="AddressPreference"/>
</map>
</class>
<sql-query name="findAddress">
<return alias="a" class="Address"/>
<return-join alias="i" property="a.addressPreferences"/>
select
a.ADDRESS_ID as {a.addressId},
a.FIRST_NAME as {a.firstName},
a.MIDDLE_NAME as {a.middleName},
a.LAST_NAME as {a.lastName},
a.ADDR1 as {a.addr1},
a.ADDR2 as {a.addr2},
a.ADDR3 as {a.addr3},
a.CITY as {a.city},
a.STATE as {a.state},
a.ZIP as {a.zip},
a.COUNTRY as {a.country},
a.CREATED_DTM as {a.createdDtm},
i.ADDRESS_ID,
i.PREFERENCE_TYPE_ID,
i.SOURCE_ID,
i.CURRENT_STATUS,
i.STATUS_CHANGE_DTM,
i.SUB_TYPE,
i.CREATED_BY,
i.CREATED_DTM,
i.MODIFIED_BY,
i.MODIFIED_DTM
from
clm.wcp_address a
left outer join
clm.wcp_address_preference i
on a.ADDRESS_ID=i.ADDRESS_ID
where
a.ADDRESS_ID =:addressId
</sql-query>

</hibernate-mapping>

<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Mar 25, 2009 4:02:23 PM by Hibernate Tools 3.2.4.CR1 -->
<hibernate-mapping package="com.test.dataobj">
<class name="AddressPreference" table="wcp_address_preference" catalog="clm">
<composite-id name="id" class="AddressPreferenceId" >
<key-property name="addressId" type="int" >
<column name="ADDRESS_ID" />
</key-property>
<key-property name="preferenceTypeId" type="long">
<column name="PREFERENCE_TYPE_ID" />
</key-property>
</composite-id>
<many-to-one name="SourceLookup" class="SourceLookup" fetch="select">
<column name="SOURCE_ID" not-null="true" />
</many-to-one>
<many-to-one name="Address" class="Address" update="false" insert="false" fetch="select" >
<column name="ADDRESS_ID" not-null="true" />
</many-to-one>
<many-to-one name="PreferenceType" class="PreferenceType" update="false" insert="false" fetch="select">
<column name="PREFERENCE_TYPE_ID" not-null="true" />
</many-to-one>
<property name="currentStatus" type="int">
<column name="CURRENT_STATUS" not-null="true" />
</property>
<property name="statusChangeDtm" type="timestamp">
<column name="STATUS_CHANGE_DTM" length="19" not-null="true" />
</property>
<property name="subType" type="string">
<column name="SUB_TYPE" length="1" />
</property>
<property name="createdBy" type="string">
<column name="CREATED_BY" length="30" not-null="true" />
</property>
<property name="createdDtm" type="timestamp">
<column name="CREATED_DTM" length="19" not-null="true" />
</property>
<property name="modifiedBy" type="string">
<column name="MODIFIED_BY" length="30" />
</property>
<property name="modifiedDtm" type="timestamp">
<column name="MODIFIED_DTM" length="19" />
</property>
</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
Query q = session.getNamedQuery("findAddress");
q.setParameter("addressId", 304717);

List l = q.list();
Address o = (Address) l.get(0);

Full stack trace of any exception that occurs:
SEVERE: Error in named query: findAddress
org.hibernate.QueryException: No column name found for property [id] for alias [i] [select
a.ADDRESS_ID as {a.addressId},
a.FIRST_NAME as {a.firstName},
a.MIDDLE_NAME as {a.middleName},
a.LAST_NAME as {a.lastName},
a.ADDR1 as {a.addr1},
a.ADDR2 as {a.addr2},
a.ADDR3 as {a.addr3},
a.CITY as {a.city},
a.STATE as {a.state},
a.ZIP as {a.zip},
a.COUNTRY as {a.country},
a.CREATED_DTM as {a.createdDtm},
i.ADDRESS_ID as {i.id},
i.PREFERENCE_TYPE_ID as {i.id},
i.SOURCE_ID,
i.CURRENT_STATUS,
i.STATUS_CHANGE_DTM,
i.SUB_TYPE,
i.CREATED_BY,
i.CREATED_DTM as {i.createdDtm},
i.MODIFIED_BY,
i.MODIFIED_DTM
from
clm.wcp_address a
left outer join
clm.wcp_address_preference i
on a.ADDRESS_ID=i.ADDRESS_ID
where
a.ADDRESS_ID =:addressId]
at org.hibernate.loader.custom.sql.SQLQueryParser.resolveCollectionProperties(SQLQueryParser.java:175)
at org.hibernate.loader.custom.sql.SQLQueryParser.substituteBrackets(SQLQueryParser.java:121)
at org.hibernate.loader.custom.sql.SQLQueryParser.process(SQLQueryParser.java:74)
at org.hibernate.loader.custom.sql.SQLCustomQuery.<init>(SQLCustomQuery.java:133)
at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:67)
at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:136)
at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:476)
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:384)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1327)
at com.wm.www.ppc.persistance.HibernateUtil.<clinit>(HibernateUtil.java:13)
at com.wm.www.ppc.test.PCC_App.execute(PCC_App.java:103)
at com.wm.www.ppc.test.PCC_App.main(PCC_App.java:97)
Exception in thread "main" java.lang.ExceptionInInitializerError
at com.wm.www.ppc.persistance.HibernateUtil.<clinit>(HibernateUtil.java:15)
at com.wm.www.ppc.test.PCC_App.execute(PCC_App.java:103)
at com.wm.www.ppc.test.PCC_App.main(PCC_App.java:97)
Caused by: org.hibernate.HibernateException: Errors in named queries: findAddress
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:397)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1327)
at com.wm.www.ppc.persistance.HibernateUtil.<clinit>(HibernateUtil.java:13)
... 2 more
Name and version of the database you are using:
MySQL 5.1

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html