Android演示操作数据库之二

Android演示操作数据库SQLite之二，演示如何使用SQLiteOpenHelper。

Android开发中还可以使用SQLiteOpenHelper的子类操作数据库，具体步骤如下：

1. 继承SQLiteOpenHelper得到SuperDBHelper子类，实现构造函数、onCreate、onUpgrade等方法；
2. 实现数据库查询、插入、删除等方法；
3. Activity中构造SuperDBHelper对象，然后使用；

具体代码如下(演示例子参见点击打开链接)：

SuperDBHelper.java

package com.freesoft.utility;

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.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SuperDBHelper extends SQLiteOpenHelper {
	private static String TAG="SQLiteOpenHelper";
	private static int VERSION = 1;
	private static String TABLE_NAME = "myTestTable";
	
	public SuperDBHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	public SuperDBHelper(Context context, String name, int version) {
		this(context, name, null, 1);
	}
	
	public SuperDBHelper(Context context, String name) {
		this(context, name, VERSION);
	}
	
	public SuperDBHelper(Context context) {
		this(context, TABLE_NAME);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "";
		try {
			sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ("
					+ "ID INTEGER PRIMARY KEY autoincrement,  "
					+ "NAME TEXT,  " + "NICKNAME TEXT, " + "AGE INTEGER, "
					+ "EMAIL TEXT)";
			db.execSQL(sql);
		} catch (SQLException e) {
			Log.v(TAG, "Create Table failed.");
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int iOldVer, int iNewVer) {
		String sql = "";
		try {
			sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
			db.execSQL(sql);
			
			onCreate(db);
		} catch (SQLException e) {
			Log.v(TAG, "Upgrade Table failed.");
		}
	}
	
	public void addRecord(String username, String nickname, int age, String email){
		ContentValues values = new ContentValues();
		values.clear();
		values.put("NAME", username);
		values.put("NICKNAME", nickname);
		values.put("AGE", age);
		values.put("EMAIL", email);

		this.getWritableDatabase().insert(TABLE_NAME, null, values);
	}

	public Cursor queryAll(){
		return this.getReadableDatabase().query(TABLE_NAME, null, "ID>?", new String[]{"0"},
				null, null, null);
	}
	
	public void delRecord(int id){
		this.getWritableDatabase().delete(TABLE_NAME, "ID=?", new String[]{String.format("%d", id)});
	}
}

使用SuperDBHelper的Activity:

package com.freesoft.anttest;

import com.freesoft.utility.IntWrapper;
import com.freesoft.utility.SuperDBHelper;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class SuperDatabaseActivity extends Activity {
	private static String TABLE_NAME = "testtable";

	private EditText et_id = null;
	private EditText et_username = null;
	private EditText et_nickname = null;
	private EditText et_age = null;
	private EditText et_email = null;

	private Button btn_next = null;
	private Button btn_prev = null;
	private Button btn_del = null;
	private Button btn_insert = null;

	private SuperDBHelper dbhelper = null;
	private Cursor cur = null;

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.database);

		findControls();
		initControls();
	}

	private void initControls() {
		dbhelper = new SuperDBHelper(SuperDatabaseActivity.this, TABLE_NAME);
		cur = dbhelper.queryAll();		
		if (cur != null) {
			cur.moveToFirst();
			refreshUI();
		}

	}

	private void findControls() {
		et_id = (EditText) findViewById(R.id.et_db_id);
		et_username = (EditText) findViewById(R.id.et_db_username);
		et_nickname = (EditText) findViewById(R.id.et_db_nickname);
		et_age = (EditText) findViewById(R.id.ed_db_age);
		et_email = (EditText) findViewById(R.id.ed_db_email);

		btn_next = (Button) findViewById(R.id.btn_db_next);
		btn_prev = (Button) findViewById(R.id.btn_db_prev);
		btn_del = (Button) findViewById(R.id.btn_db_del);
		btn_insert = (Button) findViewById(R.id.btn_db_insert);

		btn_next.setOnClickListener(new btnnext_onclick());
		btn_prev.setOnClickListener(new btnprev_onclick());
		btn_del.setOnClickListener(new btndel_onclick());
		btn_insert.setOnClickListener(new btninsert_onclick());
	}

	private void refreshUI() {
		// 确保cur内存有有效数据
		if ((cur != null) && (cur.getCount() > 0)) {
			btn_del.setEnabled(true);

			et_id.setText(cur.getInt(0) + "");
			et_username.setText(cur.getString(1));
			et_nickname.setText(cur.getString(2));
			et_age.setText(cur.getInt(3) + "");
			et_email.setText(cur.getString(4));

			if (cur.isLast())
				btn_next.setEnabled(false);
			else
				btn_next.setEnabled(true);

			if (cur.isFirst())
				btn_prev.setEnabled(false);
			else
				btn_prev.setEnabled(true);
		} else {
			btn_next.setEnabled(false);
			btn_prev.setEnabled(false);
			btn_del.setEnabled(false);

			et_id.setText("");
			et_username.setText("");
			et_nickname.setText("");
			et_age.setText("");
			et_email.setText("");
		}
	}

	class btnnext_onclick implements OnClickListener {
		@Override
		public void onClick(View arg0) {
			if (cur != null) {
				cur.moveToNext();
				refreshUI();
			}
		}
	}

	class btnprev_onclick implements OnClickListener {
		@Override
		public void onClick(View arg0) {
			if (cur != null) {
				cur.moveToPrevious();
				refreshUI();
			}
		}
	}

	class btndel_onclick implements OnClickListener {
		@Override
		public void onClick(View arg0) {
			dbhelper.delRecord(IntWrapper.wrap(et_id.getText().toString(), 1));
			cur = dbhelper.queryAll();
			if (cur != null) {
				cur.moveToFirst();
				refreshUI();
			}
		}
	}

	class btninsert_onclick implements OnClickListener {
		@Override
		public void onClick(View arg0) {
			dbhelper.addRecord(et_username.getText().toString(), et_nickname
					.getText().toString(), IntWrapper.wrap(et_age.getText()
					.toString(), 1), et_email.getText().toString());
			cur = dbhelper.queryAll();
			cur.moveToLast();
			refreshUI();
		}
	}
}