I have inherited a legacy app and database. Part of the database schema was designed to represent relationships between entities using an association table that can represent associations between rows in any two tables. The definition of the association table is:
Associations {
id int not null auto_increment,
from_table varchar not null,
from_column varchar not null,
from_key int not null,
to_table varchar not null,
to_column varchar not null,
to_key int not null
}

I'm mapping classes to the entity tables (legacy app uses straight JDBC) and trying to decide on the best strategy for handling these associations until I can redefine the schema and data to use more typical foreign key relationships.
For instance if I have an entity A which normally has associations to entity B in a one-to-many relationship, I'd like to model the relationship in A as a collection of B's and have hibernate collect the B instances by resolving the relationships through the association table. Also, I'm using JPA with annotations using hibernate as the implementation.

Some of the approaches that I'm trying to figure out are using Map entities, using <loader> queries to resolve collections (not sure if this is supported in JPA), or using DAO methods to populate transient collections on demand.

If there are known patterns for dealing with this type of loose association between entities, I'd be grateful to learn of them.

Thanks!
Kalin

One approach that comes to my minds would be using views.

Step 1: find all combinations of from_table, from_field, to_table, to_field. E.g. (the count(*) is there so you can see those associations that exists just once, and maybe decide on another strategy to handle that case).

Step 2: for each combination of from_table/from_field/to_table/to_field, create a view à laThis view will serve as your association table.

Step 3: reverse engineer as usual with association tables. Hibernate reveng doesn't care much whether the association table is actually a view or not.

Some caveats apply:
A) Make sure that either the views are insertable and updatable, or that your code doesn't try to do inserts resp. updates. It depends on the capabilities of the database whether these views are or not.
B) You may have to point reveng in the right direction to make it figure out what view is an association table for what combinations of tables. I can't help with that because I never reverse engineered a database with many-to-many associations; one problem that I have seen that might come up again is that Reveng needs to know what the primary keys are, but the JDBC driver does not provide that information, hence the need for explicitness.
C) In general, reveng is a rather awkward and cumbersome process. It's worth it because it makes less errors than a reverse-engineering human would, but reveng does fight back.

Hmmm, thanks Joachim. That's an interesting approach.