NHibernate: Querying Persistent Objects

Summary

IQuery and ICriteria Interfaces

The IQuery and ICriteria interfaces are created using the Persistent Manager (ISession), and define methods for controlling execution of a query.

To create an instance of the IQuery interface, invoke the CreateQuery() method of the Persistent Manager (ISession).

NHibernate allows the creation of named queries, which are queries defined in the xml mapping file, using the <query> element. These queries always relate to a particular persistent class. Use the GetNamedQuery() method of the IQuery interface to retrieve a named query.

Hibernate Query Language (HQL)

HQL is a fully object-oriented query language integrated into NHibernate.

HQL Operators Comments
from HQL from operator supports queries for instances of a class and all instances of its subclasses.
where HQL where operator supports the same basic operators as SQL, including arithmetic expressions.
HQL where operator also supports calling SQL functions.
HQL supports subqueries in the where operator.
like HQL like operator allows wildcard searches, using the wildcard symbols "%" and "_"
order by HQL order by operator clause is similar to SQL and supports ascending and descending order using "asc" or "desc".
join HQL provides four ways of expressing inner and outer joins:
An ordinary join in the from clause.
A fetch join in the from clause.
A theta-style join in the where clause.
An implicit association join.
select HQL select operator allows allows the specification of which object or properties of objects to be returned in the query results. The select operator also support calling SQL functions.
Use the HQL select new operator to define a class to represent each row of results.
HQL supports the following aggregate functions: count(), min(), max(), sum(), and avg().
distinct HQL distinct operator is used to eliminate duplicates rows in query resultset.
group by HQL supports the group by operator to groups rows in the query resultset.
having HQL having operator performs restriction on groups.

Native SQL

Using the ISQLQuery interface, NHibernate supports the execution of SQL queries and stored procedures. To create an instance of the ISQLQuery interface, invoke the CreateSQLQuery() method of the Persistent Manager (ISession).

NHibernate named SQL queries can call stored procedures. However, a stored procedure must return a resultset to be able to work with NHibernate.

Customizing SQL Commands

NHibernate supports custom SQL statements for create, retrieve, update, and delete operations, which will override the SQL generated by NHibernate.

The custom commands are written in the mapping of the concerned class, using the <sql-insert>, <sql-update> and <sql-delete> properties.