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

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