Hibernate - Is there a way to join 2 columns against 1?

Question

I'm developing webapp using Spring & Hibernate.

Table 1: BaseTable

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| Id         | bigint(20)   | NO   | PRI |         | auto_increment |
| Serial1    | varchar(255) | YES  |     | NULL    |                |
| Serial2    | varchar(255) | YES  |     | NULL    |                |
| ModelNum   | varchar(255) | YES  |     | NULL    |                |
| ...        | ....         | ..   | 0   |         |                |
+------------+--------------+------+-----+---------+----------------+ 

Table 2: DetailTable

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| Id1        | varchar(20)  | NO   | PRI |         |                |
| Id2        | varchar(20)  | NO   | PRI |         |                |
| Id3        | varchar(20)  | NO   | PRI |         |                |
| Serial     | varchar(255) | YES  |     | NULL    |                |
| ...        | ....         | ..   | 0   |         |                |
+------------+--------------+------+-----+---------+----------------+

I need join the tables based on serials. The Serial in Table2 may contain values from either Serial1 or Serial2 from Table1 so it should compare like an OR operator. I'm using hbm.xml for tables. No annotation mapping. I've joined tables like:

<one-to-one name="notes"
    class="Notes" entity-name="Notes">
</one-to-one>

I was using this Query before:

SELECT A.* FROM Table2 As a INNER JOIN Table1 As b 
ON (a.Serial = b.Serial1 or a.Serial = b.Serial2);

I went through this http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/associations.html but only 1 key column is being used.

How do I join using HBM.XML for this scenario? Is it possible?

Answer 1

Solution 1

Create a database view on the Table1 which exposes foreign key referencing Table2. Project the foreign key from your posted query which you will use for the view anyway. Then map your entity to the view.

Solution 2

Use join formula:

For example, in the entity mapped to Table1 define the many-to-one association with the entity mapped to Table2 (seems to be your use case):

@ManyToOne
@JoinColumnsOrFormulas({
      @JoinColumnOrFormula(formula=@JoinFormula(value="(SELECT t2.serial FROM Table2 t2 WHERE serial1 = t2.serial OR serial2 = t2.serial)", referencedColumnName="serial"))
    })
private Entity2 entity2;

However, join formulas seem to be very fragile in Hibernate for the time being (I managed to make this work only for many-to-one association and I had to make Entity2 implement Serializable; otherwise it did not work and threw some strange NullPointer- and ClassCastExceptions).

Answer 2

You need to use native query for this purpose

String sql = "SELECT A.* FROM Table2 As a INNER JOIN Table1 As b "
           + " ON (a.Serial = b.Serial1 or a.Serial = b.Serial2);";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Table2.class);
List<Table2> tableContent  = query.list();