自己封装的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;// 下载器idprivate 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;}@Overridepublic 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>() {@Overridepublic 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;}});}@Overridepublic 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;}@Overridepublic 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);}@Overridepublic int deleteDownloadInfoWithDownloadUrl(String downloadUrl)throws DaoException {String sql = "DELETE FROM downloadInfo WHERE downloadUrl = ?";String[] values = {downloadUrl};return super.deleteUnique(sql, values);}@Overridepublic 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);}@Overridepublic 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);}@Overridepublic 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)

更多相关文章

随机推荐