JPA & enum table (aka "one true lookup table")

Question

Problem

With no SQL enumeration type, unfortunately, the somewhat popular database design pattern seems to create one table for all enumeration values (thanks for the link, Nathan). I have seen many variations of this over the years, but the one I am facing right now looks something like this:

ID | ENUM | VALUE -----+-------------+---------- 1 | DAY_OF_WEEK | SUNDAY 2 | DAY_OF_WEEK | MONDAY ... 7 | DAY_OF_WEEK | SATURDAY ... 18 | PERSON_TYPE | EMPLOYEE 19 | PERSON_TYPE | MANAGER 

which is then used like this - for example, in the face table:

  ID | NAME | TYPE ----+----------+------ 1 | Jane Doe | 19 

Meaning that Jane is a manger, because 19 is the primary key of personalization of the manager type in the enumeration table.

Question

Using JPA (2.1), is there an elegant way to map this construct to Java propper Java?

Important: There are many versions of my "enum" table in different versions with different primary key values, that is, the "manager" can sometimes be line # 19, but sometimes line No. 231. However, the values ​​never change at run time. Unfortunately, changing the database schema is also not an option, but using the proprietary features of any JPA provider will be an option.

What worked

I really found a solution that worked, but it was too bad for me:

 public enum PersonType { EMPLOYEE, MANAGER } @Table(name="PERSONS") @Entity public class Persons { @Id @Column(name="ID") long id; @Column(name="NAME") String name; @Convert(converter = PtConv.class) @Column(name="TYPE") PersonType type; // ... } @Converter public class PtConv implements AttributeConverter<PersonType, Integer> { // In a static initializer run a JDBC query to fill these maps: private static Map<Integer, PersonType> dbToJava; private static Map<PersonType, Integer> javaToDb; @Override public Integer convertToDatabaseColumn(PersonType attribute) { return javaToDb.get(attribute); } @Override public PersonType convertToEntityAttribute(Integer dbData) { return dbToJava.get(dbData); } } 

I would live with this if CDI were available in @Converter , but with a static construct was a nightmare.

Answer 1

For reference, here is how I solved the problem. I would prefer the correct Java listings, and I will answer any answer that is better than this.

 @Table(name="PERSONS") @Entity public class Persons { @Id @Column(name="ID") long id; @Column(name="NAME") String name; @Column(name="TYPE") BaseEnum type; // known to be "PersonTypeEnum" public PersonType getType() { switch(type.getValue()) { case "EMPLOYEE": return PersonType.EMPLOYEE; case "MANAGER": return PersonType.MANAGER; } throw new IllegalStateException(); } public void setType(PersonTypeEnum type) { this.type = type; } // ... } @Entity @Inheritance @DiscriminatorColumn(name="ENUM") @Table(name="ENUMS") public abstract class BaseEnum { @Id private int id; @Column(name="VALUE") String value; // ... } @Entity @DiscriminatorValue("PERSON_TYPE") public class PersonTypeEnum extends BaseEnum { } 

Thus, the receivers and setters for enumeration values ​​are of different types, and to set the value, a reference to an object is required, which further explodes the code.

Answer 2

You can use the old fashioned approach:

 public final class EnumMapping { ... // define & load singleton data public int getId(Enum<?> e) { ... } public <E extends Enum<E>> E valueOf(int id, Class<E> strictCheck) { ... return strictCheck.cast(result); } } @Table(name="PERSONS") @Entity public class Persons { @Id @Column(name="ID") long id; @Column(name="NAME") String name; @Column(name="TYPE") int typeId; public void setPersonType(PersonType type) { this.typeId = EnumMapping.getInstance().getId(type); } public PersonType getPersonType() { return EnumMapping.getInstance().valueOf(typeId, PersonType.class); } } 

So, you can (a) use java enumerations to get / set values ​​and (b) initialize the mapping at the specific moment you want.