SQLite学习实例, 日记本

这是一个简单的android日记程序, 按下菜单栏后, 会出现添加新日记和删除日记两项, 点击主页面的日记可以直接编辑日记

整个程序分成三个文件, 主程序逻辑是ActivityMain.java, 编辑逻辑是ActivityDiaryEdit.java, 还有一个DiaryDbAdapter.java文件定义了一个DiaryDbAdapter类,封装数据库的操作.

以下是ActivityMain.java文件的code:

/**
*ActivityMain.java
*/

import android.app.ListActivity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

public class ActivityMain extends ListActivity {
	private static final int ACTIVITY_CREATE = 0;
	private static final int ACTIVITY_EDIT = 1;

	private static final int INSERT_ID = Menu.FIRST;
	private static final int DELETE_ID = Menu.FIRST + 1;

	private DiaryDbAdapter mDbHelper;  //操作数据库的实例
	private Cursor mDiaryCursor;       //存储日记

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.diary_list);
		mDbHelper = new DiaryDbAdapter(this);     //创建自定义类DiaryDbAdapter的实例
		mDbHelper.open();                        //打开数据库
		renderListView();                       //刷新显示主界面

	}

	private void renderListView() {
                mDiaryCursor = mDbHelper.getAllNotes();   //获得所有日志文件, 并赋给Cursor实例
		startManagingCursor(mDiaryCursor);
		String[] from = new String[] { DiaryDbAdapter.KEY_TITLE,
				DiaryDbAdapter.KEY_CREATED };
		int[] to = new int[] { R.id.text1, R.id.created };
		SimpleCursorAdapter notes = new SimpleCursorAdapter(this,
				R.layout.diary_row, mDiaryCursor, from, to);   //把游标数据显示在视图上的指定位置
                                                                              //R.layout.diary_row为每一行的布局
                                setListAdapter(notes);                   //显示界面
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {              
		super.onCreateOptionsMenu(menu);
		menu.add(0, INSERT_ID, 0, R.string.menu_insert);      //加入菜单项, 添加日记
                menu.add(0, DELETE_ID, 0, R.string.menu_delete);      //加入菜单项, 删除日记
		return true;
	}

	@Override
	public boolean onMenuItemSelected(int featureId, MenuItem item) {
		switch (item.getItemId()) {
		case INSERT_ID:
			createDiary();     //点击添加日记时的动作
			return true;
		case DELETE_ID:
			mDbHelper.deleteDiary(getListView().getSelectedItemId());  //点击删除日记时的动作
			renderListView();
			return true;
		}
		return super.onMenuItemSelected(featureId, item);
	}

	private void createDiary() {
		Intent i = new Intent(this, ActivityDiaryEdit.class);       
		startActivityForResult(i, ACTIVITY_CREATE);           //跳转到编辑界面
	}

	@Override
	/* 需要对position和id进行一个区分
	 * position指的是点击的这个ViewItem在当前ListView中的位置
	 * 每一个和ViewItem绑定的数据，肯定都有一个id，通过这个资源id可以找到那条数据。
         */
        protected void onListItemClick(ListView l, View v, int position, long id) {
        //点击日记时打开编辑界面,直接编辑日记
		super.onListItemClick(l, v, position, id);
		Cursor c = mDiaryCursor;
		c.moveToPosition(position);      //游标移动到你点击的位置, 即获得日记的数据
		Intent i = new Intent(this, ActivityDiaryEdit.class);
		i.putExtra(DiaryDbAdapter.KEY_ROWID, id);          //把id值存入intent
		i.putExtra(DiaryDbAdapter.KEY_TITLE, c.getString(c
				.getColumnIndexOrThrow(DiaryDbAdapter.KEY_TITLE)));   //把title的值存入intent
		i.putExtra(DiaryDbAdapter.KEY_BODY, c.getString(c
				.getColumnIndexOrThrow(DiaryDbAdapter.KEY_BODY)));    //把body的值存入intent
		startActivityForResult(i, ACTIVITY_EDIT);
	}

	@Override
	protected void onActivityResult(int requestCode, int resultCode,
			Intent intent) {
		super.onActivityResult(requestCode, resultCode, intent);
		renderListView();        //编辑完成返回后, 刷新显示主界面
	}
}

以下是ActivityDiaryEdit.java的code:

/**
*ActivityDiaryEdit.java
*/

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;

public class ActivityDiaryEdit extends Activity {

	private EditText mTitleText;
	private EditText mBodyText;
	private Long mRowId;
	private DiaryDbAdapter mDbHelper;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		mDbHelper = new DiaryDbAdapter(this);
		mDbHelper.open();
		setContentView(R.layout.diary_edit);

		mTitleText = (EditText) findViewById(R.id.title);
		mBodyText = (EditText) findViewById(R.id.body);

		Button confirmButton = (Button) findViewById(R.id.confirm);

		mRowId = null;
		// 每一个intent都会带一个Bundle型的extras数据。
		Bundle extras = getIntent().getExtras();
		if (extras != null) {      //如果extras为空,则是创建日记; 如果非空,则是编辑日记
			String title = extras.getString(DiaryDbAdapter.KEY_TITLE);
			String body = extras.getString(DiaryDbAdapter.KEY_BODY);
			mRowId = extras.getLong(DiaryDbAdapter.KEY_ROWID);

			if (title != null) {
				mTitleText.setText(title);   //显示需要编辑的日记题目
			}
			if (body != null) {
				mBodyText.setText(body);     //显示需要编辑的日记内容
			}
		}

		confirmButton.setOnClickListener(new View.OnClickListener() {  //保存按钮
			public void onClick(View view) {
				String title = mTitleText.getText().toString();
				String body = mBodyText.getText().toString();
				if (mRowId != null) {   //如果为空,则是创建日记; 如果非空,则是更新日记
					mDbHelper.updateDiary(mRowId, title, body);   //操作数据库, 更新日记内容
				} else
					mDbHelper.createDiary(title, body);     //操作数据库, 创建日记
				Intent mIntent = new Intent();
				setResult(RESULT_OK, mIntent);     //设置返回RESULE_OK
				finish();
			}

		});
	}
}

以下是DiaryDbAdapter.java的code:

/**
*DiaryDbAdapter.java
*/

import java.util.Calendar;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DiaryDbAdapter {

	public static final String KEY_TITLE = "title";
	public static final String KEY_BODY = "body";
	public static final String KEY_ROWID = "_id";
	public static final String KEY_CREATED = "created";

	private static final String TAG = "DiaryDbAdapter";
	private DatabaseHelper mDbHelper;       //负责创建,打开,升级,关闭数据库
	private SQLiteDatabase mDb;             //负责增,删,改,查等数据库的基本操作

	private static final String DATABASE_CREATE_TABLE = "create table diary (_id integer primary key autoincrement, "
			+ "title text not null, body text not null, created text not null);";

	private static final String DATABASE_NAME = "database";
	private static final String DATABASE_TABLE = "diary";
	private static final int DATABASE_VERSION = 1;

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE_TABLE);     //创建数据表
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS diary");    //通过直接删除旧数据库,重建新数据库,实现数据库升级
			onCreate(db);
		}
	}

	public DiaryDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public DiaryDbAdapter open() throws SQLException {    //打开数据库, 获得SQLiteDatabase实例
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();  //关闭数据库
	}

	public long createDiary(String title, String body) {
		ContentValues initialValues = new ContentValues();   //ContentValues与map类似, 只能存储基本类型的值
		initialValues.put(KEY_TITLE, title);
		initialValues.put(KEY_BODY, body);
		Calendar calendar = Calendar.getInstance();
		String created = calendar.get(Calendar.YEAR) + "年"
				+ calendar.get(Calendar.MONTH) + "月"
				+ calendar.get(Calendar.DAY_OF_MONTH) + "日"
				+ calendar.get(Calendar.HOUR_OF_DAY) + "时"
				+ calendar.get(Calendar.MINUTE) + "分";   
		initialValues.put(KEY_CREATED, created);             //存储创建日记的时间
		return mDb.insert(DATABASE_TABLE, null, initialValues);
	}

	public boolean deleteDiary(long rowId) {    //删除指定ID的日记

		return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}

	public Cursor getAllNotes() {

		return mDb.query(DATABASE_TABLE, new String[] { KEY_ROWID, KEY_TITLE,
				KEY_BODY, KEY_CREATED }, null, null, null, null, null);
	}

	public Cursor getDiary(long rowId) throws SQLException {

		Cursor mCursor =

		mDb.query(true, DATABASE_TABLE, new String[] { KEY_ROWID, KEY_TITLE,
				KEY_BODY, KEY_CREATED }, KEY_ROWID + "=" + rowId, null, null,
				null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

	}

	public boolean updateDiary(long rowId, String title, String body) {
		ContentValues args = new ContentValues();
		args.put(KEY_TITLE, title);
		args.put(KEY_BODY, body);
		Calendar calendar = Calendar.getInstance();
		String created = calendar.get(Calendar.YEAR) + "年"
				+ calendar.get(Calendar.MONTH) + "月"
				+ calendar.get(Calendar.DAY_OF_MONTH) + "日"
				+ calendar.get(Calendar.HOUR_OF_DAY) + "时"
				+ calendar.get(Calendar.MINUTE) + "分";
		args.put(KEY_CREATED, created);

		return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;  //更新指定ID的日记
	}
}

最后来一张实现图, 右上角是菜单项,白色部分是每个日记的标题.