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

对不起,标题不好,我的问题不好总结。

我有两个相关实体:

为了保护敏感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中的逻辑:

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的期望太高了吗?我应该将查询分成更易于管理和可行的部分,还是可以使用hibernate?