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

Hibernate version: 3.1rc2

Hi,

Are there any examples of complex usage of <load-collection> and <return-join> ? In the v3 documentation I see that they are marked as TODO and I did not see any Junit test cases that use these mappings either.

Thank you,

Calin

im pretty sure we have test for those in the h3.1 testsuite.

Found them,

Thanks!

Calin Medianu

an example of load-collection from the JUnit Test cases is:

<set name="employments"
inverse="true"
order-by="DUMMY">
<key column="employer"/> <!-- only needed for DDL generation -->
<one-to-many class="Employment"/>
<loader query-ref="organizationEmployments"/>
</set>
.............................
<sql-query name="organizationEmployments">
<load-collection alias="empcol"
role="Organization.employments"/>
SELECT {empcol.*}
FROM EMPLOYMENT empcol
WHERE EMPLOYER = :id
ORDER BY STARTDATE ASC, EMPLOYEE ASC
</sql-query>

It looks to me like the key column is used not only for DDL Generation, but it is part of the generated SQL for collection loading (it gets selected from the destination table of the association).

Should the key column not be ignored when using custom collection loaders? In other words, <key column="NoSuchColumn"/> should work as well in the test case.

Thanks again,

Calin

well for this example {xx.*} is used, meaning it will be in the sql.

Hi,

The question was why does the column attribute of the key tag end up in the generated SQL? It is not because of the {xx.*} , if I enter <key column="NoSuchColumn"/> the generated SQL will contain NoSuchColumn and will result in an error.

In the hibernate testcases, I think the file /test/org/hibernate/test/sql/Employment.hbm.xml should be modified as follows:

INSTEAD OF:

<set name="employments"
inverse="true"
order-by="DUMMY">
<key column="employer"/> <!-- only needed for DDL generation -->
<one-to-many class="Employment"/>
<loader query-ref="organizationEmployments"/>
</set>

CHANGED TO:

<set name="employments"
inverse="true"
order-by="DUMMY">
<key column="NO_SUCH_COLUMN"/> <!-- we have to put it here because of the way the XML Schema is defined -->
<one-to-many class="Employment"/>
<loader query-ref="organizationEmployments"/>
</set>

Then the test will fail, because the generated SQL will select NO_SUCH_COLUMN from EMPLOYMENT. There is no need for a foreign key since the collection is loaded with the custom query.

Thank you,

Calin

what generated sql are you referring too ?

I am referring to the SQL that gets generated by Hibernate when we try to load the Employments set. This SQL will look something like:

SELECT empcol.NO_SUCH_COLUMN as NO35_0__, empcol.ID as ID1_0__ ... etc.

and will break. At least this is what it's doing in my code.

Thanks!

Calin

again - that is because of the usage of {xx.*}.
use specific columns if there is something you dont want in there.

Oh, I see what you mean.

However, Don't you think that {empcol.*} should expand only to the columns that are actually listed in the maping document for EMPLOYMENT, and that the column "NO_SUCH_COLUMN" should not be in the SQL? We only put NO_SUCH_COLUMN in the mapping because the DTD forces us to have a <key> inside of <set> even if it's useless.

What do you think should be inside of the mandatory element <key > inside of a set that is custom loaded, if there is no foreign key relationship? If I just put <key/>, it gets replaces with ID, and the SQL breaks again. If I just pick a column that exists, the set ends up empty, I don't know why. The only case when I actually get something in the set is when there is a foregn key relation between the tables and I put the foreign key in there. In other words I can use the custom SQL only to narrow down the selection.


Thanks,

Calin

well hibernate needs some key to look up by in sql. There must be some key to bind them together.

Hi Max,

Sorry to keep going on about this.

Below is the mapping again. I don't think Hibernate "needs some key to look up by in sql" because how to retrieve the objects is specified in the <sql-query>. The key just gets in the way, so it should be optional or ignored.

From hibernate-3.1\test\org\hibernate\test\sql\Employment.hbm.xml:

<set name="employments"
inverse="true"
order-by="DUMMY">
<key column="NOT_NEEDED"/>
<one-to-many class="Employment"/>
<loader query-ref="organizationEmployments"/>
</set>

<sql-query name="organizationEmployments">
<load-collection alias="empcol" role="Organization.employments"/>
SELECT {empcol.*}
FROM EMPLOYMENT empcol
WHERE EMPLOYER = :id
ORDER BY STARTDATE ASC, EMPLOYEE ASC
</sql-query>

I thought the whole point of <load-collection> was so we can define a <set> where there is no foreign key to define the association, just some arbitrary join SQL.

Thanks again,

Calin

no the whole point is you can have your native sql there. and yes, you dont need a physical column, but there need to be a column in the result that allows hibernate to tie it back to the owning entity.

Hello Max,

Are you saying that it is not possible to have a Set of employments in the organization unless employment has a foreign key to organization? That is, a mapping such as below would never work? I am assuming that some_pl_sql_function is a function that joins a bunch of tables together and does some calculation and that there is no foreign key between organization and employment.

<sql-query name="organizationEmployments">
<load-collection alias="empcol" role="Organization.employments"/>
SELECT {empcol.*}
FROM EMPLOYMENT empcol
WHERE some_pl_sql_function(empcol.id) = :id
</sql-query>

Thank you,

Calin

no - i said the output resultset should just have that information; the underlying tables does not need that info.