Android数据库创建更新帮助类

DBAdapter.java

package org.xkit.android.demo;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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

public class DBAdapter {
	private Context context;
	private SQLiteDatabase sqlite;

	private MyDatabaseHelper helper;

	public DBAdapter(Context c) {
		this.context = c;
	}

	public void open() {
		helper = new MyDatabaseHelper(context, 10);
		sqlite = helper.getWritableDatabase();
	}

	public void execSQL(String sql) {
		Log.i("sql execute", sql);
		sqlite.execSQL(sql);
	}

	public Cursor getResultSet(String tableName, String condition,
			Object[] fields) {
		StringBuffer sb = new StringBuffer();
		String allFields = new String();
		if (fields == null) {
			allFields = "*";
		} else {
			for (int i = 0; i < fields.length; i++) {
				allFields += fields[i].toString() + ",";
			}
			allFields = allFields.substring(0, allFields.length() - 1);
		}
		sb.append("select ").append(allFields).append(" from ").append(
				tableName).append(" where ").append(condition);
		Log.i("sqlquery", sb.toString());
		return sqlite.rawQuery(sb.toString(), null);
	}

	public Cursor getResultSet(String sql) {
		Log.i("sql query", sql);
		return sqlite.rawQuery(sql, null);
	}

	public List<Map<String, String>> getResultSet(String sql, int pageSize) {
		Log.i("sql query", sql);
		Cursor cursor = sqlite.rawQuery(sql, null);
		int count = cursor.getCount();
		int columnCount = cursor.getColumnCount();
		Log.d("Column Count", "" + columnCount);
		List<Map<String, String>> list = new ArrayList<Map<String, String>>(
				count);
		Map<String, String> entity = new HashMap<String, String>(columnCount);
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			// 找出一共有多少列
			for (int i = 0; i < columnCount; i++) {
				Log.d("Column Found", cursor.getColumnName(i) + " : "
						+ cursor.getString(i));
				entity.put(cursor.getColumnName(i), cursor.getString(i));
			}
			list.add(entity);
		}
		cursor.close();
		entity = null;
		return list;
	}

	public void close() {
		// 关闭我们打开的数据库
		throw new RuntimeException("Only for Stub!");
	}
}

MyDatabaseHelper.java

package org.xkit.android.demo;

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

public class MyDatabaseHelper extends SQLiteOpenHelper {

	private static final String TAG = "MyDatabaseHelper";

	public static final String NAME = "lucane.db";

	public MyDatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	public MyDatabaseHelper(Context context, int version) {
		super(context, NAME, null, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		bootstrapDB(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i(TAG, "Upgrading DB from version " + oldVersion + " to "
				+ newVersion);
		if (oldVersion < 8) {
			// 如果版本太小，就直接删除，然后创建
			// 所以bootstrapDB应该是最新的SQL初始化语句
			dropTables(db);
			onCreate(db);
			return;
		}

		if (oldVersion == 8) {
			upgradeToVersion9(db);
			oldVersion += 1;
		}

		if (oldVersion == 9) {
			upgradeToVersion10(db);
			oldVersion += 1;
		}

		// 这是一种逐级更新的方式
		Log.v("do upgrade", "我更新了。。。");
	}

	private void bootstrapDB(SQLiteDatabase db) {
		Log.i(TAG, "Bootstrapping database");
		db
				.execSQL("CREATE TABLE person (personid integer primary key autoincrement,name varchar(20),age integer )");
	}

	private void dropTables(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS person;");
	}

	// PATCH方法开始

	static void upgradeToVersion10(SQLiteDatabase db) {
		db.execSQL("CREATE INDEX idx_person_name_gender ON person (" + "name"
				+ ", " + "gender" + ");");
	}

	static void upgradeToVersion9(SQLiteDatabase db) {
		db.execSQL("ALTER TABLE "
				+ "person ADD COLUMN gender INTEGER NOT NULL DEFAULT 1;");
	}

	// PATCH方法结束
}