Android 資料庫 大量插入 事務開啟

請叫我碼農怪蜀黍發表於2017-05-12
Android資料庫

對比在Android中批量插入資料的3中方式對比（各插入1W條資料所花費的時間）：

1、 一個一個插入

 publicstaticboolean insert(SQLiteOpenHelper openHelper,  
            RemoteAppInfo appInfo) {  
        if (null == appInfo) {  
            returntrue;  
        }  
        SQLiteDatabase db = null;  
        try {  
            db = openHelper.getWritableDatabase();  
            ContentValues values = appInfo.getContentValues();  
            return -1 != db.insert(RemoteDBHelper.TABLE_APP_REMOTE, null,  
                    values);  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
            if (null != db) {  
                db.close();  
            }  
        }  
        returnfalse;  
    }  


    for (RemoteAppInfo remoteAppInfo : list) {  
          RemoteDBUtil.insert(helper, remoteAppInfo); 
        }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25

耗時：106524ms,也就是106s

2、 開啟事務批量插入，使用SqliteDateBase中的insert(String table, String nullColumnHack, ContentValues values)方法

publicstaticboolean insert(SQLiteOpenHelper openHelper,  
        List<RemoteAppInfo> list) {  
    boolean result = true;  
    if (null == list || list.size() <= 0) {  
        returntrue;  
    }  
    SQLiteDatabase db = null;  

    try {  
        db = openHelper.getWritableDatabase();  
        db.beginTransaction();  
        for (RemoteAppInfo remoteAppInfo : list) {  
            ContentValues values = remoteAppInfo.getContentValues();  
            if (db.insert(RemoteDBHelper.TABLE_APP_REMOTE, null, values) < 0) {  
                result = false;  
                break;  
            }  
        }  
        if (result) {  
            db.setTransactionSuccessful();  
        }  
    } catch (Exception e) {  
        e.printStackTrace();  
        returnfalse;  
    } finally {  
        try {  
            if (null != db) {  
                db.endTransaction();  
                db.close();  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  
    returntrue;  
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38

耗時：2968ms

3、 開啟事務批量插入，使用SQLiteStatement

  publicstaticboolean insertBySql(SQLiteOpenHelper openHelper,  
            List<RemoteAppInfo> list) {  
        if (null == openHelper || null == list || list.size() <= 0) {  
            returnfalse;  
        }  
        SQLiteDatabase db = null;  
        try {  
            db = openHelper.getWritableDatabase();  
            String sql = "insert into " + RemoteDBHelper.TABLE_APP_REMOTE + "("  
                    + RemoteDBHelper.COL_PKG_NAME + ","// 包名  
                    + RemoteDBHelper.COL_USER_ACCOUNT + ","// 賬號  
                    + RemoteDBHelper.COL_APP_SOURCE + ","// 來源  
                    + RemoteDBHelper.COL_SOURCE_UNIQUE + ","// PC mac 地址  
                    + RemoteDBHelper.COL_MOBILE_UNIQUE + ","// 手機唯一標識  
                    + RemoteDBHelper.COL_IMEI + ","// 手機IMEI  
                    + RemoteDBHelper.COL_INSTALL_STATUS + ","// 安裝狀態  
                    + RemoteDBHelper.COL_TRANSFER_RESULT + ","// 傳輸狀態  
                    + RemoteDBHelper.COL_REMOTE_RECORD_ID // 唯一標識  
                    + ") " + "values(?,?,?,?,?,?,?,?,?)";  
            SQLiteStatement stat = db.compileStatement(sql);  
            db.beginTransaction();  
            for (RemoteAppInfo remoteAppInfo : list) {  
                stat.bindString(1, remoteAppInfo.getPkgName());  
                stat.bindString(2, remoteAppInfo.getAccount());  
                stat.bindLong(3, remoteAppInfo.getFrom());  
                stat.bindString(4, remoteAppInfo.getFromDeviceMd5());  
                stat.bindString(5, remoteAppInfo.getMoblieMd5());  
                stat.bindString(6, remoteAppInfo.getImei());  
                stat.bindLong(7, remoteAppInfo.getInstallStatus());  
                stat.bindLong(8, remoteAppInfo.getTransferResult());  
                stat.bindString(9, remoteAppInfo.getRecordId());  
                long result = stat.executeInsert();  
                if (result < 0) {  
                    returnfalse;  
                }  
            }  
            db.setTransactionSuccessful();  
        } catch (Exception e) {  
            e.printStackTrace();  
            returnfalse;  
       } finally {  
            try {  
                if (null != db) {  
                    db.endTransaction();  
                    db.close();  
                }  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
        }  
        returntrue;  
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52

耗時：1365ms

 
 

相關文章