如何在数据库中存储枚举类名和枚举值

Question

class EligibilityRule{
ProductEligibility productEligibility;
CountryEligibility countryEligibility
}

enum ProductEligibility{
PRODUCT_X,
PRODUCT_Y
}

enum CountryEligibility{
US,
IN,
CN
..
}

String id  => auto_increment id
String ruleType => enum class name (ex: ProductEligibility)
String ruleValue => enum value (ex: PRODUCT_X)

Answer 1

JOOQ支持自定义数据类型。这意味着您可以为数据库字段定义转换器，然后在加载时自动映射到自定义类型。数据库字段仍然是字符串，但是为该字段生成的记录将包含字段 。这意味着您不必显式存储类名。

为此，必须在代码生成器中注册转换器（摘自上面的文档页）：

<database>

  <!-- Then, associate custom types with database columns -->
  <forcedTypes>
    <forcedType>

      <!-- Specify the Java type of your custom type. This corresponds to the Converter's <U> type. -->
      <userType>java.util.GregorianCalendar</userType>

      <!-- Associate that custom type with your converter. -->
      <converter>com.example.CalendarConverter</converter>

      <!-- Add a Java regular expression matching fully-qualified columns. Use the pipe to separate several expressions.

           If provided, both "expressions" and "types" must match. -->
      <expression>.*\.DATE_OF_.*</expression>

      <!-- Add a Java regular expression matching data types to be forced to
           have this type.

           Data types may be reported by your database as:
           - NUMBER              regexp suggestion: NUMBER
           - NUMBER(5)           regexp suggestion: NUMBER\(5\)
           - NUMBER(5, 2)        regexp suggestion: NUMBER\(5,\s*2\)
           - any other form

           It is thus recommended to use defensive regexes for types.

           If provided, both "expressions" and "types" must match. -->
      <types>.*</types>
    </forcedType>
  </forcedTypes>
</database>

另请参见JOOQ支持的自定义数据类型绑定。

enum ProductEligibility implements Rule {...};
enum CountryEligibility implements Rule {...};

interface Rule { String getRuleType(); String getRuleValue(); }

然后在文档页或单独的链接答案中创建一个转换器，如示例所示。

当然，这也意味着您的记录中有一个字段 字段，而不是枚举的特定类型。如果这对你来说是可以接受的，这可能是一种可能的方式。

我没有得到这一部分，当然，这也意味着您的记录中有一个字段 字段，而不是枚举的特定类型。 。这是否意味着我仍将在db中存储两个字段:rule_type和rule_value，每个字段都使用CustomConverter？

eligiblity_rules
------------------
id    user    type                              value 
234   223     com.foo.bar.ProductEligibility    PRODUCT_Y
856   855     com.foo.bar.CountryEligibility    US

public Converter<Object, Rule> converter() {
    return new Converter<Object, Rule>() {
        @Override
        public Rule from(Object t) {
            // t here refers to the "value" db field above
            if (checkIsProductEligibilityRule())
                 return ProductEligibility.fromValue(...);
            else
                return CountryEligibility.fromValue(...)
        }

        // Other converter methods
    };
}

因此，在基于Jooq的代码中，您最终将拥有：

EligibilityRuleRecord record = dslContext.selectFrom(ELIGIBILITY_RULE).where(...).fetchOne();
Rule rule = record.getValue();

之后，如果要使用特定的规则类型，则需要进行检查和强制转换：

if (rule instanceof ProductEligibility) {
    ProductEligibility peRule = (ProductEligibility) rule; 
    ...
}
if (rule instanceof CountryEligibility) {
    CountryEligibility ceRule = (CountryEligibility) rule;
    ...
}
...

type数据库字段的唯一原因是选择正确的数据。不幸的是，Java代码不知道（在编译时）该类型是什么，因此每次希望知道该字段的特定类型时，都需要在运行时进行类检查。