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?

Example: T1.Serial1 contains A, T1.Serial2 Contains B Table2 has two rows one for Serial A, one for Serial B? What is your expected results? You likely want to join to the same table twice and alias your results, but I could be wrong... `Select * from table1 T1 LEFT JOIN table2 T2a on T1.Serial1 = T2a.Serial LEFT JOIN table2 T2b on T1.Serial = T2b.Serial` This will combine table 2 into table 1 as one row. Using an OR you would get multiple rows per table1 ID... which maybe you want... thus need to see expected results. — xQbert, Aug 14 '15 at 14:46
@xQbert : I've added the Query I'm using. I want to replace the Query with Java code meant for Spring/Hibernate — Viginesh, Aug 14 '15 at 14:53

score 1 · Answer 1 · answered Aug 14 '15 at 15:16

1

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();

answered Aug 14 '15 at 15:16

Shirish Bathe

627
9
22

I'm currently using the very same code. Just that I wanted to replace Query with objects. Is there no way to join 2 columns vs 1 using hbm.xml? – Viginesh Aug 14 '15 at 15:23

Dragan Bozanovic · Accepted Answer · 2015-08-16T08:32:10.267

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).

Although I've not used annotations and all mappings are based on xml, this seems like a viable option. Thanks for the answer. — Viginesh, Aug 18 '15 at 15:26

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

2 Answers2

Linked