Handling bulk updates and bulk deletions in hibernate

Bulk updating refers to the updating of mass data in a transaction, which refers to the deletion of bulk data in a transaction. The following program uses the Hibernate API to bulk update the age field for all records in the Customers table that are older than 0:

tx = session.beginTransaction();
Iterator customers=session.find("from Customer c where c.age>0").iterator();
while(customers.hasNext()){
Customer customer=(Customer)customers.next();
customer.setAge(customer.getAge()+1);
}
tx.commit();
session.close();

If there are 10,000 records in the Customers table that are older than 0, the find () method of the session loads 10,000 customer objects into memory at a draught. When the Tx.commit () method is executed, the cache is cleaned and hibernate executes the UPDATE statement for the 10,000 Update Customers table:

update CUSTOMERS set AGE=? …. where ID=i;
update CUSTOMERS set AGE=? …. where ID=j;
……
update CUSTOMERS set AGE=? …. where ID=k;

The above batch update method has two disadvantages:

(1) Consuming large amounts of memory, 10,000 customer objects must be loaded into memory before they are updated.

(2) The number of UPDATE statements executed is too large, each UPDATE statement can only update one customer object, must pass 10,000 update statements to update 10,000 customer objects, frequent access to the database, will greatly reduce the performance of the application.

To quickly release the memory occupied by 10,000 customer objects, you can immediately release its memory by calling the session's evict () method after each customer object is updated:

tx = session.beginTransaction();
Iterator customers=session.find("from Customer c where c.age>0").iterator();
while(customers.hasNext()){
Customer customer=(Customer)customers.next();
customer.setAge(customer.getAge()+1);
session.flush();
session.evict(customer);
}
tx.commit();
session.close();

In the above program, after modifying the age attribute of a customer object, the Flush () method and the evict () method of the session are called immediately, and the flush () method enables hibernate to update the database immediately based on the state change of the Customer object To immediately execute the relevant UPDATE statement; the evict () method is used to purge the customer object from the cache, thereby releasing the memory it occupies in time.

However, the evict () method can only slightly improve the performance of bulk operations because, regardless of whether or not the evict () method is used, hibernate must execute 10,000 UPDATE statements to update 10,000 customer objects, which is an important factor affecting bulk operation performance. If hibernate can execute the following SQL statement directly:

Update CUSTOMERS set age=age+1 where age>0;

Then one of the update statements can update the 10,000 records in the Customers table. However, hibernate does not directly provide an interface to execute this UPDATE statement. The application must bypass the Hibernate API and execute the SQL statement directly through the JDBC API:

tx = session.beginTransaction();
Connection con=session.connection();
PreparedStatement stmt=con.prepareStatement("update CUSTOMERS set AGE=AGE+1 "
+"where AGE>0 ");
stmt.executeUpdate();
tx.commit();

The above program demonstrates the process of bypassing the Hibernate API and accessing the database directly through the JDBC API. The application obtains the database connection used by the session through the connection () method of the session, and then creates the PreparedStatement object and executes the SQL statement through it. It is noteworthy that the application still declares the transaction boundary through the Hibernate transaction interface.

If the underlying database, such as Oracle, supports stored procedures, you can also perform bulk updates through stored procedures. Stored procedures run directly in the database and are faster. In an Oracle database, you can define a stored procedure named Batchupdatecustomer () with the following code:

create or replace procedure batchUpdateCustomer(p_age in number) as
begin
update CUSTOMERS set AGE=AGE+1 where AGE>p_age;
end;