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

panjianm

在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)