12.4.6. About Bulk Update, Insert and Delete
Hibernate allows the use of Data Manipulation Language (DML) to bulk insert, update and delete data directly in the mapped database through the Hibernate Query Language.
Warning
Using DML may violate the object/relational mapping and may affect object state. Object state stays in memory and by using DML, the state of an in-memory object is not affected depending on the operation that is performed on the underlying database. In-memory data must be used with care if DML is used.
The pseudo-syntax for UPDATE and DELETE statements is:
( UPDATE | DELETE ) FROM? EntityName (WHERE where_conditions)?.
Note
The
FROM keyword and the WHERE Clause are optional.
The result of execution of a UPDATE or DELETE statement is the number of rows that are actually affected (updated or deleted).
Example 12.5. Example of a Bulk Update Statement
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
String hqlUpdate = "update Company set name = :newName where name = :oldName";
int updatedEntities = s.createQuery( hqlUpdate )
.setString( "newName", newName )
.setString( "oldName", oldName )
.executeUpdate();
tx.commit();
session.close();
Example 12.6. Example of a Bulk Delete statement
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
String hqlDelete = "delete Company where name = :oldName";
int deletedEntities = s.createQuery( hqlDelete )
.setString( "oldName", oldName )
.executeUpdate();
tx.commit();
session.close();
The
int value returned by the Query.executeUpdate() method indicates the number of entities within the database that were affected by the operation.
Internally, the database might use multiple SQL statements to execute the operation in response to a DML Update or Delete request. This might be because of relationships that exist between tables and the join tables that may need to be updated or deleted.
For example, issuing a delete statement (as in the example above) may actually result in deletes being executed against not just the
Company table for companies that are named with oldName, but also against joined tables. Thus, a Company table in a BiDirectional ManyToMany relationship with an Employee table, would lose rows from the corresponding join table Company_Employee as a result of the successful execution of the previous example.
The
int deletedEntries value above will contain a count of all the rows affected due to this operation, including the rows in the join tables.
The pseudo-syntax for INSERT statements is:
INSERT INTO EntityName properties_list select_statement.
Note
Only the INSERT INTO ... SELECT ... form is supported; not the INSERT INTO ... VALUES ... form.
Example 12.7. Example of a Bulk Insert statement
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
String hqlInsert = "insert into Account (id, name) select c.id, c.name from Customer c where ...";
int createdEntities = s.createQuery( hqlInsert )
.executeUpdate();
tx.commit();
session.close();
If you do not supply the value for the
id attribute via the SELECT statement, an ID is generated for you, as long as the underlying database supports auto-generated keys. The return value of this bulk insert operation is the number of entries actually created in the database.
