Hibernate HQL problems with database views and deleted rows within the same transaction

I found an interesting Hibernate view-related issue yesterday when deleting some data from a table within a single transaction.

Let me set the scene a little - we have a People table which has one row per person. We have a FamilyTree database view which displays family trees using the People table as one of it's sources (it's quite a complex view with functions and the like but you get the picture).

I was writing some unit tests around the area of deleting people. I used the view to get a list of people, deleted a person from the People table and then used the view to ensure that the person was gone. All of this was done with Hibernate-annotated domain objects and some JPA-based DaoSupport style classes - the standard Spring/Hibernate way.

What I found was that when we were within a single transaction (i.e. within a unit test or within a web controller using the OpenSessionInView) and we delete from the table, the 'deleted row' is still visible and therefore incorrect. If you looked at the SQL trace you would find that the DELETE SQL statement was never flushed to the database as Hibernate is trying to be lazy and only perform such actions when it really has too.

As there are no annotations for telling Hibernate that this entity is actually backed by a view rather than a table, there is no way to tell Hibernate that it must flush changes to the People table when we access the FamilyTree view! This was (roughly) the code that was failing:

List results = getJpaTemplate().find("FROM FamilyTree ft WHERE ft.parent = ?", parent);
return results.get(0).getPerson();

As the HQL doesn't make a reference to the People table or the person relationship within the FamilyTree entity, Hibernate does not know that it needs to flush any outstanding People changes before it performs the SELECT. To prove this if we add a simple SELECT * FROM people between the row delete and the view access then the delete is flushed and so the view displays the correct information.

This didn't seem to be the cleanest of solutions so I tried changing my HQL query of the FamilyTree view to return a Person entity like so:

List results = getJpaTemplate().find("SELECT ft.person FROM FamilyTree ft WHERE ft.parent = ?", parent);
return results.get(0);

As we have explicitly mentioned in the HQL that we want the person child object of the entity Hibernate is forced to flush any outstanding People changes before it performs the SELECT!

I know that using database views within Hibernate is considered 'advanced' but it would would be nice to have some kind of @ReliesOn annotation to cover these edge cases. We now have to try and remember this strange behaviour whenever we deal with views.

I'm no expert on Spring and Hibernate so I may have got the wrong end of the stick completely - if that is the case then please comment on the right way to solve this problem as I would love to learn!

Technorati Tags: Spring, Hibernate, HQL, Database, View, Relationship, Andrew Beacock