休眠 - 有没有办法加入2列1？ [英] Hibernate - Is there a way to join 2 columns against 1?

问题描述

我正在使用Spring&休眠。

表1：BaseTable

  + ------ ------ + -------------- + ------ + ------ + --------- + ----- ----------- + 
 |字段|类型|空| Key |默认|额外| 
 + ------------ + -------------- + ------ + ----- + ---- ----- + ---------------- + 
 | Id | bigint（20）| NO | PRI | | auto_increment | 
 | Serial1 | varchar（255）|是| | NULL | | 
 | Serial2 | varchar（255）|是| | NULL | | 
 | ModelNum | varchar（255）|是| | NULL | | 
 | ... | .... | .. | 0 | | | 
 + ------------ + -------------- + ------ + ----- + ---- ----- + ---------------- + 
  

<表2：DetailTable

  + ------------ + ------ -------- + ------ + ----- + --------- + ---------------- + 
 |字段|类型|空| Key |默认|额外| 
 + ------------ + -------------- + ------ + ----- + ---- ----- + ---------------- + 
 | Id1 | varchar（20）| NO | PRI | | | 
 | Id2 | varchar（20）| NO | PRI | | | 
 | Id3 | varchar（20）| NO | PRI | | | 
 |串行| varchar（255）|是| | NULL | | 
 | ... | .... | .. | 0 | | | 
 + ------------ + -------------- + ------ + ----- + ---- ----- + ---------------- + 
  

<我需要根据连续剧加入表格。 Table2 中的 Serial 可能包含 Serial1 或 Serial2 来自 Table1 所以它应该像OR运算符那样比较。我为表使用hbm.xml。没有注释映射。我已经加入了如下表格：

 <一对一名称=笔记
 class =注释entity-name =注释> 
< / one-to-one> 
  

我之前使用过此查询：

  SELECT A. * FROM Table2作为INNER JOIN Table1作为b 
 ON（a.Serial = b.Serial1或a.Serial = b.Serial2）; 
  

我经历了这个 http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/associations.html ，但只有1个关键列是正在使用。

如何在这种情况下使用HBM.XML加入？是否有可能？

解决方案

解决方案1 ​​

在 Table1 上创建数据库视图，该视图公开外键引用 Table2 。从您发布的查询中投影外键，您将使用该外键作为视图。然后将您的实体映射到视图。

解决方案2 使用加入公式：

例如，在映射到 Table1 的实体中，定义与映射到 Table2 的实体的多对一关联。 code>（似乎是你的用例）：



  @ManyToOne 
 @JoinColumnsOrFormulas（{
 @JoinColumnOrFormula（formula = @ JoinFormula（value =（SELECT t2.serial FROM Table2 t2 WHERE serial1 = t2.serial OR serial2 = t2.serial），referencedColumnName =serial））
}）
私人实体2实体2; 
  

然而，连接公式在Hibernate中似乎非常脆弱（我设法做到这一点只适用于多对一的关联，并且我必须让 Entity2 实现 Serializable ;否则它不起作用，扔了一些奇怪的 NullPointer - 和 ClassCastException s）。

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?

解决方案

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

这篇关于休眠 - 有没有办法加入2列1？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！