Persisting to SQL Server 2008 using stored procedures, JPA and Hibernate

Question

We are implementing a web page to maintain an organisational structure. The structure is stored in SQL Server 2008 and uses the new HierarchyID data type. Because we have had problems getting JPA and Hibernate to play with this new data type we have decided to use views and stored procedures to abstract away this data type. So we want to use a stored procedure to persist our entities but how you do this with JPA is unclear.

Firstly, are we taking the correct approach and secondly, is it possible to use stored procedures to persist entities annotated with JPA?

Answer 1

Firstly, are we taking the correct approach [...]

Well, it depends. If you don't mind being tied to you database engine then I guess it's not totally wrong to want to benefit from proprietary features like HierarchyID. But you don't have to use new features...

secondly, is it possible to use stored procedures to persist entities annotated with JPA?

To my knowledge, no. You can call stored procedure using "native queries" (see @NamedNativeQuery and/or EntityManager#createNativeQuery()) but you can't use them to persist entities, at least not with JPA. If you don't mind using Hibernate extensions, have a look at @SQLInsert(callable=true, ...) (see chapter 2.4.11. Custom SQL for CRUD operations of Hibernate Annotations documentation).

---

Personally, I find it very cumbersome to create views, stored procedures and to deal with JPA extensions just to use HierarchyID. New features are cool... when they do simplify things, not when they add more complexity which is the case here. In other words, because using HierarchyID doesn't really solve anything, I think I'd rather stick with a classic parent_id column (and this will make the process of changing the database engine smoother even if this is a very unlikely event).

Answer 2

You can use what @Pascal suggested or you can also use

@org.hibernate.annotations.NamedNativeQuery(
     callable=true,
     name="queryname",
     readOnly=true,
     query="call sproc_name(?,:param)",
     resultSetMapping="your_result_mapping"
)

see javadoc for full list of options

The only problem with this approach is that the out parameter (if you need one) must be first and must output a refcursor.

Also see this (it refers to functions but can be modified for sprocs). These examples are based on Oracle but the call string is easily modified for MSSQl.

I don't know much of HierarchyId is it possible to represent as a Hibernate UserType or any of the types?

Answer 3

We have settled on an approach where we use native queries to call stored procedures whenever we need to handle the hierarchy ID data type. This has allowed us to avoid having any proprietary SQL whilst still gaining the benefit of the new data type.

Our understanding, and initial findings, is that the hierarchy ID allows us to aggregate data across a tree structure simply by requesting all descendants of a given node.

For example to get a count of all orders across a 'n' depth structure of regions, offices, stores and departments could use something like the following:

SELECT COUNT(Orders) FROM Orders WHERE NodeOrderedAt.IsDescendantOf(@Node)

@ChssPly76 Thanks for the references to the two models. I will be reading those later :)