先贴上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;
- }
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,age,height,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,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;
- }
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,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;
- }