Android SQLite 操作 2 记事本示例

一个简单通过SQLite 执行CRUD 的列表展示和编辑程序。

数据操作类

import java.util.Calendar;
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;

public class DiaryDbAdapter {

    //数据库名表名
    private static final String DATABASE_NAME = "database";
    private static final String DATABASE_TABLE = "diary";
    private static final int DATABASE_VERSION = 1;
   
    //字段属性
    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 DatabaseHelper mDbHelper;
    //数据库本身
    private SQLiteDatabase mDb;

    //建库语句
    private static final String DATABASE_CREATE = "create table diary (_id integer primary key autoincrement, "
            + "title text not null, body text not null, created text not null);";

    //上下文对象
    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);
        }

        @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 {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase(); //获得一个可写数据库
        return this;
    }
    //关闭数据库操作类
    public void closeclose() {
        mDbHelper.close();
    }

    //添加一条记录
    public long createDiary(String title, String body) {
        ContentValues initialValues = new ContentValues();
        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) {

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

    //获得所有数据并返回Cursor
    public Cursor getAllNotes() {

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

    //获得一条数据，返回也是Cursor
    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;
    }
}

List 页面

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;

/**
* @author jinyan
*
*/
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; //定义数据操作类，这个类在一个新的Java文件中定义
    private Cursor mDiaryCursor; //数据存储集合

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.diary_list);
        mDbHelper = new DiaryDbAdapter(this); //初始化数据对象
        mDbHelper.open(); //打开将进行初始化，实例化一个数据库
        renderListView(); //刷新ListView

    }

    private void renderListView() {
        mDiaryCursor = mDbHelper.getAllNotes();
        startManagingCursor(mDiaryCursor); //让数据被托管，这样可以垃圾回收
        String[] from = new String[] { DiaryDbAdapter.KEY_TITLE,
                DiaryDbAdapter.KEY_CREATED };//数据字段
        int[] to = new int[] { R.id.text1, R.id.created }; //List控件 Id
        SimpleCursorAdapter notes = new SimpleCursorAdapter(this,
                R.layout.diary_row, mDiaryCursor, from, to); //绑定Cousor对象到List
        setListAdapter(notes); //绑定当前List 数据
    }

    //重写草组偶菜单方法，建立菜单。
    @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可以作为参数传进来
        i.putExtra(DiaryDbAdapter.KEY_TITLE, c.getString(c
                .getColumnIndexOrThrow(DiaryDbAdapter.KEY_TITLE)));
        i.putExtra(DiaryDbAdapter.KEY_BODY, c.getString(c
                .getColumnIndexOrThrow(DiaryDbAdapter.KEY_BODY)));
       
        //调转带ID
        startActivityForResult(i, ACTIVITY_EDIT);
    }

    //返回后刷新列表
    @Override
    protected void onActivityResult(int requestCode, int resultCode,
            Intent intent) {
        super.onActivityResult(requestCode, resultCode, intent);
        renderListView();
    }
}

编辑页面

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) {
            //获得传递过来的参数
            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);//设置点击按键
                finish();//结束页面
            }

        });
    }
}