Android SQLite批量插入

import android.data .sqlite.SQLiteData ;
import android.data .sqlite.SQLiteOpenHelper;
import android.data .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;
        }
        SQLiteData  db = null;
        try {
            db = openHelper.getWritableData ();
            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;
        }
        SQLiteData  db = null;
        try {
            db = openHelper.getWritableData ();
            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;
        }
        SQLiteData  db = null;
        try {
            db = openHelper.getWritableData ();
            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;
        }
        SQLiteData  db = null;
        try {
            db = openHelper.getWritableData ();
            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;
        }
        SQLiteData  db = null;
        try {
            db = openHelper.getWritableData ();
            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;
    }
}