Sqlite multi-thread warehouse receiving

Today, the manager gave me an SQL file of over 30 mb and asked me to test the data to locate the problem. By convention, I use navicat for SQLite to create a table and then import the SQL file. I went on to do other things. After more than an hour, I thought the data should be imported. I looked at it, sweated, and died there. I turned off the software and re-imported it again. It's still that virtue. I also learned that the manager has been directed by myself and has not succeeded. It seems that the tool import method does not work.

However, if we think about more than 10 million data records, that is, more than 10 million insert SQL statements, is that difficult? So I want to write a program for import. Although there were some small episodes in the middle, the data was successfully imported.

The program code is as follows:

Package COM. geoway. pad. common. tool; import Java. io. bufferedreader; import Java. io. file; import Java. io. fileinputstream; import Java. io. filenotfoundexception; import Java. io. ioexception; import Java. io. inputstream; import Java. io. inputstreamreader; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. sqlexception; import Java. SQL. statement; import Java. util. concurrent. executorservice; impor T Java. util. concurrent. executors; import Java. util. concurrent. timeunit; /*** @ author likehua * @ note SQLite database creation and batch storage **/public class batchtool {// DDL Private Static string DDL = "create table if not exists pbeijing_point (objectid integer, name text, address text, phone text, fax text, type text, citycode text, URL text, email text, name2 text, X integer, y integer) "; connection jcon = NULL; // get connectionpublic s Ynchronized connection getconnection () {If (jcon = NULL) {// JSON = Statement state = NULL; try {class. forname ("org. SQLite. JDBC "); jcon = drivermanager. getconnection ("JDBC: SQLite: C: \ newd. DB "zookeeper state=jcon.createstatement();state.exe cuteupdate (DDL);} catch (sqlexception e) {e. printstacktrace ();} catch (classnotfoundexception e) {e. printstacktrace () ;}} return jcon;} // create 500 threads executorservice = executors. newf Ixedthreadpool (500); // read SQL files. Each five hundred insert statements are operated by one thread in batches by public void readbatchsql (inputstream is) throws ioexception {bufferedreader bufferreader = new bufferedreader (New inputstreamreader (is, "UTF-8"); string line; string one = ""; int tag = 0; string batchsql = ""; while (line = bufferreader. readline ())! = NULL) {one + = line; If (one. indexof (";")! =-1) {batchsql + = one; one = ""; // reset tag ++ ;}; // a thread if (tag! = 0 & amp; tag/500! = 0) {service.exe cute (New sqlitebatchhandler (batchsql); batchsql = ""; // reset tag = 0; // reset} // finally execute the remaining SQL if (batchsql. length ()> 0) {system. out. println ("finalsql:" + batchsql); runnable r = new sqlitebatchhandler(batchsql1_service.exe cute (r);}; try {// close the thread pool this. service. shutdown (); this. service. awaittermination (1, timeunit. hours );
Getconnection (). Close ();
} Catch (interruptedexception e) {e. printstacktrace ();} catch (sqlexception e) {e. printstacktrace () ;}};/*** @ note split SQL **/Private Static string [] splitsql (string batchsql) {If (batchsql! = NULL) {return batchsql. split (";") ;}; return NULL ;}/ *** @ note perform the batch update operation * due to connection. if the statement is not close during the COMIT operation, an error is reported. Therefore, this method is synchronized. **/Private synchronized void exucteupdate (string batch) {statement ste = NULL; connection con = NULL; try {con = getconnection (); con. setautocommit (false); ste = con. createstatement (); string [] sqls = This. splitsql (batch); For (string SQL: sqls) {If (SQL! Choose null1_ste.addbatch(sql1_1_1_1_ste.exe cutebatch ();
Ste. close (); con. commit (); // submit} catch (Exception e) {e. printStackTrace (); System. out. println ("execution failed:" + batch); try {con. rollback (); // rollback} catch (SQLException e1) {e1.printStackTrace () ;}} finally {if (ste! = Null) {try {ste. close ();} catch (SQLException e) {e. printStackTrace () ;}}}/*** @ author likehua * @ note receiving thread **/private class SQLiteBatchHandler implements Runnable {private String batch; public SQLiteBatchHandler (String SQL) {this. batch = SQL ;}; @ SuppressWarnings ("static-access") @ Overridepublic void run () {try {Thread. currentThread (). sleep (50);} catch (InterruptedException e) {e. printStackTrace ();} if (this. batch. length ()> 0) {exucteUpdate (batch );};}} /*** @ author likehua * @ note main function entry **/public static void main (String [] args) throws FileNotFoundException, IOException {BatchTool s = new BatchTool (); s. readBatchSQL (new FileInputStream (new File ("c: \ poi. SQL ")));}}

　　

The program uses the sqlite jdbc driver jar package. : Http://files.zentus.com/sqlitejdbc/sqlitejdbc-v056.jar