Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.12

Mapping documents:

tableA.hbm.xml
==========
<hibernate-mapping>
<class name="com.temp.TableA" table="TableA" schema="dbo" catalog="temp">
<id name="pkA" type="string">
<column name="pkA" length="10" />
<generator class="assigned" />
</id>
<property name="colA1" type="string">
<column name="colA1" length="10" />
</property>
<property name="colA2" type="string">
<column name="colA2" length="10" />
</property>
<property name="colA3" type="string">
<column name="colA3" length="10" />
</property>
<set name="tableAblnks" inverse="true">
<key>
<column name="fkA" length="10" not-null="true" />
</key>
<one-to-many class="com.temp.TableAblnk" />
</set>
</class>
</hibernate-mapping>

TableB.hbm.xml
==============
<hibernate-mapping>
<class name="com.temp.TableB" table="TableB" schema="dbo" catalog="temp">
<id name="pkB" type="string">
<column name="pkB" length="10" />
<generator class="assigned" />
</id>
<property name="colB1" type="string">
<column name="colB1" length="10" />
</property>
<property name="colB2" type="string">
<column name="colB2" length="10" />
</property>
<set name="tableAblnks" inverse="true">
<key>
<column name="fkB" length="10" not-null="true" />
</key>
<one-to-many class="com.temp.TableAblnk" />
</set>
</class>
</hibernate-mapping>

TableABLNK.hbm.xml
==============
<hibernate-mapping>
<class name="com.temp.TableAblnk" table="TableABLNK" schema="dbo" catalog="temp">
<composite-id name="id" class="com.temp.TableAblnkId">
<key-property name="fkA" type="string">
<column name="fkA" length="10" />
</key-property>
<key-property name="fkB" type="string">
<column name="fkB" length="10" />
</key-property>
</composite-id>
<many-to-one name="tableB" class="com.temp.TableB" update="false" insert="false" fetch="select">
<column name="fkB" length="10" not-null="true" />
</many-to-one>
<many-to-one name="tableA" class="com.temp.TableA" update="false" insert="false" fetch="select">
<column name="fkA" length="10" not-null="true" />
</many-to-one>
<property name="col1" type="string">
<column name="col1" length="10" />
</property>
<property name="col2" type="string">
<column name="col2" length="10" />
</property>
</class>
</hibernate-mapping>

I have a stored procedure that is as follows on SQLSERVER:
CREATE PROCEDURE dbo.A_B_Select
(
@pkB uniqueidentifier = null
)
AS
SET NOCOUNT ON;SELECT
a.colA1,
a.colA2,
ab.fkA,
ab.fkB,
ab.col1,
ab.col2

FROM
TableABLNK ab

INNER JOIN TableA a on ab.fkA = a.pkA

WHERE (ab.fkB = @pkB) OR (@pkB is null)

ORDER By a.colA1

I am not very clear on how to map my return properties for ab.fkA etc.,

How does the <sql-query> for the above procedure look ?

Thanks

try:
<sql-query name="mySqlQuery">
<return-scalar column="COLA1" type="string"/>
<return-scalar column="colA2" type="long"/>
<return-scalar column="fkA" type="long"/>
{? = myProc()}
</sql-query>

Then in your java code call w/ something like:
Query q = sess.getNamedQuery("mySqlQuery");
Object obj[] = null;
java.util.Iterator itr = q.list().iterator();
while (itr != null && itr.hasNext())
{
obj = (Object []) itr.next();
//cast the object items to the correct type.
System.out.println("obj=" + obj[0]); //first col
System.out.println("obj=" + obj[1]); //next col
System.out.println("obj=" + obj[2]); //last col
}

You may need to say
Select a.ColA1 as COLA1,
..etc.

a.colA1,
a.colA2,
ab.fkA,
ab.fkB,
ab.col1,
ab.col2

I'd like to be able to do somethign like this
<sql-query name="">
<return alias="a" class="TableA" >
<return-property name="colA1" column="COLA1" />
<return-property name="colA2" column="COLA2" />
</return>

<sql-query name="">
<return alias="ab" class="TableABLnk" >
<return-property name="id.fkA" column="COLA1" />
<return-property name="id.fkB" column="COLA2" />
<return-property name="col1" column="COL1" />
</return>

1. I'm getting an error indicating that it cant find property "id" in class TableALnk. It does exist as a property but it is a composite key and so is of type TableAblnkId which is a primary key calss for TableABLnk.

2. I'd like to know if I can ask only for certain fields to be mapped to the result from a query..Or do I HAVE TO HAVE ALL the persistent properties of a mapped class specified in <return-property> tags, when returning results from a query?

Thankyou

I have not done what you are considering doing so I can't say for sure, but I do know this...

I gave you example of returning scalar values for a reason -- they weren't associated with any hibernate managed objects... You can get scalars back and then reconstruct your objects...

If you want to to initialize objects, and if it works the way you are doing it... then I'd suspect that you have to return all the cols for that Entity, as mapped in your xml file.

I would also consider what you'd get back in terms of column names in a normal JDBC invocation of this procedure and make sure they mapped in your "column=" attribute. Off hand I don't think the "alias" thing will not work, since that is used on the preprocessing of the SQL before it goes to the database - in other words, it will use alias to determine up front what columns it expects to return.

Again these are just educated guesses... that I hope help you.