需求是从一个mysql数据库导出数据再导入到sqlite中,考虑从mysql查询,只能逐行插入sqlite效率会很低,所以把mysql中的数据导出到sql文件中,删除表结构语句,保留insert语句,在sqlite中先建好跟该表一样的结构,不要主键不要索引,否则影响插入速度;
然后上代码;
import java.io.File;
import java.io.RandomAccessFile;
import java.nio.ByteBuffer;
import java.nio.channels.FileChannel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BatchIntoSqlite {
private static String Drivde="org.sqlite.JDBC";
public static void main(String[] args) {
try {
Class.forName(Drivde);// 加载驱动,连接sqlite的jdbc
Connection connection=DriverManager.getConnection("jdbc:sqlite:db/voter.db");//连接数据库
connection.setAutoCommit(false);
Statement statement=connection.createStatement();
//加载sql文件
File file = new File("D:/java/WrokspaceOxygen/voter.sql");
FileChannel fileChannel = new RandomAccessFile(file,"r").getChannel();
ByteBuffer byteBuffer = ByteBuffer.allocate(10);
//使用temp字节数组用于存储不完整的行的内容
byte[] temp = new byte[0];
String sq="";
int n=0;
while(fileChannel.read(byteBuffer) != -1) {
byte[] bs = new byte[byteBuffer.position()];
byteBuffer.flip();
byteBuffer.get(bs);
byteBuffer.clear();
int startNum=0;
//判断是否出现了换行符,注意这要区分LF-\n,CR-\r,CRLF-\r\n,这里判断\n
boolean isNewLine = false;
for(int i=0;i < bs.length;i++) {
if(bs[i] == 10) {
isNewLine = true;
startNum = i;
}
}
if(isNewLine) {
//如果出现了换行符,将temp中的内容与换行符之前的内容拼接
byte[] toTemp = new byte[temp.length+startNum];
System.arraycopy(temp,0,toTemp,0,temp.length);
System.arraycopy(bs,0,toTemp,temp.length,startNum);
sq=new String(toTemp).trim();
n++;
statement.addBatch(sq);
//10w条记录提交一次
if(n%100000==0) {
connection.commit();
System.out.println("execute batch,round:"+n/100000);
}
System.out.println("counter:"+n);
//将换行符之后的内容(去除换行符)存到temp中
temp = new byte[bs.length-startNum-1];
System.arraycopy(bs,startNum+1,temp,0,bs.length-startNum-1);
} else {
//如果没出现换行符,则将内容保存到temp中
byte[] toTemp = new byte[temp.length + bs.length];
System.arraycopy(temp, 0, toTemp, 0, temp.length);
System.arraycopy(bs, 0, toTemp, temp.length, bs.length);
temp = toTemp;
}
}
statement.executeBatch();//必须在最后执行一次,前边的逻辑最后的数据
connection.commit();
connection.close();//关闭数据库连接
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}