java - Hibernate自定义外键

Question

我需要使用一个数据库，其中有 2 个包含一些自定义外键的表。在特定条件下它将引用一个字段，否则将引用另一个字段。

如何使用 Hibernate 对此进行注释？

这是表格。

---------------------------                      ---------------------------
|         customer        |                      |         address         |
---------------------------                      ---------------------------
| - id                    |                      | - address_code          |
| address_code            |                      | - customer_id           |
| primary_language_code   |                      | - language_code         |
| secondary_language_code |                      | ...                     |
| ...                     |                      |                         |
---------------------------                      ---------------------------

以 - 开头的字段是主键(的一部分)。外键如下:

customer join address on 
    (customer.id = address.customer_id
    and customer.address_code = address.address_code
    and (case when customer.primary_language_code <= 2 
              then customer.primary_language_code 
              else customer.secondary_language_code
         end) = address.COD_LAN)

以下是模型:

@Getter              \
@Setter              |   //lombok
@NoArgsConstructor   /
@Entity
@Table(name = "customer")
public class Customer{

    @Id
    @Column(name="id")
    private int id;

    @Column(name="primary_language_code")
    private int primaryLanguageCode;

    @Column(name="secondary_language_code")
    private int secondaryLanguageCode;

    ?
    private Address address;
}

@Getter              \
@Setter              |   //lombok
@NoArgsConstructor   /
@Entity
@Table(name = "address")
public class Address {

    @EmbeddedId
    private AddressId id;
}

@Getter              \
@Setter              |   //lombok
@NoArgsConstructor   /
@Embeddable
public class AddressId implements Serializable, Comparable<AddressId> {

    @Column(name = "customer_id")
    private int customerId;

    @NonNull
    @Column(name = "address_code")
    private String addressCode;

    ?
    private int languageCode;
}

编辑:

• 我尝试使用计算字段并从该字段映射。问题:如何映射这个计算字段？
• 我尝试使用@Where子句，但模型字段名称连接到sql查询字段customer0_.address_primary_language_code:

@Where(clause = "(customer.primary_language_code <= 2 then customer.primary_language_code else customer.secondary_language_code end) = address.COD_LAN)")

• 我尝试了其他一些方法，但也没有取得太大成功。

Answer 1

所以，我发现一些使用 SQL 注入(inject)的令人毛骨悚然的黑客解决了我的问题，但在某些情况下可能容易出现错误。

如果仅在 primary_language 上加入，代码将如下所示:

@OneToOne
@JoinColumns({@JoinColumn(name = "id", referencedColumnName = "customer_id"),
              @JoinColumn(name = "primary_language_code", referencedColumnName = "language_code"),
              @JoinColumn(name = "address_code", referencedColumnName = "address_code")})
private Address address;

@Column(name = "language_code", nullable = false)
private int languageCode;

Hibernate 将生成如下查询:

select customer0_.id as ...,
       customer0_.primary_language_code as ...,
       customer0_.secondary_language_code as ...,
       customer0_.address_code as ...

想法是更改名称值以注入(inject)sql代码:

@OneToOne
@JoinColumns({@JoinColumn(name = "id", referencedColumnName = "customer_id"),
              @JoinColumn(name = "id, case when customer.primary_language_code <= 2 " +
                                          "then customer.primary_language_code " +
                                          "else customer.secondary_language_code end",
                          referencedColumnName = "language_code"),
              @JoinColumn(name = "address_code", referencedColumnName = "address_code")})
private Address address;

生成的 SQL 查询将如下所示:

select customer0_.id as ...,
       customer0_.primary_language_code as ...,
       customer0_.secondary_language_code as ...,
       customer0_.id,                                    <--- sql injection here
       case
           when customer.primary_language_code <= 2 then customer.primary_language_code 
           else customer.secondary_language_code 
       end as ...,
       customer0_.address_code as ...

除非您需要在客户表上加入的另一个表中有主要语言或次要语言，否则这将起作用。

我很高兴找到一个更好的解决方案来解决这个问题，因为我真的不敢在生产中使用它。