I'm writing an application that has direct and readonly access to a legacy database which initially is used in Ruby on Rails app. The DB is huge, but here is part that I want to make a mapping for in Hibernate.
There is a table that has item_type field, which is one of item1, item2, item3
And there is also item_id field. It depends on the value of the item_type field. For example, if item_type is item1, then item_id means ID from item1 table.
The tables have no foreign keys and are not connected at all.
The question is, how do I make so that item1, item2 and item3 mapped if and only if the item_type is set to the corresponding value. For example, if item_type set to item2, then item2 field of Reference has reference to the item object and the other two (item1, item3) are null:
@Entity
@Getter
@StandardException
@NoArgsConstructor
@Table(name = "reference")
class Reference extends PanacheEntity {
@Column(name = "field_a")
private String fieldA;
@OneToOne(fetch = FetchType.LAZY)
private Item1 item1;
@OneToOne(fetch = FetchType.LAZY)
private Item2 item2;
@OneToOne(fetch = FetchType.LAZY)
private Item3 item3;
}
I tried to use @JoinColumnOrFormula + @JoinFormula annotation, but it does not seem to be suited for that. referencedColumnName must be the column of the item1 table. But I need to specify column from reference table.
@OneToOne(fetch = FetchType.LAZY)
@JoinColumnsOrFormulas({
@JoinColumnOrFormula(formula = @JoinFormula(value = "'item1'", referencedColumnName = "item_type")),
@JoinColumnOrFormula(column = @JoinColumn(name = "item_id", referencedColumnName = "id"))
})
private Item1 item1;
The other option I tried is @Where annotation, like this:
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "item_id", referencedColumnName = "id")
@Where(clause = "item_type = 'item1'")
private Item1 item1;
But hibernate still assumes that the clause is for the item table. It produces wrong sql, where item_type is being references from the wrong table, like this:
select <here goes field list>
from reference r
join item1 i1 on i1.id = r.item_id
and i1.item_type = 'item1' -- wrong! item_type column is in the reference table, not in item1
I tried also @Where(clause = "reference.item_type = 'item1'") but still no luck, it just ignores the annotation as if it was not there.
Any ideas? Is it doable at all?
