How should I store an Java Enum in JavaDB?

Question

How should I store an Java Enum in JavaDB?

Should I try to map the enums to SMALLINT and keep the values in source code only? The embedded database is only used by a single application. Or should I just store the values as DECIMAL? None of these solutions feels good/robust for me. Is there any better alternatives?

Here is my enum:

import java.math.BigDecimal;

public enum Vat {
    NORMAL(new BigDecimal("0.25")),
    FOOD(new BigDecimal("0.12")),
    BOOKS(new BigDecimal("0.06")),
    NONE(new BigDecimal("0.00"));

    private final BigDecimal value;

    Vat(BigDecimal val) {
        value = val;
    }

    public BigDecimal getValue() {
        return value;
    }
}

I have read other similar questions on this topic, but the problem or solution doesn't match my problem. Enum storage in Database field, Best method to store Enum in Database, Best way to store enum values in database - String or Int

Answer 1

In JPA, you have two choices:

1. By name;

2. By ordinal (integer).

I don't like (2). If you change the order of your enum it breaks. As such it's more common to use (1) (in my experience).

I would do the same in JavaDB. Just store the enum name as text. It has the advantage that you can look at a row and know what it means rather than trying to figure out what "3" means in the status_id column.

If you're concerned about space (and 99% of the time I wouldn't be) either use an integer or use a code. For example:

public enum Vat {
  NORMAL(new BigDecimal("0.25")),
  FOOD(new BigDecimal("0.12")),
  BOOKS(new BigDecimal("0.06")),
  NONE(new BigDecimal("0.00"));

  private static final Map<String, Vat> LOOKUP = new HashMap<String, Vat>();

  static {
    Vat[] values = values();
    for (Vat vat : values) {
      LOOKUP.put(vat.code, vat);
    }
  }

  private final String code;
  private final String value;

  private Vat(String code, BigDecimal value) {
    this.code = code;
    this.value = value;
  }

  public String getCode() { return code; }
  public String getValue() { return value; }

  public Vat fromCode(String code) {
    return LOOKUP.get(code);
  }
}

Answer 2

My preference is to do as follows:

• Create a dedicated enum table in your database with columns: YourEnumId smallint, YourEnumName varchar(32).
• Within your business object table add foreign key references to the enum table.
• Implement your Java DAO to map enum values to database-specific smallint values when persisting data OR implement stored procedures that accept the enum name (i.e. varchar) and translate it into a smallint when writing the data.

Advantages

• Increased normalisation (and hence lower storage overhead) compared with storing the string value explcitly in your database table.
• Your database data will not be corrupted if your java enum definition changes (e.g. if you change the ordering of the values).
• Your DAO class can fail-fast during initialisation by checking that the Java enum definition matches the contents of the YourEnum table.
• You can provide views onto the database that return the String enum values (e.g. If you or a user wishes to query the table directly).

This is similar to cletus's solution except that the enum encoding is stored explicitly in the database rather than being defined as part of the enum definition.