JPA: Storing transfers in a database

Surely, many of you have encountered the problem of storing transfers in a database, which occurs when you try to implement a convenient way of working with all sorts of official reference books - statuses, object types, and so on.

Its essence is very simple: if you store enumerations as entities ( @Entity ), then it turns out to be extremely inconvenient to work with them, the database is loaded with unnecessary queries even despite caching, and the queries to the database are complicated by unnecessary JOINs. If the enumeration is defined as enum, then it becomes convenient to work with them, but there is a problem of synchronization with the database and tracking errors of such synchronization.

This is especially true when the field containing the enumeration is annotated as @Enumerated(EnumType.ORDINAL) - everything breaks instantly when the order of the declaration of values ​​changes. If we store the values ​​in string form - like @Enumerated(EnumType.STRING) - the problem of access speed arises, since the indices for string fields are less efficient and take up more space. Moreover, regardless of the method of storing the field value in the absence of a table with a list of acceptable values ​​in the database, we are in no way protected from incorrect or outdated data and, as a result, problems.
')
However, the very idea of ​​storing in the database is tempting because of the simplicity of building queries manually, which is very valuable when debugging software or solving difficult situations. When you can write not just SELECT id, title FROM product WHERE status = 5 , but, say, SELECT id, title FROM product JOIN status ON status.id = product.status_id WHERE status.code = 'NEW' is very valuable. Including the fact that we can always be sure that status_id contains the correct value if we set FOREIGN KEY .

In fact, there is a very simple and elegant solution to this problem that kills all birds with one stone.


This solution is based on a simple hack, which, although a hack, does not introduce any side effects. As you know, enumerations in Java are just syntactic sugar, internally represented by the same instances of classes generated from java.lang.Enum . And just in the latter there is a wonderful ordinal field, declared as private , which stores the value returned by the ordinal() method and used by the ORM for placement into the database.

We just need to read from the directory in the database the current identifier of the enumeration element and place it in this field. Then we will be able to use EnumType.ORDINAL in a regular way for storing in the database with fast and convenient access, thus preserving all the delights of the Enums themselves in Java, and not have problems with identifier synchronization and their relevance.

It may seem that such an approach gives rise to problems with the serialization of objects, but this is not so, because the specification of the Java platform literally tells us the following:

1.12. Serialization of Enum Constants
Enum constants are serialized differently than ordinary serializable or externalizable objects. The serialized form of an enum constant consists solely of its name; field values ​​are not present in the form.

That is, when serializing enums, they are always converted to string form, and the numeric value is ignored. Voila!

And now a little practice. First, let's define the data model for our example:

 CREATE SEQUENCE status_id; CREATE SEQUENCE product_id; CREATE TABLE status ( id INTEGER NOT NULL DEFAULT NEXT VALUE FOR status_id, code CHARACTER VARYING (32) NOT NULL, CONSTRAINT status_pk PRIMARY KEY (id), CONSTRAINT status_unq1 UNIQUE KEY (code) ); INSERT INTO status (code) VALUES ('NEW'); INSERT INTO status (code) VALUES ('ACTIVE'); INSERT INTO status (code) VALUES ('DELETED'); CREATE TABLE product ( id INTEGER NOT NULL DEFAULT NEXT VALUE FOR product_id, status_id INTEGER NOT NULL, title CHARACTER VARYING (128) NOT NULL, CONSTRAINT product_pk PRIMARY KEY (id), CONSTRAINT product_unq1 UNIQUE KEY (title), CONSTRAINT product_fk1 FOREIGN KEY (status_id) REFERENCES status (id) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE INDEX product_fki1 ON product (status_id); 

Now we describe the same data scheme in Java. Note that in this case both the enumeration and the entity class for the reference are defined. To avoid repetition of uniform code, references for enumerations are inherited from SystemDictionary . Also note the @MappedEnum annotation, which we will use later on to determine which enums are reflected in the database.

 public enum Status { NEW, ACTIVE, DELETED } @Retention(value = RetentionPolicy.RUNTIME) public @interface MappedEnum { Class<? extends Enum> enumClass(); } @MappedSuperclass public class SystemDictionary { @Id @GeneratedValue(generator = "entityIdGenerator") @Column(name = "id", nullable = false, unique = true) private Integer id; @Column(name = "code", nullable = false, unique = true, length = 32) private String code; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getCode() { return code; } public void setCode(String code) { this.code = code; } } @Entity @Table(name = "status") @SequenceGenerator(name = "entityIdGenerator", sequenceName = "status_id") @MappedEnum(enumClass = Status.class) public class StatusEx extends SystemDictionary { } @Entity @Table(name = "product") @SequenceGenerator(name = "entityIdGenerator", sequenceName = "product_id") public class Product { @Id @GeneratedValue(generator = "entityIdGenerator") @Column(name = "id", nullable = false, unique = true) private Integer id; @Column(name = "status_id", nullable = false, unique = false) @Enumerated(EnumType.ORDINAL) private Status status; @Column(name = "title", nullable = false, unique = true) private String title; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Status getStatus() { return status; } public void setStatus(Status status) { this.status = status; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } } 

Now we just need to read the values ​​from the database and write them to the ordinal field, and also remember to update the values array so that we can get the enumeration instances by index from getEnumConstants() - this is not only used by the same Hibernate when working with transfers, but simply in places is very convenient. This can be done immediately after initializing the connection to the database using something like this:

 public interface SessionAction { void run(Session session); } public class EnumLoader implements SessionAction { @Override public void run(Session session) { Iterator<PersistentClass> mappingList = configuration.getClassMappings(); while (mappingList.hasNext()) { PersistentClass mapping = mappingList.next(); Class<?> clazz = mapping.getMappedClass(); if (!SystemDictionary.class.isAssignableFrom(clazz)) continue; if (!clazz.isAnnotationPresent(MappedEnum.class)) continue; MappedEnum mappedEnum = clazz.getAnnotation(MappedEnum.class); updateEnumIdentifiers(session, mappedEnum.enumClass(), (Class<SystemDictionary>) clazz); } } private void updateEnumIdentifiers( Session session, Class<? extends Enum> enumClass, Class<? extends SystemDictionary> entityClass) { List<SystemDictionary> valueList = (List<SystemDictionary>) session.createCriteria(entityClass).list(); int maxId = 0; Enum[] constants = enumClass.getEnumConstants(); Iterator<SystemDictionary> valueIterator = valueList.iterator(); while (valueIterator.hasNext()) { SystemDictionary value = valueIterator.next(); int valueId = value.getId().intValue(); setEnumOrdinal(Enum.valueOf(enumClass, value.getCode()), valueId); if (valueId > maxId) maxId = valueId; } Object valuesArray = Array.newInstance(enumClass, maxId + 1); for (Enum value : constants) Array.set(valuesArray, value.ordinal(), value); Field field; try { field = enumClass.getDeclaredField("$VALUES"); field.setAccessible(true); Field modifiersField = Field.class.getDeclaredField("modifiers"); modifiersField.setAccessible(true); modifiersField.setInt(field, field.getModifiers() & ~Modifier.FINAL); field.set(null, valuesArray); } catch (Exception ex) { throw new Exception("Can't update values array: ", ex); } } private void setEnumOrdinal(Enum object, int ordinal) { Field field; try { field = object.getClass().getSuperclass().getDeclaredField("ordinal"); field.setAccessible(true); field.set(object, ordinal); } catch (Exception ex) { throw new Exception("Can't update enum ordinal: " + ex); } } } 

As you can see, we simply get from Hibernate a complete list of classes reflected on the database, select from them all inherited from the SystemDictionary declared above and, simultaneously, containing the @MappedEnum annotation, and then update the numeric values ​​of the enumeration class instances. Actually, that's all. Now we can safely:

1. Store enums as Java Enum and declare fields containing them as @Enumerated(EnumType.ORDINAL)
2. Automatically control the synchronization of directories in the code and database
3. Do not care about the order of identifiers in the code and their correspondence to the identifiers in the database
4. Perform convenient database queries containing access to enumeration values ​​by their string name
5. ...
6. PROFIT!

To achieve complete Zen, you can (and should) also add a check that the database does not contain unnecessary values, that is, the reference table and the enum declaration are synchronized in the code.

This approach is used by us ( Open Source Technologies ) in fairly large systems (from half a million lines of source code and more) with a distributed JMS-based service-oriented architecture and has shown itself very well, both in terms of usability and reliability. What you want :)