Hibernate version: 3.1.2
I'm in the process of porting an application with a legacy database (about 50 tables) to Java using Hibernate. I have almost finished the job but I still have a problem with mapping the "generic attribute feature" we have implemented in our application & legacy database.
"generic attributes" means: we can add attributes with one or multiple values to any business class in the application at runtime by adding the appropriate rows to a set of several tables.
The legacy application uses a lot of hand-made SQL to handle these tables. Now I would like to use Hibernate mapping for that job but with my current knowledge of Hibernate I just can't figure out how to do it.
Here's an example with Pseudo-DDL and Pseudo-Java code (it's quite complex and I have simplified the table structure a bit. I hope the intention is clear, though):
First, the database tables:
the
attribute table
Code:
create table attribute (
id bigint not null primary key,
name varchar not null unique
);
a table of all possible values to all attributes
Code:
create table attributevalue (
id bigint not null primary key,
value varchar not null,
attribute bigint references attribute (id)
);
a table of all types (classes) in the system
Code:
create table type (
id bigint not null primary key,
classname varchar not null unique -- references the java class name
);
Table
type2attribute connects attributes to types, i.e. it tells us which Java business-class can have which
generic attribute.
Column
multi specifies if the attribute can have multiple values or not
Code:
create table type2attribute (
type bigint not null references type (id),
attribute bigint not null references attribute (id),
multi boolean not null default false
);
Example tables for our business-classes. Each businessclass which takes part in the
generic attribute system has a primary key of type bigint and of name
id.
There are several tables for different businessclasses (i.e. many of them!)
Code:
create table businessclass (
id bigint not null primary key,
...
);
this is another business class table
Code:
create table anotherbusinessclass (
id bigint not null primary key,
...
);
Table
object2attributevalue connects attribute values to a specific businessclass object.
Note: column
objectid references column
id of a specific row in the businessclass table for class
type.
I can't put a foreign key constraint here because the table this column references to depends on the class of the object.
Code:
create table object2attributevalue (
objectid bigint not null,
type bigint not null references type(id),
value bigint not null references attributevalue(id)
);
Now, the Java classes:
A simple business class:
Code:
class BusinessClass {
private static final String type = "BusinessClass";
private Long id;
private Set<Attribute> attributes;
...
};
another simple business class:
Code:
class AnotherBusinessClass {
private static final String type = "AnotherBusinessClass";
private Long id;
private Set<Attribute> attributes;
...
};
the attribute class holds a set of attribute values:
Code:
class Attribute {
private Long id;
private String name;
private Set<AttributeValue> values;
};
the AttributeValue class:
Code:
class AttributeValue {
private Long id;
private String value;
private Attribute attribute; // optional
};
There are triggers configured to provide for referentinal integrity between classes and attribute values to be put in the database tables.
Here's an example of how this whole system might work:
Values of table
attributeCode:
id | name
---+-----------
1 | attributeA
2 | attributeB
Values of table
attributevalueCode:
id | value | attribute
---+-------+----------
1 | A | 1
2 | B | 1
3 | C | 1
4 | D | 1
5 | foo | 2
6 | bar | 2
Values of table
typeCode:
id | classname
---+---------------------
1 | BusinessClass
2 | AnotherBusinessClass
Values of table
type2attributeCode:
type | attribute | multi
-----+-----------+-------
1 | 1 | true
1 | 2 | false
2 | 1 | false
2 | 2 | false
Values of table
businessclassCode:
id | ...
---+---------------
1 | ... <-- bo1
2 | ...
3 | ... <-- bo2
Values of table
anotherbusinessclassCode:
id | ...
---+----------------
1 | ...
2 | ...
3 | ... <-- bo3
Values of table
object2attributevalueCode:
objectid | type | value
---------+------+-------
1 | 1 | 1 <-- bo1
1 | 1 | 4 <-- bo1
3 | 1 | 2 <-- bo2
3 | 2 | 3 <-- bo3
3 | 2 | 6 <-- bo3
And now some Java code to use this whole setup:
Code:
// load specific business-class objects from the DB
BusinessClass bo1 = BusinessClassDAO.get(1);
BusinessClass bo2 = BusinessClassDAO.get(3);
AnotherBusinessClass bo3 = AnotherBusinessClassDAO.get(3);
// load Attributes from the DB
Attribute attributeA = AttributeDAO.get(1);
Attribute attributeB = AttributeDAO.get(2);
// attributeA now contains a set of all elements of class
// AttributeValue this Attribute may have
attributeA.printValues();
A
B
C
D
// attributeB now contains a set of all elements of class
// AttributeValue this Attribute may have
attributeB.printValues();
foo
bar
// print list of configured attributes for each business object
bo1.printAttributes();
1
bo2.printAttributes();
1
bo3.printAttributes();
1
2
// print list of attribute values each business object actually holds
Attribute bo1_attribute1 = bo1.getAttribute(1);
bo1_attribute1.printValues();
A
D
Attribute bo2_attribute1 = bo2.getAttribute(1);
bo2_attribute1.printValues();
B
Attribute bo3_attribute1 bo3.getAttrinute(1);
Attribute bo3_attribute2 bo3.getAttrinute(2);
bo3_attribute1.printValues();
C
bo3_attribute2.printValues();
bar
The users use this system to dynamically add or remove attributes with specific attribute values to business classes. From the database and legacy application point of view it works quite fine. I also think I have defined the Java classes in a good way for application developers but I just can't find the right Hibernate mapping to put all together.
I see several problems here, but IMHO the two most important are:
The relationship between a specific business class and the corresponding attributes is only weak (they are linked by a tag like the Java classname which can't be used as a foreign key inside the database)
The collection of AttributeValue elements inside a Attribute object depends on either the table object2attributevalue (if the Attribute object is part of a business-class object) or is a simple many-to-one relationship between tables attribute and attributevalue (if the Attribute object is loaded by the Attribute data access object independent of any business class object)
How do I map all this?
Can anyone help me or give me a hint?
Many thanks!
- andreas
Login
FAQ
Search