How can I model a composite key where one of the columns can be NULL?
I'm not crazy, just bear with me. It's a legacy schema.
My best idea so far is to modify Hibernate to support a new column annotation, call it "@AllowsComparableNulls", that forces any equality comparisons on it to be mapped in SQL as
Code:
tableA.column = tableB.column OR
tableA.column IS NULL AND
tableB.column IS NULL
Why the heck would I do this, you ask? I am building a user interface for an existing business process. I want to model it like this: a Policy can be associated with either a Business, or a Division (within a business). What I want is for both Business and Division to be subclasses of PolicyTarget.
Here's the rub: I am working against a legacy schema which is denormalized and has poor representation of business entities. Business and Division are conflated in the same table and keyspace (there is a Business table, a Division table and a DivisionXBusiness table, and I believe they all have the same number of records). Each Business implicitly defines a Division.
In the Policy, there are foreign key columns for both Business and Division. If a policy is targeted at a division, the Business column is populated with the Business primary key, and the Division column is NULL. If a policy is targeted at a division, the Business column is populated with the primary key of the business that owns the division, and the Division column is populated with the primary key of the division itself. If you happen to target the implicit division for a business, both columns are populated with the same value!
My UI will only be used to create Policy objects (and one other object with the same relationships, which I'll not mention again), so I can afford to access Businesses and Divisions through views that I define. But management will not let me write to a new Policy table and replace the old one with an isomorphic view. So I need a way to persist my Policy entities in that crazy table.
What I want is an inheritance relationship, with a PolicyTarget entity that is subclassed by Business and Division, and with a bidirectional one to many relationships with Policy. I attempt to use a composite key called ExoticKey:
Code:
@Entity
@Inheritance(strategy=InheritanceType.JOINED)
@DiscriminatorColumn(name="targetType")
class PolicyTarget {
@Id
ExoticKey id
@OneToMany
List<Policy> policies;
}
class ExoticKey {
Long businessId;
Long divisionId;
}
class Business extends PolicyTarget {
String name;
}
class Division extends PolicyTarget {
String description;
@ManyToOne
Business parent;
}
class Policy {
@Id
Long id;
@ManyToOne
PolicyTarget target;
}
I tried this and it seemed to work. But then, I think while I was doing something with SQL, I wrote a query that tried to select a Business. And of course for a Business, the division id is NULL. So I tried to compare NULL to NULL. And I learned a valuable lesson :-)
I'm pretty sure it happened in my application also, but I can't quite remember. In any case, at that point, I started redesigning tables, and now I have run afoul of management as described above and must use the legacy Policy table.
What can I do to allow Hibernate to work with me? It seems inevitable that I will have to compare NULL to NULL.
Can anyone give me a hint on how to do this, or a better idea?
Login
FAQ
Search
