java JPA 或 Hibernate - 在不同类型的列上连接表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3096985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JPA or Hibernate - Joining tables on columns of different types
提问by user373614
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.
有没有办法告诉 Hibernate 在使用 to_char 连接到另一个表或相反地将 NUMBER 转换为 VARCHAR 时将列包装在 to_char 中？我有一种情况，我有一个表，其中包含一个 VARCHAR 类型的通用键列，该列存储另一个表的 Id，该表是一个数字。当 Hibernate 执行它生成的使用“=”来比较两列的 SQL 时，我收到一个 SQL 异常。
Thanks...
谢谢...
P.S. I know this is not ideal but I am stuck with the schema so I have to deal with it.
PS 我知道这并不理想，但我坚持使用模式，所以我必须处理它。
回答by Pascal Thivent
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):
这应该可以在多对一中使用公式。来自第5.1.22节。列和公式元素（上一个答案中也提到了解决方案）：
columnandformulaattributes 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>
column和formula属性甚至可以在同一属性或关联映射中组合以表达，例如，奇异的连接条件。<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):
带注释（如果您使用的是 Hibernate 3.5.0-Beta-2+，请参阅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:
或者也许检查@JoinColumnsOrFormula：
@ManyToOne
@JoinColumnsOrFormulas(
{ @JoinColumnOrFormula(formula=@JoinFormula(value="SUBSTR(product_idnf, 1, 3)", referencedColumnName="product_idnf")) })
@Fetch(FetchMode.JOIN)
private Product productFamily;
