NHibernate: Mapping a Class to Several Tables
In wake of the recent discussion on Microsoft's Entity Framework, mapping types to more than one table was an issue that came up. One of the central features in EDM is that you can map a type to any number of tables. If you're creating a new application, with its own private database, this usually is not an issue because you'll probably map your classes to single tables. However, if you are working with a legacy database there is a chance that the database will not map one-to-one with the domain model.
Imagine that you have a database with the schema below. For some reason beyond your control, the employee details are stored in a different table than the employees. In your domain model you want to keep the Employee entity as one sole entity, rather than having the database dictate your model.
Both Hibernate and most JDO implementations support the joined tables pattern which allows a class to be mapped to several tables. Currently, NHibernate does not support this pattern, but it is still possible to map an entity to more than one table. The most common way is to adapt your domain model to fit the database schema. Below is an example of an Employee entity which is mapped to both the Employee and EmployeeDetails tables shown above.
public class Employee
{
private int id;
private string name;
private string code;
private EmployeeDetails details;
public int Id
{
get { return id; }
set
{
id = value;
details.Id = value;
}
}
public string Name
{
get { return name; }
set { name = value; }
}
public string Code
{
get { return code; }
set { code = value; }
}
public DateTime DateOfBirth
{
get { return details.DateOfBirth; }
set { details.DateOfBirth = value; }
}
public string PhoneNumber
{
get { return details.PhoneNumber; }
set { details.PhoneNumber = value; }
}
public string Comment
{
get { return details.Comment; }
set { details.Comment = value; }
}
public Employee()
{
details = new EmployeeDetails();
}
}
public class EmployeeDetails
{
public int id;
private DateTime dateOfBirth;
private string phoneNumber;
private string comment;
public int Id
{
get { return id; }
set { id = value; }
}
public DateTime DateOfBirth
{
get { return dateOfBirth; }
set { dateOfBirth = value; }
}
public string PhoneNumber
{
get { return phoneNumber; }
set { phoneNumber = value; }
}
public string Comment
{
get { return comment; }
set { comment = value; }
}
}
As you can see, there are actually two persisted classes; Employee and EmployeeDetails, but this is not apparent to the user because the EmployeeDetails type is encapsulated within the Employee class.
The two classes are mapped to their respective tables with the following mapping.
<hibernate-mapping xmlns='urn:nhibernate-mapping-2.2'>
<class name='JoinedTables.Employee, JoinedTables' table='Employees'>
<id name='Id'>
<generator class='native'/>
</id>
<property name='Name' column='name' />
<property name='Code' column='code' />
<one-to-one name="details" access="field"/>
</class>
<class name='JoinedTables.EmployeeDetails, JoinedTables' table='EmployeeDetails'>
<id name='Id'>
<generator class='assigned'/>
</id>
<property name='DateOfBirth' column='dob' />
<property name='PhoneNumber' column='phone' />
<property name='Comment' column='comment' />
</class>
</hibernate-mapping>
If you don't want to change the domain model to fit the database schema, you can delegate the creation and persistence of a composite Employee entity to a DAO or Repository. This will help you keep the domain model tidy. The example below show how this might be implemented. The mappings are similar to the previous example, and there is no need for a one-to-one mapping here.
public class EmployeeRepository : IRepository<Employee>
{
public Employee Load(object id)
{
EmployeeComponent employeeComponent =
(EmployeeComponent ) session.Load(typeof (EmployeeComponent ), id);
EmployeeDetailsComponent employeeDetailsComponent =
(EmployeeDetailsComponent ) session.Load(typeof (EmployeeDetailsComponent ), id);
Employee employee=new Employee();
employee.Id = employeeComponent.Id;
employee.Name = employeeComponent.Name;
employee.Code = employeeComponent.Code;
employee.DateOfBirth = employeeDetailsComponent.Dob;
employee.PhoneNumber =
employeeDetailsComponent.PhoneNumber;
employee.Comment = employeeDetailsComponent.Comment;
return employee;
}
public object Save(Employeeobj)
{
EmployeeComponent employeeComponent=
new EmployeeComponent (obj.Id,obj.Name,obj.Code);
EmployeeDetailsComponent employeeDetailsComponent=
new EmployeeDetailsComponent (obj.Id,obj.DateOfBirth,obj.PhoneNumber,obj.Comment);
object employeeId = session.Save(employeeComponent);
object employeeDetailsId =
session.Save(employeeDetailsComponent);
Debug.Assert(employeeId==employeeDetailsId);
return employeeId;
}
}
Playing along with the database schema might be ok in small applications, but in larger solutions it is better to have the database and domain model decoupled because the database schema should be modeled to enforce data integrity and enable efficient storage and retrieval of data. The domain model should be designed to reflect the business problems and make the business logic easy to build and maintain.
Both Microsoft SQL Server and Oracle support adapteable views. If you can add new views to the database or use another database to build views on the actual database schema, you can leverage this to create a "domain friendly" view of the logical employee entity.
Microsoft SQL Server's support for updateable views has some limits that we have to take into account when mapping our domain model to a view. The most important limitation is that you can only perform updates that affect one of the underlying tables at the time. To ensure atomic operations, NHibernate issues a single update or insert when an entity is changed or created. This will cause the operation to fail when an entity is mapped to a view composed of several tables.
SQL Server 2000 introduced INSTEAD OF triggers on views. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. The trigger allows the user to specify the set of actions that need to take place in order to process the data modification statement. This means that we can overcome the limitations of updateable view.
CREATE TRIGGER [dbo].[tr_UpdateEmployee] ON [dbo].[EmployeesView]
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE Employees SET
name=
(
SELECT i.name FROM INSERTED i INNER JOIN
EmployeeIds eid ON i.id=eid.surrogate_id
),
code=
(
SELECT i.code FROM INSERTED i INNER JOIN
EmployeeIds eid ON i.id=eid.surrogate_id
)
WHERE
id=
(
SELECT eid.emp_id FROM INSERTED i INNER JOIN
EmployeeIds eid ON i.id=eid.surrogate_id
)
UPDATE EmployeeDetails SET
dob=
(
SELECT i.dob FROM INSERTED i INNER JOIN
EmployeeIds eid ON i.id=eid.surrogate_id
),
phone=
(
SELECT i.phone FROM INSERTED i INNER JOIN
EmployeeIds eid ON i.id=eid.surrogate_id
),
comment=
(
SELECT i.comment FROM INSERTED i INNER JOIN
EmployeeIds eid ON i.id=eid.surrogate_id
)
WHERE
id=
(
SELECT eid.emp_id FROM INSERTED i INNER JOIN
EmployeeIds eid ON i.id=eid.surrogate_id
)
END
The above code shows the INSTEAD OF UDATE trigger. The INSTEAD OF INSERT trigger is very similar, while the INSTEAD OF DELETE trigger is much simpler. The trigger joins the operations against the EmployeeIds table. This is due to another limitation with updateable views; The Employees table has an identity column as its primary key. Under normal circumstances a "native" generator could be used to map this column and NHibernate would issue a SELECT scope_identity() statement after the UPDATE. However, since we're using INSTEAD OF triggers to intercept the create, update and delete operations the scope for the operations becomes the trigger rather than the SQL statements issued by NHibernate. A consequence of this is that NHibernate gets null instead of the identifier of the inserted entity and the insert fails. To work around this I've introduced a table to hold surrogate keys for the actual entities and changed the mapping to use an unique identifier rather than an integer as the primary key.
<hibernate-mapping xmlns='urn:nhibernate-mapping-2.2'>
<class name='JoinedTables.Employee, JoinedTables' table='EmployeesView'>
<id name='Id'>
<generator class='guid'/>
</id>
<property name='Name' column='name' />
<property name='Code' column='code' />
<property name='DateOfBirth' column='dob' />
<property name='PhoneNumber' column='phone' />
<property name='Comment' column='comment' />
</class>
</hibernate-mapping>
Whenever an entity is inserted a surrogate key correlation is created in the EmployeeIds table. Another possible solution for the scope_identity problem would be to port the SELECT generator strategy from Hibernate. This strategy enables you to select the newly inserted identifier from a table. A requirement for using this strategy is that you have a natural key, in addition to the primary key, that you can use to identify a record. The Employee entity has an employee code that we could have used for this purpose.
It is important to set the NOCOUNT ON option when mapping against updateable views to ensure that create, update and delete operations do not fail because the number of affected rows will be different from what NHibernate expects.
You have to jump through quite a few hoops to get NHibernate to map against a SQL Server updateable view. Unfortunately, I haven't got any Oracle servers with the advanced replication option around, so I haven't tried mapping against a materialized view.
With all these options, the best way to map an entity to more than one table is still to use the joined tables pattern. Karl Chu has written a patch that brings the capability to NHibernate, but this won't make it into the main branch until after NHibernate 1.2 is final.
With Karl's patch you can use the following mapping to map the two tables to a single Employee class.
<hibernate-mapping xmlns='urn:nhibernate-mapping-2.2'>
<class name='Employee' table='Employees'>
<id name='Id' type='int'>
<generator class="native"/>
</id>
<property name='Name' column='name' />
<property name='Code' column='code' />
<join table="EmployeeDetails">
<key>
<column name='id' />
</key>
<property name='DateOfBirth' column='dob'/>
<property name='PhoneNumber' column='phone'/ >
<property name='Comment' column='comment' />
</join>
</class>
</hibernate-mapping>
If you want to use this patch today, you'll have to build NHibernate your self. Still, this is not as difficult as it might appear.