NHibernate's "Stored Procedures" - Named Queries
time to read 12 min | 2350 words

First things first, don't get too excited, NHibernate doesn't support Stored Procedures (yet).

What I am talking about is encapsulating HQL queries in a similar manner to the way stored procedures work.

A little background before I start talking about the technical details. When using NHibernate, I'm usually pushing the use of NHibernate's Criteria approach, since this is by far the most easy way for people to comprehend how to query the database using NHibernate. While for simple uses HQL is very simple:

from

Employee e where e.MiddleName is not null

HQL can get complicated when you want to do complex stuff, and it has one big problem. It looks like SQL, but it is not SQL.

Take this example, for instnace:

from PresenseReport pr, EmployementContract ec

where pr.Employee = ex.Employee

and   pr.Date between ec.ValidityStart and ec.ValidityEnd

and   pr.Employee.IsMobile = true

and   pr.Date between :startDate and :endDate

and   pr.Employee.Manager = :manager

I will spare you the effort of trying to come up with the equilent SQL. This is still a simple query, but it is not something that can be expressed using NHibernate's Criterias.

Now we have got several options of how to use it. We can embed it in our code, but this has some serious disadvantages. I really hate to have strings in my application, and I would much rather use a seperate file or a resource somewhere, but it turns out that we don't need to. NHibernate has the concept of a Named Query, which allow you to define a query in the mapping files and give it a name, like this:

<?xml version="1.0" encoding="utf-8"?>

<hibernate-mapping xmlns:xsd="http://www.w3.org/2001/XMLSchema"

                               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

                              xmlns="urn:nhibernate-mapping-2.0">

       <query name="GetPresenceReportAndContractsForSubordinaceMobileEmployeesInDateRange">

              <![CDATA[

              from PresenseReport pr, EmployementContract ec

              where  pr.Employee = ex.Employee

              and    pr.Date between ec.ValidityStart and ec.ValidityEnd

              and    pr.Employee.IsMobile = true

              and    pr.Date between :startDate and :endDate

              and    pr.Employee.Manager = :manager

              ]]>

       </query>

</hibernate-mapping>

You may consider the query name too long, but I like to be explicit about such things.

Now we can use the same query in several locations, without copying and pasting the code. If you really like, you can even change the query without changing the client code (which is supposed to be the holy grail of stored procedures.

You can use this query like this:

session.GetNamedQuery("GetPresenceReportAndContractsForSubordinaceMobileEmployeesInDateRange").

       SetParameter("manager", currentEmployee).

       SetParameter("startDate", startDate).

       SetParameter("endDate", endDate).

       List();

Now, this still doesn't free the application developers from understanding what is going on, of course. For instnace, this query returns a list of tuples, of of those contains a precense report and the matching employement contract. I need to understand the return value and unpack it into a form that is easier to work with.

By the way, in this case, the idea is to get the employement contract and use that to validate the report. Notice that I am checking for the date of botht he employement contract validity and the precense report itself. It removes a whole set of issues that I suddenly do not hvave to think about.

If you wanted to be really fancy, it is trivial to take the XML above and use code generation to generate a query class that will wrap this functionality.

Tweet Share Share 0 comments
Tags: