Android SQLite批量插入

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

import java.util.List;
import java.util.Set;

/**
 * Created by wangqixu on 2017/5/26.
 */

public class SQLiteTools {
    public static String TAG = "SQLiteTools";

    /**
     * 第二种方式批量插入(插入1W条数据耗时：1365ms)
     * <p>
     * mysql 删除某行并删除与之关联的行 SQL语句
     * https://bbs.csdn.net/topics/392217805?list=lz
     * delete from 表 where id in
     * (select id from
     * (
     * select * from 表 where parentid in (select ownid from 表 where 组织名称 = 3)
     * union
     * select * from 表 where 组织名称 = 3
     * ) t)
     *
     * @param openHelper
     * @param list
     * @return
     */
    public static boolean insertAllRosterEntryToDb(String account, SQLiteOpenHelper openHelper,
                                                   List<RosterInfo> list) {
        //LogUtils.i(TAG, "28 list.size()="+list.size());
        //LogUtils.i(TAG, "28 openHelper="+openHelper);
        if (null == openHelper || null == list || list.size() <= 0) {
            return true;
        }
        SQLiteDatabase db = null;
        try {
            db = openHelper.getWritableDatabase();
            String sql = "insert into " + RosterProvider.TABLE_ROSTER + "("
                    + RosterProvider.RosterConstants.ACCOUNT + ","// 当前账户
                    + RosterProvider.RosterConstants.OTHER_JID + ","// 好友jid
                    + RosterProvider.RosterConstants.ALIAS + ","// 好友备注
                    + RosterProvider.RosterConstants.AVATAR_URL + ","// 好友备注
                    + RosterProvider.RosterConstants.STATUS_MODE + ","// 好友状态
                    + RosterProvider.RosterConstants.STATUS_MESSAGE + ","// 自己的心情
                    + RosterProvider.RosterConstants.GROUP// 好友所在组
                    + ") " + "values(?,?,?,?,?,?,?)";
            //LogUtils.i(TAG, "44 +++++++");
            SQLiteStatement stat = db.compileStatement(sql);
            db.beginTransaction();
            //LogUtils.i(TAG, "47 +++++++");
            for (RosterInfo rosterInfo : list) {
                //LogUtils.i(TAG, "45 rosterInfo="+rosterInfo);
                stat.bindString(1, account);
                stat.bindString(2, rosterInfo.getOtherJid());
                stat.bindString(3, rosterInfo.alias);
                stat.bindString(4, rosterInfo.avatarUrl);
                stat.bindLong(5, rosterInfo.statusMode);
                stat.bindString(6, rosterInfo.group);
                long result = stat.executeInsert();
                if (result < 0) {
                    return false;
                }
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
            //LogUtils.i(TAG, "64 e="+e);
            return false;
        } finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            } catch (Exception e) {
                //LogUtils.i(TAG, "73 e="+e);
                e.printStackTrace();
            }
        }
        return true;
    }

    public static boolean deleteAllRosterEntryFromDb(String account, SQLiteOpenHelper openHelper) {
        //LogUtils.i(TAG, "82 openHelper="+openHelper);
        if (null == openHelper) {
            return false;
        }
        SQLiteDatabase db = null;
        try {
            db = openHelper.getWritableDatabase();
            String sql = "delete from " + RosterProvider.TABLE_ROSTER
                    + " where " + RosterProvider.RosterConstants.ACCOUNT
                    + " =  " + account;// 当前账户

            /*
            * String sql = "delete from " + RosterProvider.TABLE_ROSTER + " where id IN( select "+ RosterProvider.RosterConstants.OTHER_JID
                    +" from "+RosterProvider.TABLE_ROSTER +" where "
                    + RosterProvider.RosterConstants.ACCOUNT + " = ?"// 当前账户

                    + ") " + "values(?,?)";
            * */
            //LogUtils.i(TAG, "93 +++++++");
            SQLiteStatement stat = db.compileStatement(sql);
            db.beginTransaction();
            //stat.bindString(1, account);
            //LogUtils.i(TAG, "96 +++++++");
            long result = stat.executeUpdateDelete();
            if (result < 0) {
                return false;
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
            //LogUtils.i(TAG, "109 e="+e);
            return false;
        } finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            } catch (Exception e) {
                //LogUtils.i(TAG, "119 e="+e);
                e.printStackTrace();
            }
        }
        return true;
    }

    public static boolean deleteRosterEntryFromDb(String account, SQLiteOpenHelper openHelper,
                                                  List<String> list) {
        //LogUtils.i(TAG, "128 list.size()="+list.size());
        //LogUtils.i(TAG, "128 openHelper="+openHelper);
        if (null == openHelper || null == list || list.size() <= 0) {
            return true;
        }
        SQLiteDatabase db = null;
        try {
            db = openHelper.getWritableDatabase();
            String sql = "delete from " + RosterProvider.TABLE_ROSTER + " where " + RosterProvider.RosterConstants.ACCOUNT
                    + " = ? AND " + RosterProvider.RosterConstants.OTHER_JID + " = ? ";// 当前账户

            /*
            * String sql = "delete from " + RosterProvider.TABLE_ROSTER + " where id IN( select "+ RosterProvider.RosterConstants.OTHER_JID
                    +" from "+RosterProvider.TABLE_ROSTER +" where "
                    + RosterProvider.RosterConstants.ACCOUNT + " = ?"// 当前账户

                    + ") " + "values(?,?)";
            * */
            //LogUtils.i(TAG, "146 +++++++");
            SQLiteStatement stat = db.compileStatement(sql);
            db.beginTransaction();
            //LogUtils.i(TAG, "149 +++++++");
            for (String rosterInfo : list) {
                //LogUtils.i(TAG, "151 rosterInfo="+rosterInfo);
                stat.bindString(1, account);
                stat.bindString(2, rosterInfo);
                long result = stat.executeUpdateDelete();
                if (result < 0) {
                    return false;
                }
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
            //LogUtils.i(TAG, "162 e="+e);
            return false;
        } finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            } catch (Exception e) {
                //LogUtils.i(TAG, "171 e="+e);
                e.printStackTrace();
            }
        }
        return true;
    }

    public static boolean deleteAllChatFromDb(String account, SQLiteOpenHelper openHelper) {
        //LogUtils.i(TAG, "179 openHelper="+openHelper);
        if (null == openHelper) {
            return false;
        }
        SQLiteDatabase db = null;
        try {
            db = openHelper.getWritableDatabase();
            String sql = "delete from "
                    + ChatProvider.TABLE_CHATS + " where " + ChatProvider.ChatConstants.ACCOUNT + " =  " + account
                    + " and " + ChatProvider.ChatConstants.CHAT_TYPE + " = " + ChatProvider.ChatConstants.CHAT;// 当前账户
            //+" WHERE " + ChatProvider.ChatConstants.ACCOUNT  + " not in ( select * from (select "+ ChatProvider.ChatConstants.ACCOUNT  +" from "+ ChatProvider.TABLE_CHATS +" t_chats WHERE t_chats.account > 4)u) "
            //+ " and NOT (" + ChatProvider.ChatConstants.OTHER_JID + " like " + XmppConfig.SystemAccount +"% )";
            /*
            * String sql = "delete from " + RosterProvider.TABLE_ROSTER + " where id IN( select "+ RosterProvider.RosterConstants.OTHER_JID
                    +" from "+RosterProvider.TABLE_ROSTER +" where "
                    + RosterProvider.RosterConstants.ACCOUNT + " = ?"// 当前账户

                    + ") " + "values(?,?)";
            * */
            //LogUtils.i(TAG, "196 +++++++");
            SQLiteStatement stat = db.compileStatement(sql);
            db.beginTransaction();
            //LogUtils.i(TAG, "199 +++++++");
            //stat.bindString(1, account);
            long result = stat.executeUpdateDelete();
            if (result < 0) {
                return false;
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
            //LogUtils.i(TAG, "207 e="+e);
            return false;
        } finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            } catch (Exception e) {
                //LogUtils.i(TAG, "216 e="+e);
                e.printStackTrace();
            }
        }
        return true;
    }

    public static boolean deleteChatFromDb(String account, SQLiteOpenHelper openHelper,
                                           Set<String> deleteChatSet) {
        //LogUtils.i(TAG, "225 list.size()="+list.size());
        //LogUtils.i(TAG, "226 openHelper="+openHelper);
        if (null == openHelper || null == deleteChatSet || deleteChatSet.size() <= 0) {
            return true;
        }
        SQLiteDatabase db = null;
        try {
            db = openHelper.getWritableDatabase();
            String sql = "delete from " + ChatProvider.TABLE_CHATS + " where " + ChatProvider.ChatConstants.ACCOUNT
                    + " = ? AND " + ChatProvider.ChatConstants.OTHER_JID + " = ? ";// 当前账户

            /*
            * String sql = "delete from " + RosterProvider.TABLE_ROSTER + " where id IN( select "+ RosterProvider.RosterConstants.OTHER_JID
                    +" from "+RosterProvider.TABLE_ROSTER +" where "
                    + RosterProvider.RosterConstants.ACCOUNT + " = ?"// 当前账户

                    + ") " + "values(?,?)";
            * */
            //LogUtils.i(TAG, "243 +++++++");
            SQLiteStatement stat = db.compileStatement(sql);
            db.beginTransaction();
            //LogUtils.i(TAG, "246 +++++++");
            for (String chatInfo : deleteChatSet) {
                //LogUtils.i(TAG, "248 rosterInfo="+chatInfo);
                stat.bindString(1, account);
                stat.bindString(2, chatInfo);
                long result = stat.executeUpdateDelete();
                if (result < 0) {
                    return false;
                }
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
            //LogUtils.i(TAG, "259 e="+e);
            return false;
        } finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            } catch (Exception e) {
                //LogUtils.i(TAG, "268 e="+e);
                e.printStackTrace();
            }
        }
        return true;
    }
}