Bulk saving data using JDBC (jdbcdaosupport,jdbctemplete)

Recently done a project to use Hibernate, and then the database bulk INSERT data when the use of Hibernate batch processing, but the efficiency is low, see the online said there are some restrictions, to prohibit the level two cache, but also more than a batch_size configuration, etc. Do not know whether to use the wrong or how to drop, insert 10,000 data the fastest time also need more than 30 seconds time, slow more than 50 seconds, compare tangled, and then use the JDBC batch processing, here are three tables, Device,alarm and Syslogalarm, but the Device table can be ignored, Little use, that is, and alarm have a one-to-many relationship, alarm and syslogalarm the primary key are manual control, not self-increment, alarm and Syslogalarm is a one-to-two relationship, now control alarm and syslogalarm the same primary key value, That is, the relationship between the alarm and syslogalarm the primary key value of the same, know one of the primary key can be found in another table, Syslogalarm has a alarm foreign key, so there is no way to let the primary key self-increment.

First, a tool class is needed to get the same connection link

//Get the same connection, referring to the implementation of Hibernate's Getcurrentsessionimport Java.sql.connection;import java.sql.sqlexception;import javax.sql.DataSource; Public classconnectionutil{ Public StaticFinal threadlocal<connection> connections =NewThreadlocal<connection>(); Public StaticConnection getconnection (DataSource DataSource) throws sqlexception{Connection C= Connections.Get();if(NULL==c) {C=datasource.getconnection (); Connections.set (c);
}returnC; }}

Spring's configuration file is gone. The following is a concrete implementation of the DAO layer

@Repository @scope ("prototype") Public classSyslogAlarmDao2 extends Jdbcdaosupport implements initializingbean{@resource Public voidSetdatasource (DataSource DataSource) { This. Setdatasource (DataSource); }Private LongCount =0L; String SQL1=""; String SQL2=""; PreparedStatement PS1=NULL; PreparedStatement PS2=NULL; Connection C=NULL; /**
Here is an article to help understand the following annotations http://blog.csdn.net/yaerfeng/article/details/8447530
*/
@PostConstruct// 　　  Public void Set() throws sqlexception{SQL1="SQL";//omitted, SQL string with question markSQL2 ="SQL"; Ibid. C= Connectionutil.getconnection ( This. Getdatasource ()); PS1=c.preparestatement (SQL1); PS2=c.preparestatement (SQL2);
/**
Because the primary key is manually controlled, you need to check the maximum value of the ID that already exists in the database, and then add the data from the maximum value of +1
Alarm and Syslogalarm have the same primary key value, so check one to
*/PreparedStatement PS3= C.preparestatement ("select Max (ID) from Alarm2"); ResultSet RS=Ps3.executequery ();  while(Rs.next ()) {count= Rs.getlong (1); }} Public voidExecuteBatch (Syslogalarm salarm) throws sqlexception{if(salarm==NULL|| Salarm.getalarm = =NULL) {System. out. println ("Input Error"); return; } ps1.setlong (1, Count); Populate the SQL string with values from Salarm to PS1
   //..............Ps2.setlong (1, Count); Populate the SQL string with values from Salarm to PS2
    //..........Ps1.addbatch ();　　Ps2.addbatch (); System. out. println ("called the"+count+"Times"); if(count% +==0) {//for 10 when inserting 10,000 takes 4.061 seconds, 100 inserts 10,000 data need 1.403 seconds, 1000 for the time to insert 10,000 data need about 0.747 seconds time Ps1.executebatch ();　　　　Ps2.executebatch ();　　　　Ps1.clearbatch ();　　Ps2.clearbatch (); }//Ps1.executebatch (); //Execute remaining SQL//Ps2.executebatch (); Ibid .}}

Test

//Service Shenglue@Test Public voidTestbatchinsert () {ApplicationContext AC=NewClasspathxmlapplicationcontext ("Spring XML Path");DoubleStart =System.currenttimemillis (); Syslogalarmservice SAS= (Syslogalarmservice) Ac.getbean ("Syslogalarmservice"); for(intI=1;i<10001; i++{//When 1000 batches are processed, inserting 10,000 data takes about one second, 100,000 data is about 7 seconds, 200,000 data is about 13 seconds, 500,000 data 31 seconds or so device D=NewDevice ();d. SetId (1); Alarm Alarm=NewAlarm (); Alarm.setdevice (d);//Alarm.setsyslogalarm Salarm=Newsyslogalarm (); Salarm.setalarm (alarm);//Salarm.setSas.batchinsert (salarm);DoubleEnd =System.currenttimemillis (); System. out. println ("carried out:"+ (End-start)/ ++"seconds");}}

Bulk saving data using JDBC (jdbcdaosupport,jdbctemplete)