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.