In-depth 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:
Copy codeThe 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;
Import java. util. concurrent. Executors;
Import java. util. concurrent. TimeUnit;
/**
* @ Author likehua
* @ Note SQLite database creation and batch warehouse receiving
**/
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 connection
Public synchronized Connection getConnection (){
If (jCon = null ){
// Json =
Statement state = null;
Try {
Class. forName ("org. sqlite. JDBC ");
JCon = DriverManager. getConnection ("jdbc: sqlite: c: \ newD. db ");
State = jCon. createStatement ();
State.exe cuteUpdate (ddl );
} Catch (SQLException e ){
E. printStackTrace ();
} Catch (ClassNotFoundException e ){
E. printStackTrace ();
}
}
Return jCon;
}
// Create 500 threads
ExecutorService service = Executors. newFixedThreadPool (500 );
// Read SQL files. Each five hundred insert statements are operated in batches by one thread.
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 ++;
};
// Enable a thread to meet the conditions
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 (batchSql );
Service.exe cute (r );
};
Try {
// Close the thread pool
This. service. shutdown ();
This. service. awaitTermination (1, TimeUnit. HOURS); <BR> getConnection (). close (); <BR >}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.
* If the statement is not close during the connection. comit operation, an error is returned. Therefore, this method is added for synchronization.
**/
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! = Null ){
Ste. addBatch (SQL );
};
};
Ste.exe cuteBatch (); <BR> ste. close ();
Con. commit (); // submit
} Catch (Exception e ){
E. printStackTrace ();
System. out. println ("execution failed:" + batch );
Try {
Con. rollback (); // roll back
} Catch (SQLException e1 ){
E1.printStackTrace ();
}
} Finally {
If (ste! = Null ){
Try {
Ste. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
}
}
}
/**
* @ Author likehua
* @ Note: The receiving thread
**/
Private class SQLiteBatchHandler implements Runnable {
Private String batch;
Public SQLiteBatchHandler (String SQL ){
This. batch = SQL;
};
@ SuppressWarnings ("static-access ")
@ Override
Public 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 ")));
}
}