Android記事本開發之SQLite資料庫實現

package com.jk.service;/** * 完成資料庫的建立和版本更新 */import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class DbHelper extends SQLiteOpenHelper {public DbHelper(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);}public DbHelper(Context context) {super(context, "diary.db",null,1);}@Overridepublic void onCreate(SQLiteDatabase db) {String sql = "CREATE TABLE Diary "+ "(diaryId integer primary key autoincrement, title varchar(20),body varchar(1000),time varchar(40))";    db.execSQL(sql);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {//String sql="ALTER TABLE diary ADD body varchar(1000) null";       //db.execSQL(sql);System.out.println("DataBase Updated!!");}}

package com.jk.service;import com.jk.Diary;import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;/** * 完成對資料庫的增刪改查操作 * @author jkxqj */public class DiaryAdapter {private DbHelper dbOpenHelper; public DiaryAdapter(Context context) { dbOpenHelper = new DbHelper(context);}  public void save(Diary diary) {           /*            getWritableDatabase和getReadableDatabase()方法在磁碟空間沒滿的情況下一樣    其中getWritableDatabase() 方法以讀寫方式開啟資料庫，一旦資料庫的磁碟空間滿了，    資料庫就只能讀而不能寫，倘若使用的是getWritableDatabase() 方法就會出錯。    getReadableDatabase()方法則是先以讀寫方式開啟資料庫，如果資料庫的磁碟空間滿了，    就會開啟失敗，當開啟失敗後會繼續嘗試以唯讀方式開啟資料庫。如果該問題成功解決，    則唯讀資料庫物件就會關閉，然後返回一個可讀寫的資料庫物件。 */ SQLiteDatabase db = dbOpenHelper.getWritableDatabase();/* SQLiteDatabase db2=dbOpenHelper.getWritableDatabase(); */// db==db2String sql = "insert into Diary(title,body,time) values(?,?,?)";   // 防止潛在 的bugdb.execSQL(sql, new Object[] { diary.getTitle(), diary.getBody(),diary.getTime()});db.close();}public void update(Diary diary) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();String sql = "update Diary set title=?,body=? where diaryId=?";  db.execSQL(sql, new Object[] { diary.getTitle(), diary.getBody(),diary.getId() });db.close();}public void delete(Integer id) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();String sql = "delete from Diary where diaryId=?"; db.execSQL(sql, new Object[] { id });db.close();}public Diary find(Integer id) {SQLiteDatabase db = dbOpenHelper.getReadableDatabase();String sql = "select * from Diary where diaryId=?"; Cursor cursor = db.rawQuery(sql, new String[] { id.toString() });if (cursor.moveToFirst()) {     //將遊標移到第一條記錄,若成功說明存在記錄int diaryId = cursor.getInt(cursor.getColumnIndex("diaryId"));String title = cursor.getString(cursor.getColumnIndex("title"));String body = cursor.getString(cursor.getColumnIndex("body"));String time = cursor.getString(cursor.getColumnIndex("time"));return new Diary(diaryId, title, body,time);}return null;}public List<Diary> getScrollData(int offset, int maxResult) {List<Diary> Diarys = new ArrayList<Diary>();SQLiteDatabase db = dbOpenHelper.getReadableDatabase();// offset指位移量，maxResult指最大結果集String sql = "select * from Diary limit ?,?"; Cursor cursor = db.rawQuery(sql, new String[] { String.valueOf(offset),String.valueOf(maxResult) });while (cursor.moveToNext()) {//遊標移到下一個說明資料沒讀完int diaryId = cursor.getInt(cursor.getColumnIndex("diaryId"));String title = cursor.getString(cursor.getColumnIndex("title"));String body = cursor.getString(cursor.getColumnIndex("body"));String time = cursor.getString(cursor.getColumnIndex("time"));Diarys.add(new Diary(diaryId, title, body,time));}db.close();cursor.close();return Diarys;}public Cursor getCursorScrollData(int offset, int maxResult) {SQLiteDatabase db = dbOpenHelper.getReadableDatabase();// offset指位移量，maxResult指最大結果集String sql = "select diaryId as _id,title, body,time from Diary limit ?,?";//SimpleCursorAdapter需要有_id這個欄位Cursor cursor = db.rawQuery(sql, new String[] { String.valueOf(offset),String.valueOf(maxResult) });return cursor;}public long getCount() {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();String sql = "select count(*) from Diary"; Cursor cursor = db.rawQuery(sql,null);cursor.moveToFirst();db.close();long i = cursor.getLong(0);cursor.close();return i;}}