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

问题描述

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

谢谢...

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

解决方案

这应该可以在多对一中使用公式.来自 5.1.22 部分.列和公式元素(此之前的答案中也提到了解决方案):><块引用>

column 和 formula 属性可以甚至可以组合在同一个属性或关联映射到表达，例如，异国情调的加入条件.

<column name="person_id" not-null="true" length="10"/><公式>'邮寄'</公式></多对一>

带注释(如果您使用的是 Hibernate 3.5.0-Beta-2+，请参阅 HHH-4382):

@ManyToOne@Formula(value="( 从 v_pipe_offerprice 中选择 v_pipe_offerprice.offerprice_fk 其中 v_pipe_offerprice.id = id )")public OfferPrice getOfferPrice() { return offerPrice;}

或者检查@JoinColumnsOrFormula:

@ManyToOne@JoinColumnsOrFormulas({ @JoinColumnOrFormula(formula=@JoinFormula(value="SUBSTR(product_idnf, 1, 3)", referencedColumnName="product_idnf")) })@Fetch(FetchMode.JOIN)私人产品产品系列；

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.

Thanks...

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

解决方案

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>

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; } 

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屋！