android SQLite数据库基本操作示范

package com.yarin.android.Examples_06_05;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Color;
import android.os.Bundle;
import android.view.KeyEvent;
import android.widget.LinearLayout;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

public class Activity01 extends Activity
{
	private static int			miCount			= 0;

	/* 数据库对象 */
	private SQLiteDatabase		mSQLiteDatabase	= null;

	/* 数据库名 */
	private final static String	DATABASE_NAME	= "Examples_06_05.db";
	/* 表名 */
	private final static String	TABLE_NAME		= "table1";

	/* 表中的字段 */
	private final static String	TABLE_ID		= "_id";
	private final static String	TABLE_NUM		= "num";
	private final static String	TABLE_DATA		= "data";

	/* 创建表的sql语句 */
	private final static String	CREATE_TABLE	= "CREATE TABLE " + TABLE_NAME + " (" + TABLE_ID + " INTEGER PRIMARY KEY," + TABLE_NUM + " INTERGER,"+ TABLE_DATA + " TEXT)";

	/* 线性布局 */
	LinearLayout				m_LinearLayout	= null;
	/* 列表视图-显示数据库中的数据 */
	ListView					m_ListView		= null;


	/** Called when the activity is first created. */
	@Override
	public void onCreate(Bundle savedInstanceState)
	{
		super.onCreate(savedInstanceState);

		/* 创建LinearLayout布局对象 */
		m_LinearLayout = new LinearLayout(this);
		/* 设置布局LinearLayout的属性 */
		m_LinearLayout.setOrientation(LinearLayout.VERTICAL);
		m_LinearLayout.setBackgroundColor(android.graphics.Color.BLACK);

		/* 创建ListView对象 */
		m_ListView = new ListView(this);
		LinearLayout.LayoutParams param = new LinearLayout.LayoutParams(LinearLayout.LayoutParams.FILL_PARENT, LinearLayout.LayoutParams.WRAP_CONTENT);
		m_ListView.setBackgroundColor(Color.BLACK);

		/* 添加m_ListView到m_LinearLayout布局 */
		m_LinearLayout.addView(m_ListView, param);

		/* 设置显示m_LinearLayout布局 */
		setContentView(m_LinearLayout);

		// 打开已经存在的数据库
		mSQLiteDatabase = this.openOrCreateDatabase(DATABASE_NAME, MODE_PRIVATE, null);

		// 获取数据库Phones的Cursor
		try
		{
			/* 在数据库mSQLiteDatabase中创建一个表 */
			mSQLiteDatabase.execSQL(CREATE_TABLE);
		}
		catch (Exception e)
		{
			UpdataAdapter();
		}
	}


	public boolean onKeyUp(int keyCode, KeyEvent event)
	{
		switch (keyCode)
		{
			case KeyEvent.KEYCODE_DPAD_LEFT:
				AddData();
				break;
			case KeyEvent.KEYCODE_DPAD_RIGHT:
				DeleteData();
				break;
			case KeyEvent.KEYCODE_1:
				UpData();
				break;
			case KeyEvent.KEYCODE_2:
				DeleteTable();
				break;
			case KeyEvent.KEYCODE_3:
				DeleteDataBase();
				break;
		}
		return true;
	}


	/* 删除数据库 */
	public void DeleteDataBase()
	{
		this.deleteDatabase(DATABASE_NAME);
		this.finish();
	}


	/* 删除一个表 */
	public void DeleteTable()
	{
		mSQLiteDatabase.execSQL("DROP TABLE " + TABLE_NAME);
		this.finish();
	}


	/* 更新一条数据 */
	public void UpData()
	{
		ContentValues cv = new ContentValues();
		cv.put(TABLE_NUM, miCount);
		cv.put(TABLE_DATA, "修改后的数据" + miCount);

		/* 更新数据 */
		mSQLiteDatabase.update(TABLE_NAME, cv, TABLE_NUM + "=" + Integer.toString(miCount - 1), null);

		UpdataAdapter();
	}


	/* 向表中添加一条数据 */
	public void AddData()
	{
		ContentValues cv = new ContentValues();
		cv.put(TABLE_NUM, miCount);
		cv.put(TABLE_DATA, "测试数据库数据" + miCount);
		/* 插入数据 */
		mSQLiteDatabase.insert(TABLE_NAME, null, cv);
		miCount++;
		UpdataAdapter();
	}
   

	/* 从表中删除指定的一条数据 */
	public void DeleteData()
	{

		/* 删除数据 */
		mSQLiteDatabase.execSQL("DELETE FROM " + TABLE_NAME + " WHERE _id=" + Integer.toString(miCount));

		miCount--;
		if (miCount < 0)
		{
			miCount = 0;
		}
		UpdataAdapter();
	}


	/* 更行试图显示 */
	public void UpdataAdapter()
	{
		// 获取数据库Phones的Cursor
		Cursor cur = mSQLiteDatabase.query(TABLE_NAME, new String[] { TABLE_ID, TABLE_NUM, TABLE_DATA }, null, null, null, null, null);

		miCount = cur.getCount();
		if (cur != null && cur.getCount() >= 0)
		{
			// ListAdapter是ListView和后台数据的桥梁
			ListAdapter adapter = new SimpleCursorAdapter(this,
			// 定义List中每一行的显示模板
				// 表示每一行包含两个数据项
				android.R.layout.simple_list_item_2,
				// 数据库的Cursor对象
				cur,
				// 从数据库的TABLE_NUM和TABLE_DATA两列中取数据
				new String[] { TABLE_NUM, TABLE_DATA },
				// 与NAME和NUMBER对应的Views
				new int[] { android.R.id.text1, android.R.id.text2 });

			/* 将adapter添加到m_ListView中 */
			m_ListView.setAdapter(adapter);
		}
	}


	/* 按键事件处理 */
	public boolean onKeyDown(int keyCode, KeyEvent event)
	{
		if (keyCode == KeyEvent.KEYCODE_BACK)
		{
			/* 退出时，不要忘记关闭 */
			mSQLiteDatabase.close();
			this.finish();
			return true;
		}
		return super.onKeyDown(keyCode, event);
	}
}

Android提供了非SQL指令的对数据表进行操作的方法，以下表格是一些常用方法： 

ContentValues主要是存放表中的数据段，以及其对应的值，与Hashtable一样采用名值对的形式存储，但是它存储的名值对中，名是一个String类型，值是基本数据类型。其使用示例如下： 

Android采用游标对从数据库中查询出来的结果进行随机的读写访问，在查询数据库后，将结果返回给游标(即android.database.Cursor)，这是查询结果的记录集，示意图如下： 

Cursor类常见的方法如下： 

要创建一个Cursor(游标)，必须执行一个查询，通过SQL使用rawQuery()方法或是更精心的query()方法，而不能使用execSQL(String sql)方法。以下为Cursor的使用示例： 

在实际使用的时候，通常都要封装一个继承SQLiteOpenHelper类的数据库操作类。SQLiteOpenHelper类是一个抽象的辅助类，主要用来生成一个数据库并对数据库的版本进行管理，在SQLiteOpenHelper类的构造方法中分别传入Context、数据库名称，CursorFactory(一般传入null，否则为默认数据库)，数据库版本号(不能为负数)。在SQLiteOpenHelper中首先执行的是onCreate方法(当数据库第一次创建时)，一般在这个方法里生成数据表。要注意的是，在构造函数时并没有真正创建数据库，而是在调用getWriteableDatabase()或者getReadableDatabase()方法的时候系统才会真正创建数据库，如果当时系统中不存在这个数据库，系统会自动生成一个数据库，然后返回SQLiteDatabase对象。在继承这个类的时候，要实现里面的3个方法，其中前两个方法是必须重写的。见如下表格： 

<!--EndFragment-->

以下示例代码详细给出一封装好的数据库操作类： 
public class MyDbHelper {
    public static final String KEY_TITLE = "title";    //数据表字段
    public static final String KEY_BODY = "body";    //数据表字段
    public static final String KEY_ROWID = "_id";    //数据表字段
 
    private DatabaseHelper mDbHelper;    //SQLiteOpenHelper实例对象
    private SQLiteDatabase mDb;    //数据库实例对象
    
    //数据表创建语句
    private static final String DATABASE_CREATE  = "create table notes (_id integer primary key autoincrement, "
                    + "title text not null, body text not null);";
 
    private static final String DATABASE_NAME = "data";    //数据库名
    private static final String DATABASE_TABLE = "notes";    //数据库表名
    private static final int DATABASE_VERSION = 2;    //数据库版本号
 
    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 notes");
/**
     * Constructor - takes the context to allow the database to be opened/created
     * 
     * @param ctx the Context within which to work
     */
    public MyDbHelper(Context ctx) {
        this.mCtx = ctx;
    }
 
    /**
     * Open the notes database. If it cannot be opened, try to create a new instance of the database. 
* If it cannot be created, throw an exception to signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public MyDbHelper open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    
    public void close() {
        mDbHelper.close();
    }
 
 
    /**
     * Create a new note using the title and body provided. If the note is successfully created 
* return the new rowId for that note, otherwise return a -1 to indicate failure.
     * 
     * @param title the title of the note
     * @param body the body of the note
     * @return rowId or -1 if failed
     */
    public long createNote(String title, String body) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, title);
        initialValues.put(KEY_BODY, body);
        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }
 
    /**
     * Delete the note with the given rowId
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteNote(long rowId) {
        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }
 
    /**
     * Return a Cursor over the list of all notes in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchAllNotes() {
        return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,KEY_BODY}, null, null, null, null, null);
    }
 
    /**
     * Return a Cursor positioned at the note that matches the given rowId
     * 
     * @param rowId id of note to retrieve
     * @return Cursor positioned to matching note, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchNote(long rowId) throws SQLException {
 
        Cursor mCursor = mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,KEY_TITLE, 
KEY_BODY}, KEY_ROWID + "=" + rowId, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
 
    /**
     * Update the note using the details provided. The note to be updated is specified using the rowId, 
*and it is altered to use the title and body values passed in
     * 
     * @param rowId id of note to update
     * @param title value to set note title to
     * @param body value to set note body to
     * @return true if the note was successfully updated, false otherwise
     */
    public boolean updateNote(long rowId, String title, String body) {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, title);
        args.put(KEY_BODY, body);
        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
}