Android数据库操作封装
android数据库操作轻量级封装,主要实现了:
- 多线程数据库安全访问
- 数据插入,删除(提供单条数据操作,和多条数据的批量操作)
- 提供根据type,key搜索
- 数据库数据有效期处理
- 插入bean无需与数据库表本身字段对应(需要实现数据库操作接口)
原理:使用java泛型编程将数据库搜索结果解析为目标JavaBean,目标JavaBean通过实现指定接口实现key,type访问.引入原子操作解决多线程下数据库连接池异常问题.
接口实现类:
public interface DbBeanInterface {
//存储在数据库中的主键
String getDb_Id();
//存储在数据库中的type
String getDb_Type();
}
很简单的接口,规定了必须的key和type
SqliteHelper类
public class SqlLiteHelper extends SQLiteOpenHelper {
public final String TABLE_TEMP = "tbl_temp";
public SqlLiteHelper(Context context) {
super(context, "fly.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL("create table if not exists " + TABLE_TEMP + "(num integer primary key autoincrement, cid text null, jsonData text null, cacheTime text null, type text null);");
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
数据库表名”tbl_temp”数据库字段:自增长的key, 搜索主键:cid,json字符串,缓存有效期,搜索类型type.
数据库操作类
**
* 简化数据库操作
* 提供数据批量插入,单个插入,单个更新.数据表清空,数据有效期判断!
* 插入数据库中的bean只需要实现DbBeanInterface即可
* Created by heyf on 2018/4/14 0014.
*/
@Singleton//Dagger2依赖注入,单例标识
public class DbService {
private SqlLiteHelper mSql;
private AtomicInteger mOpenCounter;//原子操作的Integer的类,线程安全
private SQLiteDatabase mDatabase;
private Gson gson = null;
@Inject
public DbService(SqlLiteHelper sqlLiteHelper) {
mSql = sqlLiteHelper;
mOpenCounter = new AtomicInteger();
gson = new Gson();
}
/**
* 数据插入
*
* @param bean 要插入的对象 需要实现DbBeanInterface
* @return
*/
public <T extends DbBeanInterface> int insert(T bean) {
if (!isRightBean(bean) || TextUtils.isEmpty(bean.getDb_Id())) {
return -1;
}
String catcheTime = System.currentTimeMillis() + "";
if (queryById(bean.getDb_Id(), bean.getClass()) != null) {
return updata(bean);
}
int flag = -1;
SQLiteDatabase database = getWriteDatabase();
database.beginTransaction();
try {
ContentValues values = new ContentValues();
values.put("cid", bean.getDb_Id());
values.put("cacheTime", catcheTime);
values.put("jsonData", gson.toJson(bean));
values.put("type", TextUtils.isEmpty(bean.getDb_Type()) ? "no_type" : bean.getDb_Type());
database.insert(mSql.TABLE_TEMP, null, values);
database.setTransactionSuccessful();
flag = 1;
} catch (Exception e) {
e.printStackTrace();
} finally {
database.endTransaction();
dbclose(null);
}
return flag;
}
/**
* 批量插入数据(数据更新操作),
*
* @param bean
* @param <T>
* @return
*/
public <T extends DbBeanInterface> int update(List<T> bean) {
if (bean == null || bean.size() <= 0) {
return -1;
}
String cacheTime = System.currentTimeMillis() + "";
int flag = -1;
clearTable();
SQLiteDatabase database = getWriteDatabase();
database.beginTransaction();
try {
for (T dataBean : bean) {
if (!TextUtils.isEmpty(dataBean.getDb_Id())) {
ContentValues values = new ContentValues();
values.put("cid", dataBean.getDb_Id());
values.put("cacheTime", cacheTime);
values.put("jsonData", gson.toJson(dataBean));
values.put("type", TextUtils.isEmpty(dataBean.getDb_Type()) ? "no_type" : dataBean.getDb_Type());
database.insert(mSql.TABLE_TEMP, null, values);
}
}
database.setTransactionSuccessful();
flag = 1;
} catch (Exception e) {
e.printStackTrace();
} finally {
database.endTransaction();
dbclose(null);
}
return flag;
}
private <T extends DbBeanInterface> int updata(T bean) {
if (!isRightBean(bean)) {
return -1;
}
int flag = -1;
SQLiteDatabase db = getWriteDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
values.put("cid", bean.getDb_Id());
values.put("cacheTime", System.currentTimeMillis() + "");
values.put("jsonData", gson.toJson(bean));
values.put("type", TextUtils.isEmpty(bean.getDb_Type()) ? "no_type" : bean.getDb_Type());
flag = db.update(mSql.TABLE_TEMP, values, "cid=?", new String[]{bean.getDb_Id()});
db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally {
db.endTransaction();
dbclose(null);
}
return flag;
}
private <T> boolean isRightBean(T bean) {
return !(bean == null || !(bean instanceof DbBeanInterface));
}
/**
* 根据缓存有效期取数据
*
* @param id
* @param classOfT
* @param cachetime 缓存有效期(单位 毫秒)
* @param <T>
* @return
*/
public <T> T queryById(String id, Class<T> classOfT, long cachetime) {
SQLiteDatabase db = getWriteDatabase();
Cursor cursor = null;
T bean = null;
db.beginTransaction();
try {
cursor = db.rawQuery("select * from " + mSql.TABLE_TEMP + " where cid=?", new String[]{id});
cursor.move(-1);
if (cursor.moveToNext()) {
bean = gson.fromJson(cursor.getString(cursor.getColumnIndex("jsonData")), classOfT);
if (cachetime > 0) {
String startTime = cursor.getString(cursor.getColumnIndex("cacheTime"));
if (TextUtils.isEmpty(startTime) || isCacheDataOverTime(Long.parseLong(startTime), cachetime)) {
//deleteById(cursor.getString(cursor.getColumnIndex("cid")));
bean = null;
}
}
}
db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally {
db.endTransaction();
dbclose(cursor);
}
return bean;
}
/**
* 通过type查找数据
*
* @param type
* @param classOfT
* @param catcheTime 缓存有效期0表示无有效期
* @param <T>
* @return
*/
public <T> List<T> queryByType(String type, Class<T> classOfT, long catcheTime) {
List<T> list = null;
if (!TextUtils.isEmpty(type)) {
SQLiteDatabase db = getWriteDatabase();
Cursor cursor = null;
String starttime = "";
list = new ArrayList<>();
db.beginTransaction();
try {
cursor = db.rawQuery("select * from " + mSql.TABLE_TEMP + " where type=?", new String[]{type});
cursor.move(-1);
while (cursor.getCount() > 0 && cursor.moveToNext()) {
starttime = cursor.getString(cursor.getColumnIndex("cacheTime"));
if (catcheTime > 0) {
if (TextUtils.isEmpty(starttime) || isCacheDataOverTime(Long.parseLong(starttime), catcheTime)) {
//deleteById(cursor.getString(cursor.getColumnIndex("cid")));
} else {
list.add(gson.fromJson(cursor.getString(cursor.getColumnIndex("jsonData")), classOfT));
}
} else {
list.add(gson.fromJson(cursor.getString(cursor.getColumnIndex("jsonData")), classOfT));
}
}
db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally {
db.endTransaction();
dbclose(cursor);
}
}
return list;
}
/**
* 引入原子操作,防止还在使用中的数据库线程被异常关闭
*
* @param cursor
*/
private synchronized void dbclose(Cursor cursor) {
if (mOpenCounter.decrementAndGet() == 0 && mDatabase != null) {
// Closing database
mDatabase.close();
if (cursor != null) {
if (!cursor.isClosed()) {
cursor.close();
}
}
}
}
/**
* 引入原子操作,解决多线程并发访问数据库线程池异常问题
*
* @return
*/
private synchronized SQLiteDatabase getWriteDatabase() {
if (mOpenCounter.incrementAndGet() == 1) {
// Opening new database
mDatabase = mSql.getWritableDatabase();
}
return mDatabase;
}
/**
* 通过Id删除数据
*
* @param id
* @return
*/
public int deleteById(String id) {
int flag = -1;
if (!TextUtils.isEmpty(id)) {
SQLiteDatabase db = getWriteDatabase();
db.beginTransaction();
try {
flag = db.delete(mSql.TABLE_TEMP, "cid=?", new String[]{id});
db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally {
db.endTransaction();
dbclose(null);
}
}
return flag;
}
/**
* 根据id查找数据
*
* @param id
* @param classOfT
* @param <T>
* @return
*/
public <T> T queryById(String id, Class<T> classOfT) {
return queryById(id, classOfT, 0);
}
/**
* 判断数据是否过期
*
* @param starttime 数据存储时间
* @param cacheTime 缓存有效期
* @return true 数据失效 false 未过期
*/
private boolean isCacheDataOverTime(long starttime, long cacheTime) {
long timenow = System.currentTimeMillis();
return starttime + cacheTime < timenow;
}
/**
* 据缓存有效期取数据
*
* @param type
* @param classOfT
* @param <T>
* @return
*/
public <T> List<T> queryByType(String type, Class<T> classOfT) {
return queryByType(type, classOfT, 0);
}
public int deleteByType(String type) {
int flag = -1;
if (!TextUtils.isEmpty(type)) {
SQLiteDatabase db = getWriteDatabase();
db.beginTransaction();
try {
flag = db.delete(mSql.TABLE_TEMP, "type=?", new String[]{type});
db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally {
db.endTransaction();
dbclose(null);
}
}
return flag;
}
/**
* 清空本地数据
*
* @return
*/
public void clearTable() {
SQLiteDatabase db = getWriteDatabase();
db.beginTransaction();
try {
db.execSQL("DELETE FROM " + mSql.TABLE_TEMP);
db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally {
db.endTransaction();
dbclose(null);
}
}
数据库操作类:提供单条数据的增删改查,以及按照type的增删改查,以及数据库数据初始化(批量插入数据)!
测试及使用:
public void testDb() {
Context context = getContext();
Student s1 = new Student("李丽华", 20, "10010");
SqlLiteHelper sqlLiteHelper = new SqlLiteHelper(getSystemContext());
System.out.println("--------------------------------------" + sqlLiteHelper == null);
final DbService service = new DbService(sqlLiteHelper);
service.deleteByType("no_type");
for (int i=0;i<100;i++)
{
final int finalI = i;
new Thread(){
@Override
public void run() {
service.insert(new Student("线程"+(finalI+1),100-finalI,"线程id"+finalI));
LogUtils.d("flyisme", finalI +"线程 批量插入:" + new Gson().toJson(service.queryByType("student", Student.class)));
}
}.start();
}
try {
Thread.sleep(2000);
LogUtils.d("flyisme", "条数:" + service.queryByType("student", Student.class,5000).size());
LogUtils.d("flyisme", "数据查找:" + new Gson().toJson(service.queryById("线程id10", Student.class,5000)));
} catch (InterruptedException e) {
e.printStackTrace();
}
}
public class Student implements DbBeanInterface {
private String name;
private int age;
private String persionId;
public Student(String name, int age, String persionId) {
this.name = name;
this.age = age;
this.persionId = persionId;
}
@Override
public String getDb_Id() {
return persionId;
}
@Override
public String getDb_Type() {
return "student";
}
}
使用的时候需要让要插入数据库中的类实现DbBeanInterface 接口即可!
数据库操作总结!