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, we'll show you how to use HQL to update all of our books with a single statement.
HqlBulkChanges
to the project.Recipe
to the folder:using System; using NH4CookbookHelpers.Queries; using NHibernate; namespace QueryRecipes.HqlBulkChanges { public class Recipe : QueryRecipe { protected override void Run(ISession session) { var hql = @"update Book b set b.UnitPrice = :minPrice where b.UnitPrice < :minPrice"; var updated=session.CreateQuery(hql) .SetDecimal("minPrice", 55M) .ExecuteUpdate(); Console.WriteLine("Number of books updated:" + updated); hql = @"delete from Book where UnitPrice=:minPrice"; var deleted = session.CreateQuery(hql) .SetDecimal("minPrice", 55M) .ExecuteUpdate(); Console.WriteLine("Number of books deleted:" + deleted); hql = @"insert into Book (Name,Description) select concat(Name,' - the book'), Description from Movie"; var inserted = session.CreateQuery(hql) .ExecuteUpdate(); Console.WriteLine(@"Number of movies recreated as books:" + inserted); } } }
HqlBulkChanges
recipe.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 where ProductType = 'Book' and UnitPrice < 55
The next query performs a deletion, with exactly the same logic. We delete the book we recently updated. The following SQL statement is executed:
delete from Product where ProductType = 'Book' and UnitPrice = 55
Now that we are all out of books, let's see if we can't create a couple of new ones by inserting "recreations" of all the movies. We execute the following HQL:
insert into Book (Name,Description) select concat(Name,' - the book'),Description from Movie
The HQL function concat
is used to form a new product name from the concatenation of the movie name and the " – the book" String. SQL Server receives the following statement:
insert into Product ( Name, Description, ProductType ) select (movie0_.Name+' - the book') as col_0_0_, movie0_.Description as col_1_0_, 'Book' from Product movie0_ where movie0_.ProductType='Movie'
Two things are worth noting here. First, the concat
function is automatically translated to the correct statement for the target database, in this case using the plus operator. Also, NHibernate is clever enough to add the necessary ProductType
column to the insert, so that the newly created entities will indeed be books.
These statements will only affect the database. The 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 these queries in the mapping and load queries like any other named queries.
As we saw in the last query, 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 things 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.
The id
values are particularly limited. There are two options:
id
is database-generated. This excludes nearly all of the preferred identity generators, such as guid.comb
and hilo
. To use the entity's id
generator, simply omit the id
column from the list of properties to be set.3.144.35.122