CODEWITHSUNDEEP
javaormjpa-2.1
Tilo
Tilo

Reputation: 3325

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

Problem

In lack of a SQL enum type an unfortunately somewhat popular database design pattern seems to be to create one table for all enum values (thank you for the link, Nathan). I have seen many variations of this over the years, but the one that I'm currently struggling with 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 a table of persons:

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

Meaning that Jane is a manger because 19 is the primary key of the person type "manager" in the enum table.

Question

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

Important: There are many versions of my "enum table" out in the wild with different primary key values, i.e. "manager" might sometimes be row #19, but sometimes row #231. However, the values never change at runtime. Changing the database schema is unfortunately also not an option, but using proprietary features of any JPA provider would be an option.

What worked

I actually found a solution that worked but that was too hacky for my liking:

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 have lived with this if CDI was available in @Converters - but with the static construct testing was a nightmare.

Upvotes: 9

Views: 7889

Answers (2)

Tilo
Tilo

Reputation: 3325

For reference this is how I solved the problem. Proper Java enums would be my preference, and I will accept 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 { }

So the getters and the setters for enum values have different types, and setting the value requires having a reference to the entity which further blows up the code.

Upvotes: 1

ursa
ursa

Reputation: 4601

You can use the old-fashion 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 enums to get/set values and (b) initialize mapping at the specific moment you want.

Upvotes: 0

Related Questions