SQLiteTemplate.java

package com.ch_linghu.fanfoudroid.dao;

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

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Database Helper
 * 
 * @see SQLiteDatabase
 */
public class SQLiteTemplate {
	/**
	 * Default Primary key
	 */
	protected String mPrimaryKey = "_id";

	/**
	 * SQLiteDatabase Open Helper
	 */
	protected SQLiteOpenHelper mDatabaseOpenHelper;

	/**
	 * Construct
	 * 
	 * @param databaseOpenHelper
	 */
	public SQLiteTemplate(SQLiteOpenHelper databaseOpenHelper) {
		mDatabaseOpenHelper = databaseOpenHelper;
	}

	/**
	 * Construct
	 * 
	 * @param databaseOpenHelper
	 * @param primaryKey
	 */
	public SQLiteTemplate(SQLiteOpenHelper databaseOpenHelper, String primaryKey) {
		this(databaseOpenHelper);
		setPrimaryKey(primaryKey);
	}

	/**
	 * 根据某一个字段和值删除一行数据, 如 name="jack"
	 * 
	 * @param table
	 * @param field
	 * @param value
	 * @return
	 */
	public int deleteByField(String table, String field, String value) {
		return getDb(true).delete(table, field + "=?", new String[] { value });
	}

	/**
	 * 根据主键删除一行数据
	 * 
	 * @param table
	 * @param id
	 * @return
	 */
	public int deleteById(String table, String id) {
		return deleteByField(table, mPrimaryKey, id);
	}

	/**
	 * 根据主键更新一行数据
	 * 
	 * @param table
	 * @param id
	 * @param values
	 * @return
	 */
	public int updateById(String table, String id, ContentValues values) {
		return getDb(true).update(table, values, mPrimaryKey + "=?",
				new String[] { id });
	}

	/**
	 * 根据主键查看某条数据是否存在
	 * 
	 * @param table
	 * @param id
	 * @return
	 */
	public boolean isExistsById(String table, String id) {
		return isExistsByField(table, mPrimaryKey, id);
	}

	/**
	 * 根据某字段/值查看某条数据是否存在
	 * 
	 * @param status
	 * @return
	 */
	public boolean isExistsByField(String table, String field, String value) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) FROM ").append(table).append(" WHERE ")
				.append(field).append(" =?");

		return isExistsBySQL(sql.toString(), new String[] { value });
	}

	/**
	 * 使用SQL语句查看某条数据是否存在
	 * 
	 * @param sql
	 * @param selectionArgs
	 * @return
	 */
	public boolean isExistsBySQL(String sql, String[] selectionArgs) {
		boolean result = false;

		final Cursor c = getDb(false).rawQuery(sql, selectionArgs);
		try {
			if (c.moveToFirst()) {
				result = (c.getInt(0) > 0);
			}
		} finally {
			c.close();
		}
		return result;
	}

	/**
	 * Query for cursor
	 * 
	 * @param <T>
	 * @param rowMapper
	 * @return a cursor
	 * 
	 * @see SQLiteDatabase#query(String, String[], String, String[], String,
	 *      String, String, String)
	 */
	public <T> T queryForObject(RowMapper<T> rowMapper, String table,
			String[] columns, String selection, String[] selectionArgs,
			String groupBy, String having, String orderBy, String limit) {
		T object = null;

		final Cursor c = getDb(false).query(table, columns, selection,
				selectionArgs, groupBy, having, orderBy, limit);
		try {
			if (c.moveToFirst()) {
				object = rowMapper.mapRow(c, c.getCount());
			}
		} finally {
			c.close();
		}
		return object;
	}

	/**
	 * Query for list
	 * 
	 * @param <T>
	 * @param rowMapper
	 * @return list of object
	 * 
	 * @see SQLiteDatabase#query(String, String[], String, String[], String,
	 *      String, String, String)
	 */
	public <T> List<T> queryForList(RowMapper<T> rowMapper, String table,
			String[] columns, String selection, String[] selectionArgs,
			String groupBy, String having, String orderBy, String limit) {
		List<T> list = new ArrayList<T>();

		final Cursor c = getDb(false).query(table, columns, selection,
				selectionArgs, groupBy, having, orderBy, limit);
		try {
			while (c.moveToNext()) {
				list.add(rowMapper.mapRow(c, 1));
			}
		} finally {
			c.close();
		}
		return list;
	}

	/**
	 * Get Primary Key
	 * 
	 * @return
	 */
	public String getPrimaryKey() {
		return mPrimaryKey;
	}

	/**
	 * Set Primary Key
	 * 
	 * @param primaryKey
	 */
	public void setPrimaryKey(String primaryKey) {
		this.mPrimaryKey = primaryKey;
	}

	/**
	 * Get Database Connection
	 * 
	 * @param writeable
	 * @return
	 * @see SQLiteOpenHelper#getWritableDatabase();
	 * @see SQLiteOpenHelper#getReadableDatabase();
	 */
	public SQLiteDatabase getDb(boolean writeable) {
		if (writeable) {
			return mDatabaseOpenHelper.getWritableDatabase();
		} else {
			return mDatabaseOpenHelper.getReadableDatabase();
		}
	}

	/**
	 * Some as Spring JDBC RowMapper
	 * 
	 * @see org.springframework.jdbc.core.RowMapper
	 * @see com.ch_linghu.fanfoudroid.db.dao.SqliteTemplate
	 * @param <T>
	 */
	public interface RowMapper<T> {
		public T mapRow(Cursor cursor, int rowNum);
	}

}