Hi Folks,

I've been using hibernate for a little while but I've got an issue with a many-to-one relationship I have, so here goes:

I have two tables, one is a relationship table and one is a data table, the first one inbound_messages has a pk called inbound_message_id. This is linked to subscription_addresses through inbound_message_id. My Mappings look like this :

<hibernate-mapping>
<class
name="com.flytxt.data.hibernate.autogen.InboundMessage"
table="IN_MSGS_V"
>

<id
name="messageId"
type="java.lang.Long"
column="INBOUND_MESSAGE_ID"
>
<generator class="sequence">
<param name="sequence">INBOUND_ACTIONS_SEQ</param>
</generator>
</id>

<property
name="senderNumber"
type="java.lang.String"
column="SENDER_NUMBER"
not-null="true"
unique="false"
length="250"
/>
..... <Some poperties> .....
</class>
</hibernate-mapping>

<hibernate-mapping>
<class
name="com.flytxt.data.hibernate.autogen.SubscriptionAddress"
table="SUBSCRIPTION_ADDRESSES_V"
>

<composite-id>
<key-property column="USER_ADDRESS_ID" name="userAddressId" type="java.lang.Long"/>
<key-property column="DELIVERY_TYPE_ID" name="deliveryTypeId" type="java.lang.Long"/>
</composite-id>
...... <some properties> ......
<many-to-one
name="inboundMessage"
class="com.flytxt.data.hibernate.autogen.InboundMessage"
>
<column name="INBOUND_MESSAGE_ID"/>
</many-to-one>
</class>


I think that I should be able to do the following query and it should
populate the SubscriptionAddress object with an InboundMessage, joining
on inbound_message_id.

Query query = s.createQuery("from SubscriptionAddress adr, InboundMessage inb where adr.channelId = 2716 and adr.userId = 11848718");

in the hope that the query gets the correct row and the mapping sorts out the join. Problem is the SQL generated by Hibernate while containing inbound_messages doesn't join them correctly leaving me with a big cartesian join, each table containing in the order of 25 million rows. The SQL is as follows:

SELECT
subscripti0_.USER_ADDRESS_ID as USER_ADD1_0_,
subscripti0_.DELIVERY_TYPE_ID as DELIVERY2_0_,
inboundmes1_.INBOUND_MESSAGE_ID as INBOUND_1_1_,
subscripti0_.USER_ID as USER_ID0_,
subscripti0_.CHANNEL_ID as CHANNEL_ID0_,
subscripti0_.CREATION_DATE as CREATION5_0_,
subscripti0_.INBOUND_MESSAGE_ID as INBOUND_6_0_,
inboundmes1_.SENDER_NUMBER as SENDER_N2_1_,
inboundmes1_.MESSAGE as MESSAGE1_,
inboundmes1_.USER_ID as USER_ID1_
FROM
SUBSCRIPTION_ADDRESSES_V subscripti0_,
IN_MSGS_V inboundmes1_
WHERE
(subscripti0_.CHANNEL_ID=2716 )and
(subscripti0_.USER_ID=11848718 )

Note how there are no join criteria inboundmes1_ hence the cartesian product.

I know I'm probably doing something dim in hibernate, I just can't tell you what. BTW, I also created a corrosponding one to many relationship that was lazily loaded in attempt to provide a bit more information to the mapping layer but that didn't make any difference to the SQL that was output.

Save me from my own stupidity,
Max

Read the Hibernate documentation to learn how to express an association join in HQL.