In the previous chapter, we learned how to use NHibernate to insert, update, and delete individual entities using ISession
methods. NHibernate also allows us to perform some bulk data changes with executable HQL. In this recipe, I'll show you how we can use HQL to update all of our books with a single statement.
Set up a new NHibernate console application using our Eg.Core
model from Chapter 1. Configure log4net to send the NHibernate.SQL debug output to the .NET trace, just as we did in Chapter 2.
Add the following code to your Main
method:
using (var session = sessionFactory.OpenSession()) { using (var tx = session.BeginTransaction()) { var hql = @"update Book b set b.UnitPrice = :minPrice where b.UnitPrice < :minPrice"; session.CreateQuery(hql) .SetDecimal("minPrice", 55M) .ExecuteUpdate(); tx.Commit(); } }
We have the following executable HQL query:
update Book b set b.UnitPrice = :minPrice where b.UnitPrice < :minPrice
We call ExecuteUpdate
method of IQuery
to run this statement. This results in the following SQL statement:
update Product set UnitPrice = 55 /* @p0 */ where ProductType = 'Eg.Core.Book' and UnitPrice < 55 /* @p1 */
This will only affect the database. These changes will not be reflected in the state of in-memory objects, the second level cache, or anywhere else outside the database.
We could also define this query in a mapping and load it like any other named query.
In addition to bulk updates, NHibernate also supports bulk deletes and bulk inserts. The syntax for bulk deletes is identical to bulk updates, but without set
. Neither update nor delete support joins. Instead, use sub-queries in the where clause.
NHibernate supports bulk inserts in the following form:
insert into destinationEntity (id, prop1, prop2) select b.id, b.prop1, b.prop2 from sourceEntity b where...
There are a few items to keep in mind when considering this solution. First, property types must match exactly. While the database may be perfectly able to convert between types such as int
and long
, NHibernate requires them to be the same type.
IDs are particularly limited. There are two options:
guidcomb
and hilo
. To use the entity's ID generator, simply omit the ID column from the list of properties to be set.