sqlite插入速度优化方案

概述先贴上SQLiteOpenHelper的代码，我们创建测试的表是user，它有四个字段id,name,age,height,remark，测试用的小米2，默认条数为100行。 //MySQliteOpenHelper作为一个访问SQLite的帮助类,提供两方面的功能//1.getReadableDatabase(),getWritableDatabase()可以获得SQLiteDatatbase

先贴上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,version);	}	// 必须要有这一个构造方法	public MysqLiteOpenHelper(Context context,String name,CursorFactory factory,int version) {		super(context,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",cv);		}		db.close();		long after = System.nanoTime();		return after - before;	}

2。使用基本slq语句插入。完成时间:3734808485(纳秒)

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,remark) values('zhangsan',23,1.78,'无')";			db.execsql(sql);		}		db.close();		long after = System.nanoTime();		return after - before;	}

3。使用sqlliteStatement插入。完成时间：4754616203(纳秒)

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,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;	}

4。使用一次插入多条的方式。完成时间：245414315(纳秒)

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,'无'),"					+ "('zhangsan','无')";			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,'无')";			db.execsql(sql);		}		db.setTransactionSuccessful();		db.endTransaction();		db.close();		long after = System.nanoTime();		return after - before;	}

总结

以上是内存溢出为你收集整理的sqlite插入速度优化方案全部内容，希望文章能够帮你解决sqlite插入速度优化方案所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。