Android数据存储之——SQLite数据库操作

对于一些重复的或者结构化的数据，保存到数据库是一个理想的选择，下面对SQLite数据库的操作进行讲解。

首先是定义一个数据结构体：

package com.easyliu.sqlite.db;

import android.provider.BaseColumns;

/**
 * 一般继承自BaseColumns,从中继承public static final String _ID = "_id" 字段
 * 
 * @author LiuYi
 *
 */
public class FeedReaderContract implements BaseColumns {
	private String mName;
	private String mMobilePhone;
	private String mAddress;
	public static final String COLUMN_NAME_NAME = "name";
	public static final String COLUMN_NAME_MOBILE = "mobilePhone";
	public static final String COLUMN_NAME_ADDRESS = "address";

	/**
	 * 
	 */
	public FeedReaderContract() {
	}

	/**
	 * @param name
	 * @param mobilePhone
	 * @param address
	 */
	public FeedReaderContract(String name, String mobilePhone, String address) {
		mName = name;
		mMobilePhone = mobilePhone;
		mAddress = address;
	}

	/**
	 * @return the name
	 */
	public String getName() {
		return mName;
	}

	/**
	 * @param name
	 *            the name to set
	 */
	public void setName(String name) {
		mName = name;
	}

	/**
	 * @return the mobilePhone
	 */
	public String getMobilePhone() {
		return mMobilePhone;
	}

	/**
	 * @param mobilePhone
	 *            the mobilePhone to set
	 */
	public void setMobilePhone(String mobilePhone) {
		mMobilePhone = mobilePhone;
	}

	/**
	 * @return the address
	 */
	public String getAddress() {
		return mAddress;
	}

	/**
	 * @param address
	 *            the address to set
	 */
	public void setAddress(String address) {
		mAddress = address;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
		// TODO Auto-generated method stub
		return super.hashCode();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		// TODO Auto-generated method stub
		return super.toString();
	}

}

然后是定义一个继承自SQLiteOpenHelper的辅助类， 用来管理数据库的创建和版本更新，它提供两个方面的功能。 

1、通过调用getReadableDatabase()、getWritableDatabase()可以获得SQLiteDatabase对象，通过该对象可以对数据库进行操作。 
2、提供了onCreate()、onUpgrade()两个回调函数，允许数据库的操作和升级。

如下所示：

	/**
	 * SQLiteOpenHelper
	 * 
	 * @author LiuYi
	 *
	 */
	class MyDBHelper extends SQLiteOpenHelper {
		private static final String TEXT_TYPE = " TEXT";
		private static final String COMMA_SEP = ",";
		private static final String SQL_CREATE_ENTRIES = "CREATE TABLE "
				+ DEFAULT_DB_TABLE_NAME + " (" + FeedReaderContract._ID
				+ " INTEGER primary key autoincrement,"
				+ FeedReaderContract.COLUMN_NAME_NAME + TEXT_TYPE + COMMA_SEP
				+ FeedReaderContract.COLUMN_NAME_MOBILE + TEXT_TYPE + COMMA_SEP
				+ FeedReaderContract.COLUMN_NAME_ADDRESS + TEXT_TYPE + " )";
		private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS "
				+ DEFAULT_DB_TABLE_NAME;

		public MyDBHelper(Context context, String name, int version) {
			super(context, name, null, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(SQL_CREATE_ENTRIES);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// This database is only a cache for online data, so its upgrade
			// policy is
			// to simply to discard the data and start over
			db.execSQL(SQL_DELETE_ENTRIES);
			myDBHelper.onCreate(db);
		}
	}

注意：onCreate函数是在数据库（注意是数据库）第一次创建的时候执行，实际上是第一次得到SQLiteDatabase对象的时候才会调用这个方法 ，也就是说当你想创建多个表时，不能放在onCreate方法里面 ，因为这个方法只会执行一次！

下面是自定义的一个DBHelper类，用于对数据库进行操作：

package com.easyliu.sqlite.db;

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

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 DBHelper {
	public static final String DEFAULT_DB_TABLE_NAME = "user";// 默认的表名
	public static final String DATABASE_NAME = "Contacts.db";// 数据库名称
	public static final int DATABASE_VERSION = 1; // 数据库版本
	public static SQLiteDatabase dbInstance = null;
	private MyDBHelper myDBHelper;
	private Context context;
	private String mTableName = null;// 新建的表名,当需要新建多个表的时候使用

	/**
	 * 构造函数，使用默认的表对数据库进行操作
	 * 
	 * @param context
	 */
	public DBHelper(Context context) {
		this.context = context;
	}

	/**
	 * 构造函数，传入新建的表
	 * 
	 * @param context
	 * @param tableName
	 */
	public DBHelper(Context context, String tableName) {
		this.context = context;
		this.mTableName = tableName;
	}

	/**
	 * 打开数据库，在使用这个方法之前，需要调用构造函数
	 */
	public void openDatabase() {
		if (dbInstance == null) {
			myDBHelper = new MyDBHelper(context, DATABASE_NAME,
					DATABASE_VERSION);
			dbInstance = myDBHelper.getWritableDatabase();
		}
	}

	/**
	 * 在数据库中新建表,在使用这个方法之前,需要在构造函数中传入要新建的表的名称：
	 * DBHelper helper = new DBHelper(MainActivity.this,tableName);
	 * 
	 */
	public void createNewTable() {
		/**
		 * 如果为空，说明dbInstance还没有初始化 就要调用myDBHelper.getWritableDatabase();进行初始化
		 */
		openDatabase();
		/**
		 * 需要自己手动建立表
		 */
		String TEXT_TYPE = " TEXT";
		String COMMA_SEP = ",";
		String SQL_CREATE_ENTRIES = "CREATE TABLE " + mTableName + " ("
				+ FeedReaderContract._ID
				+ " INTEGER primary key autoincrement,"
				+ FeedReaderContract.COLUMN_NAME_NAME + TEXT_TYPE + COMMA_SEP
				+ FeedReaderContract.COLUMN_NAME_MOBILE + TEXT_TYPE + COMMA_SEP
				+ FeedReaderContract.COLUMN_NAME_ADDRESS + TEXT_TYPE + " )";
		System.out.println(SQL_CREATE_ENTRIES);
		dbInstance.execSQL(SQL_CREATE_ENTRIES);
	}

	/**
	 * 插入数据
	 * 
	 * @param user
	 * @return
	 */
	public long insert(FeedReaderContract user) {
		ContentValues values = new ContentValues();
		values.put(FeedReaderContract.COLUMN_NAME_NAME, user.getName());
		values.put(FeedReaderContract.COLUMN_NAME_MOBILE, user.getMobilePhone());
		values.put(FeedReaderContract.COLUMN_NAME_ADDRESS, user.getAddress());
		long row;
		if (mTableName == null) {
			row = dbInstance.insert(DEFAULT_DB_TABLE_NAME, null, values);
		} else {
			row = dbInstance.insert(mTableName, null, values);
		}
		return row;
	}

	/**
	 * 往数据库中批量插入数据,使用事务，提高效率
	 */
	public long insert(List<FeedReaderContract> list) {
		long databack = 0;
		dbInstance.beginTransaction();
		try {
			for (int i = 0; i < list.size(); i++) {
				ContentValues values = new ContentValues();
				FeedReaderContract user = list.get(i);
				values.put(FeedReaderContract.COLUMN_NAME_NAME, user.getName());
				values.put(FeedReaderContract.COLUMN_NAME_MOBILE,
						user.getMobilePhone());
				values.put(FeedReaderContract.COLUMN_NAME_ADDRESS,
						user.getAddress());
				databack = dbInstance.insert(mTableName, null, values);
			}
			dbInstance.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbInstance.endTransaction();
		}
		return databack;
	}

	/**
	 * 删除字段FeedReaderContract.COLUMN_NAME_NAME为name的数据
	 * 
	 * @param name
	 */
	public void delete(String name) {
		String selection = FeedReaderContract.COLUMN_NAME_NAME + " LIKE ?";
		String[] selectionArgs = { String.valueOf(name) };
		if (mTableName == null) {
			dbInstance.delete(DEFAULT_DB_TABLE_NAME, selection, selectionArgs);
		} else {
			dbInstance.delete(mTableName, selection, selectionArgs);
		}
	}

	/**
	 * 修改用户
	 * 
	 * @param user
	 */
	public void modify(FeedReaderContract user) {
		ContentValues values = new ContentValues();
		values.put(FeedReaderContract.COLUMN_NAME_NAME, user.getName());
		values.put(FeedReaderContract.COLUMN_NAME_MOBILE, user.getMobilePhone());
		values.put(FeedReaderContract.COLUMN_NAME_ADDRESS, user.getAddress());
		String selection = FeedReaderContract.COLUMN_NAME_NAME + " LIKE ?";
		String[] selectionArgs = { String.valueOf(user.getName()) };
		if (mTableName == null) {
			dbInstance.update(DEFAULT_DB_TABLE_NAME, values, selection,
					selectionArgs);
		} else {
			dbInstance.update(mTableName, values, selection, selectionArgs);
		}
	}

	/**
	 * 得到所有的用户
	 * 
	 * @return
	 */
	public ArrayList<HashMap<String, Object>> getalluser() {
		String[] projection = { FeedReaderContract._ID,
				FeedReaderContract.COLUMN_NAME_NAME,
				FeedReaderContract.COLUMN_NAME_MOBILE,
				FeedReaderContract.COLUMN_NAME_ADDRESS };
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		Cursor cursor;
		if (mTableName == null) {
			cursor = dbInstance.query(DEFAULT_DB_TABLE_NAME, projection, null,
					null, null, null, null);
		} else {
			cursor = dbInstance.query(mTableName, projection, null, null, null,
					null, null);
		}
		while (cursor.moveToNext()) {
			HashMap<String, Object> map = new HashMap<String, Object>();
			map.put(FeedReaderContract._ID, cursor.getInt(cursor
					.getColumnIndex(FeedReaderContract._ID)));
			map.put(FeedReaderContract.COLUMN_NAME_NAME,
					cursor.getString(cursor
							.getColumnIndex(FeedReaderContract.COLUMN_NAME_NAME)));
			map.put(FeedReaderContract.COLUMN_NAME_MOBILE,
					cursor.getString(cursor
							.getColumnIndex(FeedReaderContract.COLUMN_NAME_MOBILE)));
			map.put(FeedReaderContract.COLUMN_NAME_ADDRESS,
					cursor.getString(cursor
							.getColumnIndex(FeedReaderContract.COLUMN_NAME_ADDRESS)));
			list.add(map);
		}
		return list;
	}

	/**
	 * 删除表的全部数据
	 */
	public void deleteAll() {
		dbInstance.delete(mTableName, null, null);
	}

	/**
	 * 表中总的个数
	 * 
	 * @return
	 */
	public int getTotalCount() {
		Cursor cursor = dbInstance.query(mTableName,
				new String[] { "count(*)" }, null, null, null, null, null);
		cursor.moveToNext();
		return cursor.getInt(0);
	}

	/**
	 * 遍历数据库中的表名
	 * 
	 * @return
	 */
	public ArrayList<HashMap<String, Object>> Traversal() {
		int count = 0;
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		Cursor cursor = dbInstance
				.rawQuery(
						"select name from sqlite_master where type='table' order by name",
						null);
		while (cursor.moveToNext()) {
			HashMap<String, Object> item = new HashMap<String, Object>();
			// 遍历出表名
			String name = cursor.getString(0);
			item.put("count", count++);
			item.put("name", name);
			list.add(item);
			Log.i("System.out", name);
		}
		return list;
	}

	/**
	 * 删除指定表
	 * 
	 * @param tableName
	 */
	public void deletetable(String tableName) {
		try {
			dbInstance.execSQL("DROP TABLE IF EXISTS " + tableName);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 删除数据库中所有的表
	 * 
	 * @param list
	 */
	public void deleteAllTable(List<String> list) {
		Log.i("System.out", "deleteAllTable");
		try {
			for (int i = 0; i < list.size(); i++) {
				String TableName = list.get(i);
				dbInstance.execSQL("DROP TABLE IF EXISTS " + TableName);
				Log.i("System.out", TableName);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭数据库
	 */
	public void closedatabase() {
		try {
			dbInstance.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 重命名表名称
	 * 
	 * @param oldTableName
	 * @param newTableName
	 */
	public void renameTable(String oldTableName, String newTableName) {
		try {
			dbInstance.execSQL("alter table" + oldTableName + "rename to"
					+ newTableName + ";");
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	/**
	 * SQLiteOpenHelper
	 * 
	 * @author LiuYi
	 *
	 */
	class MyDBHelper extends SQLiteOpenHelper {
		private static final String TEXT_TYPE = " TEXT";
		private static final String COMMA_SEP = ",";
		private static final String SQL_CREATE_ENTRIES = "CREATE TABLE "
				+ DEFAULT_DB_TABLE_NAME + " (" + FeedReaderContract._ID
				+ " INTEGER primary key autoincrement,"
				+ FeedReaderContract.COLUMN_NAME_NAME + TEXT_TYPE + COMMA_SEP
				+ FeedReaderContract.COLUMN_NAME_MOBILE + TEXT_TYPE + COMMA_SEP
				+ FeedReaderContract.COLUMN_NAME_ADDRESS + TEXT_TYPE + " )";
		private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS "
				+ DEFAULT_DB_TABLE_NAME;

		public MyDBHelper(Context context, String name, int version) {
			super(context, name, null, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(SQL_CREATE_ENTRIES);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// This database is only a cache for online data, so its upgrade
			// policy is
			// to simply to discard the data and start over
			db.execSQL(SQL_DELETE_ENTRIES);
			myDBHelper.onCreate(db);
		}
	}

}

使用方法：

情况一：操作默认的表或对整个数据库进行操作。

DBHelper dbHelper = new DBHelper(MainActivity.this);
dbHelper.openDatabase();
list = dbHelper.getalluser();

情况二：在数据库中新建表。

DBHelper dbHelper=new DBHelper(getApplicationContext(), "test");
dbHelper.createNewTable();
list=dbHelper.getalluser();

情况三：操作特定的表。

DBHelper dbHelper=new DBHelper(getApplicationContext(), "test");
dbHelper.openDatabase();
list=dbHelper.getalluser();