Java 在数据库中保存枚举的方法

Ways to save enums in database

提问by user20298

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?

我知道Java提供name()和valueOf()方法来枚举值转换为字符串和背部。但是还有其他（灵活的）选项来存储这些值吗？

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

有没有一种聪明的方法可以将枚举变成唯一的数字（ordinal()使用起来不安全）？

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

但是，请注意“工具包”；那是一种方式。问题是我必须向我创建的每个 Enum 类型添加相同的方法。这是很多重复的代码，目前，Java 不支持任何解决方案（Java 枚举不能扩展其他类）。

采纳答案by Ian Boyd

We neverstore 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 Hymanson      2
Ian Boyd            1

verses

诗句

Name                Suit
==================  ==========
Shelby Hymanson      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:

此外，如果您使用数字值，您将与它们联系在一起。您不能很好地插入或重新排列成员而不必强制使用旧的数值。例如，将 Suit 枚举更改为：

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 Suitsdimension table:

但如果你真的想这样做，我会创建一个Suits维度表：

| 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 KingsNew Deck Orderyou can change it for display purposes without throwing away all your data:

这样，当您想更改卡片以使用Kissing KingsNew Deck Order 时，您可以更改它以用于显示目的，而不会丢弃所有数据：

| 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

回答by oxbow_lakes

I would argue that the only safe mechanism here is to use the String name()value. When writing to the DB, you coulduse 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.

我认为这里唯一的安全机制是使用 Stringname()值。写入数据库时​​，您可以使用 sproc 插入值，读取时使用视图。以这种方式，如果枚举更改，则 sproc/视图中存在一定程度的间接性，以便能够将数据呈现为枚举值，而不会将其“强加”到数据库上。

回答by toolkit

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?

但是如果你更新 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

最好只使用字符串表示

回答by JeeBee

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.

并且当您有很多值时，您需要在枚举中包含一个 Map 以保持 getFromXYZ 方法较小。

回答by Roger Hayes

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.

对于大型数据库，我不愿意失去数字表示的大小和速度优势。我经常得到一个代表 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.

该解决方案的另一半是编写一些测试代码，检查 Java 枚举和数据库枚举表是否具有相同的内容。这留给读者作为练习。

回答by Dov Wasserman

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);

回答by Tom

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

1. If you ever refactor your enumeration to be classes with behavior (such as priority), your database already models it correctly
2. 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).
3. Your database values (suit_id) are independent from your enumeration value, which helps you work on the data from other languages as well.

1. 如果您曾经将枚举重构为具有行为（例如优先级）的类，则您的数据库已经对其进行了正确建模
2. 您的 DBA 很高兴，因为您的架构已标准化（为每个玩家存储一个整数，而不是整个字符串，该字符串可能有也可能没有错别字）。
3. 您的数据库值 ( suit_id) 独立于您的枚举值，这也有助于您处理来自其他语言的数据。

回答by Kryszal

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.

一个枚举字段具有 OR 关系的多个值。.NET 在数据库中存储枚举类型（如字节或整数）并在代码中使用 FlagsAttribute 的概念。

http://blogs.msdn.com/b/efdesign/archive/2011/06/29/enumeration-support-in-entity-framework.aspx

http://blogs.msdn.com/b/efdesign/archive/2011/06/29/enumeration-support-in-entity-framework.aspx

回答by Metaphore

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:

我所有的经验都告诉我，在任何地方持久化枚举的最安全方法是使用额外的代码值或 id（@jeebee 答案的某种演变）。这可能是一个很好的想法示例：

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"))

现在，您可以使用任何持久性通过其代码引用您的枚举常量。即使您决定更改某些常量名称，您也始终可以保存代码值（例如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")

并像魅力一样使用它： Race race = resolveByCode(Race.class, "elf")

回答by SaravanaC

I have faced the same issue where my objective is to persist Enum String value into database instead of Ordinal value.

我遇到了同样的问题，我的目标是将 Enum String 值持久化到数据库中，而不是 Ordinal 值。

To over come this issue, I have used @Enumerated(EnumType.STRING)and my objective got resolved.

为了克服这个问题，我已经使用@Enumerated(EnumType.STRING)并且我的目标得到了解决。

For Example, you have an EnumClass:

例如，您有一个Enum类：

public enum FurthitMethod {

    Apple,
    Orange,
    Lemon
}

In the entity class, define @Enumerated(EnumType.STRING):

在实体类中，定义@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".

当您尝试将值设置为 Database 时，String 值将作为“ APPLE”、“ ORANGE”或“ LEMON”保存在数据库中。