user20298 user20298 - 6 months ago 19
Java Question

Ways to save enums in database

I am wondering what the best ways to save enums into a database is.

I know there are

name()
and
valueOf()
methods to make it into a String back. But are there any other (flexible) options to store these values?

Is there a smart way to make them into unique numbers (
ordinal()
is not safe to use)?

Any comments and suggestions would be helpful :)

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 with each enum type i create. Thats a lot of duplicated code and, at the moment, Java does not support any solutions to this (You cannot let enum extend other classes).

However, thanks for all answers!

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
==================  ==========
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.