JPA或Hibernate - 在不同类型的列上连接表 [英] JPA or Hibernate - Joining tables on columns of different types

问题描述

有没有办法告诉Hibernate在使用它连接到另一个表时将一个列封装到to_char中，或者相反地将一个NUMBER转换为VARCHAR？我有一种情况，我有一个表，其中包含一个VARCHAR类型的通用键列，其中存储另一个表中的数字，该数字是一个数字。当Hibernate执行它生成的使用'='比较两列的SQL时，我收到一个SQL异常。

Is there a way to tell Hibernate to wrap a column in a to_char when using it to join to another table or conversely convert a NUMBER to a VARCHAR? I have a situation where I have a table which contains a generic key column of type VARCHAR which stores the Id of another table which is a Number. I am getting a SQL exception when Hibernate executes the SQL it generates which uses '=' to compare the two columns.

谢谢...

PS我知道这并不理想，但我坚持使用模式，所以我必须处理它。

P.S. I know this is not ideal but I am stuck with the schema so I have to deal with it.

推荐答案

这应该可以使用多对一的公式。从部分 5.1.22。列和公式元素（解决方法也在此上一个答案中提到）：

This should be possible using a formula in your many-to-one. From section 5.1.22. Column and formula elements (solution also mentioned in this previous answer):

列和公式属性
甚至可以在相同的
属性或关联映射中加入到
快递中，例如，异国情调的加入
条件。

column and formula attributes can even be combined within the same property or association mapping to express, for example, exotic join conditions.

<many-to-one name="homeAddress" class="Address"
        insert="false" update="false">
    <column name="person_id" not-null="true" length="10"/>
    <formula>'MAILING'</formula>
</many-to-one>

使用注释（如果您使用的是Hibernate 3.5.0-Beta -2+，请参阅 HHH-4382 ）：

With annotations (if you are using Hibernate 3.5.0-Beta-2+, see HHH-4382):

@ManyToOne
@Formula(value="( select v_pipe_offerprice.offerprice_fk from v_pipe_offerprice where v_pipe_offerprice.id = id )")
public OfferPrice getOfferPrice() { return offerPrice; } 

或者检查 @JoinColumnsOrFormula ：

Or maybe check the @JoinColumnsOrFormula:

@ManyToOne
@JoinColumnsOrFormulas(
{ @JoinColumnOrFormula(formula=@JoinFormula(value="SUBSTR(product_idnf, 1, 3)", referencedColumnName="product_idnf")) })
@Fetch(FetchMode.JOIN)
private Product productFamily;

这篇关于JPA或Hibernate - 在不同类型的列上连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！