JSP hibernate batch update and bulk delete processing code _JSP programming

Source: Internet
Author: User
Tags flush stmt oracle database
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 (); 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 (); 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 will 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) Asbeginupdate CUSTOMERS set age=age+1 where age>p_age; End

The above stored procedure has a parameter p_age that represents the customer's age, and the application can invoke the stored procedure in the following ways:
tx = Session.begintransaction (); Connection con=session.connection (); String procedure = "{call Batchupdatecustomer (?)}"; CallableStatement cstmt = Con.preparecall (procedure); Cstmt.setint (1,0); Setting the Age parameter to 0cstmt.executeupdate (); Tx.commit ();

As you can see from the above program, the application must also bypass the Hibernate API to invoke the stored procedure directly through the JDBC API.
The update () method of the various overloaded forms of the session can update only one object at a time, while some overloaded forms of the Delete () method allow HQL statements as arguments, such as:
Session.delete ("from Customer C where c.age>0");

If there are 10,000 records in the Customers table that are older than 0, the above code can delete 10,000 records. But the session's delete () method does not execute the following DELETE statement:
Delete from CUSTOMERS where age>0;

The session's Delete () method loads 10,000 customer objects into memory first through the following SELECT statement:
SELECT * FROM CUSTOMERS where age>0;

Next, execute the 10,000 DELETE statement, deleting the Customer object individually:
Delete from CUSTOMERS where Id=i;delete to CUSTOMERS where Id=j;......delete from CUSTOMERS where id=k;

This shows that directly through the Hibernate API for batch update and bulk deletion is not recommended. Using the JDBC API directly to execute related SQL statements or call related stored procedures is the best way to batch update and bulk Delete, both of which have the following advantages:
(1) Do not need to load the bulk of the data in the database into memory, and then update or modify them individually, so do not consume a lot of memory.
(2) can update or delete large quantities of data in an SQL statement.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.