如何在数据库中建模（Java）枚举（使用SQL92） [英] How to model a (Java) Enum in a Database (with SQL92)

问题描述

您好，我们用一个名为Gender的列来建模实体。在应用程序代码中，性别应该是（Java）枚举类型，具有2个值（MALE和FEMALE）。您将如何对它进行建模，知道枚举数据类型不是通用SQL（语言）92的一部分数据模型必须是可移植的，以便由几个DBMS（Mysql，Oracle，Derby）和几个持久性提供程序（JPA，Hibernate， JDBC）。
我感觉到，它以2个级别命中：
- 首先是Schema和实体的级别（将枚举存储为-VAR-CHAR或INT）
- 应用程序级别代码（JDBC不支持枚举，JPA但是取决于实现）

是否有通用的解决方案，例如适用于Mysql DBMS？
谢谢!!

解决方案

我通常采用以下解决方案，它提供了一个完全规范化的数据库模式。 >

1. 创建一个专用表来表示你的枚举;例如颜色。该表将有两列：
   
   
   
   • 一个 ID 列（主键）。通常一个自动递增的 int ，smallint或 tinyint 。
   
   
   • A name 列（候选键）。这将是一个 varchar ，其值应与Java枚举相同。
   
   


2. 创建Java枚举：与数据库中颜色表具有相同值的颜色。



3. 假设您现在有包含引用 Color 表的记录的Data表实现一个用于插入记录的存储过程，该记录采用Color String值（如您的Java枚举所述）;例如

   
   
   

   CREATE PROCEDURE dbo.InsertRecord
   @colour varchar（32）
   AS
   ...

存储过程将执行必要的String-to-int转换，允许您的数据表通过int外键引用Color表。 / p>

同样，您可以在数据表上创建一个视图，这些表将加入到幕后的颜色表中，以将您的数据的非规范化通过回到应用程序或最终用户。

存储过程方法的替代方法是在启动时读取并缓存应用程序中的序号，名称映射。这具有与数据库中的Color表的内容相比较能够验证Java Color枚举的完整性的优点。

Hi am modelling an Entity with a column named "Gender". In the application code, the gender is supposed to be an (Java) Enum type, with 2 values (MALE and FEMALE).. how would you model it, knowing that Enums as Datatype are not part of the generic SQL (language) 92.

It the data model would have to be portable, in order to be used by several DBMS (Mysql, Oracle, Derby) and several persistence provider (JPA, Hibernate, JDBC). I feel, it hits at 2 levels: - first the level of the Schema and the entity (store the enums as -VAR-CHAR or INT) - second the level of the app code (JDBC doesn't support enums, JPA does but it depends on the implementation)

Is there a generic solution, applicable for example to an Mysql DBMS ? Thanks !!

解决方案

I typically adopt the following solution, which gives a fully normalised database schema.

1. Create a dedicated table to represent your enumeration; e.g. Colour. The table will have two columns:
   • An ID column (primary key). Typically an auto-incrementing int, smallint or tinyint.
   • A name column (candidate key). This will be a varchar whose value should be identical to the Java enumeration.
2. Create a Java enum: Colour with identical values to the Colour table in the database.

3. Assuming you now have a "Data" table containing records that reference the Colour table, implement a stored procedure for inserting a record, which takes the Colour String value (as described by your Java enum); e.g.

   CREATE PROCEDURE dbo.InsertRecord @colour varchar(32) AS ...

The stored procedure will perform the necessary String-to-int translation allowing your data table to reference a the Colour table via an int foreign key.

Similarly you can create a view onto your data table(s) that will join to the Colour table behind the scenes to present a denormalised via of your data back to the application or end user.

The alternative to the stored procedure approach is that you read and cache the ordinal, name mappings within your application on startup. This has the advantage of being able to verify the integrity of your Java Colour enum compared with the contents of your Colour table in the database.

这篇关于如何在数据库中建模（Java）枚举（使用SQL92）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！