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

耗时：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; }

耗时：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; }

耗时：1365ms

，上天完全是为了坚强你的意志，才在道路上设下重重的障碍。