Android SQLiteOpenHelper(手机数据库)

通过使用SQLiteOpenHelper来操作android内置的一个很小的关系型数据库（SQLite）。

1）效果图

2）关键代码如下

1. 创建class MyDataBase.java代码如下

package com.example.androidsqlitedemo;

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

public class MyDataBase extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "demo_db";//数据库的名字
	private static final int DATABASEVERSION = 1;//版本号
	private static final String TABLE_NAME = "demo_table";//表名
	public static final String FIELD_id = "_id";//必须为_id
	public static final String FIELD_TEXT = "demo_text";
	private SQLiteDatabase db;//数据库
	private static final String TAG = "MyDataBase";

	public MyDataBase(Context context) {
		super(context, DATABASE_NAME, null, DATABASEVERSION);
		// TODO Auto-generated constructor stub
		db = this.getWritableDatabase();// 打开或新建数据库(第一次时创建)获得SQLiteDatabase对象，为了读取和写入数据

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		/* 建立表table */
		Log.i(TAG, " onCreate() ");
		String sql = "CREATE TABLE " + TABLE_NAME + " (" + FIELD_id
				+ " INTEGER PRIMARY KEY AUTOINCREMENT," + " " + FIELD_TEXT
				+ " TEXT)";
		db.execSQL(sql);
	}

	//更新数据库
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		Log.i(TAG, " onUpgrade() ");
		//删除表的SQL
		String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
		db.execSQL(sql);
		onCreate(db);
	}

	//关闭数据库
	@Override
	public synchronized void close() {
		// TODO Auto-generated method stub
		Log.i(TAG, " close() ");
		db.close();
		super.close();
	}

	@Override
	public void onOpen(SQLiteDatabase db1) {
		// TODO Auto-generated method stub
		Log.i(TAG, " onOpen() ");
		super.onOpen(db1);
	}

	// 增加数据
	public long addMethod(String str) {
		/* 将新增的值放入ContentValues */
		ContentValues cv = new ContentValues();
		cv.put(FIELD_TEXT, str);//键值对
		long row = db.insert(TABLE_NAME, null, cv);
		Log.i(TAG, "addMethod row=" + row);
		return row;
	}

	// 删除
	public void deleteMethod(int id) {
		String[] whereArgs = { Integer.toString(id) };
		int rowsaffected = db.delete(TABLE_NAME, FIELD_id + "=?", whereArgs);
		Log.i(TAG, "deleteMethod() rowsaffected=" + rowsaffected);
	}

	// 修改
	public void modMethod(int id, String str) {
		ContentValues values = new ContentValues();
		values.put(FIELD_TEXT, str);
		String[] whereArgs = { Integer.toString(id) };
		int rowsaffected = db.update(TABLE_NAME, values, FIELD_id + " = ?",
				whereArgs);
		Log.i(TAG, "modMethod() rowsaffected=" + rowsaffected);
	}

	// 查询所有的数据 ，返回Cursor对象
	public Cursor query() {
		//asc是升序desc为降序（默认为asc）
		return db.query(TABLE_NAME, null, null, null, null, null,  FIELD_id + " ASC");
	}

}

2.在res\layout下创建list.xml代码如下

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent" >

    <TextView
        android:id="@+id/textView1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="TextView" 
        android:textSize="30sp"/>

    <TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="TextView" 
        android:textSize="30sp"/>

</LinearLayout>

3.修改activity_main.xml代码如下

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >

    <EditText
        android:id="@+id/editText1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="text" >

        <requestFocus />
    </EditText>

    <ListView
        android:id="@+id/listView1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >
    </ListView>

</LinearLayout>

4.修改MainActivity.java代码如下

package com.example.androidsqlitedemo;

import android.os.Bundle;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteCursor;
import android.support.v4.widget.SimpleCursorAdapter;
import android.view.KeyEvent;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.EditText;
import android.widget.ListView;


public class MainActivity extends Activity {

	private final static int MENU_ADD=0;
	private final static int MENU_EDIT=1;
	private final static int MENU_DEL=2;
	private EditText editText1=null;
	private ListView listView1=null;
	private MyDataBase mDataBase=null;
	private SimpleCursorAdapter adapter=null; 
	private Cursor mCursor=null;
	private int _id;
	@SuppressWarnings("deprecation")
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		editText1=(EditText)findViewById(R.id.editText1);
		listView1=(ListView)findViewById(R.id.listView1);
		listView1.setOnItemClickListener(clickListener);
		listView1.setOnItemSelectedListener(selectedListener);
		/*创建SQLiteOpenHelper对象引用*/
		mDataBase=new MyDataBase(this);
		/*获得DataBase里的数据*/
		mCursor=mDataBase.query();
		/*new SimpleCursorAdapter并将mCursor传入,
		  显示数据字段为_id,todo_text*/
		adapter=new SimpleCursorAdapter(
				this, 
				R.layout.list,
				mCursor, 
				new String[]{MyDataBase.FIELD_id,MyDataBase.FIELD_TEXT},
				new int[]{R.id.textView1,R.id.textView2});
		listView1.setAdapter(adapter);
	}

	@Override
	/*添加Menu*/
	public boolean onCreateOptionsMenu(Menu menu) {
		menu.add(0, MENU_ADD, 0, "添加");
		menu.add(0, MENU_EDIT, 1, "修改");
		menu.add(0, MENU_DEL, 2, "删除");
		return super.onCreateOptionsMenu(menu);
	}
	
	/*ListView的单击事件处理*/
	OnItemClickListener clickListener=new OnItemClickListener() {
		public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3){
			mCursor.moveToPosition(arg2);
			_id=mCursor.getInt(mCursor.getColumnIndex(MyDataBase.FIELD_id));
			editText1.setText(mCursor.getString(mCursor.getColumnIndex(MyDataBase.FIELD_TEXT)));
		}
	};
	/*监听处理鼠标滚轮的事件处理*/
	OnItemSelectedListener selectedListener=new OnItemSelectedListener() {
		public void onItemSelected(AdapterView<?> arg0, View arg1, int arg2, long arg3){
			_id=mCursor.getInt(mCursor.getColumnIndex(MyDataBase.FIELD_id));
			SQLiteCursor c=(SQLiteCursor)arg0.getSelectedItem();
			editText1.setText(c.getString(c.getColumnIndex(MyDataBase.FIELD_TEXT)));
		}

		@Override
		public void onNothingSelected(AdapterView<?> arg0) {
			// TODO Auto-generated method stub
			
		}
	};
	@Override
	/*给Menu添加事件*/
	public boolean onOptionsItemSelected(MenuItem item){
		super.onOptionsItemSelected(item);
		switch (item.getItemId()) {
		case MENU_ADD://添加
			addToDo();
			break;
		case MENU_DEL://删除
			deleteToDo();
			break;
		case MENU_EDIT://修改
			editToDo();
			break;
		default:
			break;
		}
		return true;
	}
	/*添加*/
	private void addToDo(){
		String str=editText1.getText().toString();
		if(str.equalsIgnoreCase("")){
			return;
		}
		mDataBase.addMethod(str);
		myUpDataShow();
	}
	/*删除*/
	private void deleteToDo(){
		if(_id==0){
			return;
		}
		mDataBase.deleteMethod(_id);
		myUpDataShow();
	}
	/*修改*/
	private void editToDo(){
		String str=editText1.getText().toString();
		if(str.equalsIgnoreCase("")||_id==0){
			return;
		}
		mDataBase.modMethod(_id, str);
		myUpDataShow();
	}
	/*更新ListView*/
	private void myUpDataShow(){
		/*重新查询*/
		mCursor.requery();
		/*告知适配器已经变化*/
		adapter.notifyDataSetChanged();
		editText1.setText("");
		_id=0;
	}
	
	@Override
	/*退出程序*/
	public boolean onKeyDown(int keyCode, KeyEvent event){
		if(keyCode==KeyEvent.KEYCODE_BACK){
			mDataBase.close();
			this.finish();
		}
		return super.onKeyDown(keyCode, event);
	}
}