Android 纯代码化编码4_访问本地数据库帮助类

安卓自己就提供一个本地数据库供用户使用，它的具体放置的位置我记不住了，大家百度一下就知道，我们实际开发的过程中肯定是要进行一定程度的封装的，方便操作，下面就是我自己封装的一个帮助类，希望对大家有所帮助，方法名仿照的hibernate

 

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

/**
 * 安卓本地数据库访问对象
 * 
 * @author pandong
 * @date 2012-7-12 上午10:15:20
 */
public class SqliteDataBase extends SQLiteOpenHelper {

	private static final int VERSION = 1;

	// 在SQLiteOepnHelper的子类当中，必须有该构造函数
	public SqliteDataBase(Context context, String name, CursorFactory factory, int version) {
		// 必须通过super调用父类当中的构造函数
		super(context, name, factory, version);
	}

	public SqliteDataBase(Context context, String name) {
		this(context, name, VERSION);
	}

	public SqliteDataBase(Context context, String name, int version) {
		this(context, name, null, version);
	}

	/**
	 * 执行语句并获取首行首列
	 */
	public int queryForInt(String sql) {
		SQLiteDatabase db = getReadableDatabase();
		String result = null;
		try {
			SQLiteStatement stmt = db.compileStatement(sql);
			result = stmt.simpleQueryForLong() + "";
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(db, null, null);
		}

		return Integer.parseInt(result);
	}

	/**
	 * 执行语句返回一组元素
	 */
	public List<Map> queryForList(String sql) {
		List<Map> resultList = new ArrayList<Map>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = null;
		try {
			cursor = db.rawQuery(sql, null);
			while (cursor.moveToNext()) {
				int columnCount = cursor.getColumnCount();
				Map columnMap = new HashMap();
				for (int i = 0; i < columnCount; i++) {
					String columnName = cursor.getColumnName(i);
					String columnValue = cursor.getString(i);
					columnMap.put(columnName, columnValue);
				}
				resultList.add(columnMap);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(db, null, cursor);
		}

		return resultList;
	}

	/**
	 * 执行语句并返回一个元素
	 */
	public Map queryForMap(String sql) {
		Map columnMap = new HashMap();
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = null;
		try {
			cursor = db.rawQuery(sql, null);
			if (cursor.moveToNext()) {
				int columnCount = cursor.getColumnCount();
				for (int i = 0; i < columnCount; i++) {
					String columnName = cursor.getColumnName(i);
					String columnValue = cursor.getString(i);
					columnMap.put(columnName, columnValue);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(db, null, cursor);
		}
		return columnMap;
	}

	/**
	 * 执行一条insert语句
	 */
	public int insert(String sql) {
		return executeUpdate(sql);
	}

	/**
	 * 事务方式执行一组insert语句
	 */
	public int insert(String[] sql) {
		return executeUpdate(sql);
	}

	/**
	 * 执行一条预格式语句，预格式语句：insert t_test values(?,?,?)
	 */
	public int insert(String psql, String[] data) {
		return executeUpdate(psql, data, "update");
	}

	/**
	 * 执行一条更新语句update/delete
	 */
	public int update(String sql) {
		return executeUpdate(sql);
	}

	/**
	 * 事务执行一组更新语句update/delete
	 */
	public int update(String[] sql) {
		return executeUpdate(sql);
	}

	/**
	 * 执行一条预格式语句，预格式语句：update t_test set t1 = ?
	 */
	public int update(String psql, String[] data) {
		return executeUpdate(psql, data, "update");
	}

	/**
	 * 统一的执行语句的方法
	 * 
	 * @param sql
	 * @return
	 */
	private int executeUpdate(String sql) {
		SQLiteDatabase db = getWritableDatabase();
		try {
			db.execSQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
			close(db, null, null);
		}
		return 1;
	}

	/**
	 * 统一的执行一组语句的方法
	 * 
	 * @param sql
	 * @return
	 */
	private int executeUpdate(String[] sql) {
		SQLiteDatabase db = getWritableDatabase();
		try {
			db.beginTransaction();
			for (String curSql : sql) {
				db.execSQL(curSql);
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
			db.endTransaction();
			close(db, null, null);
		}
		return 1;
	}

	/**
	 * 统一的执行预格式语句的方法
	 * 
	 * @param psql
	 * @param data
	 * @param type
	 * @return
	 */
	private int executeUpdate(String psql, String[] data, String type) {
		SQLiteDatabase db = getWritableDatabase();
		SQLiteStatement stmt = null;
		String reuslt = "";
		try {
			stmt = db.compileStatement(psql);
			db.beginTransaction();
			for (int i = 1; i <= data.length; i++) {
				stmt.bindString(i, data[i - 1]);
			}
			if ("insert".equals(type)) {
				reuslt = stmt.executeInsert() + "";
			} else {
				reuslt = stmt.executeUpdateDelete() + "";
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
			db.endTransaction();
			close(db, stmt, null);
		}
		return Integer.parseInt(reuslt);
	}

	public void onCreate(SQLiteDatabase db) {
		// 初始化基本表
		db.execSQL("create table t_sys_user(userId varchar(10) PRIMARY KEY,userName varchar(10), userPass varchar(10))");
	}

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// 什么也不做
	}

	/**
	 * 释放数据库资源的方法
	 * 
	 * @param db
	 * @param stmt
	 * @param cursor
	 */
	private void close(SQLiteDatabase db, SQLiteStatement stmt, Cursor cursor) {
		if (cursor != null) {
			cursor.close();
		}
		if (stmt != null) {
			stmt.close();
		}
		if (db != null) {
			db.close();
		}
	}
}