Stored Procedures and Spring Data JPA

Spring Data JPA is an amazing product. It has some quirks, but I like to think that I’ve figured out a way to navigate those and love working with it.

My latest endeavour had me searching for a way to call SQL Server stored procedures and grab the result set. I was surprised to find that there isn’t strong support for this type of pattern and moreover there’s a bug logged for the past year.

So, what if we had to get some information about a set of orders for a history report. The data is plentiful and we need to use a stored procedure for performance reasons. Also, this is the only time we will need the data in this fasion so a View would not make sense.

In our pretend case we may find a stored procedure like the one below to be the best approach. However, there are some pitfalls we should avoid.

IF OBJECT_ID ( 'dbo.spOrderHistoryForCustomer', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.spOrderHistoryForCustomer;
GO

CREATE PROCEDURE dbo.spOrderHistoryForCustomer
  @accountId INT
AS

  SELECT
    O.OrderId,
    O.AccountId,
    O.OrderTotal,
    C.FirstName,
    C.LastName
  FROM Orders WITH(NOLOCK)
    JOIN Customer C WITH(NOLOCK) ON C.AccountId = O.AccountId
  WHERE O.AccountId = @accountId;

GO

At first glance this may look to be a perfect place to use @NamedStoredProcedureQuery and @Procedure, however that approach is suffering from the aforementioned bug and quirks. Check out the following Jira ticket for more information https://jira.spring.io/browse/DATAJPA-1092

The issue is not with pure JPA, it’s actually a Spring Data bug that is blocked by a different Hibernate issue. The original reporter actually provides a solution similar to the one below. We can build on it a bit and morph our newfound quirk and make it a win.

Here’s the code snippet for now, with more to come later.

@PersistenceContext
private EntityManager em;

private List getOrderHistoryForCustomer(Integer accountId) {
    StoredProcedureQuery spq =
      em.createStoredProcedureQuery("spOrderHistoryForCustomer", Orders.class);
    spq.registerStoredProcedureParameter("accountId", Integer.class, ParameterMode.IN);
    spq.setParameter("accountId", accountId);
    return spq.getResultList();
}

Note: This was typed out in a bit of a rush, but I wanted to share before signing off for a bit. Check back soon.