在項(xiàng)目開(kāi)發(fā)中都會(huì)碰要將一些數(shù)據(jù)緩存在本地凌盯，SharedPreferences付枫、流的方式寫(xiě)入文件、數(shù)據(jù)庫(kù)等方式都可實(shí)現(xiàn)驰怎，在這些方式中阐滩，數(shù)據(jù)庫(kù)相對(duì)來(lái)說(shuō)要繁瑣些，使用的頻率相應(yīng)的也會(huì)少些县忌，剛好這段時(shí)間在學(xué)習(xí)數(shù)據(jù)庫(kù)掂榔，故將所學(xué)記錄于此。
一般情況下都會(huì)將數(shù)據(jù)庫(kù)存儲(chǔ)在data/目錄下症杏，這里是將數(shù)據(jù)庫(kù)存在的sd里面装获，不是data/目錄下，在學(xué)習(xí)中涉及到這些方面的知識(shí)：
1厉颤、泛型
2穴豫、注解
3、反射
4逼友、數(shù)據(jù)庫(kù)拼接語(yǔ)句
5精肃、單例等設(shè)計(jì)模式
6、Android6.0權(quán)限適配
實(shí)現(xiàn)了增帜乞、刪司抱、改、查功能黎烈，同時(shí)適配了Android6.0權(quán)限問(wèn)題
增：實(shí)現(xiàn)了單條數(shù)據(jù)插入和批量插入习柠，批量插入5000千條數(shù)據(jù)耗時(shí)500多毫秒
查：實(shí)現(xiàn)了本地分頁(yè)查詢及提供了多條件查詢的接口
代碼實(shí)現(xiàn)：
/**
* Created by Administrator on 2017/11/10.
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DbFiled {
String value();
}
/**
* Created by Administrator on 2017/11/10.
* 表名注解
*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface DbTable {
String value();
}
上面這兩個(gè)是注解類匀谣，DbFiled主要用于數(shù)據(jù)模型的注解，DbTable用于數(shù)據(jù)庫(kù)表名的注解津畸；
public interface IBaseDao<T> {
/**
* 插入數(shù)據(jù)庫(kù)
* @param entity 插入的數(shù)據(jù)對(duì)象
* @return
*/
Long insert(T entity);
/**
* 批量插入數(shù)據(jù)庫(kù)
* @param entity 插入的數(shù)據(jù)對(duì)象
* @return
*/
void insert(List<T> entity);
/**
* 更新數(shù)據(jù)庫(kù)
* @param entity 更新的數(shù)據(jù)
* @param where 條件
* @return
*/
int update(T entity,T where);
/**
* 刪除數(shù)據(jù)庫(kù)
* @param entity
* @return
*/
int delete(T entity);
/**
* 查詢數(shù)據(jù)
* @param where 查詢條件
* @return
*/
List<T> query(T where);
/**
* 查詢數(shù)據(jù)
* @param where 查詢條件
* @param orderBy 查詢排序
* @param startIndex 開(kāi)始的位置
* @param limit 查詢限制條件
* @return
*/
List<T> query(T where, String orderBy, Integer startIndex, Integer limit);
/**
* 查詢數(shù)據(jù) 用于多條件查詢
* @param sql 查詢語(yǔ)句
* @return
*/
List<T> query(String sql);
}
數(shù)據(jù)庫(kù)接口振定，增、刪肉拓、改、查等方法都定義在這里梳庆，具體的讓實(shí)現(xiàn)類去實(shí)現(xiàn)暖途；
public class BaseDaoFactory {
private String sqliteDatabasePath;
private SQLiteDatabase sqLiteDatabase;
private static BaseDaoFactory instance = new BaseDaoFactory();
public BaseDaoFactory() {
sqliteDatabasePath = Environment.getExternalStorageDirectory().getAbsolutePath() + "/teacher.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);
}
public static BaseDaoFactory getInstance() {
return instance;
}
}
BaseDaoFactory類主要實(shí)現(xiàn)數(shù)據(jù)庫(kù)的初始化和開(kāi)啟及儲(chǔ)存路徑的初始化；
public abstract class BaseDao<T> implements IBaseDao<T> {
/**
* 保證實(shí)例化一次
*/
private boolean isInit = false;
/**
* 持有操作數(shù)據(jù)庫(kù)表所對(duì)應(yīng)的java類型
* User
*/
private Class<T> entityClass;
private String tableName;
/**
* ]
* 持有數(shù)據(jù)庫(kù)操作類的引用
*/
private SQLiteDatabase database;
/**
* 維護(hù)這表名與成員變量名的映射關(guān)系
* key---》表名
* value --》Field
*/
private HashMap<String, Field> cacheMap;
protected synchronized boolean init(Class<T> entity, SQLiteDatabase sqLiteDatabase) {
if (!isInit) {
entityClass = entity;
database = sqLiteDatabase;
//獲取數(shù)據(jù)庫(kù)表名
if (entity.getAnnotation(DbTable.class) == null) {
tableName = entity.getClass().getSimpleName();
} else {
tableName = entity.getAnnotation(DbTable.class).value();
}
//判斷數(shù)據(jù)庫(kù)是否打開(kāi)
if (!database.isOpen()) {
return false;
}
if (!TextUtils.isEmpty(createTable(entity,tableName))) {
//執(zhí)行建表語(yǔ)句
database.execSQL(createTable(entity,tableName));
}
cacheMap = new HashMap<>();
//緩存維護(hù)映射關(guān)系
initCacheMap();
isInit = true;
}
return isInit;
}
/**
* 維護(hù)映射關(guān)系
*/
private void initCacheMap() {
String sql="select * from "+this.tableName+" limit 1 , 0";
Cursor cursor=null;
try {
cursor=database.rawQuery(sql,null);
//表的列名數(shù)組
String[] columnNames = cursor.getColumnNames();
//拿到Filed數(shù)組
Field[] colmunFields = entityClass.getFields();
for (Field filed : colmunFields) {
//設(shè)置私有可以訪問(wèn)
filed.setAccessible(true);
}
//開(kāi)始找對(duì)應(yīng)關(guān)系
for (String columnName : columnNames) {
//如果找到對(duì)應(yīng)的Field就賦值給他
Field columnFiled=null;
for (Field filed : colmunFields) {
String filedName="";
if(filed.getAnnotation(DbFiled.class)!=null){
filedName=filed.getAnnotation(DbFiled.class).value();
}else{
filedName=filed.getName();
}
//如果表的列名等于了成員變量的注解名字
if(columnName.equals(filedName)){
columnFiled=filed;
break;
}
}
//找到了對(duì)應(yīng)關(guān)系
if(columnFiled!=null){
cacheMap.put(columnName,columnFiled);
}
}
}catch (Exception e){
e.printStackTrace();
}finally {
//關(guān)閉游標(biāo)
cursor.close();
}
}
@Override
public Long insert(T entity) {
Map<String,String> map=getValues(entity);
ContentValues values=getContentValues(map);
Long insert = database.insert(tableName, null, values);
return insert;
}
@Override
public void insert(List<T> entity) {
//批量插入采用事務(wù)
database.beginTransaction();
for (T data : entity) {
insert(data);
}
database.setTransactionSuccessful();
database.endTransaction();
}
@Override
public int update(T entity, T where) {
int result=-1;
Map<String, String> values = getValues(entity);
//將條件對(duì)象轉(zhuǎn)成map
Map<String, String> whereValue = getValues(where);
Condition codition=new Condition(whereValue);
ContentValues contentValues = getContentValues(values);
result=database.update(tableName,contentValues,codition.getWhereClause(),codition.getWhereArgs());
return result;
}
@Override
public int delete(T entity) {
Map<String, String> values = getValues(entity);
Condition condition=new Condition(values);
int result=database.delete(tableName,condition.getWhereClause(),condition.getWhereArgs());
return result;
}
@Override
public List<T> query(T where, String orderBy, Integer startIndex, Integer limit) {
Map<String, String> values = getValues(where);
String limitString="";
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);
//關(guān)閉游標(biāo)
cursor.close();
return result;
}
@Override
public List<T> query(T where) {
return query(where,null,null,null);
}
/**
* 根據(jù)查詢條件獲取查詢結(jié)果
* @param cursor 數(shù)據(jù)庫(kù)游標(biāo)
* @param where 查詢條件
* @return 根據(jù)查詢條件返回的結(jié)果
*/
private List<T> getResult(Cursor cursor, T where) {
List list=new ArrayList();
Object item;
while (cursor.moveToNext()){
try {
item=where.getClass().newInstance();
//遍歷緩存的映射關(guān)系
Iterator<Map.Entry<String, Field>> iterator = cacheMap.entrySet().iterator();
while (iterator.hasNext()){
Map.Entry<String, Field> entry = iterator.next();
//得到列名
String colomunName = entry.getKey();
//然后以列名拿到 列名在游標(biāo)的位置
Integer columnIndex = cursor.getColumnIndex(colomunName);
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 (Exception e){
e.printStackTrace();
}
}
return list;
}
/**
* 封裝修改語(yǔ)句
*/
class Condition{
//查詢條件
//username=? && passwrod=?
private String whereClause;
private String [] whereArgs;
public Condition(Map<String,String> whereClause){
List list=new ArrayList();
StringBuilder sb=new StringBuilder();
sb.append(" 1=1 ");
Set<String> keys = whereClause.keySet();
Iterator<String> iterator = keys.iterator();
while (iterator.hasNext()){
String key = iterator.next();
String value = whereClause.get(key);
if(value!=null){
//拼接條件查詢語(yǔ)句
sb.append(" and "+key+" =?");
list.add(value);
}
}
this.whereClause=sb.toString();
this.whereArgs= (String[]) list.toArray(new String[list.size()]);
}
public String getWhereClause() {
return whereClause;
}
public String[] getWhereArgs() {
return whereArgs;
}
}
/**
* 將緩存的map數(shù)據(jù)轉(zhuǎn)成ContentValues
* @param map
* @return
*/
private ContentValues getContentValues(Map<String, String> map) {
ContentValues contentValues=new ContentValues();
Set<String> 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;
}
/**
* 根據(jù)數(shù)據(jù)對(duì)象和數(shù)據(jù)庫(kù)表字段膏执，將數(shù)據(jù)轉(zhuǎn)成key value的形式
* @param entity 數(shù)據(jù)對(duì)象
* @return 轉(zhuǎn)換后獲取到的數(shù)據(jù)
*/
private Map<String,String> getValues(T entity) {
Map<String,String> result=new HashMap<>();
//遍歷緩存數(shù)據(jù)驻售，并進(jìn)行映射
Iterator<Field> fieldIterator = cacheMap.values().iterator();
while (fieldIterator.hasNext()){
Field colmunToFiled = fieldIterator.next();
String cacheKey="";
String cacheValue="";
if(colmunToFiled.getAnnotation(DbFiled.class)!=null){
cacheKey=colmunToFiled.getAnnotation(DbFiled.class).value();
}else{
cacheKey=colmunToFiled.getName();
}
try {
if(null==colmunToFiled.get(entity)){
continue;
}
cacheValue=colmunToFiled.get(entity).toString();
}catch (Exception e){
e.printStackTrace();
}
result.put(cacheKey,cacheValue);
}
return result;
}
/**
* 創(chuàng)建表
*
* @return
*/
protected abstract String createTable(Class<T> entity,String tableName);
}
增、刪更米、改欺栗、查功能的實(shí)現(xiàn)都在BaseDao類中，并提供了一個(gè)createTable建表的抽象方法征峦，由具體子類來(lái)實(shí)現(xiàn)迟几；
public class UserDao<T> extends BaseDao<T> {
@Override
protected String createTable(Class<T> entity,String tableName) {
//創(chuàng)建表 動(dòng)態(tài)創(chuàng)建數(shù)據(jù)庫(kù)表
StringBuffer sb = new StringBuffer();
sb.append("create table if not exists ")
.append(tableName)
.append(" ( id integer primary key autoincrement, ");
Field[] fields = entity.getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
String name = field.getName();
String type = field.getType().getSimpleName();
//進(jìn)行轉(zhuǎn)換 int->integer string->text
sb.append(name).append(getColumnType(type)).append(", ");
}
sb.replace(sb.length() - 2, sb.length(), ")");
String createTableSql = sb.toString();
Log.e("create table", "表語(yǔ)句-->" + createTableSql);
// return "create table if not exists tb_user(username varchar(20),password varchar(20))";
return createTableSql;
}
private String getColumnType(String type) {
String value = "";
if (type.contains("String")) {
value = " text";
} else if (type.contains("int")) {
value = " integer";
} else if (type.contains("boolean")) {
value = " boolean";
} else if (type.contains("float")) {
value = " float";
} else if (type.contains("double")) {
value = " double";
} else if (type.contains("char")) {
value = " varchar";
} else if (type.contains("long")) {
value = " long";
}
return value;
}
@Override
public List<T> query(String sql) {
//用于多條件查詢
return null;
}
}
UserDao具體的建表類，這里建表采用的是動(dòng)態(tài)建表語(yǔ)句建表栏笆；
@DbTable("tb_user")
public class User {
public String username;
public String password;
public User(){
//這里需要提供無(wú)參構(gòu)造类腮，用于反射
}
public User(String name,String pwd){
this.password=pwd;
this.username=name;
}
}
下面就是具體的調(diào)用：
public class MainActivity extends AppCompatActivity {
private static final String TAG = "MainActivity";
IBaseDao<User> baseDao;
private static final int REQUEST_DODE=1000;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
PermissionHelper.with(MainActivity.this).
requestPermission(new String[]{Manifest.permission.READ_EXTERNAL_STORAGE,
Manifest.permission.WRITE_EXTERNAL_STORAGE}).
requestCode(REQUEST_DODE).
request();
}
@PermissionSuccess(requestCode =REQUEST_DODE)
private void dbSuccess(){
baseDao= BaseDaoFactory.getInstance().getDataHelper(UserDao.class,User.class);
}
@PermissionFail(requestCode =REQUEST_DODE)
private void dbFail(){
Toast.makeText(this, "sd卡申請(qǐng)權(quán)限", Toast.LENGTH_SHORT).show();
}
@Override
public void onRequestPermissionsResult(int requestCode, @NonNull String[] permissions, @NonNull int[] grantResults) {
super.onRequestPermissionsResult(requestCode, permissions, grantResults);
PermissionHelper.requestPermissionsResult(this,requestCode,permissions,grantResults);
}
/**
* 插入數(shù)據(jù)
* @param view
*/
public void insertData(View view){
User user=new User("李四","1234567898");
baseDao.insert(user);
}
/**
* 插入批量數(shù)據(jù)
* @param view
*/
public void insertList(View view){
long startTime = System.currentTimeMillis();
List<User> list=new ArrayList<>();
for(int i=0;i<5000;i++){
User user=new User("張三","1234567890");
list.add(user);
}
baseDao.insert(list);
long endTime = System.currentTimeMillis();
Log.e("time","耗時(shí):"+(endTime-startTime));
}
/**
* 更新數(shù)據(jù)庫(kù)指定數(shù)據(jù)
* @param view
*/
public void updatDB(View view){
User user=new User();
user.username="李四";
User where=new User();
where.username="王五";
baseDao.update(where,user);
}
/**
* 刪除數(shù)據(jù)庫(kù)指定數(shù)據(jù)
* @param view
*/
public void deleteDB(View view){
User where=new User();
where.username="王五";
baseDao.delete(where);
}
/**
* 數(shù)據(jù)庫(kù)查詢數(shù)據(jù)
* @param view
*/
public void queryDB(View view){
User user=new User();
user.username="李四";
List<User> query = baseDao.query(user);
Log.e(TAG,"數(shù)據(jù)庫(kù)查詢數(shù)據(jù)"+query.size());
for (User user1 : query) {
Log.e(TAG,"姓名:"+user1.username+"密碼:"+user1.password);
}
}
/**
* 數(shù)據(jù)庫(kù)分頁(yè)查詢數(shù)據(jù)
* @param view
*/
public void queryDB1(View view){
User user=new User();
user.username="張三";
List<User> query = baseDao.query(user,"",10,20);
Log.e(TAG,"數(shù)據(jù)庫(kù)查詢數(shù)據(jù)"+query.size());
for (User user1 : query) {
Log.e(TAG,"姓名:"+user1.username+"密碼:"+user1.password);
}
}
}
