JPA Lookup Hierarchy Mapping
- by Andy Trujillo
Given a lookup table:
| ID | TYPE | CODE | DESCRIPTION |
| 1 | ORDER_STATUS | PENDING | PENDING DISPOSITION |
| 2 | ORDER_STATUS | OPEN | AWAITING DISPOSITION |
| 3 | OTHER_STATUS | OPEN | USED BY OTHER ENTITY |
If I have an entity:
@MappedSuperclass @Table(name="LOOKUP")
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name="TYPE", discriminatorType=DiscriminatorType.STRING)
public abstract class Lookup {
@Id @Column(name="ID") int id;
@Column(name="TYPE") String type;
@Column(name="CODE") String code;
@Column(name="DESC") String description;
...
}
Then a subclass:
@Entity @DiscriminatorValue("ORDER_STATUS")
public class OrderStatus extends Lookup {
}
The expectation is to map it:
@Entity @Table(name="ORDERS")
public class OrderVO {
...
@ManyToOne
@JoinColumn(name="STATUS", referencedColumnName="CODE")
OrderStatus status;
...
}
So that the CODE is stored in the database. I expected that if I wrote:
OrderVO o = entityManager.find(OrderVO.class, 123);
the SQL generated using OpenJPA would look something like:
SELECT t0.id, ..., t1.CODE, t1.TYPE, t1.DESC
FROM ORDERS t0
LEFT OUTER JOIN LOOKUP t1 ON
t0.STATUS = t1.CODE AND t1.TYPE = 'ORDER_STATUS' WHERE
t0.ID = ?
But the actual SQL that gets generated is missing the
AND t1.TYPE = 'ORDER_STATUS'
This causes a problem when the CODE is not unique. Is there a way to have the discriminator included on joins or am I doing something wrong here?
