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

	@Override
	public 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);
	}

	@Override
	public 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==db2

			String sql = "insert into Diary(title,body,time) values(?,?,?)";   // 防止潜在 的bug
			db.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;
		}
}