Ways to save enums in database

Question

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

User · Answer

If saving enums as strings in the database  you can create utility methods to  de serialize any enum      public static String getSerializedForm Enum lt   gt  enumVal            String name   enumVal name               possibly quote value          return name             public static  lt E extends Enum lt E gt  gt  E deserialize Class lt E gt  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

User · Answer

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

User · Answer

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  lt T extends Enum  amp  CodeValue gt  T resolveByCode Class lt T gt  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

User · Answer

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    gt  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

User · Answer

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   Suits table   suit id suit name 1       Clubs 2       Hearts 3       Spades 4       Diamonds   Players table  player name suit id Ian Boyd           4 Shelby Lake        2    If you ever refactor your enumeration to be classes with behavior  such as priority   your database already models it correctly Your DBA is happy because your schema is normalized  storing a single integer per player  instead of an entire string  which may or may not have typos   Your database values  suit id  are independent from your enumeration value  which helps you work on the data from other languages as well

User · Answer

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

User · Answer

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

User · Answer

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    gt  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

User · Answer

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

User · Answer

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

User · Answer

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  lt T extends Enum  amp  CodeValue gt  T resolveByCode Class lt T gt  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

User · Answer

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

User · Answer

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   Suits table   suit id suit name 1       Clubs 2       Hearts 3       Spades 4       Diamonds   Players table  player name suit id Ian Boyd           4 Shelby Lake        2    If you ever refactor your enumeration to be classes with behavior  such as priority   your database already models it correctly Your DBA is happy because your schema is normalized  storing a single integer per player  instead of an entire string  which may or may not have typos   Your database values  suit id  are independent from your enumeration value  which helps you work on the data from other languages as well

User · 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   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

User · Answer

If saving enums as strings in the database  you can create utility methods to  de serialize any enum      public static String getSerializedForm Enum lt   gt  enumVal            String name   enumVal name               possibly quote value          return name             public static  lt E extends Enum lt E gt  gt  E deserialize Class lt E gt  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

User · 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   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

User · Answer

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

User · Answer

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

User · Answer

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   Suits table   suit id suit name 1       Clubs 2       Hearts 3       Spades 4       Diamonds   Players table  player name suit id Ian Boyd           4 Shelby Lake        2    If you ever refactor your enumeration to be classes with behavior  such as priority   your database already models it correctly Your DBA is happy because your schema is normalized  storing a single integer per player  instead of an entire string  which may or may not have typos   Your database values  suit id  are independent from your enumeration value  which helps you work on the data from other languages as well

User · Answer

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

User · Answer

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

User · Answer

If saving enums as strings in the database  you can create utility methods to  de serialize any enum      public static String getSerializedForm Enum lt   gt  enumVal            String name   enumVal name               possibly quote value          return name             public static  lt E extends Enum lt E gt  gt  E deserialize Class lt E gt  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

User · Answer

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

User · Answer

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   Suits table   suit id suit name 1       Clubs 2       Hearts 3       Spades 4       Diamonds   Players table  player name suit id Ian Boyd           4 Shelby Lake        2    If you ever refactor your enumeration to be classes with behavior  such as priority   your database already models it correctly Your DBA is happy because your schema is normalized  storing a single integer per player  instead of an entire string  which may or may not have typos   Your database values  suit id  are independent from your enumeration value  which helps you work on the data from other languages as well

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If saving enums as strings in the database  you can create utility methods to  de serialize any enum      public static String getSerializedForm Enum lt   gt  enumVal            String name   enumVal name               possibly quote value          return name             public static  lt E extends Enum lt E gt  gt  E deserialize Class lt E gt  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

User · 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   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

User · Answer

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

User · Answer

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

User · 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   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

User · Answer

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    gt  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

User · Answer

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

User · Answer

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    gt  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

User · Answer

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

[java] Ways to save enums in database

Examples related to java

Examples related to database

Examples related to enums