在以前，操作数据库的时候，往往是通过DBHelper类去操作数据库，然而这种操作方式存在其固有缺陷，因为其是创建在DB目录下，会随着软件的卸载而卸载，那么有时有需要在本次卸载并不删除数据库，又或者是通过面向对象的思想去操作数据库，主要是对比ormlite框架
使用DBHelper
在之前，使用DBHelper会比较繁琐，例如添加一个用户，会涉及以下操作
public void saveUser(User name) {
DBHelper dbHelper = new DBHelper(this);
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.execSQL("create table if not exists tb_user(name varchar(20),password varchar(10), )");
ContentValues values = new ContentValues();
values.put("name", user.getName());
values.put("password", user.getPassword());
db.beginTransaction();
int scheduleID = -1;
try {
db.insert("schedule", null, values);
db.setTransactionSuccessful();
} finally {
}
db.endTransaction();
db.close();
}
public void updateUser(User user, String name) {
DBHelper dbHelper = new DBHelper(this);
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.execSQL("create table if not exists tb_user(name varchar(20),password varchar(10), )");
ContentValues values = new ContentValues();
values.put("name", user.getName());
values.put("password", user.getPassword());
db.beginTransaction();
try {
db.update("course", values, "name=?", new String[]{String.valueOf(user.getName())});
db.setTransactionSuccessful();
} finally {
}
db.endTransaction();
db.close();
}
数据库框架的实现效果
通过数据库框架的实现，我们可以更简单的操作数据库，以下就是实现其框架以后增加用户的使用例子，明显更加方便易行
public void insert() {
User user = new User();
user.setName("jack");
user.setPassword("123456");
baseDao.insert(user);
}
数据库框架的设计思路
将调用层与SQLiteDatabase进行隔离，在中间层完成相应的数据库框架设计
其简单来说就是调用层不关心数据的存入过程，只需要满足调用层将对象给与中间层，中间层完成存储的相应操作
通过映射关系使用HashMap得到数据库的key和value键值对
通过注解维护表明与数据库存值的关系
数据库框架实现
设计一个数据库操作接口
public interface IBaseDao<T> {
//插入数据
long insert(T entity);
//更新数据
int update(T entity, T where);
//删除数据
int detele(T where);
//查询数据
List<T> query(T where);
List<T> query(T where, String orderBy, Integer startIndex, Integer limit);
}
添加注解类
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface DbTable {
String value();
}
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DbField {
String value();
}
实现接口声明的方法，通过反射和注解得到对象的值，然后进行相应操作
public abstract class BaseDao<T> implements IBaseDao<T> {
private static final String TAG = "BaseDao";
private SQLiteDatabase database;//持有数据库操作类的引用
private boolean isInit = false;//保证只实例化一次
private Class<T> entityClass;//持有数据库表所对应的java类型
private String tableName;
private HashMap<String, Field> cacheMap;//维护表名与成员变量的映射关系
//实例化
protected boolean init(Class<T> entity, SQLiteDatabase sqLiteDatabase) {
if (!isInit) {
database = sqLiteDatabase;
entityClass = entity;
cacheMap = new HashMap<>();
//拿到注解中的表名
if (entity.getAnnotation(DbTable.class) != null) {
tableName = entity.getAnnotation(DbTable.class).value();
} else {
tableName = entity.getClass().getSimpleName();
}
if (!database.isOpen()) {
return false;
}
if (!TextUtils.isEmpty(createTable())) {
database.execSQL(createTable());
}
initCacheMap();
isInit = true;
}
return isInit;
}
//创建表，在子类中完成
protected abstract String createTable();
//维护映射关系
private void initCacheMap() {
String sql = "select * from " + this.tableName + " limit 1 , 0";
Cursor cursor = null;
try {
cursor = database.rawQuery(sql, null);
String[] columnNames = cursor.getColumnNames();//表的列名数组
Field[] columnFields = entityClass.getFields();//拿到Filed数组
for (Field field : columnFields) {
field.setAccessible(true);
}
//查找对应关系
for (String columnName : columnNames) {
Field columnField = null;
for (Field field : columnFields) {
String fieldName = null;
if (field.getAnnotation(DbField.class) != null) {
fieldName = field.getAnnotation(DbField.class).value();
} else {
fieldName = field.getName();
}
if (columnName.equals(fieldName)) {
columnField = field;
break;
}
}
//找到对应关系，存入关系表
if (columnField != null) {
cacheMap.put(columnName, columnField);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
cursor.close();
}
}
@Override
public long insert(T entity) {
Map<String, String> map = getValues(entity);
ContentValues values = getContentValues(map);
long result = database.insert(tableName, null, values);
Log.d(TAG, "insert: result = " + result);
return result;
}
@Override
public int update(T entity, T where) {
int result = -1;
Map values = getValues(entity);
ContentValues contentValues = getContentValues(values);
Map whereClause = getValues(where);
Condition condition = new Condition(whereClause);
result = database.update(tableName, contentValues, condition.getWhereClause(), condition.getWhereArgs());
return result;
}
@Override
public int detele(T where) {
int result = -1;
Map values = getValues(where);
Condition condition = new Condition(values);
result = database.delete(tableName, condition.getWhereClause(), condition.getWhereArgs());
return result;
}
@Override
public List<T> query(T where) {
return query(where, null, null, null);
}
@Override
public List<T> query(T where, String orderBy, Integer startIndex, Integer limit) {
Map values = getValues(where);
String limitString = null;
if (startIndex != null && limit != null) {
limitString = startIndex + " , " + limit;
}
Condition condition = new Condition(values);
Cursor cursor = database.query(tableName, null, condition.getWhereClause(),
condition.getWhereArgs(), null, null, orderBy, limitString);
List<T> result = getResult(cursor, where);
return result;
}
//通过Map构建ContentValues
private ContentValues getContentValues(Map<String, String> map) {
ContentValues contentValues = new ContentValues();
Set keys = map.keySet();
Iterator<String> iterator = keys.iterator();
while (iterator.hasNext()) {
String key = iterator.next();
String value = map.get(key);
if (value != null) {
contentValues.put(key, value);
}
}
return contentValues;
}
//获取value值，存入Map
private Map<String, String> getValues(T entity) {
HashMap<String, String> result = new HashMap<>();
Iterator<Field> filedsIterator = cacheMap.values().iterator();
//循环遍历映射map的Filed
while (filedsIterator.hasNext()) {
Field colmunToField = filedsIterator.next();
String cacheKey = null;
String cacheValue = null;
if (colmunToField.getAnnotation(DbField.class) != null) {
cacheKey = colmunToField.getAnnotation(DbField.class).value();
} else {
cacheKey = colmunToField.getName();
}
try {
if (colmunToField.get(entity) == null) {
continue;
}
cacheValue = colmunToField.get(entity).toString();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
result.put(cacheKey, cacheValue);
}
return result;
}
//构建查询条件
class Condition {
//查询条件
private String whereClause;
private String[] whereArgs;
public Condition(Map<String, String> whereClause) {
ArrayList arrayList = new ArrayList();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append(" 1=1 ");
Set keys = whereClause.keySet();
Iterator<String> iterator = keys.iterator();
while (iterator.hasNext()) {
String key = iterator.next();
String value = whereClause.get(key);
if (value != null) {
//拼接条件查询语句 1=1 and name=? and password=?
stringBuilder.append(" and " + key + " =?");
arrayList.add(value);
}
}
this.whereClause = stringBuilder.toString();
this.whereArgs = (String[]) arrayList.toArray(new String[arrayList.size()]);
}
public String getWhereClause() {
return whereClause;
}
public String[] getWhereArgs() {
return whereArgs;
}
}
//获取查询结果
private List<T> getResult(Cursor cursor, T where) {
List list = new ArrayList();
Object item;
while (cursor.moveToNext()) {
try {
item = where.getClass().newInstance();
Iterator iterator = cacheMap.entrySet().iterator();
while (iterator.hasNext()) {
Map.Entry entry = (Map.Entry) iterator.next();
String columnName = (String) entry.getKey(); //得到列名
Integer columnIndex = cursor.getColumnIndex(columnName); //拿到位置
Field field = (Field) entry.getValue();
Class type = field.getType();
if (columnIndex != -1) {
if (type == String.class) {
//反射方式赋值
field.set(item, cursor.getString(columnIndex));
} else if (type == Integer.class) {
field.set(item, cursor.getInt(columnIndex));
} else if (type == Double.class) {
field.set(item, cursor.getDouble(columnIndex));
} else if (type == Long.class) {
field.set(item, cursor.getLong(columnIndex));
} else if (type == byte[].class) {
field.set(item, cursor.getBlob(columnIndex));
} else {
continue;
}
}
}
list.add(item);
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
return list;
}
}
添加工厂模式
public class BaseDaoFactory {
private String sqliteDatabasePath;
private SQLiteDatabase sqLiteDatabase;
private static BaseDaoFactory instance = new BaseDaoFactory();
public static BaseDaoFactory getInstance(){
return instance;
}
public BaseDaoFactory() {
sqliteDatabasePath = Environment.getExternalStorageDirectory().getAbsolutePath() + File.separator + "user.db";
openDatabase();
}
public synchronized <T extends BaseDao<M>, M> T getDataHelper(Class<T> clazz, Class<M> entityClass) {
BaseDao baseDao = null;
//反射得到对象类型
try {
baseDao = clazz.newInstance();
baseDao.init(entityClass, sqLiteDatabase);
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return (T) baseDao;
}
private void openDatabase() {
//打开数据库，如果不存在则创建
this.sqLiteDatabase = SQLiteDatabase.openOrCreateDatabase(sqliteDatabasePath,null);
}
}
编写测试类
@DbTable("tb_user")
public class User {
@DbField("name")
public String name;
@DbField("password")
public String password;
public User() {
}
public User(String name, String password) {
this.name = name;
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "name: " + name + ",password: " + password;
}
}
测试类的数据操作，继承BaseDao，完成数据库的创建
public class UserDao extends BaseDao{
@Override
protected String createTable() {
return "create table if not exists tb_user(name varchar(20),password varchar(10))";
}
}
在主活动中测试
public class MainActivity extends AppCompatActivity {
private static final String TAG = "cj5785";
IBaseDao<User> baseDao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
baseDao = BaseDaoFactory.getInstance().getDataHelper(UserDao.class, User.class);
}
public void onSave(View view) {
User user = new User("jack", "123456");
baseDao.insert(user);
}
public void onUpdate(View view) {
User where = new User();
where.setName("jack");
User user = new User("json", "654321");
baseDao.update(user, where);
}
public void onDelete(View view) {
User user = new User();
user.setName("json");
baseDao.detele(user);
}
public void onQuery(View view) {
User user = new User();
user.setName("jack");
List<User> list = baseDao.query(user);
Log.d(TAG, "查询到数据条目：" + list.size());
for (User u : list) {
Log.d(TAG, u.toString());
}
}
}
测试结果
添加五条"jack"数据
更新"jack"数据为"json"
再添加五条"jack"数据
查询"jack"数据
删除"json"数据
对比OrimLite框架
OrimLite框架插入1000条数据，在我测试机上，测试十次，平均用时：5110ms
自定义框架插入1000条数据十次平均用时：6430ms