android sqlite数据库使用

xiaoxiao2021-03-25 112

我这里只是贴代码，不做详细讲解，demo展示

1、DBHelper类，WeiXinDBManager

package com.yueniu.addcustomers.data.local; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.util.Log; import com.yueniu.addcustomers.bean.MessageInfo; import com.yueniu.addcustomers.bean.RcontactInfo; import com.yueniu.addcustomers.bean.SubmitSourceInfo; import com.yueniu.addcustomers.util.LogUtil; import com.yueniu.addcustomers.util.WriteLogToSD; import java.util.ArrayList; import java.util.List; import java.util.concurrent.atomic.AtomicInteger; /** * Date: 2015/1/6. */ public class WeiXinDBManager { private static final String TAG = "WeiXinDBManager"; private AtomicInteger mOpenCounter = new AtomicInteger(); private static WeiXinDBManager instance; private static WeixinDBHelper weixinDBHelper; private SQLiteDatabase mDatabase; private final String limit = "50"; // 列定义 private final String[] MESSAGE_COLUMNS = new String[]{"chat_type", "content", "chat_from", "chat_to", "datetime", "initwechatfriend_number", "imgpath", "issend","wechat_number", "msgSvrId","talkerId"}; private final String[] RCONTACT = new String[]{"buddyname", "buddyphotourl", "imgid", "wxname","province","city", "pyInitial","sex","pyQuanPin","iskeepUser","lastchattime", "wechatfriend_number","initwechatfriend_number","show_head"}; private final String[] ERRORSUBMITSOURCE = new String[]{"logid", "source"}; private static synchronized void initializeInstance(WeixinDBHelper helper) { if (instance == null) { instance = new WeiXinDBManager(); weixinDBHelper = helper; } } public static synchronized WeiXinDBManager getInstance(WeixinDBHelper helper) { if (instance == null) { initializeInstance(helper); } return instance; } private synchronized SQLiteDatabase getWritableDatabase() { if (mOpenCounter.incrementAndGet() == 1) { mDatabase = weixinDBHelper.getWritableDatabase(); } return mDatabase; } private synchronized SQLiteDatabase getReadableDatabase() { if (mOpenCounter.incrementAndGet() == 1) { mDatabase = weixinDBHelper.getReadableDatabase(); } return mDatabase; } private synchronized void closeDatabase() { if (mOpenCounter.decrementAndGet() == 0) { mDatabase.close(); } } // 判断聊天记录里面是否存在信息 public boolean isDataExistMessage() { int count = 0; Cursor cursor = null; try { mDatabase = getReadableDatabase(); cursor = mDatabase.query(WeixinDBHelper.TABLE_NAME_MESSAGE, new String[]{"COUNT('X')"}, null, null, null, null, null); if (cursor.moveToFirst()) { count = cursor.getInt(0); } if (count > 0) return true; } catch (Exception e) { LogUtil.d(TAG, "isDataExistMessage open db error"); WriteLogToSD.SaveToSDcard(TAG, "isDataExistMessage失败："+e.toString()); } finally { if (cursor != null) { cursor.close(); } if (mDatabase != null) { closeDatabase(); } } return false; } /** * 插入数据库聊天记录 * * @param messageInfos */ public void insertMessage(List<MessageInfo> messageInfos) { if (messageInfos == null) { return; } try { mDatabase = getWritableDatabase(); mDatabase.beginTransaction(); int count = messageInfos.size(); for (int i = 0; i < count; i++) { MessageInfo messageInfo = messageInfos.get(i); ContentValues contentValues = new ContentValues(); contentValues.put("chat_type", messageInfo.getChat_type()); contentValues.put("content", messageInfo.getContent()); contentValues.put("chat_from", messageInfo.getChat_from()); contentValues.put("chat_to", messageInfo.getChat_to()); contentValues.put("datetime", messageInfo.getDatetime()); contentValues.put("initwechatfriend_number", messageInfo.getInitwechatfriend_number()); contentValues.put("imgpath", messageInfo.getImgpath()); contentValues.put("issend", messageInfo.getIssend()); contentValues.put("wechat_number",messageInfo.getWechat_number()); contentValues.put("msgSvrId",messageInfo.getMsgSvrId()); contentValues.put("talkerId",messageInfo.getTalkerId()); mDatabase.insertOrThrow(WeixinDBHelper.TABLE_NAME_MESSAGE, null, contentValues); } mDatabase.setTransactionSuccessful(); Log.e(TAG, "insertMessage:" + messageInfos.size()); } catch (Exception e) { Log.e(TAG, "", e); WriteLogToSD.SaveToSDcard(TAG, "insertMessage失败:"+e.toString()); } finally { if (mDatabase != null) { mDatabase.endTransaction(); closeDatabase(); } } } /** * 删除已上传的聊天记录 * * @param messageInfos * @return */ public boolean deleteMessage(List<MessageInfo> messageInfos) { if (messageInfos==null) return true; try { mDatabase = getWritableDatabase(); mDatabase.beginTransaction(); int count = messageInfos.size(); for (int i = 0; i < count; i++) { MessageInfo messageInfo = messageInfos.get(i); mDatabase.delete(WeixinDBHelper.TABLE_NAME_MESSAGE, "datetime = ? and content= ?", new String[]{messageInfo.getDatetime(),messageInfo.getContent()}); } mDatabase.setTransactionSuccessful(); Log.e(TAG, "deleteMessage:" + messageInfos.size()); return true; } catch (Exception e) { Log.e(TAG, "", e); WriteLogToSD.SaveToSDcard(TAG, "deleteMessage失败:"+e.toString()); } finally { if (mDatabase != null) { mDatabase.endTransaction(); closeDatabase(); } } return false; } /** * 查询聊天记录 * * @return */ public List<MessageInfo> selectMessage(String wechat_number) { Cursor cursor = null; try { mDatabase = getReadableDatabase(); cursor = mDatabase.query(WeixinDBHelper.TABLE_NAME_MESSAGE, MESSAGE_COLUMNS, "wechat_number=? limit ?", new String[]{wechat_number,limit}, null, null, null); if (cursor.getCount() > 0) { List<MessageInfo> messageInfos = new ArrayList<MessageInfo>(cursor.getCount()); while (cursor.moveToNext()) { MessageInfo messageInfo = parseMessageInfo(cursor); messageInfos.add(messageInfo); } Log.e(TAG, "select:" + messageInfos.size()); return messageInfos; } } catch (Exception e) { Log.e(TAG, "open fail", e); WriteLogToSD.SaveToSDcard(TAG, "selectMessage失败:"+e.toString()); } finally { if (cursor != null) { cursor.close(); } if (mDatabase != null) { closeDatabase(); } } return null; } /** * 将查找到的数据转换成MessageInfo类 */ private MessageInfo parseMessageInfo(Cursor cursor) { MessageInfo messageInfo = new MessageInfo(); messageInfo.setChat_type(cursor.getString(cursor.getColumnIndex("chat_type"))); messageInfo.setContent(cursor.getString(cursor.getColumnIndex("content"))); messageInfo.setChat_from(cursor.getString(cursor.getColumnIndex("chat_from"))); messageInfo.setChat_to(cursor.getString(cursor.getColumnIndex("chat_to"))); messageInfo.setDatetime(cursor.getString(cursor.getColumnIndex("datetime"))); messageInfo.setInitwechatfriend_number(cursor.getString(cursor.getColumnIndex("initwechatfriend_number"))); messageInfo.setImgpath(cursor.getString(cursor.getColumnIndex("imgpath"))); messageInfo.setIssend(cursor.getString(cursor.getColumnIndex("issend"))); messageInfo.setWechat_number(cursor.getString(cursor.getColumnIndex("wechat_number"))); messageInfo.setMsgSvrId(cursor.getString(cursor.getColumnIndex("msgSvrId"))); messageInfo.setTalkerId(cursor.getString(cursor.getColumnIndex("talkerId"))); return messageInfo; } // 判断聊天联系人里面是否存在信息 public boolean isDataExistContact() { int count = 0; Cursor cursor = null; try { mDatabase = getReadableDatabase(); cursor = mDatabase.query(WeixinDBHelper.TABLE_NAME_Contact, new String[]{"COUNT('X')"}, null, null, null, null, null); if (cursor.moveToFirst()) { count = cursor.getInt(0); } if (count > 0) return true; } catch (Exception e) { LogUtil.d(TAG, "isDataExistContact open db error"); WriteLogToSD.SaveToSDcard(TAG, "isDataExistContact失败:"+e.toString()); } finally { if (cursor != null) { cursor.close(); } if (mDatabase != null) { closeDatabase(); } } return false; } /** * 插入数据库聊天记录 * * @param rcontactInfos */ public void insertRcontactInfo(List<RcontactInfo> rcontactInfos) { if (rcontactInfos == null) { return; } try { mDatabase = getWritableDatabase(); mDatabase.beginTransaction(); int count = rcontactInfos.size(); for (int i = 0; i < count; i++) { RcontactInfo rcontactInfo = rcontactInfos.get(i); ContentValues contentValues = new ContentValues(); contentValues.put("buddyname", rcontactInfo.getBuddyname()); contentValues.put("buddyphotourl", rcontactInfo.getBuddyphotourl()); contentValues.put("imgid", rcontactInfo.getImgid()); contentValues.put("wxname", rcontactInfo.getWxname()); contentValues.put("province", rcontactInfo.getProvince()); contentValues.put("city", rcontactInfo.getCity()); contentValues.put("pyInitial", rcontactInfo.getPyInitial()); contentValues.put("sex", rcontactInfo.getSex()); contentValues.put("pyQuanPin", rcontactInfo.getPyQuanPin()); contentValues.put("iskeepUser", rcontactInfo.getIskeepUser()); contentValues.put("lastchattime", rcontactInfo.getLastchattime()); contentValues.put("wechatfriend_number", rcontactInfo.getWechatfriend_number()); contentValues.put("initwechatfriend_number", rcontactInfo.getInitwechatfriend_number()); contentValues.put("show_head", rcontactInfo.getShow_head()); mDatabase.insertOrThrow(WeixinDBHelper.TABLE_NAME_Contact, null, contentValues); } mDatabase.setTransactionSuccessful(); Log.e(TAG, "insertuserInfo:" + rcontactInfos.size()); } catch (Exception e) { Log.e(TAG, "", e); WriteLogToSD.SaveToSDcard(TAG, "insertRcontactInfo失败:"+e.toString()); } finally { if (mDatabase != null) { mDatabase.endTransaction(); closeDatabase(); } } } /** * 删除已上传的联系人 * * @param rcontactInfos * @return */ public boolean deleteRcontactInfos(List<RcontactInfo> rcontactInfos) { try { mDatabase = getWritableDatabase(); mDatabase.beginTransaction(); mDatabase.delete(WeixinDBHelper.TABLE_NAME_Contact, "", new String[]{}); mDatabase.setTransactionSuccessful(); Log.e(TAG, "deleteuserInfo:" + rcontactInfos.size()); return true; } catch (Exception e) { Log.e(TAG, "", e); WriteLogToSD.SaveToSDcard(TAG, "deleteRcontactInfos失败:"+e.toString()); } finally { if (mDatabase != null) { mDatabase.endTransaction(); closeDatabase(); } } return false; } /** * 查询聊天记录 * * @return */ public List<RcontactInfo> selectRcontactInfo() { Cursor cursor = null; try { mDatabase = getReadableDatabase(); cursor = mDatabase.query(WeixinDBHelper.TABLE_NAME_Contact, RCONTACT, "1==1 limit ?", new String[]{limit}, null, null, null); if (cursor.getCount() > 0) { List<RcontactInfo> userInfos = new ArrayList<RcontactInfo>(cursor.getCount()); while (cursor.moveToNext()) { RcontactInfo userInfo = parseRcontactInfo(cursor); userInfos.add(userInfo); } Log.e(TAG, "selectuserInfos:" + userInfos.size()); return userInfos; } } catch (Exception e) { Log.e(TAG, "open fail", e); WriteLogToSD.SaveToSDcard(TAG, "selectRcontactInfo失败:"+e.toString()); } finally { if (cursor != null) { cursor.close(); } if (mDatabase != null) { closeDatabase(); } } return null; } /** * 将查找到的数据转换成RcontactInfo类 */ private RcontactInfo parseRcontactInfo(Cursor cursor) { RcontactInfo userInfo = new RcontactInfo(); userInfo.setBuddyname(cursor.getString(cursor.getColumnIndex("buddyname"))); userInfo.setBuddyphotourl(cursor.getString(cursor.getColumnIndex("buddyphotourl"))); userInfo.setImgid(cursor.getString(cursor.getColumnIndex("imgid"))); userInfo.setWxname(cursor.getString(cursor.getColumnIndex("wxname"))); userInfo.setProvince(cursor.getString(cursor.getColumnIndex("province"))); userInfo.setCity(cursor.getString(cursor.getColumnIndex("city"))); userInfo.setPyInitial(cursor.getString(cursor.getColumnIndex("pyInitial"))); userInfo.setSex(cursor.getString(cursor.getColumnIndex("sex"))); userInfo.setPyQuanPin(cursor.getString(cursor.getColumnIndex("pyQuanPin"))); userInfo.setIskeepUser(cursor.getString(cursor.getColumnIndex("iskeepUser"))); userInfo.setLastchattime(cursor.getString(cursor.getColumnIndex("lastchattime"))); userInfo.setWechatfriend_number(cursor.getString(cursor.getColumnIndex("wechatfriend_number"))); userInfo.setInitwechatfriend_number(cursor.getString(cursor.getColumnIndex("initwechatfriend_number"))); userInfo.setShow_head(cursor.getString(cursor.getColumnIndex("show_head"))); return userInfo; } /** * 插入一条上传错误信息 * @param sourceInfo */ public void insertErrorSubmitSource(SubmitSourceInfo sourceInfo){ if (sourceInfo == null) { return; } try { mDatabase = getWritableDatabase(); mDatabase.beginTransaction(); ContentValues contentValues = new ContentValues(); contentValues.put("logid", sourceInfo.getLogid()); contentValues.put("source", sourceInfo.getSource()); mDatabase.insertOrThrow(WeixinDBHelper.TABLE_NAME_ErrorSubmitSource, null, contentValues); mDatabase.setTransactionSuccessful(); } catch (Exception e) { Log.e(TAG, "", e); WriteLogToSD.SaveToSDcard(TAG, "insertErrorSubmitSource失败:"+e.toString()); } finally { if (mDatabase != null) { mDatabase.endTransaction(); closeDatabase(); } } } /** * 查询错误上传的信息 */ public SubmitSourceInfo selectErrorSubmitSource(){ Cursor cursor = null; try { mDatabase = getReadableDatabase(); cursor = mDatabase.query(WeixinDBHelper.TABLE_NAME_ErrorSubmitSource, ERRORSUBMITSOURCE, "1==1 limit ?", new String[]{"1"}, null, null, null); if (cursor.getCount() > 0) { SubmitSourceInfo sourceInfo = new SubmitSourceInfo(); while (cursor.moveToNext()) { sourceInfo.setLogid(cursor.getString(cursor.getColumnIndex("logid"))); sourceInfo.setSource(cursor.getString(cursor.getColumnIndex("source"))); } return sourceInfo; } } catch (Exception e) { Log.e(TAG, "open fail", e); WriteLogToSD.SaveToSDcard(TAG, "selectErrorSubmitSource失败:"+e.toString()); } finally { if (cursor != null) { cursor.close(); } if (mDatabase != null) { closeDatabase(); } } return null; } /** * 删除上传成功的数据 * @param sourceInfo * @return */ public boolean deleteErrorSubmitSource(SubmitSourceInfo sourceInfo){ if (sourceInfo==null) return true; try { mDatabase = getWritableDatabase(); mDatabase.beginTransaction(); mDatabase.delete(WeixinDBHelper.TABLE_NAME_ErrorSubmitSource, "logid = ?", new String[]{sourceInfo.getLogid()}); mDatabase.setTransactionSuccessful(); return true; } catch (Exception e) { Log.e(TAG, "", e); WriteLogToSD.SaveToSDcard(TAG, "deleteErrorSubmitSource失败:"+e.toString()); } finally { if (mDatabase != null) { mDatabase.endTransaction(); closeDatabase(); } } return false; } } 2、数据库操作类 WeiXinDBManager

final SubmitSourceInfo sourceInfo = WeiXinDBManager.getInstance(new WeixinDBHelper(context)).selectErrorSubmitSource(); 4、使用 sqlcipher DBHelper package com.yueniu.addcustomers.data.local; import android.content.Context; import com.yueniu.addcustomers.util.LogUtil; import com.yueniu.addcustomers.util.UinUtils; import com.yueniu.addcustomers.util.WriteLogToSD; import com.yueniu.addcustomers.util.XmlSaveUtil; import net.sqlcipher.database.SQLiteDatabase; import net.sqlcipher.database.SQLiteDatabaseHook; import java.io.File; /** * Created by maoshenbo on 2016/11/24 9:23. */ public class MMDBHelper { private static String TAG = "MMDBHelper"; private static SQLiteDatabase db; private static MMDBHelper instance = null; private static SQLiteDatabaseHook hook = new SQLiteDatabaseHook(){ @Override public void preKey(SQLiteDatabase sqLiteDatabase) { } public void postKey(SQLiteDatabase database){ database.rawExecSQL("PRAGMA cipher_migrate;"); //最关键的一句！！！ database.rawExecSQL("PRAGMA cipher_use_hmac = OFF;"); LogUtil.i(TAG, "----------调用了--------------"); WriteLogToSD.SaveToSDcard(TAG, "hook调用了"); } }; public static MMDBHelper getInstance(){ if(instance == null){ instance = new MMDBHelper(); } return instance; } public SQLiteDatabase getReadableDatabase(Context context, File messageFile, File configFile,File compatibleFile) { try{ String secret = UinUtils.getDBKey(context, configFile,compatibleFile); WriteLogToSD.SaveToSDcard(TAG, "数据库密码获取："+secret); LogUtil.i(TAG, "secret="+secret); XmlSaveUtil.GetInstance().printSecretXml(secret, configFile.getAbsolutePath()); db = SQLiteDatabase.openDatabase(messageFile.getPath(), secret, null, 0, hook); WriteLogToSD.SaveToSDcard(TAG, "数据库打开了"); return db; } catch (Exception e){ e.printStackTrace(); WriteLogToSD.SaveToSDcard(TAG, "数据库打开失败"); } return null; } }

转载请注明原文地址: https://ju.6miu.com/read-18443.html

技术

最新回复(0)