sqlite安插速度优化方案

先贴上SQLiteOpenHelper的代码，我们创建测试的表是user，它有四个字段id,name,age,height,remark，测试用的小米2，默认条数为100行。

//MySQliteOpenHelper作为一个访问SQLite的帮助类,提供两方面的功能//1.getReadableDatabase(),getWritableDatabase()可以获得SQLiteDatatbase对象,//对这个对象进行相关操作//2.提供了onCreate()和onUpgrade()两个回调函数，允许我们在创建和升级数据库时进行操作public class MySQLiteOpenHelper extends SQLiteOpenHelper {	public static final String DATABASE_NAME = "test.db";	public static final String TAG = "MySQLiteOpenHelper";	public MySQLiteOpenHelper(Context context) {		this(context, DATABASE_NAME, null, 1);	}	public MySQLiteOpenHelper(Context context, int version) {		this(context, DATABASE_NAME, null, version);	}	// 必须要有这一个构造方法	public MySQLiteOpenHelper(Context context, String name,			CursorFactory factory, int version) {		super(context, name, factory, version);		// TODO Auto-generated constructor stub	}	// 当数据库第一次创建的时候被调用,	// 当调用getReadableDatabase ()或getWritableDatabase 的时候	@Override	public void onCreate(SQLiteDatabase db) {		// TODO Auto-generated method stub		Log.d(TAG, "onCreate");		String sql = "create table user(id integer primary key autoincrement,"				+ "name varchar(20)," + "age integer," + "height long,"				+ "remark varchar(12))";		db.execSQL(sql);	}	public void close() {		SQLiteDatabase db = this.getWritableDatabase();		db.execSQL("drop table user");	}	@Override	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {		// TODO Auto-generated method stub		Log.d(TAG, "onUpgrade");	}}

1。使用ContentValues插入。完成时间:4805493666(纳秒)

	/**	 * ContentValues方式	 * 	 * @param sum	 * @return	 */	public long insert1(int sum) {		long before = System.nanoTime();		MySQLiteOpenHelper dbHelper = new MySQLiteOpenHelper(MainActivity.this);		// 得到数据库对象		SQLiteDatabase db = dbHelper.getWritableDatabase();		for (int i = 0; i < sum; i++) {			ContentValues cv = new ContentValues();			cv.put("name", "zhangsan");			cv.put("age", "23");			cv.put("height", 1.78);			cv.put("remark", "无");			db.insert("user", null, cv);		}		db.close();		long after = System.nanoTime();		return after - before;	}

public long insert2(int sum) {		long before = System.nanoTime();		MySQLiteOpenHelper dbHelper = new MySQLiteOpenHelper(MainActivity.this);		// 得到数据库对象		SQLiteDatabase db = dbHelper.getWritableDatabase();		for (int i = 0; i < sum; i++) {			String sql = "insert into user(name,age,height,remark) values('zhangsan',23,1.78,'无')";			db.execSQL(sql);		}		db.close();		long after = System.nanoTime();		return after - before;	}

public long insert3(int sum) {		long before = System.nanoTime();		MySQLiteOpenHelper dbHelper = new MySQLiteOpenHelper(MainActivity.this);		// 得到数据库对象		SQLiteDatabase db = dbHelper.getWritableDatabase();		String sql = "insert into user(name,age,height,remark) values(?,?,?,?)";		SQLiteStatement stmt = db.compileStatement(sql);		for (int i = 0; i < sum; i++) {			stmt.clearBindings();			stmt.bindString(1, "zhangsan");			stmt.bindLong(2, 23);			stmt.bindLong(3, 178);			stmt.bindString(4, "无");			stmt.execute();		}		db.close();		long after = System.nanoTime();		return after - before;	}

public long insert4(int sum) {		long before = System.nanoTime();		MySQLiteOpenHelper dbHelper = new MySQLiteOpenHelper(MainActivity.this);		// 得到数据库对象		SQLiteDatabase db = dbHelper.getWritableDatabase();		for (int i = 0; i < sum / 10; i++) {			String sql = "insert into user(name,age,height,remark) values('zhangsan',23,1.78,'无'),"					+ "('zhangsan',23,1.78,'无'),"					+ "('zhangsan',23,1.78,'无'),"					+ "('zhangsan',23,1.78,'无'),"					+ "('zhangsan',23,1.78,'无'),"					+ "('zhangsan',23,1.78,'无'),"					+ "('zhangsan',23,1.78,'无'),"					+ "('zhangsan',23,1.78,'无'),"					+ "('zhangsan',23,1.78,'无'),"					+ "('zhangsan',23,1.78,'无')";			db.execSQL(sql);		}		db.close();		long after = System.nanoTime();		return after - before;	}

5.使用事务处理插入方式。完成时间:229787881(纳秒)

public long insert5(int sum) {		long before = System.nanoTime();		MySQLiteOpenHelper dbHelper = new MySQLiteOpenHelper(MainActivity.this);		// 得到数据库对象		SQLiteDatabase db = dbHelper.getWritableDatabase();		db.beginTransaction();		for (int i = 0; i < sum; i++) {			String sql = "insert into user(name,age,height,remark) values('zhangsan',23,1.78,'无')";			db.execSQL(sql);		}		db.setTransactionSuccessful();		db.endTransaction();		db.close();		long after = System.nanoTime();		return after - before;	}