Just as with SQL, mixing inline HQL with business logic is often a losing battle. The code becomes unreadable and the queries are nearly impossible to unit test properly. In this recipe, we'll show you how to move these HQL queries out of our code, improve readability and testability, and even improve performance by parsing and pre-compiling queries.
- Add a new folder named
NamedQueriesto the project. - Add a new mapping document named
Queries.hbm.xmlwith the followingxmlcode. Don't forget to set the Build action to Embedded Resource:<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> <query name="GetBookByISBN"> <![CDATA[ from Book b where b.ISBN = :isbn ]]> </query> </hibernate-mapping> - Add a new class named
NamedQueriesto the folder containing the following code:using NH4CookbookHelpers.Queries.Model; using NHibernate; namespace QueryRecipes.NamedQueries { public class NamedQueries { private readonly ISession _session; public NamedQueries(ISession session) { _session = session; } public Book GetBookByISBN(string isbn) { return _session.GetNamedQuery("GetBookByISBN") .SetString("isbn", isbn) .UniqueResult<Book>(); } } } - Add a new class named
Recipeto the folder:using NH4CookbookHelpers.Queries; using NHibernate; using NHibernate.Cfg; namespace QueryRecipes.NamedQueries { public class Recipe : QueryRecipe { protected override void Configure(Configuration nhConfig) { nhConfig.AddResource( "QueryRecipes.NamedQueries.Queries.hbm.xml", GetType().Assembly); } protected override void Run(ISession session) { var queries = new NamedQueries(session); Show("This book:", queries.GetBookByISBN( "Steven Spielberg")); } } } - Run the application and start the
NamedQueriesrecipe.
In this recipe, we use the familiar GetBookByISBN query. We use GetNamedQuery to build a standard HQL IQuery object. This time, we've defined the query in a mapping document rather than in code. For that reason, we used an override in the recipe class, which allows us to inject additional mapping:
nhConfig.AddResource( "QueryRecipes.NamedQueries.Queries.hbm.xml", GetType().Assembly);
This adds the named resource from the specified assembly to the existing mapping. The resource's name is derived from the assembly name and the location of the file.
As with any HQL query, NHibernate will parse, compile, and verify this query against our entity mappings and model. Since it's in a mapping document, this work is done upfront when we build the session factory. If NHibernate finds any errors, it will throw an exception when we build our session factory, instead of when we execute the query. This is preferable for the same reasons that compiler errors are preferable to runtime exceptions. It provides an obvious, upfront check. In addition, this upfront parsing and compilation is cached for later use. NHibernate only has to build the necessary SQL once.
In addition to HQL, NHibernate also allows us to create named queries in SQL. This is only appropriate in advanced cases where HQL simply won't work or where a query has been hand-optimized. The C# code for working with a SQL named query is identical to an HQL named query. This allows you to create queries in HQL and swap in a faster SQL query later without changing your application code. Only the mapping document is different. It looks similar to the following code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<sql-query name="GetBookByISBN_SQL">
<return alias="b" class="Eg.Core.Book, Eg.Core" />
<![CDATA[
SELECT
b.Id AS [b.Id],
b.Name AS [b.Name],
b.Description AS [b.Description],
b.UnitPrice AS [b.UnitPrice],
b.Author AS [b.Author],
b.ISBN as [b.ISBN]
FROM Product b
WHERE b.ProductType = 'Eg.Core.Book'
AND b.ISBN = :isbn
]]>
<query-param name="isbn" type="string"/>
</sql-query>
</hibernate-mapping>The return element defines the alias we use in our query results, as well as the entity to build from that data.
MultiQuery (described in Chapter 5, Improving Performance) provides a shortcut for including named queries. It looks similar to the following code:
var multiQuery = session.CreateMultiQuery()
.AddNamedQuery<int>("count", "CountAllProducts")
.Add<Product>("page", pageQuery);In this case, we use the shortcut to add our count query. In order to set the first result and maximum result count, we need to build our page query separately.