This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes Object Relational Mapping and the fly likes Best mapping approach for reference/LOV/lookup data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Best mapping approach for reference/LOV/lookup data" Watch "Best mapping approach for reference/LOV/lookup data" New topic

Best mapping approach for reference/LOV/lookup data

Rs Carter

Joined: Mar 02, 2012
Posts: 4
I'm currently working on a simple sample application and trying to figure out the best way to model what I call reference data, or list-of-values or lookup data. By this, I am referring to the static, or relatively static, data that typically comes in name-value pairs for specifying certain properties on my business entities. Common examples would be gender (M/F), month (jan/feb/...), marital status (married/single/divorded/...) etc. From here on out I will refer to these as "reference tables" for the sake of brevity. The primary characteristic of these reference tables is that they are read-only, or maybe just read-mostly with the only write ability in an application admin function. In some scenarios you may need more than just two columns (the name-value pair) or a more complex table structure to support multiple languages, but for the sake of simplicity we can ignore that for our purposes here and presume that any reference table is a simple name-value pair.

The key requirement here is that I do not want to have to define a full-blown JPA entity for each of these reference tables. I have an application framework component which I used to store, cache, and lookup all reference table data, so all I need is the key values for a particular entity. So this pretty much takes care of itself for entities that have a many-to-one relationship with a particular reference table, as I can just define the foreign key in the entities table as a basic attribute and it will be automatically populated by JPA. Where this becomes frustratingly complex for something so conceptually simple, though, is when the entity has a many-to-many relationship between itself and the reference table. (This would be a classic case where you can say, select multiple reference values for any given entity instance in a multi-select box.) In this case, I'm having difficulty coming up with a satisfactory solution.

To illustrate, here's the simple sample application I've been building to explore this issue. It's a simple recipe manager application (I'm a cook, so this was as convenient a sample use case as any). It has a Recipe entity, and the Recipe entity has references to multiple reference tables. One of these references is to the CookingMethod reference table, with is a many-to-one (every Recipe has one CookingMethod). One of the other references is to the Month reference table, indicating what months of the year the recipe is in season. A recipe can be in season multiple months of the year, so this is a many-to-many example.

Here's the table structure:

Here's what the entity looks like:

I of course have accessors and mutators for all the fields and the O/R mappings defined. Note that I've got the attribute corresponding to the FK for the cooking method on the entity, so JPA is able to populate that just fine when I just define it as a basic attribute in the mappings. Where JPA gets tripped up is on populating the months List<Integer> field. So far as I can tell, there isn't a way to have JPA populate this automatically. It seems like I could easily define a Month entity, or also define and entity called MonthRef and map this to the RECIPE_XREF_MONTH table and give it a one-to-many relationship with Recipe, but then I'd been creating extraneous entities which I don't really want. The best approach I've been able to implement this now while keeping the months field as a List<Integer> is by manually populating the months list in my DAO with the following code:

I then have to do a similar manual insertion or update with native queries in my dao insertRecipe(Recipe) and updateRecipe(Recipe) methods. This is a workable solution, but I see two problems:
1) The code's an unnecessary mess, it would be preferable if I could use O/R mapping to populate the list automatically.
2) This works fine for querying individual entity instances, but the months will be unpopulated if I do a query for a list of Recipe. Either that or I have to iterate through the entire result list and populate manually. This will put me in N+1 land, where I obviously don't want to be.

I can live with limitation #1, but #2 is pretty hard to swallow as even in this simple sample application there is a use case where I need access to the months for a list of recipes, which makes the N+1 issue here unavoidable.

Does anyone see a more elegant solution to this problem? In essence, what I'm looking to accomplish is an eager fetch-join style solution for populating the months List<Integer> property, also without having to redefine the property as a List<MonthRef> with a made-up entity of MonthRef just so JPA can execute the query. My current persistence provider is Toplink Essentials, but I'm open to switching to others. I'd also be interested to know if there is a more elegant solution for this using Hibernate and accessing Hibernate APIs directly without going through JPA.

Thanks in advance for any assistance or input.
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Not sure why you don't want to map the reference data as Entities, this would greatly simplify things, and avoid you having to have another application framework to maintain this stuff.

In TopLink or EclipseLink you can mark them as @ReadOnly and they would be automatically cached and not be updateable.

I would not recommend what you are doing, but if you wish to do it, the OneToMany to the reference data's Id could just be mapped as an @ElementCollection mapping (just map it to the join table).
This requires JPA 2.0 and upgrading from TopLink Essentials to EclipseLink, but you could even do this with TopLink Essential using a DirectCollectionMapping using a DescriptorCustomizer.

TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Rs Carter

Joined: Mar 02, 2012
Posts: 4
Thanks very much James. The update to EclipseLink and use of @ElementCollection provides exactly the kind of behavior I was looking for and works like a charm.
I agree. Here's the link:
subject: Best mapping approach for reference/LOV/lookup data