java - 具有多列连接引用的 JoinColumn。引用 * from * 的外键具有错误的列数

Question

抱歉标题尴尬，没有简单的方法来总结我的问题。

我有 2 个相关实体：

真实数据已被修改以保护敏感IP，但问题是一样的。所以尽量不要被不完全有意义的领域分心。

# Permission
+----------------+--------------+
|    Column      |     type     |
+----------------+--------------+
| perm_id        | Number(20,0) |
| item_id1_id2   | VARCHAR2(8)  |
| date_code      | VARCHAR2(6)  |
+----------------+--------------+

# Item
+-----------------+-------------+
|    Column       |    type     |
+-----------------+-------------+
| id1             | VARCHAR2(4) |
| id2             | VARCHAR2(4) |
| date_code       | VARCHAR2(6) |
| some_data_field | VARCHAR(20) |
+-----------------+-------------+

Permission与 有@ManyToOne关系Item。通过以下 SQL 中的逻辑Permission链接：Item

SELECT p.*, i.*
FROM Permission p
JOIN (
    SELECT
        id1 || id2 as joined_ids,  -- the p.item_id1_id2 is 2 CONCATed columns to Item.id1 and Item.id2
        effective_date_code, -- this column specifies WHEN this data is effective by, i.e. all date codes for permissions between this date and not including the next greatest date should link to this record.
        some_data_field, -- and arbitrary data point that gives this object its usefulness.
        rank() over (partition by id1, id2 order by effective_date_code DESC) max_date_code -- this essentially gives us the 
    FROM Item
--    where   effective_date_code <= p.date_code
    ORDER BY max_date_code
) i
ON i.max_date_code = 1
and p.item_id1_id2 = i.joined_ids
;

如您所见，连接相当复杂，到目前为止，我与 Hibernate 的 API 争论的尝试都没有结果。请注意，这些高度依赖于无法承受架构更改的遗留表，因此这是不可能的。

我尝试使用@JoinColumnsOrFormulas注释和相关：

public class Permission {
    // ...

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumnsOrFormulas(value = {
            @JoinColumnOrFormula(formula = @JoinFormula(value = "item_id1_id2", referencedColumnName = "id1 || id2")),
            @JoinColumnOrFormula(column = @JoinColumn(name = "date_code", referencedColumnName = "effective_date_code")) // This isn't the final thing, but just for testing purposes I'm linking like this.
    })
    public Subject subject;
}

但我收到投诉：

java.lang.RuntimeException: org.hibernate.AnnotationException: A Foreign key 
\ refering com.example.Item from com.example.Permission has the wrong number 
\ of column. should be 3...

我是否对 ORM 期望过高，是否应该将查询拆分为更易于管理和可行的部分，或者这是否可以使用休眠？

标签： javasqloraclehibernate-5.x