aspose file tools*
The moose likes Object Relational Mapping and the fly likes Storing enums in database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Storing enums in database" Watch "Storing enums in database" New topic
Author

Storing enums in database

Soheil Tayari
Greenhorn

Joined: Apr 04, 2008
Posts: 29
I plan to create an enum to store some types for one of my classes. I was wondering what would be the best way to save this enum into the database. Should I even save it in database? If not how to handle the change in types. If so is it efficient to have such a small table in database?

Thank you.
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Having your enums in a reference table won't serve much purpose. For me, the most important part is that if I am looking at the data in the database, I know what the the column values mean, either by looking at a reference table, or by the data that is there. But when using Hibernate to store enum values in a table you have to tell hibernate how you want those stored, or rather, as what types.

So you have to annotate your enum property correctly...



Without the annotation the values stored in the database would be 0 or 1. Telling hibernate to store them as a string will use the value of the enum, so MALE or FEMALE.

GenRocket - Experts at Building Test Data
Diego M. da Rosa
Greenhorn

Joined: Mar 22, 2006
Posts: 10
I am not a Hibernate expert but I think this is a very interesting discussion. Usually people say the best way to store a Java (or whatever other language) enum in the database is by storing the enum constant name in string/varchar format. However, I do see some problems with that approach:

1. For tables containing a big number of rows, storing VARCHARs may be inefficient if compared to storing INTEGERs (or even smaller data types);
2. Data integrity may be corrupted as any string that fits the VARCHAR column may be inserted into there;
3. The lack of a reference table makes it impossible to know, in the context of the database, what are the possible values for the enum.

Having a reference table containing an INTEGER primary key and a VARCHAR for the name of the enum constant would solve these three problems. In that case, another question arises: should we load the enum data from the database and treat the enum as a regular entity or just keep the enum and the reference table with duplicated data? Any ideas?!

These are the possibilities I see when storing enumerated values:

A. Store an int in an INTEGER column;
B. Store a String in a VARCHAR column;
C. Maintain an Enum in the source code and a reference table in the database potentially duplicating data;
D. Maintain only a reference table in the database and treat the enumerated values as a regular entity in the source code. The values would need to be loaded in runtime and possibly constants would need to be created in the source code.

Cheers,
Diego
deepak prasee
Greenhorn

Joined: Mar 02, 2009
Posts: 7
[size=12]Hi

I am using Enumerated.oridnal in the PK class to insert an integer field in the mysql database.
Now i read somewhr that enum sholud not used to build the composite primary key..
Can any one suggest me whtr i can use enum as one of the datatype to build composite primary key...
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Deepak, please stasrt a new thread when asking questions that have no real meaning to other threads. Just because this thread is about Enums doesn't make your question on topic.

Thanks.
deepak prasee
Greenhorn

Joined: Mar 02, 2009
Posts: 7
Its ok...sorry for the inconvinence caused
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
Hi,

In one of my previous projects, we used to have a reference table and the tables which needed the enums had a foreign key to that reference table.
So, only the id(numeric or varchar) was stored in the tables requiring the enums.
Also, in our application, these enums were not changed by the application(they were more or less read only), so we loaded them(the data in the reference table) in the cache on server startup, and used them whenever they were required.
Even if the data in reference table was not read only, i think we could somehow manage the values in the cache(whenever the application updates or inserts the record in the reference table, check whether it is in the cache, if it is, update the cache, else insert in the cache).
However, i think this approach should not be used if the data being inserted, is updated/inserted frequently.

Any ideas on what better approach could be taken?


Rahul Babbar
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Storing enums in database