Hibernate – Join by none primary key columns


In a good database design the references (foreign keys) are based on the primary key.

Hibernate assume that, so by default hibernate will do the join based on the entity id (primary key).


 

When you write join in SQL you define the join keys.

For example:

Select * from Customers

Left join Products ON Customers.CustomerId = Products.FkCustomerId


 

In hibernate you don't need to define the join keys (assume join by primary key), so the HQL will look like this:

From Customers C left Join C.Products


 

Hibernate will use the definition of the one-to-many to figure out the column in the Product table and it will join it with the id field of the Customer.


 

What about cases when the join is not based on the CustomerId?

Suppose we have a legacy database where the product join with the customer is based on the customer externalid and not on the customer pk, how we tell hibernate to join on customer external id and not by customer pk?


 

The solution is pretty simple - we use in property-ref.

<set name="Products" table="Products" fetch="select">

    <key column="FkCustomerexternalId" property-ref="CustomerExternalId" />

    <one-to-many class="Product" />

</set>

The column - 'FkCustomerId' is the foreign key in the Products table.

The property ref 'customerExternalId' is the name of the field (not column) in the Customer Pojo.


 

Let's make it more complex:

What if the join is by multiple fields?

Suppose the relation is based on two columns - CustomExternalId and CustomerName how do we tell hibernate to the join based on the two columns?

During my Google search for a solution I encounter in the following link:

https://forum.hibernate.org/viewtopic.php?p=2315103


 


 

The solution suggested by one of the expert is to group the keys properties into properties section, like this:

<properties name="keysNeededByB">

<property name="column1" column="C1" insert="false" update="false"/>

<property name="column2" column="C2" insert="false" update="false"/>

<property name="column3" column="C3" insert="false" update="false"/>

</properties>


 

<bag name="relatedMappings">

<key property-ref="keysNeededByB"/>

<one-to-many class="B"/>

</bag>


 

But, unfortunately all suggestions weren't work for me.

My work solution:

<component name="CustomerKey" class="CustomerKey">

<property name="CustomerexternalId" column="CustomerexternalId" />

<property name="CustomerName" column="CustomerName" />

</component>


 

<set name="Products" lazy="true">

<key property-ref="CustomerKey">

<!-- this is the column names in the product table -->

    <column name="CustomerExternalId" />

    <column name="CustomerName" />

</key>

<one-to-many class="Product" />

</set>


 

אין תגובות:

הוסף רשומת תגובה