11.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.
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)?.
FROMkeyword and the
WHERE Clauseare optional.
The result of execution of a UPDATE or DELETE statement is the number of rows that are actually affected (updated or deleted).
Example 11.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 11.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();
intvalue 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
Companytable 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_Employeeas a result of the successful execution of the previous example.
int deletedEntriesvalue 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.
Only the INSERT INTO ... SELECT ... form is supported; not the INSERT INTO ... VALUES ... form.
Example 11.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
idattribute 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.