Search code examples
javadatabasejpaopenjpa

Is there a way of using a lookup table as a converter in JPA?

Imagine that I have a simple entity as follows:

@Entity
@Table(name = "PERSON")
public class Person {

@Id
@Column(name = "NAME")
private String name;

@Column(name = "GENDER")
private String gender;
}

And two tables, the actual table holding the information and a lookup table. 

TABLE PERSON (
NAME VARCHAR2 NOT NULL,
GENDER INT NOT NULL);

TABLE GENDER_LOOKUP (
GENDER_ID INT NOT NULL,
GENDER_NAME VARCHAR2 NOTNULL);

I want to save the information from my entity into the table, so that the String field gender is automatically converted to the corresponding gender int, using the lookup table as a reference. I thought of two approaches, but I was wondering if there was a more efficient way.

  1. Create an enum and use ordinal enum to persist. I would rather avoid this because I'd like to have only one "source of truth" for the information and for various business reasons, it has to be a lookup table.

  2. Use the @Converter annotation and write a custom converter. I think that this would require me to query the table to pull out the relevant row, so it would mean that I would have to make a JPA call to the database every time something was converted.

I'm currently planning to use 2, but I was wondering if there was any way to do it within the database itself, since I assume using JPA to do all of these operations has a higher cost than if I did everything in the database. Essentially attempt to persist a String gender, and then the database would look at the lookup table and translate it to the correct Id and save it.

I'm specifically using openJpa but hopefully this isn't implementation specific.

Solution

  • Since you seriously considered using enum, it means that GENDER_LOOKUP is static, i.e. the content doesn't change while the program is running.

    Because of that, you should use option 2, but have the converter cache/load all the records from GENDER_LOOKUP on the first lookup. That way, you still only have one "source of truth", without the cost of hitting the database on every lookup.

    If you need to add a new gender1, you'll just have to restart the app to refresh the cache.

    1) These days, who know what new genders will be needed.