便签软件开发笔记——数据库操作类

本文将详细说明mymemo 1.0的数据库操作部分。

 

从《便签软件开发笔记——概述》一本知道，mymemo 1.0需要两张数据库表，一张表存储日期，该日期创建的总便签数以及完成便签数。这张表我称之为“datelist”；一张表存储便签内容，包括内容、进度，以及该便签所从属的日期的ID，称之为“memolist”。创建这两张表的sql如下：

 

create table datelist (_id integer primary key autoincrement, mmdate text not null, mmtotal integer not null, mmcomp integer not null);

 

create table memolist (_id integer primary key autoincrement, mmcontent text not null, mmdateid integer not null, mmprocess integer not null);

 

在创建数据库操作类之前，需要说明一下一个辅助类，定义代码如下：

private static class MemoDbHelper extends SQLiteOpenHelper{ public MyMemoOpenHelper(Context context) { /*创建数据库并制定版本*/ super(context, DB_NAME, null, DB_VERSION); } public void onCreate(SQLiteDatabase db) { /*创建表*/ db.execSQL(THING_CREATE); db.execSQL(DATE_CREATE); } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { /*数据库升级*/ db.execSQL("DROP TABLE IF EXISTS datelist"); db.execSQL("DROP TABLE IF EXISTS thinglist"); onCreate(db); } }

根据google android的开发手册，SQLiteOpenHelper类的说明如下：

 

用于数据库创建和版本管理的一个辅助类。开发人员创建该类的子类时需要实现onCreate(SQLiteDatabase)、onUpgrade(SQLiteDatabase, int,int)两个方法，onOpen(SQLiteDatabase)方法的实现是可选的。当我们使用这个类获取数据库实例的时候，如果数据库存在那么就会打开它，如果不存在则会创建它。也可以使用事务。onUpgrade(SQLiteDatabase, int, int)方法用于升级数据库，我们可以在这个方法中添加表，删除表，或者更改表，或者其他升级所需要做的事。

 

说明这个辅助类之后，接着说我所定义的数据库操作类MemoDbHelper。定义如下：

 

 package com.flyfish.memo; import android.app.Activity; import android.os.Bundle; import android.database.SQLException; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteDatabase; import android.content.ContentValues; public class MemoDb { public static final String DB_NAME = "memodb"; public static int DB_VERSION = 1; private MemoDbHelper memoDbHelper = null; private Context ctx = null; private SQLiteDatabase myDb = null; private Cursor mCursor = null; /*datelist和memolist的id列的别名，用于intent传值， * 否则如果一个activity需要接收datelist和memolist的id时就会出现问题*/ public static final String SUDO_DATE_ROWID = "date_id"; public static final String SUDO_MEMO_ROWID = "thing_id"; /*datelist表的列名*/ public static final String DATE_TABLE = "datelist"; public static final String DATE_ROWID = "_id"; public static final String DATE_DATE = "mmdate"; public static final String DATE_TOTAL = "mmtotal"; public static final String DATE_COMP = "mmcomp"; /*memolist表的列名*/ public static final String MEMO_TABLE = "memolist"; public static final String MEMO_ROWID = "_id"; public static final String MEMO_DATEID ="mmdateid"; public static final String MEMO_CONTENT = "mmcontent"; public static final String MEMO_PROCESS = "mmprocess"; /*创建表的sql*/ private static final String DATE_CREATE = "create table datelist ( _id integer primary key autoincrement, mmdate text not null, mmtotal integer not null, mmcomp integer not null);"; private static final String MEMO_CREATE = "create table memolist ( _id integer primary key autoincrement, mmdateid integer not null, mmcontent text not null, mmprocess integer not null);"; private static class MemoDbHelper extends SQLiteOpenHelper{ public MemoDbHelper(Context context) { super(context, DB_NAME, null, DB_VERSION); } public void onCreate(SQLiteDatabase db) { db.execSQL(DATE_CREATE); db.execSQL(MEMO_CREATE); } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS " + DATE_TABLE); db.execSQL("DROP TABLE IF EXISTS " + MEMO_TABLE); onCreate(db); } } public MemoDb(Context ctx) { this.ctx = ctx; } public MemoDb openDb() throws SQLException { memoDbHelper = new MemoDbHelper(ctx); myDb = memoDbHelper.getWritableDatabase(); return this; } public void closeDb() { if (myDb != null) { memoDbHelper.close(); } } public long insertDateItem(String date) { ContentValues cv = new ContentValues(); cv.put(DATE_DATE, date); cv.put(DATE_TOTAL, 0); cv.put(DATE_COMP, 0); if (myDb == null) { openDb(); } return myDb.insert(DATE_TABLE, null, cv); } public int getDateTotal(long dateId) { mCursor = myDb.query(DATE_TABLE, new String[]{DATE_TOTAL}, DATE_ROWID+"="+dateId, null, null, null, null); int colsIndex = mCursor.getColumnIndex(DATE_TOTAL); if (mCursor != null) { if (mCursor.moveToFirst()) { return mCursor.getInt(colsIndex); } } return -1; } public long insertMemoItem(long dateId, String content, int process) { ContentValues cv = new ContentValues(); cv.put(MEMO_DATEID, dateId); cv.put(MEMO_CONTENT, content); cv.put(MEMO_PROCESS, process); if (myDb == null) { openDb(); } long memoId = myDb.insert(MEMO_TABLE, null, cv); if (memoId >= 0) { int total = getDateTotal(dateId); updateDateTotal(dateId, total+1); } return memoId; } public boolean updateDateItem(long dateId, int total, int compNum) { ContentValues cv = new ContentValues(); cv.put(DATE_TOTAL, total); cv.put(DATE_COMP, compNum); return (myDb.update(DATE_TABLE, cv, DATE_ROWID+"="+dateId, null) > 0); } public boolean updateDateTotal(long rowId, int total) { ContentValues cv = new ContentValues(); cv.put(DATE_TOTAL, total); return (myDb.update(DATE_TABLE, cv, DATE_ROWID+"="+rowId, null) > 0); } public boolean updateThingItem(long rowid, String content, int complete) { ContentValues cv = new ContentValues(); cv.put(MEMO_CONTENT, content); cv.put(MEMO_PROCESS, complete); return (myDb.update(MEMO_TABLE, cv, MEMO_ROWID+"="+rowid, null) > 0); } public boolean delDateItem(long rowid) { int ret = 0; ret = myDb.delete(DATE_TABLE, DATE_ROWID+"="+rowid, null); if (ret > 0) { return (myDb.delete(MEMO_TABLE, MEMO_DATEID+"="+rowid, null) > 0); } return false; } public boolean delMemoItem(long dateId, long rowid) { Cursor cursor = myDb.query(MEMO_TABLE, new String[]{MEMO_PROCESS}, MEMO_ROWID+"="+rowid, null, null, null, null); cursor.moveToFirst(); int cols = cursor.getColumnIndex(MEMO_PROCESS); int process = cursor.getInt(cols); cursor = myDb.query(DATE_TABLE, new String[]{DATE_TOTAL,DATE_COMP}, DATE_ROWID+"="+dateId, null, null, null, null); cursor.moveToFirst(); cols = cursor.getColumnIndex(DATE_TOTAL); int total = cursor.getInt(cols); cols = cursor.getColumnIndex(DATE_COMP); int comp = cursor.getInt(cols); total -= 1; if (process >= 100) { comp -= 1; } updateDateItem(dateId, total, comp); myDb.delete(MEMO_TABLE, MEMO_ROWID+"="+rowid, null); return true; } public Cursor fetchAllDates() { mCursor = myDb.query(DATE_TABLE, new String[]{DATE_ROWID, DATE_DATE, DATE_TOTAL, DATE_COMP}, null, null, null, null, null); return mCursor; } public Cursor fetchDateId(String date) { mCursor = myDb.query(DATE_TABLE, new String[]{DATE_ROWID}, DATE_DATE + "='" + date +"'", null, null, null, null); return mCursor; } public String fetchDateById(long id) { mCursor = myDb.query(DATE_TABLE, new String[]{DATE_DATE}, DATE_ROWID+"="+id, null, null, null, null); mCursor.moveToFirst(); int cols = mCursor.getColumnIndex(DATE_DATE); String date = mCursor.getString(cols); return date; } public Cursor fetchMemoByDateId(long rowid) { mCursor = myDb.query(MEMO_TABLE, new String[]{MEMO_ROWID,MEMO_CONTENT, MEMO_PROCESS}, MEMO_DATEID+"="+rowid, null, null, null, null); return mCursor; } public long fetchDateIdByMemoId(long memoId) { mCursor = myDb.query(MEMO_TABLE, new String[]{MEMO_DATEID}, MEMO_DATEID+"="+memoId, null, null, null, null); mCursor.moveToFirst(); int cols = mCursor.getColumnIndex(MEMO_DATEID); long dateId = mCursor.getLong(cols); return dateId; } public Cursor fetchMemoById(long memoId) { mCursor = myDb.query(MEMO_TABLE, new String[]{MEMO_CONTENT,MEMO_PROCESS}, MEMO_ROWID+"="+memoId, null, null, null, null); return mCursor; } }

需要注意的是，创建表的sql必须以“;”结束，否则创建不成功；另外，如果你在编程的过程中更改了列名，最好数据库表也换成新的，否则也会出现错误。

感觉这些是很基础的东西，没什么需要再说的，呵呵：)