Using the Hibernate Query Language

So far, we've covered various queries using LINQ, the Criteria API and its sibling, the QueryOver syntax. NHibernate provides another, more powerful, query method named Hibernate Query Language, which is a domain-specific language that blends familiar SQL-like syntax with Object-Oriented thinking. In this recipe, we'll show you how to use the Hibernate Query Language to perform the same queries as in the previous recipes.

Getting ready

Complete the Getting Ready section at the beginning of this chapter.

How to do it…

  1. Add a new folder named QueryByHql to the project.
  2. Add a new class named HqlQueries to the folder:
    using System.Collections.Generic;
using NH4CookbookHelpers.Queries;
using NH4CookbookHelpers.Queries.Model;
using NHibernate;

namespace QueryRecipes.QueryByHql
{
  public class HqlQueries : IQueries, IAggregateQueries
  {
    private readonly ISession _session;

    public HqlQueries(ISession session)
    {
      _session = session;
    }

    public IEnumerable<Movie> GetMoviesDirectedBy(
      string directorName)
    {
      var hql = @"from Movie m 
        where m.Director = :director";
      return _session.CreateQuery(hql)
        .SetString("director", directorName)
        .SetLockMode("m",LockMode.Upgrade)
        .List<Movie>();
    }

    public IEnumerable<Movie> GetMoviesWith(
      string actorName)
    {
      var hql = @"select m
        from Movie m
        inner join m.Actors as ar
        where ar.Actor = :actorName";
      return _session.CreateQuery(hql)
        .SetString("actorName", actorName)
        .List<Movie>();
    }

    public Book GetBookByISBN(string isbn)
    {
      var hql = @"from Book b
        where b.ISBN = :isbn";
      return _session.CreateQuery(hql)
        .SetString("isbn", isbn)
        .UniqueResult<Book>();
    }

    public IEnumerable<Product> GetProductsByPrice(
      decimal minPrice,
      decimal maxPrice)
    {
      var hql = @"from Product p
        where p.UnitPrice >= :minPrice
        and p.UnitPrice <= :maxPrice
        order by p.UnitPrice asc";

      return _session.CreateQuery(hql)
        .SetDecimal("minPrice", minPrice)
        .SetDecimal("maxPrice", maxPrice)
        .List<Product>();
    }

    public IEnumerable<NameAndPrice> GetMoviePriceList()
    {
      var hql = @"select new NameAndPrice(
        m.Name, m.UnitPrice)
        from Movie m";
      return _session.CreateQuery(hql)
        .List<NameAndPrice>();

    }

    public decimal GetAverageMoviePrice()
    {
      var hql = @"select Cast(avg(m.UnitPrice) 
        as Currency)
        from Movie m";
      return _session.CreateQuery(hql)
        .UniqueResult<decimal>();

    }

    public IEnumerable<NameAndPrice> GetAvgDirectorPrice()
    {
      var hql = @"select new NameAndPrice(
        m.Director, 
        Cast(avg(m.UnitPrice) as Currency)
        )
        from Movie m
        group by m.Director";
      return _session.CreateQuery(hql)
        .List<NameAndPrice>();

    }
  }
}
  3. Add a new class named Recipe to the folder:
    using NH4CookbookHelpers;
using NH4CookbookHelpers.Queries.Model;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Mapping.ByCode;

namespace QueryRecipes.QueryByHql
{
  public class Recipe : QueryRecipe
  {
    protected override void Configure(
      Configuration nhConfig)
    {
      var modelMapper = new ModelMapper();
      modelMapper.Import<NameAndPrice>();
      var mapping = modelMapper.
        CompileMappingForAllExplicitlyAddedEntities();
      nhConfig.AddMapping(mapping);
    }

    protected override void Run(ISession session)
    {
      var queries = new HqlQueries(session);
      ShowQueryResults(queries);
      ShowAggregateQueryResults(queries);
    }
  }
}
  4. Run the application and start the QueryByHql recipe. You should be able to see the following output:
    How to do it…

How it works…

Hibernate Query Language syntax resembles SQL in many ways, but operates at an object level. We build all of our queries as strings. Much like DbCommands in ADO.NET, we create IQuery objects around those query strings, set the parameter values, and execute our queries with List or UniqueResult. Similar to the at sign (@) in Microsoft SQL Server queries, in HQL, we prepend our parameter names with a colon (:) in the query string. When we set the parameter value, we don't include the colon:

  • GetMoviesDirectedBy query:

    We have a very basic HQL query, as shown:

    from Movie m 
where m.Director = :director

    For brevity, we've aliased our movies as simply m. In this case, there is an implied select m to project our movies. We have a single parameter, director, which we use to filter our movies.

  • GetMoviesWith query:
    select m
from Movie m
inner join m.Actors as ar
where ar.Actor = :actorName

    In this query, we join from movies to their actor roles. Note that unlike SQL, we don't need to specify ActorRoles or set up a comparison with an ON clause explicitly. NHibernate already understands the relationships between our entities. We filter those actor roles based on the actor name. Just as with SQL, because we use an inner join, this filter on actor role effectively filters our movies as well.

  • GetProductByPrice query:
    from Product p
where p.UnitPrice >= :minPrice
and p.UnitPrice <= :maxPrice
order by p.UnitPrice asc

    In this query, we filter our Product based on a price range defined by the two parameters, minPrice and maxPrice. This query could also be written using HQL's between:

    from Product p
where p.UnitPrice between
:minPrice and :maxPrice
order by p.UnitPrice asc

    As with SQL, the order by clause sorts our products by unit price.

  • GetMoviePriceList query:

    We have this simple query:

    select new NameAndPrice(m.Name, m.UnitPrice)
from Movie m

    When working with HQL, think in terms of objects and properties, not tables and columns. This query passes the Name and UnitPrice properties into this constructor of our NameAndPrice class:

    public NameAndPrice(string name, decimal unitPrice)

    Then it projects the resulting NameAndPrice instances. To make NHibernate aware of this class, we can use the following import mapping:

    <import class="NameAndPrice"/>

    In the recipe, we used mapping by code to do the same thing, as it can be seen in the Configure method.

    var modelMapper = new ModelMapper();
modelMapper.Import<NameAndPrice>();
var mapping = modelMapper.
  CompileMappingForAllExplicitlyAddedEntities();
nhConfig.AddMapping(mapping);

    As an alternative, just as with Criteria and QueryOver, we could simply project Name and UnitPrice, return a list of object arrays and then use LINQ to Objects to transform those object arrays into NameAndPrice instances, as shown in the following code:

    var hql = @"select m.Name, m.UnitPrice
            from Movie m";
var query = session.CreateQuery(hql);
return query.List<object[]>()
  .Select(props =>
    new NameAndPrice(
      (string)props[0],
      (decimal)props[1]));

    In this case, we wouldn't need to import our NameAndPrice class.

  • GetAverageMoviePrice query:
    select Cast(avg(m.UnitPrice) as Currency)
from Movie m

    In this query, we use the aggregate function average. This returns a scalar value of type double, so we cast it back to NHibernate's Currency type. The equivalent .NET type is decimal, so we execute the query using UniqueResult<decimal>().

  • GetAvgDirectorPrice query:
    select new NameAndPrice(
    m.Director, 
    Cast(avg(m.UnitPrice) as Currency)
)
from Movie m
group by m.Director

    In this query, we group by Director. We then pass Director and our average UnitPrice into the constructor of NameAndPrice. Just as before, because avg returns a double, we'll need to Cast it back to Currency first.

There's more…

In addition to the mapped properties and collections on our entities, HQL allows you to query on two implied and special properties:

  • The property class is the full name of the type of our entity. For example, to query for books, we could write the following:
    from Product p where p.class='Eg.Core.Book'
  • The property id always represents the POID of the entity, regardless of what we may name it in our entity. We can query for three books at a time with this query:
    from Book b where b.id in (@id0, @id1, @id2)

See also

  • Using CriteriaQueries
  • Using QueryOver
  • Using MultiQuery
  • Using named queries
  • Using detached queries
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.12.108.86