Deep into the problem of Sqlite multi threaded library
- 2020-04-01 01:59:22
- OfStack
Today, the manager gave me a SQL file of more than 30 M and asked me to test the problem of data positioning. By convention, I use navicat for sqlite to create a table and then import the SQL file. Then I went to do something else, and after about an hour or so, I thought the data was ready to be imported. I opened it and saw, sweat, dead there. I turned it off and imported it again, just like that. I also learned that the manager himself had guided, did not succeed. It seems that the tool import method is not working.
But is it that hard when you think about just a hundred thousand pieces of data, that's a hundred thousand insert SQL statements? So, I want to write a program to import it. There were a few hickeys, but I managed to get the data in.
The code of the program is as follows:
But is it that hard when you think about just a hundred thousand pieces of data, that's a hundred thousand insert SQL statements? So, I want to write a program to import it. There were a few hickeys, but I managed to get the data in.
The code of the program 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;
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.executeUpdate(ddl);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
return jCon;
}
//Create 500 threads
ExecutorService service=Executors.newFixedThreadPool(500);
//Read SQL file & NBSP; One thread batches every 500 insert statements & NBSP;
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++;
};
//Qualify & NBSP; Create a thread
if(tag!=0&&tag/500!=0){
service.execute(new SQLiteBatchHandler(batchSql));
batchSql="";//reset
tag=0;//reset
}
}
//Finally, execute the rest of the SQL
if(batchSql.length()>0){
System.out.println("finalSQL:"+batchSql);
Runnable r=new SQLiteBatchHandler(batchSql);
service.execute(r);
};
try {
//Turn off 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();
}
};
private static String[] splitSQL(String batchSQl){
if(batchSQl!=null){
return batchSQl.split(";");
};
return null;
}
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.executeBatch();<BR> ste.close();
con.commit();//Submit
}catch(Exception e){
e.printStackTrace();
System.out.println(" On failure :"+batch);
try {
con.rollback();//The rollback
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
if(ste!=null){
try {
ste.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
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);
};
}
}
public static void main(String[] args) throws FileNotFoundException, IOException{
BatchTool s=new BatchTool();
s.readBatchSQL(new FileInputStream(new File("c:\poi.sql")));
}
}