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 that I create. That’s a lot of duplicated code and, at the moment, Java does not support any solutions for this (a Java enum cannot extend other classes).
Advertisement
Answer
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 ------------ ---- Kylie Guénin 2 Ian Boyd 1
verses
Name Suit ------------ ------- Kylie Guénin 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.
How to sort them 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