Android数据库大批量数据插入优化

对比在android中批量插入数据的3中方式对比（各插入1W条数据所花费的时间）：

1、 一个一个插入

 1 public static boolean insert(SQLiteOpenHelper openHelper,
 2             RemoteAppInfo appInfo) {
 3         if (null == appInfo) {
 4             returntrue;
 5         }
 6         SQLiteDatabase db = null;
 7         try {
 8             db = openHelper.getWritableDatabase();
 9             ContentValues values = appInfo.getContentValues();
10             return -1 != db.insert(RemoteDBHelper.TABLE_APP_REMOTE, null,
11                     values);
12         } catch (Exception e) {
13             e.printStackTrace();
14         } finally {
15             if (null != db) {
16                 db.close();
17             }
18         }
19         returnfalse;
20     }
21
22
23     for (RemoteAppInfo remoteAppInfo : list) {
24           RemoteDBUtil.insert(helper, remoteAppInfo);
25         }

耗时：106524ms

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

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

耗时：2968ms

3、 开启事务批量插入，使用SQLiteStatement

 
 
 1 public static boolean insertBySql(SQLiteOpenHelper openHelper,
 2             List list) {
 3         if (null == openHelper || null == list || list.size() <= 0) {
 4             returnfalse;
 5         }
 6         SQLiteDatabase db = null;
 7         try {
 8             db = openHelper.getWritableDatabase();
 9             String sql = “insert into “ + RemoteDBHelper.TABLE_APP_REMOTE + “(“
10                     + RemoteDBHelper.COL_PKG_NAME + “,”// 包名  
11                     + RemoteDBHelper.COL_USER_ACCOUNT + “,”// 账号  
12                     + RemoteDBHelper.COL_APP_SOURCE + “,”// 来源  
13                     + RemoteDBHelper.COL_SOURCE_UNIQUE + “,”// PC mac 地址  
14                     + RemoteDBHelper.COL_MOBILE_UNIQUE + “,”// 手机唯一标识  
15                     + RemoteDBHelper.COL_IMEI + “,”// 手机IMEI  
16                     + RemoteDBHelper.COL_INSTALL_STATUS + “,”// 安装状态  
17                     + RemoteDBHelper.COL_TRANSFER_RESULT + “,”// 传输状态  
18                     + RemoteDBHelper.COL_REMOTE_RECORD_ID // 唯一标识  
19                     + “) “ + “values(?,?,?,?,?,?,?,?,?)”;
20             SQLiteStatement stat = db.compileStatement(sql);
21             db.beginTransaction();
22             for (RemoteAppInfo remoteAppInfo : list) {
23                 stat.bindString(1, remoteAppInfo.getPkgName());
24                 stat.bindString(2, remoteAppInfo.getAccount());
25                 stat.bindLong(3, remoteAppInfo.getFrom());
26                 stat.bindString(4, remoteAppInfo.getFromDeviceMd5());
27                 stat.bindString(5, remoteAppInfo.getMoblieMd5());
28                 stat.bindString(6, remoteAppInfo.getImei());
29                 stat.bindLong(7, remoteAppInfo.getInstallStatus());
30                 stat.bindLong(8, remoteAppInfo.getTransferResult());
31                 stat.bindString(9, remoteAppInfo.getRecordId());
32                 long result = stat.executeInsert();
33                 if (result < 0) {
34                     returnfalse;
35                 }
36             }
37             db.setTransactionSuccessful();
38         } catch (Exception e) {
39             e.printStackTrace();
40             returnfalse;
41        } finally {
42             try {
43                 if (null != db) {
44                     db.endTransaction();
45                     db.close();
46                 }
47             } catch (Exception e) {
48                 e.printStackTrace();
49             }
50         }
51         returntrue;
52     }
耗时：1365ms