【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 + " inte

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

总结

以上是内存溢出为你收集整理的【SQLite】常用 *** 作SQL及压缩SQLite的实例代码全部内容，希望文章能够帮你解决【SQLite】常用 *** 作SQL及压缩SQLite的实例代码所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。