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:
- The first option is to copy the ID from a property of the source entity. This may be the ID of the source entity, or any other property. Depending on your existing data, this is not always appropriate.
- The second option uses the entity's POID generator to create an identity for each newly inserted object. However, this only works when the ID is database-generated. This excludes nearly all of the preferred identity generators, such as
guidcombandhilo. To use the entity's ID generator, simply omit the ID column from the list of properties to be set.