What is the best way to save enums into a database?
I know Java provides name() and valueOf() methods to convert enum values into a String and back. But are there any other (flexible) options to store these values?
Is there a smart way to make enums into unique numbers (ordinal() is not safe to use)?
Update:
Thanks for all awesome and fast answers! It was as I suspected.
However a note to 'toolkit'; That is one way. The problem is that I would have to add the same methods to each Enum type I create. Thats a lot of duplicated code and, at the moment, Java does not support any solutions for this (a Java enum cannot extend other classes).
We never store enumerations as numerical ordinal values anymore; it makes debugging and support way too difficult. We store the actual enumeration value converted to string:
public enum Suit { Spade, Heart, Diamond, Club }
Suit theSuit = Suit.Heart;
szQuery = "INSERT INTO Customers (Name, Suit) " +
"VALUES ('Ian Boyd', %s)".format(theSuit.name());
and then read back with:
Suit theSuit = Suit.valueOf(reader["Suit"]);
The problem was in the past staring at Enterprise Manager and trying to decipher:
Name Suit
================== ==========
Shelby Jackson 2
Ian Boyd 1
verses
Name Suit
================== ==========
Shelby Jackson Diamond
Ian Boyd Heart
the latter is much easier. The former required getting at the source code and finding the numerical values that were assigned to the enumeration members.
Yes it takes more space, but the enumeration member names are short, and hard drives are cheap, and it is much more worth it to help when you're having a problem.
Additionally, if you use numerical values, you are tied to them. You cannot nicely insert or rearrange the members without having to force the old numerical values. For example, changing the Suit enumeration to:
public enum Suit { Unknown, Heart, Club, Diamond, Spade }
would have to become :
public enum Suit {
Unknown = 4,
Heart = 1,
Club = 3,
Diamond = 2,
Spade = 0 }
in order to maintain the legacy numerical values stored in the database.
The question comes up: lets say i wanted to order the values. Some people may want to sort them by the enum's ordinal value. Of course, ordering the cards by the numerical value of the enumeration is meaningless:
SELECT Suit FROM Cards
ORDER BY SuitID; --where SuitID is integer value(4,1,3,2,0)
Suit
------
Spade
Heart
Diamond
Club
Unknown
That's not the order we want - we want them in enumeration order:
SELECT Suit FROM Cards
ORDER BY CASE SuitID OF
WHEN 4 THEN 0 --Unknown first
WHEN 1 THEN 1 --Heart
WHEN 3 THEN 2 --Club
WHEN 2 THEN 3 --Diamond
WHEN 0 THEN 4 --Spade
ELSE 999 END
The same work that is required if you save integer values is required if you save strings:
SELECT Suit FROM Cards
ORDER BY Suit; --where Suit is an enum name
Suit
-------
Club
Diamond
Heart
Spade
Unknown
But that's not the order we want - we want them in enumeration order:
SELECT Suit FROM Cards
ORDER BY CASE Suit OF
WHEN 'Unknown' THEN 0
WHEN 'Heart' THEN 1
WHEN 'Club' THEN 2
WHEN 'Diamond' THEN 3
WHEN 'Space' THEN 4
ELSE 999 END
My opinion is that this kind of ranking belongs in the user interface. If you are sorting items based on their enumeration value: you're doing something wrong.
But if you wanted to really do that, i would create a Suits dimension table:
| Suit | SuitID | Rank | Color |
|------------|--------------|---------------|--------|
| Unknown | 4 | 0 | NULL |
| Heart | 1 | 1 | Red |
| Club | 3 | 2 | Black |
| Diamond | 2 | 3 | Red |
| Spade | 0 | 4 | Black |
This way, when you want to change your cards to use Kissing Kings New Deck Order you can change it for display purposes without throwing away all your data:
| Suit | SuitID | Rank | Color | CardOrder |
|------------|--------------|---------------|--------|-----------|
| Unknown | 4 | 0 | NULL | NULL |
| Spade | 0 | 1 | Black | 1 |
| Diamond | 2 | 2 | Red | 1 |
| Club | 3 | 3 | Black | -1 |
| Heart | 1 | 4 | Red | -1 |
Now we are separating an internal programming detail (enumeration name, enumeration value) with a display setting meant for users:
SELECT Cards.Suit
FROM Cards
INNER JOIN Suits ON Cards.Suit = Suits.Suit
ORDER BY Suits.Rank,
Card.Rank*Suits.CardOrder
If saving enums as strings in the database, you can create utility methods to (de)serialize any enum:
public static String getSerializedForm(Enum<?> enumVal) {
String name = enumVal.name();
// possibly quote value?
return name;
}
public static <E extends Enum<E>> E deserialize(Class<E> enumType, String dbVal) {
// possibly handle unknown values, below throws IllegalArgEx
return Enum.valueOf(enumType, dbVal.trim());
}
// Sample use:
String dbVal = getSerializedForm(Suit.SPADE);
// save dbVal to db in larger insert/update ...
Suit suit = deserialize(Suit.class, dbVal);
I would argue that the only safe mechanism here is to use the String name() value. When writing to the DB, you could use a sproc to insert the value and when reading, use a View. In this manner, if the enums change, there is a level of indirection in the sproc/view to be able to present the data as the enum value without "imposing" this on the DB.
I have faced the same issue where my objective is to persist Enum String value into database instead of Ordinal value.
To over come this issue, I have used @Enumerated(EnumType.STRING) and my objective got resolved.
For Example, you have an Enum Class:
public enum FurthitMethod {
Apple,
Orange,
Lemon
}
In the entity class, define @Enumerated(EnumType.STRING):
@Enumerated(EnumType.STRING)
@Column(name = "Fruits")
public FurthitMethod getFuritMethod() {
return fruitMethod;
}
public void setFruitMethod(FurthitMethod authenticationMethod) {
this.fruitMethod= fruitMethod;
}
While you try to set your value to Database, String value will be persisted into Database as "APPLE", "ORANGE" or "LEMON".
For a large database, I am reluctant to lose the size and speed advantages of the numeric representation. I often end up with a database table representing the Enum.
You can enforce database consistency by declaring a foreign key -- although in some cases it might be better to not declare that as a foreign key constraint, which imposes a cost on every transaction. You can ensure consistency by periodically doing a check, at times of your choosing, with:
SELECT reftable.* FROM reftable
LEFT JOIN enumtable ON reftable.enum_ref_id = enumtable.enum_id
WHERE enumtable.enum_id IS NULL;
The other half of this solution is to write some test code that checks that the Java enum and the database enum table have the same contents. That's left as an exercise for the reader.
You can use an extra value in the enum constant that can survive both name changes and resorting of the enums:
public enum MyEnum {
MyFirstValue(10),
MyFirstAndAHalfValue(15),
MySecondValue(20);
public int getId() {
return id;
}
public static MyEnum of(int id) {
for (MyEnum e : values()) {
if (id == e.id) {
return e;
}
}
return null;
}
MyEnum(int id) {
this.id = id;
}
private final int id;
}
To get the id from the enum:
int id = MyFirstValue.getId();
To get the enum from an id:
MyEnum e = MyEnum.of(id);
I suggest using values with no meaning to avoid confusion if the enum names have to be changed.
In the above example, I've used some variant of "Basic row numbering" leaving spaces so the numbers will likely stay in the same order as the enums.
This version is faster than using a secondary table, but it makes the system more dependent on code and source code knowledge.
To remedy that, you can set up a table with the enum ids in the database as well. Or go the other way and pick ids for the enums from a table as you add rows to it.
Sidenote: Always verify that you are not designing something that should be stored in a database table and maintained as a regular object though. If you can imagine that you have to add new constants to the enum at this point, when you are setting it up, that's an indication you may be better off creating a regular object and a table instead.
Unless you have specific performance reasons to avoid it, I would recommend using a separate table for the enumeration. Use foreign key integrity unless the extra lookup really kills you.
suit_id suit_name
1 Clubs
2 Hearts
3 Spades
4 Diamonds
player_name suit_id
Ian Boyd 4
Shelby Lake 2
suit_id) are independent from your enumeration value, which helps you work on the data from other languages as well.If saving enums as strings in the database, you can create utility methods to (de)serialize any enum:
public static String getSerializedForm(Enum<?> enumVal) {
String name = enumVal.name();
// possibly quote value?
return name;
}
public static <E extends Enum<E>> E deserialize(Class<E> enumType, String dbVal) {
// possibly handle unknown values, below throws IllegalArgEx
return Enum.valueOf(enumType, dbVal.trim());
}
// Sample use:
String dbVal = getSerializedForm(Suit.SPADE);
// save dbVal to db in larger insert/update ...
Suit suit = deserialize(Suit.class, dbVal);
Unless you have specific performance reasons to avoid it, I would recommend using a separate table for the enumeration. Use foreign key integrity unless the extra lookup really kills you.
suit_id suit_name
1 Clubs
2 Hearts
3 Spades
4 Diamonds
player_name suit_id
Ian Boyd 4
Shelby Lake 2
suit_id) are independent from your enumeration value, which helps you work on the data from other languages as well.If saving enums as strings in the database, you can create utility methods to (de)serialize any enum:
public static String getSerializedForm(Enum<?> enumVal) {
String name = enumVal.name();
// possibly quote value?
return name;
}
public static <E extends Enum<E>> E deserialize(Class<E> enumType, String dbVal) {
// possibly handle unknown values, below throws IllegalArgEx
return Enum.valueOf(enumType, dbVal.trim());
}
// Sample use:
String dbVal = getSerializedForm(Suit.SPADE);
// save dbVal to db in larger insert/update ...
Suit suit = deserialize(Suit.class, dbVal);
As you say, ordinal is a bit risky. Consider for example:
public enum Boolean {
TRUE, FALSE
}
public class BooleanTest {
@Test
public void testEnum() {
assertEquals(0, Boolean.TRUE.ordinal());
assertEquals(1, Boolean.FALSE.ordinal());
}
}
If you stored this as ordinals, you might have rows like:
> SELECT STATEMENT, TRUTH FROM CALL_MY_BLUFF
"Alice is a boy" 1
"Graham is a boy" 0
But what happens if you updated Boolean?
public enum Boolean {
TRUE, FILE_NOT_FOUND, FALSE
}
This means all your lies will become misinterpreted as 'file-not-found'
Better to just use a string representation
Multiple values with OR relation for one, enum field. The concept for .NET with storing enum types in database like a byte or an int and using FlagsAttribute in your code.
http://blogs.msdn.com/b/efdesign/archive/2011/06/29/enumeration-support-in-entity-framework.aspx
For a large database, I am reluctant to lose the size and speed advantages of the numeric representation. I often end up with a database table representing the Enum.
You can enforce database consistency by declaring a foreign key -- although in some cases it might be better to not declare that as a foreign key constraint, which imposes a cost on every transaction. You can ensure consistency by periodically doing a check, at times of your choosing, with:
SELECT reftable.* FROM reftable
LEFT JOIN enumtable ON reftable.enum_ref_id = enumtable.enum_id
WHERE enumtable.enum_id IS NULL;
The other half of this solution is to write some test code that checks that the Java enum and the database enum table have the same contents. That's left as an exercise for the reader.
We just store the enum name itself - it's more readable.
We did mess around with storing specific values for enums where there are a limited set of values, e.g., this enum that has a limited set of statuses that we use a char to represent (more meaningful than a numeric value):
public enum EmailStatus {
EMAIL_NEW('N'), EMAIL_SENT('S'), EMAIL_FAILED('F'), EMAIL_SKIPPED('K'), UNDEFINED('-');
private char dbChar = '-';
EmailStatus(char statusChar) {
this.dbChar = statusChar;
}
public char statusChar() {
return dbChar;
}
public static EmailStatus getFromStatusChar(char statusChar) {
switch (statusChar) {
case 'N':
return EMAIL_NEW;
case 'S':
return EMAIL_SENT;
case 'F':
return EMAIL_FAILED;
case 'K':
return EMAIL_SKIPPED;
default:
return UNDEFINED;
}
}
}
and when you have a lot of values you need to have a Map inside your enum to keep that getFromXYZ method small.
For a large database, I am reluctant to lose the size and speed advantages of the numeric representation. I often end up with a database table representing the Enum.
You can enforce database consistency by declaring a foreign key -- although in some cases it might be better to not declare that as a foreign key constraint, which imposes a cost on every transaction. You can ensure consistency by periodically doing a check, at times of your choosing, with:
SELECT reftable.* FROM reftable
LEFT JOIN enumtable ON reftable.enum_ref_id = enumtable.enum_id
WHERE enumtable.enum_id IS NULL;
The other half of this solution is to write some test code that checks that the Java enum and the database enum table have the same contents. That's left as an exercise for the reader.
Unless you have specific performance reasons to avoid it, I would recommend using a separate table for the enumeration. Use foreign key integrity unless the extra lookup really kills you.
suit_id suit_name
1 Clubs
2 Hearts
3 Spades
4 Diamonds
player_name suit_id
Ian Boyd 4
Shelby Lake 2
suit_id) are independent from your enumeration value, which helps you work on the data from other languages as well.We just store the enum name itself - it's more readable.
We did mess around with storing specific values for enums where there are a limited set of values, e.g., this enum that has a limited set of statuses that we use a char to represent (more meaningful than a numeric value):
public enum EmailStatus {
EMAIL_NEW('N'), EMAIL_SENT('S'), EMAIL_FAILED('F'), EMAIL_SKIPPED('K'), UNDEFINED('-');
private char dbChar = '-';
EmailStatus(char statusChar) {
this.dbChar = statusChar;
}
public char statusChar() {
return dbChar;
}
public static EmailStatus getFromStatusChar(char statusChar) {
switch (statusChar) {
case 'N':
return EMAIL_NEW;
case 'S':
return EMAIL_SENT;
case 'F':
return EMAIL_FAILED;
case 'K':
return EMAIL_SKIPPED;
default:
return UNDEFINED;
}
}
}
and when you have a lot of values you need to have a Map inside your enum to keep that getFromXYZ method small.
All my experience tells me that safest way of persisting enums anywhere is to use additional code value or id (some kind of evolution of @jeebee answer). This could be a nice example of an idea:
enum Race {
HUMAN ("human"),
ELF ("elf"),
DWARF ("dwarf");
private final String code;
private Race(String code) {
this.code = code;
}
public String getCode() {
return code;
}
}
Now you can go with any persistence referencing your enum constants by it's code. Even if you'll decide to change some of constant names, you always can save code value (e.g. DWARF("dwarf") to GNOME("dwarf"))
Ok, dive some more deeper with this conception. Here is some utility method, that helps you find any enum value, but first lets extend our approach.
interface CodeValue {
String getCode();
}
And let our enum implement it:
enum Race implement CodeValue {...}
This is the time for magic search method:
static <T extends Enum & CodeValue> T resolveByCode(Class<T> enumClass, String code) {
T[] enumConstants = enumClass.getEnumConstants();
for (T entry : enumConstants) {
if (entry.getCode().equals(code)) return entry;
}
// In case we failed to find it, return null.
// I'd recommend you make some log record here to get notified about wrong logic, perhaps.
return null;
}
And use it like a charm: Race race = resolveByCode(Race.class, "elf")
If saving enums as strings in the database, you can create utility methods to (de)serialize any enum:
public static String getSerializedForm(Enum<?> enumVal) {
String name = enumVal.name();
// possibly quote value?
return name;
}
public static <E extends Enum<E>> E deserialize(Class<E> enumType, String dbVal) {
// possibly handle unknown values, below throws IllegalArgEx
return Enum.valueOf(enumType, dbVal.trim());
}
// Sample use:
String dbVal = getSerializedForm(Suit.SPADE);
// save dbVal to db in larger insert/update ...
Suit suit = deserialize(Suit.class, dbVal);
You can use an extra value in the enum constant that can survive both name changes and resorting of the enums:
public enum MyEnum {
MyFirstValue(10),
MyFirstAndAHalfValue(15),
MySecondValue(20);
public int getId() {
return id;
}
public static MyEnum of(int id) {
for (MyEnum e : values()) {
if (id == e.id) {
return e;
}
}
return null;
}
MyEnum(int id) {
this.id = id;
}
private final int id;
}
To get the id from the enum:
int id = MyFirstValue.getId();
To get the enum from an id:
MyEnum e = MyEnum.of(id);
I suggest using values with no meaning to avoid confusion if the enum names have to be changed.
In the above example, I've used some variant of "Basic row numbering" leaving spaces so the numbers will likely stay in the same order as the enums.
This version is faster than using a secondary table, but it makes the system more dependent on code and source code knowledge.
To remedy that, you can set up a table with the enum ids in the database as well. Or go the other way and pick ids for the enums from a table as you add rows to it.
Sidenote: Always verify that you are not designing something that should be stored in a database table and maintained as a regular object though. If you can imagine that you have to add new constants to the enum at this point, when you are setting it up, that's an indication you may be better off creating a regular object and a table instead.
Unless you have specific performance reasons to avoid it, I would recommend using a separate table for the enumeration. Use foreign key integrity unless the extra lookup really kills you.
suit_id suit_name
1 Clubs
2 Hearts
3 Spades
4 Diamonds
player_name suit_id
Ian Boyd 4
Shelby Lake 2
suit_id) are independent from your enumeration value, which helps you work on the data from other languages as well.I would argue that the only safe mechanism here is to use the String name() value. When writing to the DB, you could use a sproc to insert the value and when reading, use a View. In this manner, if the enums change, there is a level of indirection in the sproc/view to be able to present the data as the enum value without "imposing" this on the DB.
As you say, ordinal is a bit risky. Consider for example:
public enum Boolean {
TRUE, FALSE
}
public class BooleanTest {
@Test
public void testEnum() {
assertEquals(0, Boolean.TRUE.ordinal());
assertEquals(1, Boolean.FALSE.ordinal());
}
}
If you stored this as ordinals, you might have rows like:
> SELECT STATEMENT, TRUTH FROM CALL_MY_BLUFF
"Alice is a boy" 1
"Graham is a boy" 0
But what happens if you updated Boolean?
public enum Boolean {
TRUE, FILE_NOT_FOUND, FALSE
}
This means all your lies will become misinterpreted as 'file-not-found'
Better to just use a string representation
I would argue that the only safe mechanism here is to use the String name() value. When writing to the DB, you could use a sproc to insert the value and when reading, use a View. In this manner, if the enums change, there is a level of indirection in the sproc/view to be able to present the data as the enum value without "imposing" this on the DB.
I have faced the same issue where my objective is to persist Enum String value into database instead of Ordinal value.
To over come this issue, I have used @Enumerated(EnumType.STRING) and my objective got resolved.
For Example, you have an Enum Class:
public enum FurthitMethod {
Apple,
Orange,
Lemon
}
In the entity class, define @Enumerated(EnumType.STRING):
@Enumerated(EnumType.STRING)
@Column(name = "Fruits")
public FurthitMethod getFuritMethod() {
return fruitMethod;
}
public void setFruitMethod(FurthitMethod authenticationMethod) {
this.fruitMethod= fruitMethod;
}
While you try to set your value to Database, String value will be persisted into Database as "APPLE", "ORANGE" or "LEMON".
As you say, ordinal is a bit risky. Consider for example:
public enum Boolean {
TRUE, FALSE
}
public class BooleanTest {
@Test
public void testEnum() {
assertEquals(0, Boolean.TRUE.ordinal());
assertEquals(1, Boolean.FALSE.ordinal());
}
}
If you stored this as ordinals, you might have rows like:
> SELECT STATEMENT, TRUTH FROM CALL_MY_BLUFF
"Alice is a boy" 1
"Graham is a boy" 0
But what happens if you updated Boolean?
public enum Boolean {
TRUE, FILE_NOT_FOUND, FALSE
}
This means all your lies will become misinterpreted as 'file-not-found'
Better to just use a string representation
For a large database, I am reluctant to lose the size and speed advantages of the numeric representation. I often end up with a database table representing the Enum.
You can enforce database consistency by declaring a foreign key -- although in some cases it might be better to not declare that as a foreign key constraint, which imposes a cost on every transaction. You can ensure consistency by periodically doing a check, at times of your choosing, with:
SELECT reftable.* FROM reftable
LEFT JOIN enumtable ON reftable.enum_ref_id = enumtable.enum_id
WHERE enumtable.enum_id IS NULL;
The other half of this solution is to write some test code that checks that the Java enum and the database enum table have the same contents. That's left as an exercise for the reader.
We just store the enum name itself - it's more readable.
We did mess around with storing specific values for enums where there are a limited set of values, e.g., this enum that has a limited set of statuses that we use a char to represent (more meaningful than a numeric value):
public enum EmailStatus {
EMAIL_NEW('N'), EMAIL_SENT('S'), EMAIL_FAILED('F'), EMAIL_SKIPPED('K'), UNDEFINED('-');
private char dbChar = '-';
EmailStatus(char statusChar) {
this.dbChar = statusChar;
}
public char statusChar() {
return dbChar;
}
public static EmailStatus getFromStatusChar(char statusChar) {
switch (statusChar) {
case 'N':
return EMAIL_NEW;
case 'S':
return EMAIL_SENT;
case 'F':
return EMAIL_FAILED;
case 'K':
return EMAIL_SKIPPED;
default:
return UNDEFINED;
}
}
}
and when you have a lot of values you need to have a Map inside your enum to keep that getFromXYZ method small.
All my experience tells me that safest way of persisting enums anywhere is to use additional code value or id (some kind of evolution of @jeebee answer). This could be a nice example of an idea:
enum Race {
HUMAN ("human"),
ELF ("elf"),
DWARF ("dwarf");
private final String code;
private Race(String code) {
this.code = code;
}
public String getCode() {
return code;
}
}
Now you can go with any persistence referencing your enum constants by it's code. Even if you'll decide to change some of constant names, you always can save code value (e.g. DWARF("dwarf") to GNOME("dwarf"))
Ok, dive some more deeper with this conception. Here is some utility method, that helps you find any enum value, but first lets extend our approach.
interface CodeValue {
String getCode();
}
And let our enum implement it:
enum Race implement CodeValue {...}
This is the time for magic search method:
static <T extends Enum & CodeValue> T resolveByCode(Class<T> enumClass, String code) {
T[] enumConstants = enumClass.getEnumConstants();
for (T entry : enumConstants) {
if (entry.getCode().equals(code)) return entry;
}
// In case we failed to find it, return null.
// I'd recommend you make some log record here to get notified about wrong logic, perhaps.
return null;
}
And use it like a charm: Race race = resolveByCode(Race.class, "elf")
As you say, ordinal is a bit risky. Consider for example:
public enum Boolean {
TRUE, FALSE
}
public class BooleanTest {
@Test
public void testEnum() {
assertEquals(0, Boolean.TRUE.ordinal());
assertEquals(1, Boolean.FALSE.ordinal());
}
}
If you stored this as ordinals, you might have rows like:
> SELECT STATEMENT, TRUTH FROM CALL_MY_BLUFF
"Alice is a boy" 1
"Graham is a boy" 0
But what happens if you updated Boolean?
public enum Boolean {
TRUE, FILE_NOT_FOUND, FALSE
}
This means all your lies will become misinterpreted as 'file-not-found'
Better to just use a string representation
Multiple values with OR relation for one, enum field. The concept for .NET with storing enum types in database like a byte or an int and using FlagsAttribute in your code.
http://blogs.msdn.com/b/efdesign/archive/2011/06/29/enumeration-support-in-entity-framework.aspx
I would argue that the only safe mechanism here is to use the String name() value. When writing to the DB, you could use a sproc to insert the value and when reading, use a View. In this manner, if the enums change, there is a level of indirection in the sproc/view to be able to present the data as the enum value without "imposing" this on the DB.
Source: Stackoverflow.com