Using the Hibernate Query Language

So far, we've covered various queries using NHibernate's Criteria API and its new QueryOver syntax. NHibernate provides another, more powerful query method named Hibernate Query Language, a domain-specific language that blends familiar SQL-like syntax with object-oriented thinking. In this recipe, I'll show you how to use the Hibernate Query Language to perform those same queries.

How to do it...

  1. Complete the steps in the introduction at the beginning of this chapter, naming the new console application HQLExample.
  2. Add a new mapping document named NameAndPrice.hbm.xml with this 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"
    assembly="HQLExample"
    namespace="HQLExample">
  <import class="NameAndPrice"/>
</hibernate-mapping>
  3. In App.config, add <mapping assembly="HQLExample"/> below the mapping element for Eg.Core.
  4. Add the following methods to the Queries class:
    public IEnumerable<Movie> GetMoviesDirectedBy(string directorName)
{
  var hql = @"from Movie m 
              where m.Director = :director";
  return _session.CreateQuery(hql)
    .SetString("director", directorName)
    .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> GetProductByPrice(
  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>();
}
  5. In Program.cs, use the following code in the RunQueries method:
    static void RunQueries(ISession session)
{
  var queries = new Queries(session);

  Show("Movies directed by Spielberg:",
    queries.GetMoviesDirectedBy(
    "Steven Spielberg"));

  Show("Movies with Morgan Freeman:",
    queries.GetMoviesWith(
    "Morgan Freeman"));

  Show("This book:",
    queries.GetBookByISBN(
    "978-1-849513-04-3"));

  Show("Cheap products:",
    queries.GetProductByPrice(0M, 15M));

  Show("Movie Price List:",
    queries.GetMoviePriceList());

  Show("Average Movie Price:",
    queries.GetAverageMoviePrice());

  Show("Average Price by Director:",
    queries.GetAvgDirectorPrice());
}
  6. Build and run your application. You should see the following output:
    How to do it...

How it works...

Hibernate Query Language (HQL) 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 this very basic HQL query:

    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. Notice 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 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 use the following import mapping:

    <import class="NameAndPrice"/>

    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 Criteria Queries
  • 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
52.15.135.175