自己封装的Android sqlite-helper.jar包使用方法

在Android项目开发中会经常使用到其内嵌的SQLite数据库，但是疲于Android本身的SQLiteOpenHelper的繁琐（也许个人从事过Web开发，惯用MySql的偏见），经过统一思想的 方式封装了该sqlite-helper.jar包，对于从Java Web方向转过来的人也许算是一种帮助吧！
1.新建实体类

package com.whl.helper.entity;

import com.fasterxml.jackson.annotation.JsonIgnore;

public class DownloadInfo {
	@JsonIgnore(value=true)
	private int id;
	private int threadId;// 下载器id
	private int startPos;// 开始点
	private int endPos;// 结束点
	private int completeSize;// 完成度
	private String downloadUrl;// 下载器网络标识
	private int finish;//下载完成（0：未完成，1：已完成）
	
	public DownloadInfo(){}
	
	public DownloadInfo(int threadId, int startPos, int endPos,int completeSize, String downloadUrl) {
		super();
		this.threadId = threadId;
		this.startPos = startPos;
		this.endPos = endPos;
		this.completeSize = completeSize;
		this.downloadUrl = downloadUrl;
	}
	
	public int getId(){
		return id;
	}
	
	public void setId(int id){
		this.id = id;
	}
	
	public int getThreadId() {
		return threadId;
	}

	public void setThreadId(int threadId) {
		this.threadId = threadId;
	}

	public int getStartPos() {
		return startPos;
	}

	public void setStartPos(int startPos) {
		this.startPos = startPos;
	}

	public int getEndPos() {
		return endPos;
	}

	public void setEndPos(int endPos) {
		this.endPos = endPos;
	}

	public int getCompleteSize() {
		return completeSize;
	}

	public void setCompleteSize(int completeSize) {
		this.completeSize = completeSize;
	}

	public String getDownloadUrl() {
		return downloadUrl;
	}

	public void setDownloadUrl(String downloadUrl) {
		this.downloadUrl = downloadUrl;
	}

	public int getFinish() {
		return finish;
	}

	public void setFinish(int finish) {
		this.finish = finish;
	}

	@Override
	public String toString() {
		return "DownloadInfo [id=" + id + ", threadId=" + threadId
				+ ", startPos=" + startPos + ", endPos=" + endPos
				+ ", completeSize=" + completeSize + ", downloadUrl="
				+ downloadUrl + ", finish=" + finish + "]";
	}
}

2.该实体类的Dao接口

package com.whl.helper.database.dao;

import java.util.List;

import com.whl.dao.common.BaseDao;
import com.whl.dao.common.DaoException;
import com.whl.helper.entity.DownloadInfo;

public interface DownloadInfoDao extends BaseDao<DownloadInfo> {
	public Boolean isHasDownloadInfosWithDownloadUrl(String downloadUrl) throws DaoException; 
	public List<DownloadInfo> findDownloadInfoListWithDownloadUrl(String downloadUrl) throws DaoException;
	public int deleteDownloadInfoWithDownloadUrl(String downloadUrl) throws DaoException;
	public int update(int completeSize,int threadId,String downloadUrl) throws DaoException;
	public DownloadInfo findDownloadInfoWithThreadIdAndDownloadUrl(int threadId, String downloadUrl) throws DaoException;
	public long findDownloadInfoSumCompleteSizeWithDownloadUrl(String downloadUrl) throws DaoException;
}

3.该实体类的Dao接口实现类

package com.whl.helper.database.dao.impl;

import java.util.List;

import android.content.Context;
import android.database.Cursor;

import com.whl.dao.common.DaoException;
import com.whl.dao.impl.CreateBaseDaoImpl;
import com.whl.helper.database.dao.DownloadInfoDao;
import com.whl.helper.entity.DownloadInfo;
import com.whl.utils.RowMapper;

public class DownloadInfoDaoImpl extends CreateBaseDaoImpl<DownloadInfo> implements DownloadInfoDao {
	public DownloadInfoDaoImpl(Context context){
		super.prepare(context, new RowMapper<DownloadInfo>() {
			@Override
			public DownloadInfo onRowMapper(Cursor cursor) {
				DownloadInfo downloadInfo = new DownloadInfo();
				downloadInfo.setId(cursor.getInt(cursor.getColumnIndex("id")));
				downloadInfo.setCompleteSize(cursor.getInt(cursor.getColumnIndex("completeSize")));
				downloadInfo.setDownloadUrl(cursor.getString(cursor.getColumnIndex("downloadUrl")));
				downloadInfo.setEndPos(cursor.getInt(cursor.getColumnIndex("endPos")));
				downloadInfo.setStartPos(cursor.getInt(cursor.getColumnIndex("startPos")));
				downloadInfo.setThreadId(cursor.getInt(cursor.getColumnIndex("threadId")));
				downloadInfo.setFinish(cursor.getInt(cursor.getColumnIndex("finish")));
				return downloadInfo;
			}
		});
	}
	@Override
	public Boolean isHasDownloadInfosWithDownloadUrl(String downloadUrl) throws DaoException {
		String sql = "SELECT COUNT(*) AS size FROM downloadInfo WHERE downloadUrl = ?";
		String[] values = {downloadUrl};
		return super.countSqlResult(sql, values)==0?true:false;
	}

	@Override
	public List<DownloadInfo> findDownloadInfoListWithDownloadUrl(String downloadUrl) throws DaoException {
		String sql = "SELECT * FROM downloadInfo WHERE downloadUrl = ?";
		String[] values = {downloadUrl};
		//List<DownloadInfo> downloadInfoList = super.find(sql, values);
		return super.find(sql, values);
	}
	@Override
	public int deleteDownloadInfoWithDownloadUrl(String downloadUrl)throws DaoException {
		String sql = "DELETE FROM downloadInfo WHERE downloadUrl = ?";
		String[] values = {downloadUrl};
		return super.deleteUnique(sql, values);
	}
	@Override
	public int update(int completeSize,int threadId,String downloadUrl) throws DaoException {
		String sql = "UPDATE downloadInfo SET completeSize = ? WHERE threadId=? AND downloadUrl = ?";
		String[] values = {String.valueOf(completeSize),String.valueOf(threadId),downloadUrl};
		return super.batchExecute(sql, values);
	}
	
	@Override
	public DownloadInfo findDownloadInfoWithThreadIdAndDownloadUrl(int threadId, String downloadUrl) throws DaoException{
		String sql = "SELECT * FROM downloadInfo WHERE threadId = ? AND downloadUrl = ?";
		String[] values = {String.valueOf(threadId),downloadUrl};
		return super.findUniqueResult(sql, values);
	}
	@Override
	public long findDownloadInfoSumCompleteSizeWithDownloadUrl(String downloadUrl) throws DaoException {
		long sumCompletedSize = 0;
		List<DownloadInfo> downloadInfoList = findDownloadInfoListWithDownloadUrl(downloadUrl);
		for (DownloadInfo downloadInfo : downloadInfoList) {
			sumCompletedSize+=downloadInfo.getCompleteSize();
		}
		return sumCompletedSize;
	}

}

4.该实体类的Service类

package com.whl.helper.database.service;

import java.util.List;

import android.content.Context;

import com.whl.dao.common.DaoException;
import com.whl.helper.database.dao.DownloadInfoDao;
import com.whl.helper.database.dao.impl.DownloadInfoDaoImpl;
import com.whl.helper.entity.DownloadInfo;
import com.whl.service.common.BaseService;

public class DownloadInfoService extends BaseService<DownloadInfo> {
	private DownloadInfoDao downloadInfoDao;
	private static DownloadInfoService downloadInfoServiceInstance;
	private DownloadInfoService(Context context){
		downloadInfoDao = new DownloadInfoDaoImpl(context);
		super.prepareBaseDao(downloadInfoDao);
	}
	
	public static DownloadInfoService getInstance(Context context){
		if(downloadInfoServiceInstance==null){
			downloadInfoServiceInstance = new DownloadInfoService(context);
		}
		return downloadInfoServiceInstance;
	}
	public Boolean isHasDownloadInfosWithDownloadUrl(String downloadUrl){
		try {
			return downloadInfoDao.isHasDownloadInfosWithDownloadUrl(downloadUrl);
		} catch (DaoException e) {
			e.printStackTrace();
			return false;
		}
	} 
	public List<DownloadInfo> findDownloadInfoListWithDownloadUrl(String downloadUrl){
		try {
			return downloadInfoDao.findDownloadInfoListWithDownloadUrl(downloadUrl);
		} catch (DaoException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public int deleteDownloadInfoWithDownloadUrl(String downloadUrl){
		try {
			return downloadInfoDao.deleteDownloadInfoWithDownloadUrl(downloadUrl);
		} catch (DaoException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	public int update(int completeSize,int threadId,String downloadUrl){
		try {
			return downloadInfoDao.update(completeSize,threadId, downloadUrl);
		} catch (DaoException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	public DownloadInfo findDownloadInfoWithThreadIdAndDownloadUrl(int threadId, String downloadUrl){
		try {
			return downloadInfoDao.findDownloadInfoWithThreadIdAndDownloadUrl(threadId, downloadUrl);
		} catch (DaoException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public long findDownloadInfoSumCompleteSizeWithDownloadUrl(String downloadUrl){
		try {
			return downloadInfoDao.findDownloadInfoSumCompleteSizeWithDownloadUrl(downloadUrl);
		} catch (DaoException e) {
			e.printStackTrace();
			return 0;
		}
	}
}

当然，对于习惯写SQL语句的人来说这种方式很容易理解，jar中基本上已经封装好了常用的增删改查，特殊处理的地方也就是如上书写SQL语句并传递参数并调用响应的基本方法即可。
5.真正使用
做完上述基本工作以后接下来就是如何去使用，使用其实很简单，首先如果是新建的数据库就需要SQL语句：

/**
	 * 初始化数据库
	 */
	public void initDatabase(String[] sqlArray){
		CreateDatabaseHelper createDatabaseHelper = CreateDatabaseHelper.getInstanceDatabaseHelper(getApplicationContext());
		SQLiteDatabase database = createDatabaseHelper.getWritableDatabase();
		database.beginTransaction();
		for (String sql : sqlArray) {
			database.execSQL(sql);
		}
		database.setTransactionSuccessful();
		database.endTransaction();
		database.close();
	}

接着就是其他的一些操作只需获取响应实体类的Service对象：

public void testSqliteStatement(){
		DownloadInfoService downloadInfoService = DownloadInfoService.getInstance(this);
		try {
			long startTime = System.currentTimeMillis();
			DownloadInfo downloadInfo = downloadInfoService.get("2");
			downloadInfoService.removeUnique(downloadInfo);
			long finishTime = System.currentTimeMillis();
			Log.d(TAG, "testSqliteStatement:"+(finishTime-startTime));
		} catch (DaoException e) {
			e.printStackTrace();
		}
		/*List<DownloadInfo> downloadInfoList = new ArrayList<DownloadInfo>();
		for(int i=0;i<2;i++){
			DownloadInfo downloadInfo = new DownloadInfo();
			downloadInfo.setCompleteSize(1000+i);
			downloadInfo.setDownloadUrl("www"+i);
			downloadInfo.setEndPos(2000+i);
			downloadInfo.setFinish(0+i);
			downloadInfo.setStartPos(0+i);
			downloadInfo.setThreadId(11+i);
			downloadInfoList.add(downloadInfo);
		}
		
		
		try {
			long startTime = System.currentTimeMillis();
			for (DownloadInfo downloadInfo : downloadInfoList) {
				downloadInfoService.insert(downloadInfo);
			}
			long finishTime = System.currentTimeMillis();
			Log.d(TAG, "testSqliteStatement:"+(finishTime-startTime));
		} catch (DaoException e) {
			e.printStackTrace();
		}*/
	}

这里需要说明的是实体类的属性名必须与数据库表中的字段名一样（虽然不区分大小写），实体名也与对应的数据表名一样（虽然不区分大小写），否则会报异常，这样的好处是方便明白意义。上述只是创建了一个数据表，该表的SQL语句如下：

CREATE TABLE IF NOT EXISTS downloadInfo(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, threadId INTEGER, startPos INTEGER, endPos INTEGER, completeSize INTEGER,downloadUrl VARCHAR,finish INTEGER)