Hibernate version: 3.1.3

Hello all,

Working with a legacy database, I am supposed to map a a couple of direct JDBC queries
into Hibernate mappings. The mapping overhead will be measured.
I don't want to negatively affect the results just because I didn't made the right mappings :

Here it is the (simplified) query :

One of the queries I need to translate is:
[code]
"SELECT client.noclient, client.lang, address.ct_code FROM client "
+ "LEFT OUTER JOIN address ON client.addrcode = address.code AND address.type=1 WHERE client.noclient = ?";


The problem I have is trying to use

[code]
public InfoClient getInfoClient (String aClientId) {
InfoClient infoClient = session.get("the_client");
[/code]

The generated query doesn't want to do the join on the addrcode (foreign key) column ;
it always joins on the primary of the first table:

Hibernate version 3.1.3

Result 1.

[code]
select infoclient0_.noclient as noclient0_0_,
infoclient0_.langue as langue0_0_,
infoclient0_1_.ct_code as ct_code1_0_
from client infoclient0_ inner join address infoclient0_1_
on infoclient0_.noclient=infoclient0_1_.addrcode
where infoclient0_.noclient=?
[/code]

Ideally I wouldn't have to modify my return classes to retrieve the 3 fields I'm interessed in :clientId, languageId, countryId.
But just to test I added the addressId field in the InfoClient (see Mappings 2) and specified in my join
the property-ref = "addressId"

Still no joy.
Result 2

[code]
select infoclient0_.noclient as noclient0_0_,
infoclient0_.langue as langue0_0_,
infoclient0_.addrcode as addrcode0_0_,
infoclient0_1_.ct_code as ct_code1_0_
from client infoclient0_ inner join address infoclient0_1_
on infoclient0_.noclient=infoclient0_1_.addrcode
where infoclient0_.noclient=?
[/code]


Mappings 1.
[code]
public class InfoClient {

private String clientId;
private String languageId;
private String countryId;
}
[/code]


[code]
<hibernate-mapping>
<class
name="InfoClient"
table="client"
dynamic-update="false"
dynamic-insert="false"
lazy="false"
>
<id
name="clientId"
column="noclient"
type="java.lang.String"
length="12"
>
</id>
<property
name="languageId"
type="java.lang.String"
column="langue"
not-null="true"
length="1"
/>
<property
name="addressId"
type="java.lang.String"
column="addrcode"
not-null="true"
length="10"
/>
<join table="address">
<key column="addrcode"/>
<property
name="countryId"
type="java.lang.String"
column="ct_code"
not-null="true"
length="2"
/>
</join>
</class>
</hibernate-mapping>
[/code]

Mappings 2.

[code]
public class InfoClient {

private String clientId;
private String languageId;
private String countryId;
// added to serves in the mapping
private String addressId;
}
[/code]

[code]
<hibernate-mapping>
<class
name="ClientInfo"
table="client"
dynamic-update="false"
dynamic-insert="false"
lazy="false"
>
<id
name="clientId"
column="noclient"
type="java.lang.String"
length="12"
>
</id>
<property
name="languageId"
type="java.lang.String"
column="langue"
not-null="true"
length="1"
/>
<property
name="addressId"
type="java.lang.String"
column="addrcode"
not-null="true"
length="10"
/>
<join table="address">
<key column="addrcode" property-ref="addressId"/>
<property
name="countryId"
type="java.lang.String"
column="ct_code"
not-null="true"
length="2"
/>
<join>
</class>
</hibernate-mapping>

Try putting optional="true" on the <join> tag. This should allow addressId to be null, and use an outer join for retrieval.

Hello frusso,

You were right for the left outer join

Setting the optional="true"


did indeed generate a left outer join as you said.

But the join is still done on the primary key of first table (noclient) instead of the foreign-key specified by the property-ref

Result :
left outer join address infoclient0_1_ on infoclient0_.noclient=infoclient0_1_.addrcode

Hmm. Looks right, at least assuming that "addrcode" is now the name of the property (it seems to have changed from the earlier mapping docs you posted)? Just for kicks, try using the same name for the property and the database column, so there's no confusion. But that being said, that still wouldn't explain the SQL you posted.

I found a discussion that suggests there might be a bug in this:
http://blog.exadel.com/?p=16
See especially the exchange in the "Comments" section at the bottom, from comments 14 to 16.

Does anybody have successful examples of this?

Seems to be exactly my case.

I'll try to do a many-to-one. One of my objectives was to not have different classes that I'll later need to manually map to my return type InfoClient.
But it seems I don't have much of a choice.

I would have liked being able to
- to specify my foreign-key without having to create a property so I may use it in property-ref
- to actually take into account the property-ref

One other possibility I see is to make the address my first table and do the
<join> on the client table with the key addrcode.

Making address the first table would make the join work, but it doesn't fit with address being the optional part of the object.

I confirmed this for myself, using Hibernate 3.1.2:



The HQL query "from Foo" generates SQL:


...ignoring the property-ref in the join condition. Gotta be a bug.

This is a known issue
http://opensource.atlassian.com/projects/hibernate/browse/HHH-551
http://opensource.atlassian.com/projects/hibernate/browse/HHH-1829