【SQLite】常用操作SQL及压缩SQLite的实例代码

来源：互联网发布：织梦html5响应式模板编辑：程序博客网时间：2024/06/06 14:04

1、创建ID自增的Data表

/** * 创建发送失败的信息的Data表的SQL */public static final String createDataTable = "CREATE TABLE IF NOT EXISTS "+ DataModel.TABLE_DATA_NAME + "(" + DataModel.DATA_MODEL_ID + " integer PRIMARY KEY AUTOINCREMENT,"+ DataModel.DATA_MODEL_MAC + " varchar(30),"+ DataModel.DATA_MODEL_TYPE + " varchar(20),"+ DataModel.DATA_MODEL_RRCODE + " integer(10),"+ DataModel.DATA_MODEL_SENDCOUNT + " integer(5),"+ DataModel.DATA_MODEL_CREATETIME + " varchar(20),"+ DataModel.DATA_MODEL_SENDLASTTIME + " varchar(20),"+ DataModel.DATA_MODEL_CONTENT + " varchar(800)"+")";

2、查询

/** * 将ID按升序排序，并取出前count条数据 * @param count * @return */public List<DataModel> queryDataModelListByCount(int count){String sql = "select * from " + DataModel.TABLE_DATA_NAME +" order by " + DataModel.DATA_MODEL_ID + " asc" + " limit 0," + count;List<DataModel> dmList = queryDataModelList(sql, null);return dmList;}/** * 查询指定条件的Data表记录 * @param sql * @param params * @return */private List<DataModel> queryDataModelList(String sql, String[] params){List<DataModel> dmList=new ArrayList<DataModel>();try{Cursor cs=db.rawQuery(sql, params);if(cs!=null && cs.getCount()>0){if(GamConstants.DebugMode){Log.d(TAG, "queryMobileModelList count="+cs.getCount());}int i=0;for( cs.moveToFirst(); i<cs.getCount(); cs.moveToNext(), ++i){DataModel dm=new DataModel();dm.setId(cs.getInt(cs.getColumnIndex(DataModel.DATA_MODEL_ID)));dm.setMac(cs.getString(cs.getColumnIndex(DataModel.DATA_MODEL_MAC)));dm.setType(cs.getString(cs.getColumnIndex(DataModel.DATA_MODEL_TYPE)));dm.setReasonResultCode(cs.getInt(cs.getColumnIndex(DataModel.DATA_MODEL_RRCODE)));dm.setSendCount(cs.getInt(cs.getColumnIndex(DataModel.DATA_MODEL_SENDCOUNT)));dm.setCreateTime(cs.getString(cs.getColumnIndex(DataModel.DATA_MODEL_CREATETIME)));dm.setSendLastTime(cs.getString(cs.getColumnIndex(DataModel.DATA_MODEL_SENDLASTTIME)));dm.setContent(cs.getString(cs.getColumnIndex(DataModel.DATA_MODEL_CONTENT)));dmList.add(dm);}}}catch(Exception e){e.printStackTrace();Log.e(TAG, "queryDataModelList error");close();}return dmList;}

3、更新

/** * 更新相应数据的相应信息，如发送次数，失败码等 * @param dmList */public void updateDataModelList(List<DataModel> dmList){db.beginTransaction();try{for(int i=0; i<dmList.size(); i++){DataModel dm = dmList.get(i);String sql = "update " + DataModel.TABLE_DATA_NAME + " set " + DataModel.DATA_MODEL_SENDCOUNT + "=" + dm.getSendCount() + ", "+ DataModel.DATA_MODEL_RRCODE + "=" + dm.getReasonResultCode() + ","+ DataModel.DATA_MODEL_SENDLASTTIME + "='" + dm.getSendLastTime() + "'"+ " where "+ DataModel.DATA_MODEL_ID + "=" + dm.getId();db.execSQL(sql);}db.setTransactionSuccessful();  //设置事务成功完成 Log.d(TAG, "updateDataModelList OK");}catch(Exception e){e.printStackTrace();Log.e(TAG, "updateDataModelList error");close();}finally{db.endTransaction();}}

4、删除记录

/** * 删除数据库相应记录 * @param dmList */public void deleteDataModelList(List<DataModel> dmList){try{String ids = "";for(int i=0; i<dmList.size(); i++){ids +=dmList.get(i).getId();if(i != (dmList.size()-1)){ids += ",";}}String sql = "delete from " + DataModel.TABLE_DATA_NAME + " where id in("+ids+")";db.execSQL(sql);}catch(Exception e){e.printStackTrace();Log.e(TAG, "deleteDataModelList error");close();}}

5、删除表

/** * 升级数据库，删除相应表结构 * @param db */public static void dropTables(SQLiteDatabase db){db.beginTransaction();db.execSQL("DROP TABLE IF EXISTS "+MobileModel.TABLE_MOBILE_NAME);db.execSQL("DROP TABLE IF EXISTS "+DataModel.TABLE_DATA_NAME);db.setTransactionSuccessful();db.endTransaction();}

6、整理DB文件空闲碎片，压缩DB文件无用空间

/** * 整理DB文件空闲碎片，压缩DB文件无用空间 */public void cleanDB(){try{db.execSQL("VACUUM");Log.i(TAG, "execSQL(VACUUM) success");}catch(Exception e){e.printStackTrace();Log.e(TAG, "execSQL(VACUUM) error");close();}}

0 0