JPA Enum ORDINAL vs STRING

Question

It's possible to define enumerations in JPA using either

@Enumerated(EnumType.ORDINAL)

or

@Enumerated(EnumType.STRING)

I wonder what are advantages and disadvantages of those two definitions?

I heard that ORDINAL performs better (is faster) than STRING with EclipseLink.
Is that true?

I think ORDINAL is by default is no @Enumerated annotation is used — jacktrades, Jan 24 '13 at 23:45
In JPA 2.1 you can specify an `@Converter` and allow mapping to a string or another data type which can be simple/short as possible and as long/complex as necessary. — Kalle Richter, Mar 28 '17 at 20:38

Bohemian · Accepted Answer · 2012-04-07T19:53:00.713

87

I always go STRING.

Speed is rarely the most important issue - readability and maintainability are more important.

I use STRING because it's a lot easier to manually inspect rows from the database, but more importantly, I can do two things, without touching the database, the ORDINAL can't handle:

I can change the order of my enums
I can insert new enums in the middle of the enum list

Both of these changes will alter the ordinal values of the enums already in use in the database, thus breaking existing data if you are using ORDINAL.

If you change an enum value (not that common), handling it is simple:

UPDATE table SET enum_column = 'NEW_ENUM_NAME' where enum_column = 'OLD_ENUM_NAME';

edited Apr 07 '12 at 19:53

answered Jul 22 '11 at 13:58

Bohemian

412,405
93
575
722

11

on the other hand you can't rename your enums. ;) I wouldn't say there is a preferred one. – Bozho Jul 22 '11 at 14:02
2

sure you can: `UPDATE table SET enum_column = 'NEW_ENUM' where enum_column = 'OLD_ENUM' – Bohemian Jul 22 '11 at 14:05
renaming Enums would be just as catastrophic as renaming columns in a table or a table for that matter, it has long and far reaching non-trivial effects, I don't think it is a concern, because big changes like that will be thought through carefully. You are much more likely to be adding new Enums and not removing or renaming them, and not having to worry about changing the ordinal positions is much more important to external code, that can't be checked at compile time, renaming can. In the end the client code should not have to care about which you use, Names are easier to maintain. – Jul 22 '11 at 14:05
not if someone is unaware that the enum name is used in the db. I would say I rename enums more often than I reorder them. And you can the this UPDATE query with ordinals as well – Bozho Jul 22 '11 at 14:06
4

"catastrophic" is too emotive a term to describe renaming an enum - see answer for simple handling. – Bohemian Jul 22 '11 at 14:07
How to update the `enum` name in the DB and in the code at the same time? – Sanghyun Lee Aug 29 '14 at 02:56
@Sangdol that would be a release task: after renaming an enum, as you release the code run an sql script to update the enum name used: `update mytable set myenumcol = 'newname' where myenumcol = 'oldname'` – Bohemian Aug 29 '14 at 04:58
Ordinal has the advantage of always working with @OrderBy, whereas with String, ordering is alphabetical (I'm speaking of db SELECT statement here). – 1in9ui5t Jun 26 '15 at 03:59
1

@1in9ui5t interesting point, although most databases have ways of ordering by an enum. In particular, if you implement the column as a mysql enum too, ordering is by enum order and if a plain string you can `order by find_in_set(mycolumn, 'ENUM_NAME_1', 'ENUM_NAME_2', ...)` (a little ugly, but simple enough) – Bohemian Jun 26 '15 at 04:26
@Bohemian I wasn't aware of the ENUM type in databases. That solves the ordering issue. Just have to include the Enum values in the column definition. – 1in9ui5t Jun 26 '15 at 16:41

Bozho · Answer 2 · 2011-07-22T14:03:26.893

It's likely that ORDINAL is more efficient, but that's minor. There are a few downsides to ORDINAL:

it is less readable in the database
if you reorder your enum definitions the database will not be consistent.

With STRING you can't rename your enums.

Pick one of them and use it throughout the whole application - be consistent.

If your database is going to be used by other clients/languages - use STRING, it's more readable.

score 8 · Answer 3 · edited Sep 01 '21 at 06:36

I prefer the use of Ordinal but this really depends on the use.

By example:

You have a enum, to save all your user states, in this case the order doesn't matter, and you can add more states in the future (Best use is @Enumerated(EnumType.ORDINAL)):

public enum UserStates { ACTIVE, DELETED, PENDING }

But now, you have an enum, to save the Plantes in the Solar System (Best use @Enumerated(EnumType.STRING)):

public enum Planets {MERCURY,VENUS,EARTH,MARS,JUPITER,SATURN,URANUS,NEPTUNE,PLUTO,NINE}

Now think that you want reorder your planets, with @Enumerated(EnumType.ORDINAL) you can't, because your database can't know the new order in your Java file.

You can reorder your Plantes using @Enumerated(EnumType.STRING) because your Planet is linked to the enum name, not the enum order.

Anyway, you can modify your @Enumerated(EnumType.ORDINAL)enums because they are linked to the order, but you can't change your @Enumerated(EnumType.STRING)enums because they will use like new enums.

String types are more readable in the Database, but will occupy more size than an ordinal data. Maybe are useful if the database is used by more clients, but it's better have a good documentation of the software than save 1000 times "EARTH" than "4"

USERSTATE
------------
ID | STATE |
------------
1 | 1
2 | 2
3 | 1

Planets
------------
ID | Name |
------------
1 | EARTH
2 | EARTH
3 | MARS
4 | EARTH

Dherik · Answer 4 · 2021-12-03T12:52:11.360

This is a good question. In the past I used String but today my preference is to use Ordinal.

The main disadvantage for the String is for the DBAs. With the String they have no idea what are the possible values of the column, because this information is in the application code. The DBA only can have some idea about the possible values grouping the existent information on the table, but he will never be sure about the other possible values until the application insert them on the table.

In the Ordinal you have the same problem above. But my preference for Ordinal came to a solution to the DBA problem that seems natural to the database. You can create a new table to show the possible values of the Enumerator on database, with a foreign key between the column (ordinal enum value) and this new table. This strategy is described and implemented here.

About the problem that someone could reorder the Enumerator and break the system, a simple unit test can deal with this problem and guarantee that no one will reorder them without a very good error. The same idea is valid on renaming the Enumerator. So, renaming (on String) or reorder (on Ordinal) accidentally it is not really a strong argument against String or Ordinal approach.

By the way, for my perspective the developers have more necessity to rename than reorder an Enumerator, so I count this as one more positive point to use Ordinal.

So, with this extra table approach, you solve the main problem of the Ordinal (now, is readable) and the information will occupy less space on the database (and your DBA will be happy).

score 0 · Answer 5 · answered Apr 08 '17 at 15:12

0

It depends on your application, if there are more chances that you will add more enums use the String type, if there are more chances that you will change the name of your enums use Ordinal.

answered Apr 08 '17 at 15:12

samid hamza

1
1
1

score 0 · Answer 6 · answered Apr 18 '19 at 00:21

Lots of good advice here, but I just wanted to add something I didn't see yet:

Regardless of the solution you choose, don't forget to add a big fat warning at the top of your enum class saying which should be used. Hopefully other developers will see you've done this and use the same method for saving the enum.

score 0 · Answer 7 · answered May 22 '19 at 07:21

I would prefer EnumType.STRING. A disadvantage of EnumType.ORDINAL is the effect of time and the desire to keep enums in a logical order. With EnumType.ORDINAL any new enum elements must be added to the end of the list or you will accidentally change the meaning of all your records. please check this link: https://tomee.apache.org/examples-trunk/jpa-enumerated/

score 0 · Answer 8 · answered Jun 10 '22 at 07:56

I would throw in a third possibility: Use explicit mapping via AttributeConverter. This way you get rid of the problem with changing order for ORDINAL and with refactoring names for STRING. This way you can use meaningful enum values with compact and robust storage. For example I have an enum with values "EXPORT" and "IMPORT" which are stored as "E" and "I". If I one day decided to refactor them to "EXPORT_TYPE" and "IMPORT_TYPE", JPA storage is unaffected.

score -4 · Answer 9 · answered Jan 27 '12 at 12:04

-4

Are you really sure that a human readable database is what you need? Storing string value is a waste of space. The only compromise with readability could be use @Enumerated(STRING) and map database column as ENUM (if you are using mysql... I presume other dbms have something similar) but it's a real pain when you have to change enum names.

answered Jan 27 '12 at 12:04

Lothruin

3
3

5

(sarcastic comment warning) Yes so much better to have to look through code to find what that ordinal value actually means.... Oh and don't forget ordering - great when somebody inserts a new value in the middle/top of the enum. Don't let the implementation dictate design. @Enumerated(ORDINAL) considered harmful – earcam Mar 28 '12 at 09:21
1

Maintainability & readability is far more important than what you said. Such things (space) have virtually no cost at all in means of the service speed and memory consumption. When you end up solving an issue because of such things, this immediately costs you (more than the space). – Ville Myrskyneva Sep 19 '16 at 10:34
1

@earcam (sarcastic response) you can always add comment on column and read it every time you need. Is this that difficult to read? IMHO this ticket does not respond to the question, both cases have their pros and cons – dwilda Feb 17 '18 at 21:09
@dwilda haha, fair enough. But surely cardinality + (compound) indexing mitigates any performance concerns... [mismatch](https://en.wikipedia.org/wiki/Object-relational_impedance_mismatch) is bad enough already – earcam Feb 22 '18 at 00:07

JPA Enum ORDINAL vs STRING

9 Answers9

Linked