Hibernate BULK Insert (&&JDBC)

First, BULK INSERT (two ways)

1, via hibernate Cache

If you write this code in bulk insert (initial assumption):

Package com.anlw.util;  Import org.hibernate.Session;  Import Org.hibernate.SessionFactory;  Import org.hibernate.Transaction;  Import Org.hibernate.boot.registry.StandardServiceRegistryBuilder;  Import org.hibernate.cfg.Configuration;    Import Org.hibernate.service.ServiceRegistry;    Import com.anlw.entity.Student;      public class Sessionutil {Configuration conf = null;      Serviceregistry st = null;      Sessionfactory SF = null;      Session sess = null;        Transaction tx = NULL;          public void Hibernatetest () {conf = new Configuration (). Configure ();          st = new Standardserviceregistrybuilder (). Applysettings (Conf.getproperties ()). build ();          SF = Conf.buildsessionfactory (ST);              try {sess = Sf.opensession ();              tx = Sess.begintransaction ();                  for (int i = 0; i < i++) {Student s = new Student ();                  S.setage (i + 1);                  S.setname ("test"); SEss.save (s);           if (i%100 = = 0) {//With every 100 data as a processing unit sess.flush ();           Maintain synchronization with database data sess.clear ();          Clear all data of the first level cache at session level, free up memory in Time}} tx.commit ();              } catch (Exception e) {if (tx! = null) {tx.rollback ();              }} finally {Sess.close ();          Sf.close (); }} public static void Main (string[] args) {new Sessionutil ().      Hibernatetest ();   }  }

If the amount of data is too large, a memory overflow exception may occur;

Small knowledge:

(1). Hibernate cache, there is no limit to its capacity, forced to use, because all the objects are saved to this cache, memory will always reach a certain number of memory overflow situation;

(2). Hibernate level Two cache can be configured for size;

To solve the problem of memory overflow, you should regularly brush the data in the Sessiion cache to the database, the correct BULK Insert method:

(1). set Batch size (bloggers have not yet understood the difference between this attribute and the Flush () method)

<property name= "Hibernate.jdbc.batch_size" >2</property>

The reason to configure this parameter is to try to read the database as much as possible, the larger the parameter value, the less the number of times to read the database, the faster the speed; This configuration is hibernate is waiting for the program to accumulate 100 SQL in bulk submission;

(2). Turn off Level two cache (this blogger is not very clear)

<property name= "Hibernate.cache.use_second_level_cache" >false</property>

In addition to the session level cache, Hibernate also has a sessionfactory level two cache, and if level two caching is enabled, Hibernate is, on the mechanism, to maintain a level two cache, when in bulk INSERT, Hibernate will include objects in the level two cache, there will be significant performance loss, and may throw exceptions, so it is best to turn off the level two cache of sessionfactory;

(3). On the basis of the completion of the one or two setting, the first level cache of session level is emptied;

Package com.anlw.util;  Import org.hibernate.Session;  Import Org.hibernate.SessionFactory;  Import org.hibernate.Transaction;  Import Org.hibernate.boot.registry.StandardServiceRegistryBuilder;  Import org.hibernate.cfg.Configuration;    Import Org.hibernate.service.ServiceRegistry;    Import com.anlw.entity.Student;      public class Sessionutil {Configuration conf = null;      Serviceregistry st = null;      Sessionfactory SF = null;      Session sess = null;        Transaction tx = NULL;          public void Hibernatetest () {conf = new Configuration (). Configure ();          st = new Standardserviceregistrybuilder (). Applysettings (Conf.getproperties ()). build ();          SF = Conf.buildsessionfactory (ST);              try {sess = Sf.opensession ();              tx = Sess.begintransaction ();                  for (int i = 0; i < i++) {Student s = new Student ();                  S.setage (i + 1);                  S.setname ("test"); SEss.save (s);           if (i%100 = = 0) {//With every 100 data as a processing unit sess.flush ();           Maintain synchronization with database data sess.clear ();          Clear all data of the first level cache at session level, free up memory in Time}} tx.commit ();              } catch (Exception e) {if (tx! = null) {tx.rollback ();              }} finally {Sess.close ();          Sf.close (); }} public static void Main (string[] args) {new Sessionutil ().      Hibernatetest ();   }  }

2, bypassing hibernate, calling the JDBC API directly

Package com.anlw.util;  Import java.sql.Connection;  Import java.sql.PreparedStatement;    Import java.sql.SQLException;  Import org.hibernate.Session;  Import Org.hibernate.SessionFactory;  Import org.hibernate.Transaction;  Import Org.hibernate.boot.registry.StandardServiceRegistryBuilder;  Import org.hibernate.cfg.Configuration;  Import Org.hibernate.jdbc.Work;    Import Org.hibernate.service.ServiceRegistry;      public class Sessionutil {Configuration conf = null;      Serviceregistry st = null;      Sessionfactory SF = null;      Session sess = null;        Transaction tx = NULL;          public void Hibernatetest () {conf = new Configuration (). Configure ();          st = new Standardserviceregistrybuilder (). Applysettings (Conf.getproperties ()). build ();          SF = Conf.buildsessionfactory (ST);              try {sess = Sf.opensession ();              tx = Sess.begintransaction (); Executes the operation specified by the work object, that is, invoking the Execute () method of the Work object//session the currently used databaseThe connection is passed to the Execute () method Sess.dowork (new work () {@Override public void execute (Connection arg0) throws SQLException {//need to be aware that you do not need to call the close () method to close this connection//through the JDBC API for bulk interpolation                      SQL statements into the String sql = "INSERT into student (Name,age) VALUES (?,?)";                      PreparedStatement PS = arg0.preparestatement (SQL);                          for (int i=0;i<10;i++) {ps.setstring (1, "Kobe");                          Ps.setint (2,12);                      Ps.addbatch ();                  } ps.executebatch ();              }              });          Tx.commit ();              } catch (Exception e) {if (tx! = null) {tx.rollback ();              }} finally {Sess.close ();          Sf.close (); }} public static void Main (string[] args) {new Sessionutil (). Hibernatetest ();      }  } 

Note: Using the PreparedStatement interface in the JDBC API to execute SQL statements, the data involved in the SQL statement is not loaded into the session's cache, so it does not take up memory space and therefore calls the JDBC directly API Bulk INSERT is more efficient than hibernate cache bulk insert;

From: http://blog.csdn.net/an_2016/article/details/51759890

3,hibernate.jdbc.fetch_size and Hibernate.jdbc.batch_size

Hibernate.jdbc.fetch_size 50

Hibernate.jdbc.batch_size 25

These two options are very important!!! Will severely affect the crud performance of Hibernate!

Fetch Size is the number of record strips that are fetched from the database each time the JDBC statement reads data. For example, to query 10,000 records at a time, for the JDBC driver of Oracle, it is not 1 times to take out 10,000, but only fetch the number of fetched size, when the record set traversed these records, then go to the database fetch the size bar data. Therefore, the unnecessary memory consumption is greatly saved. Of course, the larger the fetch size is, the fewer times the database is read, and the faster the fetch size is, the more times the database is read, the slower the speed. It's kind of like when we write a program to write a hard disk file, set up a buffer, each write buffer, and so on after the buffer is full, once written to the hard disk, the same reason.

The JDBC Drive default Fetch size=10 for Oracle databases is a very conservative setting, and as a test, when fetch SIZE=50, performance increases by 1 time times, and when fetch size=100, performance continues to increase 20%,fetch Size continues to grow, and the performance gains are not significant, but consume too much memory. Therefore, it is recommended that you set the fetch size to at least when using Oracle .

However, not all databases support the Fetch size feature, for example MySQL is not supported. MySQL is like the worst-case scenario, and he always takes 10,000 records completely out of the way, and memory consumption can be very, very amazing! There is no good way out of this situation.

Batch size is the batch size that is set for bulk delete, batch update, and BULK INSERT, which is a bit equivalent to setting buffer buffer size. The larger the batch size, the smaller the number of times the batch operation sends SQL to the database, the faster it gets. One test result is that when batch size=0, it takes 25 seconds to delete 10,000 records from the Oracle database using Hibernate, and Batch Size = 50, it takes only 5 seconds to delete!!!

In addition hibernate.max_fetch_depth sets the maximum depth value of the outer join fetch tree. The recommended setting is between 0 and 3. Is every time you query, will cascade query depth, such as you set the association VO Eager, if the fetch_depth value is too small, will send many more SQL, affecting the query rate.

Hibernate BULK Insert (&&JDBC)