Hello,

I have a question concerning mapping results of hibernate SQL queries to objects that have relations to Other objects (one to one, one to many etc.). Lets assume that I have to objects that are mapped to different tables and their relation is one-to-one (both related rows have the same primary key).

public class Debt
{
private Integer id;
private String firstName;
private String name;
private Double amount;
private DebtRefData debtRefData;
}

public class DebtRefData
{
private Integer id;
private String refCity;
private String refZip;
private String refNumber;
}

(Of course I have defined appropriate getters and setters)
The mapping of these objects is defined as following:

<hibernate-mapping>
<class name="zoo.ko.debt.Debt" table="debt" schema="pay.dbo">
<id name="id" column="id" type="java.lang.Integer">
<generator class="native"/>
</id>
<property type="java.lang.String" name="firstName" column="firstName"/>
<property type="java.lang.String" name="name" column="name"/>
<property type="java.lang.Double" name="amount" column="amount"/>
<one-to-one
name="debtRefData"
class="zoo.ko.debt.DebtRefData"
cascade="save-update"
outer-join="true"
/>
</class>
</hibernate-mapping>

<hibernate-mapping>
<class name="zoo.ko.debt.DebtRefData" table="debtCourtRef" schema="pay.dbo">
<id name="id" column="cr_debt_id" type="java.lang.Integer">
<generator class="native"/>
</id>
<property type="java.lang.String" name="refZip" column="cr_zip"/>
<property type="java.lang.String" name="refCity" column="cr_city"/>
<property type="java.lang.String" name="refNumber" column="cr_court_ref_no"/>
</class>
</hibernate-mapping>


I want to create SQL query for object Debt that will fetch debt (and related DevrRefData) in ONE sql fetch. I know that I can use HQL queries for it but because of some other reasons I must write it as a SQL query.
My question is: how to write the sql query that will do it (fetch both objects in one sql call)? The actual question is how to specify sql mapping for related objects. Here is my attempt - unfortunately it doesn't work in hibernate 2.1.2 and 2.1.3.

<sql-query name="Debt:Query2">
<return alias="d" class="zoo.ko.debt.Debt"/>
select d.id as {d.id}, d.amount as {d.amount}, d.firstName as {d.firstName}, d.name as {d.name}, dc.cr_zip as {d.debtRefData.zip}, dc.cr_city as {d.debtRefData.city}, dc.cr_court_ref_no as {d.debtRefData.refNumber}
from pay.dbo.debt d
left outer join pay.dbo.debtCourtRef dc on (d.id=dc.cr_debt_id)
where d.id=:id
</sql-query>

My query generates an error regarding this part: dc.cr_zip as {d.debtRefData.zip}, dc.cr_city as {d.debtRefData.city}, dc.cr_court_ref_no as {d.debtRefData.refNumber}


java.io.IOException: net.sf.hibernate.QueryException Alias [d.debtRefData] does not correspond to any of the supplied return aliases = {[d]} [
select d.id as {d.id}, d.amount as {d.amount}, d.firstName as {d.firstName}, d.name as {d.name}, dc.cr_zip as {d.debtRefData.zip}, dc.cr_city as {d.debtRefData.city}, dc.cr_court_ref_no as {d.debtRefData.refNumber}
from pay.dbo.debt d
left outer join pay.dbo.debtCourtRef dc on (d.id=dc.cr_debt_id)
where d.id=:id
]


Thank you very much in advance for any help,

Kind regards,

Krzysztof