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

相关阅读:
区间DP中的环形DP
hdu 5251 包围点集最小矩形 ***
hdu 4858 水题
hdu 3530 单调队列 **
hdu 3338 最大流 ****
hdu 2732 最大流 **
hdu 5233 离散化 **
hdu 3555 数位dp *
zoj 3469 区间dp **
2015 安徽程序设计省赛总结

原文地址：https://www.cnblogs.com/wangfeng520/p/6844778.html

Android 数据库 大量插入 事务开启

Android 数据库大量插入事务开启