Just as with SQL, mixing inline HQL with business logic is generally a losing battle. The code becomes unreadable, and the queries are nearly impossible to properly unit test. In this recipe, I'll show you how we can move these HQL queries out of our code, improve readability and testability, and even improve performance by parsing and pre-compiling queries.
- Complete the steps in the introduction at the beginning of this chapter, naming the new console application
NamedQueryExample. - Add a new mapping document named
GetBookByISBN.hbm.xmlwith the following xml code. 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> - In
App.config, add<mapping assembly="NamedQueryExample"/>below the mapping element forEg.Core. - Add the following methods to the
Queriesclass:public Book GetBookByISBN(string isbn) { return _session.GetNamedQuery("GetBookByISBN") .SetString("isbn", isbn) .UniqueResult<Book>(); } - In
Program.cs, use the following code in theRunQueriesmethod:static void RunQueries(ISession session) { var queries = new Queries(session); Show("This book:", queries.GetBookByISBN( "978-1-849513-04-3")); } - Build and run your application. You should see the following output:
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.
This is the optimal method for querying with NHibernate in nearly every case. As with any HQL query, NHibernate will parse, compile, and verify this query against our entity mappings and model. Because 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.
MultiQuery provides a shortcut for adding named queries. It looks like 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.
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 like 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.
The free open source HQL AddIn tool from Jose Romaniello integrates with Visual Studio 2010 to provide IntelliSense syntax highlighting, and syntax checking when designing HQL queries.
More information is available from the project's website at http://hqladdin.codeplex.com/.