sqlite数据库的简单使用

1.创建数据库

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class JQSqliteOpenHepler extends SQLiteOpenHelper{

	

	public JQSqliteOpenHepler(Context context) {
		super(context, "jqDataBase.db", null, 1);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	//创建数据库的语句
	db.execSQL("create table if not exists jqDataBase(_id integer primary key autoincrement,jqid varchar(30),bjly varchar(20)," +
			"jqbt varchar(500),yszy varchar(300),jqzy varchar(300),jqdd varchar(300),jd varchar(20)," +
			"wd varchar(20),jqjb varchar(20),bjsj varchar(30),cjfj varchar(20),readStatus varchar(2),cjpcs varchar(20))");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

}

2.操作数据库，进行增删改查

public class JQDataBase {
	private JQSqliteOpenHepler helper;

	private Context context;

	// 单例模式，保证操作数据库的对象只有一个
	// 成员变量
	private static JQDataBase dao;

	// 私有化构造方法
	private JQDataBase(Context context) {
		this.context = context;
		helper = new JQSqliteOpenHepler(context);
	}

	// 得到自身对象的公有的方法, 解决线程安全问题，要加锁synchronized
	public static synchronized JQDataBase getInstance(Context context) {
		if (dao == null) {
			dao = new JQDataBase(context);
		}
		return dao;
	}

	/**
	 * 定义一个指向数据库的 URI
	 */
	Uri uri = Uri.parse("content://ysl");



	// 对数据库的增删改查
public static int count;
	// 增加一条
	public void addJQ(String json) {

		// 解析json
		SQLiteDatabase db = helper.getWritableDatabase();
		try {
			
			ContentValues values = new ContentValues();
			JSONArray jsonArray = new JSONArray(json);
			for (int i = 0; i < jsonArray.length(); i++) {
				count++;
				JSONObject jsonObject = jsonArray.getJSONObject(i);

				String jqbt = jsonObject.getString("JQBT");
				String bjsj = jsonObject.getString("BJSJ");
				String cjfj = jsonObject.getString("CJFJ");
				String jqid = jsonObject.getString("JQID");
				String bjly = jsonObject.getString("BJLY");
				String yszy = jsonObject.getString("YSZY");
				String jqzy = jsonObject.getString("JQZY");
				String jqdd = jsonObject.getString("JQDD");
				String jd = jsonObject.getString("JD");
				String wd = jsonObject.getString("WD");
				String jqjb = jsonObject.getString("JQJB");
				String cjpcs = jsonObject.getString("CJPCS");

				values.put("jqid", jqid);
				values.put("bjly", bjly);
				values.put("yszy", yszy);
				values.put("jqzy", jqzy);
				values.put("jqdd", jqdd);
				values.put("jd", jd);
				values.put("wd", wd);
				values.put("cjpcs", cjpcs);
				values.put("jqjb", jqjb);
				values.put("jqbt", jqbt);
				values.put("bjsj", bjsj);
				values.put("cjfj", cjfj);
				values.put("readStatus", "0");
				db.insert("jqDataBase", null, values);
			}
			db.close();
			// 通知系统，数据库发生变化了
			context.getContentResolver().notifyChange(uri, null);
			//计数器置为0
			count=0;
			//添加之前，设置数据库条目数
			deleteJQ();
			
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public  int insertCount() {
		int count=0;
		SQLiteDatabase db = helper.getWritableDatabase();
		db.rawQuery("select count(*) from jqDataBase", null);
		return count;
	}
	
	public void addCS() {
		SQLiteDatabase db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("jqbt", "helloworld");
		db.insert("jqDataBase", null, values);
		count = 1;
		// 通知系统，数据库发生变化了
		context.getContentResolver().notifyChange(uri, null);
	}

	// 解除一条
	public void deleteJQ(String jqid) {

		SQLiteDatabase db = helper.getWritableDatabase();
		db.delete("jqDataBase", "jqid", new String[] { jqid });

		// 通知系统，数据库发生变化了
		context.getContentResolver().notifyChange(uri, null);

		db.close();
	}

	// 查询一定数量
	public List<JingQing> queryJQ(int number) {
		List<JingQing> jqList = null;
		// JingQing jingQing = null;
		SQLiteDatabase db = helper.getReadableDatabase();
		// Cursor cursor = db.rawQuery("select * from jqDataBase", null);+
		// "  OFFSET 0"
		Cursor cursor = db.rawQuery("SELECT  *   FROM jqDataBase "
				+ "ORDER BY bjsj DESC LIMIT  " + number, null);
		jqList = getDate(number, jqList, cursor);
		cursor.close();
		db.close();
		return jqList;
	}

	// 查询未读
	public List<JingQing> querywdJQ(int number) {
		List<JingQing> jqList = null;
		// JingQing jingQing = null;
		SQLiteDatabase db = helper.getReadableDatabase();

		Cursor cursor = db.rawQuery(
				"select  *   from jqDataBase  where readStatus = 0 ORDER BY bjsj DESC  "
						+ number, null);

		jqList = getDate(number, jqList, cursor);
		cursor.close();
		db.close();
		return jqList;
	}

	private List<JingQing> getDate(int number, List<JingQing> jqList,
			Cursor cursor) {
		jqList = new ArrayList<JingQing>();
		JingQing jingQing = null;
		while (cursor.moveToNext()) {
			// 倒着便利cursor
			// for (int i = cursor.getCount() - 1; i >= cursor.getCount() -
			// number; i--) {
			jingQing = new JingQing();

			jingQing.jqid = cursor.getString(cursor.getColumnIndex("jqid"));
			jingQing.bjly = cursor.getString(cursor.getColumnIndex("bjly"));
			jingQing.yszy = cursor.getString(cursor.getColumnIndex("yszy"));
			jingQing.jqzy = cursor.getString(cursor.getColumnIndex("jqzy"));
			jingQing.jqdd = cursor.getString(cursor.getColumnIndex("jqdd"));
			jingQing.jd = cursor.getString(cursor.getColumnIndex("jd"));
			jingQing.wd = cursor.getString(cursor.getColumnIndex("wd"));
			jingQing.jqjb = cursor.getString(cursor.getColumnIndex("jqjb"));
			jingQing.cjpcs = cursor.getString(cursor.getColumnIndex("cjpcs"));
			jingQing.jqbt = cursor.getString(cursor.getColumnIndex("jqbt"));
			jingQing.bjsj = cursor.getString(cursor.getColumnIndex("bjsj"));
			jingQing.cjfj = cursor.getString(cursor.getColumnIndex("cjfj"));
			jingQing.readStatus = cursor.getString(cursor
					.getColumnIndex("readStatus"));

			jqList.add(jingQing);
			// }

		}
		return jqList;
	}

	/**
	 * // 更改读取状态，标记为已读或者未读
	 * 
	 * @param jqid
	 *            ID
	 * @param s
	 *            只能传递 0 或者 1 （0表示未读，1表示已读）
	 */
	public void update(String jqid, String s) {
		SQLiteDatabase db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("readStaus", s);
		db.update("jqDataBase", values, "jqid = ?", new String[] { jqid });

		// 通知系统，数据库发生变化了
		context.getContentResolver().notifyChange(uri, null);

		db.close();
	}

	// 获取最后一条数据的时间
	public String getLastBjsj() {
		String bjsj = null;
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery("select bjsj from jqDataBase where _id = (select  max(_id) from jqDataBase)",null);
		while (cursor.moveToNext()) {
			bjsj = cursor.getString(cursor.getColumnIndex("bjsj"));
		}
		db.close();
		if (bjsj == null){
			return null;
		}else {
			return bjsj.substring(0, bjsj.length());
		}
	}

	public void deleteJQ() throws Exception {
		SQLiteDatabase db = helper.getWritableDatabase();

		AssetManager am = context.getAssets();
		InputStream url = am.open("config.xml", AssetManager.ACCESS_STREAMING);
		Xmlbean xmlbean = XmlUtils.parseXml(url);
		long savetime = Long.parseLong(xmlbean.savetime)*24*3600*1000;

		long currentTimeMillis = System.currentTimeMillis();

		long oldTime = currentTimeMillis - savetime;

		Date date = new Date(oldTime);
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  //2016-06-29 19:51:30
		String dateformat = formatter.format(date);

		Cursor cursor = db.rawQuery("delete from jqDataBase where bjsj <'"
					+ dateformat + "'", null);
		
		db.close();
	}

}

里面定义了一个url，可以来监听数据库的变化，以便数据库有变化时，及时得到消息。这就是一个内容观察者。

3.在服务中注册数据库变化的内容观察者

// 定义一个指向数据库的 URI
		Uri uri = Uri.parse("content://ysl");
		getContentResolver().registerContentObserver(uri, true, jqObserver);