Monday, September 10, 2007

A Database Design that JPA Cannot Map

The other day, I had to JPA map a part of a database, that was built over a period of about 3-4 years using EJB 2.x CMP technology. This particular part of the database contains huge amounts of CLOB data, which also needs to be searchable on some of the data in the CLOBs. The CLOB data could be taken offline after some period of time. All of this had led to the following physical database design:

Table: REPORT
id : INTEGER
image_tablename : VARCHAR
search_attribute_1 : INTEGER
search_attribute_2 : VARCHAR

Table: IMAGE1
report_id : INTEGER
data : CLOB

So, we have a parent table REPORT, in which we have important data, extracted from the data CLOB into searchable columns on each row. Each row also contains a column image_tablename, that names which table, the data CLOB can be found in.

This design enables us to take complete image tables of CLOBs offline, without even moving rows around, having to be concerned about oracle watermarking issues, etc. But, having REPORT pinpoint the table where the data CLOB is, also makes it impossible to map with JPA. The @Entity annotation requires the table name to be set at mapping time.

Luckily, we could simply do a couple of native queries. One for locating the tablename on a report_id and one for getting the CLOB data from that table.

1 comment:

Stucco said...

I have such a scenario as well. Mine is a child/mother/father relationship.

The relationship to match getChildren is not easily possible because there is a motherid and a fatherid in the database for each person. The query would need to be get people where i am the mother union get people where i am the father.