Better way to implement reverse mapping of custom enum ordinals?

[david.karr]

Quite often databases will have columns that are stored as integers,
but represent enumerated values. In object-relational mapping, it's a
good idea to translate that integer value to the enumerated value it
represents.

The built-in "ordinal" value of an enum is almost useless. The integer
values for an enum always need to be controlled, and can't change if
you reorder things.

So you at least have to implement one custom field in the enum, which
I'll call "columnValue".

Somewhere you have to have code that translates those integer values
into the enumerated type value. The best place to do that is within
the enumerated type itself. Ideally, I'd like to do this in a way
that doesn't repeat the integer values, and is reasonably efficient.

A simple-minded implementation might look like this:

public static enum SomeType {
Foo(101),
Bar(100),
Gork(4001);

private int columnValue;

public final int getColumnValue() { return columnValue; }
public final void setColumnValue(int columnValue)
{ this.columnValue = columnValue; }

public SomeType getEnum(int columnValue) {
switch (columnValue) {
case 101: return Foo;
case 100: return Bar;
case 4001: return Gork;
default: return null;
}
}

SomeType(int columnValue) {
this.columnValue = columnValue;
}
}

Can someone think of a better way to do this, that doesn't repeat the
column values?

 

[EJP]

Can someone think of a better way to do this, that doesn't repeat the
column values?

Have each enum value enter itself on construction into a Map<Integer,
YourEnum> with its own ColumnValue as the key.

Like Peter I cannot see the point of setColumnValue(), and the Map would
of course require the columnValue to be constant.

 

[markspace]

The built-in "ordinal" value of an enum is almost useless. The integer
values for an enum always need to be controlled, and can't change if
you reorder things.

I wouldn't say that. I think enums and ordinal() have their uses, just
not the use you were hoping for.

In general, I think trying to make enums model any kind of external or
customer data is a bad idea. Customer data always changes, and enums
don't, without a lot of hassle.

Can someone think of a better way to do this, that doesn't repeat the
column values?

Map<Integer,String>.

Seriously. Set the strings and integers in a data or config file, or
better yet read them from the scheme when needed. Then you'll always
match the database, and you never have to go through the work of trying
to figure out what to do if and when someone starts to modify the schema.

public class DatabaseEnumeration {

String table;
String column;
Map<Integer, String> intToEnum;
Map<String, Integer> enumToInt;

...
}

Now you have the beginnings of a flexible class that can handle any
table's enumerations and doesn't have all the unpleasant side effects
associated with enums.

You do loose some code constructs, like case statements, which you'll
have to emulate with if-else chains, but that's not a lot to give up imo.

 

[david.karr]

Have each enum value enter itself on construction into a Map<Integer,
YourEnum> with its own ColumnValue as the key.

Like Peter I cannot see the point of setColumnValue(), and the Map would
of course require the columnValue to be constant.

Yes, you're right, there's no need for "setColumnValue()".

Concerning the Map, I had already thought of that. That's the obvious
way to do it. Now, how would you do it? I would assume you'd define
a static Map in the enum type and have the constructor put itself into
the map. The problem is, it doesn't appear to be possible to do
that. It doesn't compile. What might work is implementing some sort
of "super-map" that holds all the mappings for all enum types you
implement, so the key would have to concatenate the class and the
custom ordinal.

 

[Arved Sandstrom]

Quite often databases will have columns that are stored as integers,
but represent enumerated values. In object-relational mapping, it's a
good idea to translate that integer value to the enumerated value it
represents.

[ SNIP ]

It's an even better idea to store the enumeration "name" as a varchar.
Do you have that control or is it already past that point?

If the values are integers in the database, I would not consider
re-ordering enum constants at all. After all, once the tables that have
that integer column start being populated, are you going back and
re-writing them every time you change the order of enum constants? I
don't think so. And who cares what order the enum constants have? If you
do care then you are depending on the ordinal values, which is a form of
hard-coding.

AHS

 

[Martin Gregorie]

Quite often databases will have columns that are stored as integers,
but represent enumerated values. In object-relational mapping, it's a
good idea to translate that integer value to the enumerated value it
represents.

[ SNIP ]

It's an even better idea to store the enumeration "name" as a varchar.

Agreed - and hold the integer:name mapping as a separate table.

In a pure SQL environment you effect the translation by joining the table
holding the enumerated values to the mapping table. In Java you can do
much the same thing with Map classes.

Do you have that control or is it already past that point?

I don't think that matters if you use a mapping table. Simply provide a
(possibly batch) process for setting up the mapping table's content and
make sure that the mapping table constrains the permitted enumeration
values.

If the values are integers in the database, I would not consider
re-ordering enum constants at all.

Agreed. The ordering shouldn't matter - just ensure that the mappings are
unique.

 

[david.karr]

Quite often databases will have columns that are stored as integers,
but represent enumerated values.  In object-relational mapping, it's a
good idea to translate that integer value to the enumerated value it
represents.

[ SNIP ]

It's an even better idea to store the enumeration "name" as a varchar.
Do you have that control or is it already past that point?

If the values are integers in the database, I would not consider
re-ordering enum constants at all. After all, once the tables that have
that integer column start being populated, are you going back and
re-writing them every time you change the order of enum constants? I
don't think so. And who cares what order the enum constants have? If you
do care then you are depending on the ordinal values, which is a form of
hard-coding.

AHS

I cannot change the database at all, even adding tables or columns.
The enum values that are in the database will not change. We may
eventually add new enum values with different ordinal values.

Although integers are stored in the db, I need to map those to the
enum name on read. Mapping in the other direction isn't that bad, as
I can map to the particular enum value from the enum string out of the
box, and then get the column value mapping from the enum value.

 

[John B. Matthews]

Yes, you're right, there's no need for "setColumnValue()".

Concerning the Map, I had already thought of that. That's the
obvious way to do it. Now, how would you do it? I would assume
you'd define a static Map in the enum type and have the constructor
put itself into the map. The problem is, it doesn't appear to be
possible to do that. It doesn't compile.

A static initializer works, as suggested in the "enum Color" discussion:

<http://java.sun.com/docs/books/jls/third_edition/html/classes.html#8.9>

Here's a similar example that constructs a Map<Integer, Key>, where
Integer is a keyCode and Key is an enum:

<http://robotchase.svn.sourceforge.net/viewvc/robotchase/trunk/
src/org/gcs/robot/Key.java?revision=27&view=markup>

The mapping is persisted using java.util.prefs.Preferences rather than a
database, but the result is similar.

 

[Lew]

I'm also a little confused by the "setColumnValue()" method, as it
implies to me that there's the expectation of a mutable enum instance. I
would normally expect an enum instance to be immutable. But that's
probably just an artifact of my relative unfamiliarity with Java's
implementation of enums.

You're right. The enum should be immutable, and the integer 'columnValue'
should be 'final'.

I prefer to use string values for this sort of thing, but the principle is the
same. You have 'getColumnValue()' (or 'toString()') as an instance method to
translate the enum value to an external representation, and
'fromColumnValue()' ('fromString()') as a static method to return the enum
value from the external representation.

 

[Lew]

Quite often databases will have columns that are stored as integers,
but represent enumerated values. In object-relational mapping, it's a
good idea to translate that integer value to the enumerated value it
represents.

[ SNIP ]

It's an even better idea to store the enumeration "name" as a varchar.

Agreed - and hold the integer:name mapping as a separate table.

Or don't even bother with integers in the database at all, but just use the
VARCHAR value.

 

[Lew]

A static initializer works, as suggested in the "enum Color" discussion:

<http://java.sun.com/docs/books/jls/third_edition/html/classes.html#8.9>

Here's a similar example that constructs a Map<Integer, Key>, where
Integer is a keyCode and Key is an enum:

<http://robotchase.svn.sourceforge.net/viewvc/robotchase/trunk/
src/org/gcs/robot/Key.java?revision=27&view=markup>

The mapping is persisted using java.util.prefs.Preferences rather than a
database, but the result is similar.

enums generally have few enough values that a linear search is acceptable:

public static MyNumeration fromRepresentation( String rep )
{
for ( MyNumeration numer : values() )
{
if ( numer.toString().equals( rep ) )
{
return numer;
}
}
return valueOf( numer );
}

where 'toString()' is overridden to return the 'private final String' internal
representation of the enum value.

You can do something similar for integer or any 'Representation' type.

 

[Roedy Green]

public SomeType getEnum(int columnValue) {
switch (columnValue) {
case 101: return Foo;
case 100: return Bar;
case 4001: return Gork;
default: return null;
}
}

If you use Java enums, you can use values() to go from strict
ordinal to enum value.

What you have as your ints are basically short aliases for the enums.
You can build a HashMap in the constructors. See
http://mindprod.com/jgloss/enum.html
for sample code.

 

[Daniel Pitts]

Quite often databases will have columns that are stored as integers,
but represent enumerated values. In object-relational mapping, it's a
good idea to translate that integer value to the enumerated value it
represents.

The built-in "ordinal" value of an enum is almost useless. The integer
values for an enum always need to be controlled, and can't change if
you reorder things.

So you at least have to implement one custom field in the enum, which
I'll call "columnValue".

Somewhere you have to have code that translates those integer values
into the enumerated type value. The best place to do that is within
the enumerated type itself. Ideally, I'd like to do this in a way
that doesn't repeat the integer values, and is reasonably efficient.

A simple-minded implementation might look like this:

public static enum SomeType {
Foo(101),
Bar(100),
Gork(4001);

private int columnValue;

public final int getColumnValue() { return columnValue; }
public final void setColumnValue(int columnValue)
{ this.columnValue = columnValue; }

public SomeType getEnum(int columnValue) {
switch (columnValue) {
case 101: return Foo;
case 100: return Bar;
case 4001: return Gork;
default: return null;
}
}

SomeType(int columnValue) {
this.columnValue = columnValue;
}
}

Can someone think of a better way to do this, that doesn't repeat the
column values?

See inline comments:

public static enum SomeType {
// enum names are usually all caps.
FOO(101), BAR(100), GORK(4001);
// Exposing this as a public final field.
// One of the few times I would actually do that.
public final int columnValue;

SomeType(int columnValue) {
this.columnValue = columnValue;
}

// using a Map
private static final Map<Integer, SomeType> byId;
// Static initializer block.
static {
// Start with a HashMap.
final Map<Integer, SomeType> map = new HashMap<Integer, SomeType>();
// Add all the values.
for (SomeType st: values) {
map.put(st.columnValue, st);
}
byId = Collections.unmodifiableMap(map);
}
// Provide public method which hides the map. That way, you can
// use any implementation you want, whether it be switch, map, or
// sparse array.
public static SomeType getByColumnValue(int columnValue) {
return byId.get(columnValue);
}
}

HTH,
Daniel.

 

[Daniel Pitts]

Quite often databases will have columns that are stored as integers,
but represent enumerated values. In object-relational mapping, it's a
good idea to translate that integer value to the enumerated value it
represents.

[ SNIP ]

It's an even better idea to store the enumeration "name" as a varchar.
Do you have that control or is it already past that point?

And a yet better idea is to store the enumeration "name" as a database
"enum" type, if the DB supports it (MySQL does, not sure about others).

The database converts to and from the internal representation (which is
usually integer), but the column value looks like a string.

 

[Daniel Pitts]

I cannot change the database at all, even adding tables or columns.

That restriction is going to really suck when requirements dictate a
design change. Glad I don't work at your company.

 

[Tom Anderson]

That restriction is going to really suck when requirements dictate a
design change. Glad I don't work at your company.

You might mean literally what you wrote, but that sounds like a veiled
insult - "your company is doing it wrong". If that is what you meant,
that's childish and presumptuous. There could be a number of very good
reasons why the schema can't change. Perhaps the schema is part of some
bigger app, and David is writing a utility which works alongside it.
Perhaps there's a running production system already, and changing the
schema would involve an unacceptable maintenance downtime. Perhaps the
schema is owned by a client, and David's company have to work with what
they have. In the real world, we have to deal with situations like this -
and far worse - all the time, and they should be met with sympathy, not
sneers.

Of course, if you really did just mean that you were glad not to be in
such a situation, then disregard the above, as we're in perfect agreement!

tom

 

[Tom Anderson]

A static initializer works, as suggested in the "enum Color" discussion:

<http://java.sun.com/docs/books/jls/third_edition/html/classes.html#8.9>

You can also trick the constructor-put idea into working by moving the map
into a different class, which can even be an inner class of the enum:

public enum SomeType {
FOO(101),
BAR(100),
GORK(4001);

private static class ByColumnValue {
public static final Map<Integer, SomeType> MAP = new HashMap<Integer, SomeType>();
}

private final int columnValue;

private SomeType(int columnValue) {
this.columnValue = columnValue;
ByColumnValue.MAP.put(columnValue, this);
}

public SomeType getEnum(int columnValue) {
return ByColumnValue.MAP.get(columnValue);
}
}

tom

 

[markspace]

// Exposing this as a public final field.
// One of the few times I would actually do that.
public final int columnValue;

This bit here I had to comment on. The whole point of enums is that you
replace int, which is a homogeneous type that appears everywhere in a
program, with a specific class type, which only appears in your API
where it is a valid parameter.

The idea that you then goes back in the other direction and use enums to
store a public integer value... ow! brain hurt bad...

 

[Lew]

You can also trick the constructor-put idea into working by moving the
map into a different class, which can even be an inner class of the enum:

public enum SomeType {
FOO(101),
BAR(100),
GORK(4001);

private static class ByColumnValue {
public static final Map<Integer, SomeType> MAP = new
HashMap<Integer, SomeType>();
}

private final int columnValue;

private SomeType(int columnValue) {
this.columnValue = columnValue;
ByColumnValue.MAP.put(columnValue, this);
}

public SomeType getEnum(int columnValue) {
return ByColumnValue.MAP.get(columnValue);
}
}

'getEnum()' should be static.

 

[Lew]

This bit here I had to comment on. The whole point of enums is that you
replace int, which is a homogeneous type that appears everywhere in a
program, with a specific class type, which only appears in your API
where it is a valid parameter.

The idea that you then goes back in the other direction and use enums to
store a public integer value... ow! brain hurt bad...

But it's a type-safe int!