Chapter 4. Queries

In this chapter, we will cover the following topics:

  • Using CriteriaQueries
  • Using QueryOver
  • Using QueryOver projections and aggregates
  • Using MultiCriteria
  • Using the Hibernate Query Language
  • Using MultiQuery
  • Using Named Queries
  • Using Futures
  • Eager loading child collections
  • Using LINQ to NHibernate
  • Using Detached Queries
  • Using HQL for bulk data changes

Introduction

All but the last two recipes in this chapter begin with the following common steps. In addition to the normal process of mapping our model and configuring log4net and NHibernate, this also takes care of the necessary but repetitive plumbing code.

  1. Complete the Eg.Core model and mapping project from Chapter 1.
  2. Add a new console application to your solution.
  3. Add an App.config file.
  4. In App.config, configure NHibernate and log4net following the Configuring NHibenate with App.config and Configuring NHibernate Logging recipes in Chapter 2.
  5. Create a new class called ExampleDataCreator with the following code:
    public class ExampleDataCreator
{
  private readonly ISessionFactory _sessionFactory;

  public ExampleDataCreator(
    ISessionFactory sessionFactory)
  {
    if (sessionFactory == null) 
      throw new ArgumentNullException("sessionFactory");
    _sessionFactory = sessionFactory;
  }

  public void SetUpDatabase()
  {
    using (var session = _sessionFactory.OpenSession())
    using (var tx = session.BeginTransaction())
    {
      ClearDatabase(session);
      CreateMovies(session);
      CreateBook(session);
      tx.Commit();
    }
  }

  private static void ClearDatabase(ISession session)
  {
    session
      .CreateQuery("delete from ActorRole")
      .ExecuteUpdate();

    session
      .CreateQuery("delete from Product")
      .ExecuteUpdate();
  }

  private static void CreateMovies(ISession session)
  {
    session.Save(
      new Movie()
        {
          Name = "Raiders of the Lost Ark",
          Description = "Awesome",
          UnitPrice = 9.59M,
          Director = "Steven Spielberg",
          Actors = new List<ActorRole>()
                     {
                       new ActorRole()
                         {
                           Actor = "Harrison Ford",
                           Role = "Indiana Jones"
                         }
                     }
        });

    session.Save(
      new Movie()
        {
          Name = "The Bucket List",
          Description = "Good",
          UnitPrice = 15M,
          Director = "Rob Reiner",
          Actors = new List<ActorRole>()
                     {
                       new ActorRole()
                         {
                           Actor = "Jack Nicholson",
                           Role = "Edward Cole"
                         },
                       new ActorRole()
                         {
                           Actor = "Morgan Freeman",
                           Role = "Carter Chambers"
                         }
                     }
        });
  }

  private static void CreateBook(ISession session)
  {
    session.Save(
      new Book()
        {
          Name = "NHibernate 3.0 Cookbook",
          Description = "NHibernate examples",
          UnitPrice = 50M,
          Author = "Jason Dentler",
          ISBN = "978-1-849513-04-3"
        });
  }

}
  6. Create another class called NameAndPrice with the following code:
    public class NameAndPrice
{

  public NameAndPrice()
  {
  }

  public NameAndPrice(string name, decimal price)
  {
    Name = name;
    Price = price;
  }

  public string Name { get; set; }
  public decimal Price { get; set; }

}
  7. Create a new class called Queries with this code:
    public class Queries
{
  private readonly ISession _session;

  public Queries(ISession session)
  {
    if (session == null) 
      throw new ArgumentNullException("session");
    _session = session;
  }

}
  8. In Program.cs, add the following methods to the Program class:
    static void RunQueries(ISession session)
{
}

static void Show(string heading,
             IEnumerable<Movie> movies)
{
  Console.WriteLine(heading);
  foreach (var m in movies)
    ShowMovie(m);
  Console.WriteLine();
}

static void Show(string heading, Book book)
{
  Console.WriteLine(heading);
  ShowBook(book);
  Console.WriteLine();
}

static void Show(string heading,
                 IEnumerable<Product> products)
{
  Console.WriteLine(heading);
  foreach (var p in products)
  {
    if (p is Movie)
    {
      ShowMovie((Movie)p);
    }
    else if (p is Book)
    {
      ShowBook((Book)p);
    }
    else
      ShowProduct(p);
  }
  Console.WriteLine();
}

static void Show(string heading,
                 decimal moneyValue)
{
  Console.WriteLine(heading);
  Console.WriteLine("{0:c}", moneyValue);
  Console.WriteLine();
}

static void Show(string heading,
                 IEnumerable<NameAndPrice> results)
{
  Console.WriteLine(heading);
  foreach (var item in results)
    ShowNameAndPrice(item);
  Console.WriteLine();
}

static void ShowNameAndPrice(NameAndPrice item)
{
  Console.WriteLine("{0:c} {1}",
                    item.Price, item.Name);
}

static void ShowProduct(Product p)
{
  Console.WriteLine("{0:c} {1}",
                    p.UnitPrice, p.Name);
}

static void ShowBook(Book b)
{
  Console.WriteLine("{0:c} {1} (ISBN {2})",
    b.UnitPrice, b.Name, b.ISBN);
}

static void ShowMovie(Movie movie)
{
  var star = movie.Actors
    .Select(actorRole => actorRole.Actor)
    .FirstOrDefault();

  Console.WriteLine("{0:c} {1} starring {2}",
    movie.UnitPrice, movie.Name, star ?? "nobody");
}
  9. Add the following code in the Main method:
    log4net.Config.XmlConfigurator.Configure();

var nhConfig = new Configuration().Configure();
var sessionFactory = nhConfig.BuildSessionFactory();

new ExampleDataCreator(sessionFactory)
  .SetUpDatabase();

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
  RunQueries(session);
  tx.Commit();
}

Console.WriteLine("Press any key");
Console.ReadKey();

    Note

    The SQL queries shown in this chapter, and in fact, throughout the book, are specific to the Microsoft SQL Server 2008 dialect. If you use a different dialect and RDBMS, the SQL queries resulting from these examples may be slightly different.

..................Content has been hidden....................

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