Android SQLite数据存储的通用设计
SQLite用于存储一些数据量较多,结构比较复杂情况,使用的时候只需要实现SQLiteOpenHelper,在onCreate创建数据表,onUpgrade做升级处理
通过Helper实例对DB进行数据处理,例如,database = dbHelper.getWritableDatabase(); 获取DB对象进行插入,更新,删除操作,dbHelper.getReadableDatabase()
进行数据查询,在此不必多说,这样实现一个数据库并不复杂,但是对不同对象存储操作还需要分别各自去自己实现,比较麻烦,能不能用一种通用设计实现呢?
其实存入DB内的数据都是要分隔成基本String, int , long, double等,在android中可以使用数据集ContentValues进行存储,ContentValues可以存储基本类型,类似于Map
ContentValues是可直接用于SQLiteDatabase,ContentProvider中进行批量处理,这是android为此设计的,在SQLiteDatabase我们使用ContentValues是非常简单的。
好了下面我们需要对通用数据操作定义一种规则,我们只需要传入tableName,whereArgs(筛选条件),ContentValues即可实现insert, quere,delete,update功能
接口定义如下:
public interface IDBDelegate { public boolean insertData(String tableName,String nullColumnHack,ContentValues values); public boolean deleteData(String tableName,String whereClause, String[] whereArgs); public boolean updateData(String tableName,ContentValues values, String whereClause, String[] whereArgs); //查询单条数据集 public Map<String, String> getRowData(String tableName, String selection, String[] selectionArgs); //查询多条数据集 public List<Map<String, String>> getListData(String tableName, String selection, String[] selectionArgs); }
实现类
public class DBDelegateImpl implements IDBDelegate{ private final Object obj=new Object(); private DBHelper dbHelper=null; public DBDelegateImpl(Context context) { dbHelper=DBHelper.getInstance(context); } @Override public boolean insertData(String tableName, String nullColumnHack,ContentValues values) { synchronized (obj) { boolean flag = false; SQLiteDatabase database = null; long id = -1; try { database = dbHelper.getWritableDatabase(); id = database.insert(tableName, nullColumnHack, values); flag = (id != -1); } catch (SQLException e) { e.printStackTrace(); } finally { if (database != null) { database.close(); } } return flag; } } public int insertBatchData(String tableName, String nullColumnHack,List<ContentValues> values) { int count=0; if(values!=null && values.size()>0){ SQLiteDatabase db = dbHelper.getWritableDatabase(); try { db.beginTransaction(); ContentValues cv = null; for (int i = 0; i < values.size(); i++) { cv = values.get(i); db.insert(tableName, nullColumnHack, cv); } db.setTransactionSuccessful(); }finally { if(db!=null) { db.endTransaction(); } } } return count; } @Override public boolean deleteData(String tableName, String whereClause, String[] whereArgs) { synchronized (obj) { boolean flag = false; SQLiteDatabase database = null; int count = 0; try { database = dbHelper.getWritableDatabase(); count = database.delete(tableName, whereClause, whereArgs); flag = (count > 0); } catch (SQLException e) { e.printStackTrace(); } finally { if (database != null) { database.close(); } } return flag; } } @Override public boolean updateData(String tableName, ContentValues values, String whereClause, String[] whereArgs) { synchronized (obj) { boolean flag = false; SQLiteDatabase database = null; int count = 0; try { database = dbHelper.getWritableDatabase(); count = database.update(tableName, values, whereClause, whereArgs); flag = (count > 0); } catch (SQLException e) { e.printStackTrace(); } finally { if (database != null) { database.close(); } } return flag; } } @Override public Map<String, String> getRowData(String tableName, String selection, String[] selectionArgs) { SQLiteDatabase database = null; Cursor cursor = null; Map<String, String> map = new HashMap<String, String>(); try { database = dbHelper.getReadableDatabase(); cursor = database.query(true, tableName, null, selection, selectionArgs, null, null, null, null); //查询单条记录,记录是唯一的,所以第一个参数置为 true. int cols_len = cursor.getColumnCount(); while (cursor.moveToNext()) { for (int i = 0; i < cols_len; i++) { String cols_name = cursor.getColumnName(i); String cols_values = cursor.getString(cursor.getColumnIndex(cols_name)); if (cols_values == null) { cols_values = ""; } map.put(cols_name, cols_values); } } } catch (SQLException e) { e.printStackTrace(); } finally { if(cursor!=null){ cursor.close(); } if (database != null) { database.close(); } } return map; } @Override public List<Map<String, String>> getListData(String tableName, String selection, String[] selectionArgs) { SQLiteDatabase database = null; Cursor cursor = null; List<Map<String, String>> list = new ArrayList<Map<String, String>>(); try { database = dbHelper.getReadableDatabase(); cursor = database.query(false, tableName, null, selection, selectionArgs, null, null, null, null); //查询所有记录,所以有重复的数据也要全部检出,所以第一参数置为false. int cols_len = cursor.getColumnCount(); while (cursor.moveToNext()) { Map<String, String> map = new HashMap<String, String>(); for (int i = 0; i < cols_len; i++) { String cols_name = cursor.getColumnName(i); String cols_values = cursor.getString(cursor.getColumnIndex(cols_name)); if (cols_values == null) { cols_values = ""; } map.put(cols_name, cols_values); } list.add(map); } } catch (SQLException e) { e.printStackTrace(); } finally { if(cursor!=null){ cursor.close(); } if (database != null) { database.close(); } } return list; } }
嗯,比较优雅的实现了通用设计实现,还不错
DBHelper实现:
public class DBHelper extends SQLiteOpenHelper { private final static String DB_NAME = "Program_db"; public final static int DB_VERSION = 1; private static DBHelper mInstance=null; private DBHelper(Context context) { super(context, DB_NAME, null, DB_VERSION); } public static DBHelper getInstance(Context context) { if (mInstance == null) { synchronized (DBHelper.class) { if (mInstance == null) { mInstance = new DBHelper(context); } } } return mInstance; } @Override public void onCreate(SQLiteDatabase db) { final String sql2="CREATE TABLE "+TABLE_SMILFILE+"(" + SID + " INTEGER PRIMARY KEY AUTOINCREMENT," + FILE_VER+" TEXT,"+ FILE_SYNC+" TEXT,"+ FILE_DURATION+" INTEGER,"+ FILE_SRC+" TEXT"+ ");"; // LogUtils.debug("create taskTable", "smilSql="+sql2); db.execSQL(sql2); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // db.execSQL("DROP TABLE IF EXISTS " + TABLE_PROGRAM); } }
使用案例:
public void DBInsertTest(Context ctx){ IDBDelegate dao=new DBDelegateImpl(ctx); ContentValues values=new ContentValues(); values.put("TASK_NAME", "PlayerName"); dao.insertData("table1",null,values); } public void DBUpdateTest(Context ctx,String sid){ IDBDelegate dao=new DBDelegateImpl(ctx); ContentValues values=new ContentValues(); values.put("TASK_NAME", "PlayerName1"); values.put("TASK_TYPE", "Type1"); dao.updateData("table1",values,"sid=?",new String[]{sid}); } public void DBDeleteTest(Context ctx,String sid){ IDBDelegate dao=new DBDelegateImpl(ctx); dao.deleteData("table1","sid=?",new String[]{sid}); } public void DBQureTest(){ List<Map<String, String>> listTypeData=null; //" id = ? ", new String[] { "2" } IDBDelegate dao=new DBDelegateImpl(ctx); listTypeData=dao.getListData(AdsDatabase.TABLE_PROGRAM, AdsDatabase.TASK_TYPE+" = ?", new String[] {String.valueOf(Constance.PLAY_TYPE_INSERT)}); }
2016-08-14 22:20更新说明
以上数据库还可以优化一点处理,在数据库读取与写入时没有必要同步处理(即读取数据库不加锁,对数据库内容修改操作加锁处理)采用读写分离实现,也称为COW模式。这时可能会产生读取是旧数据,修改内容后新数据,无法更新,我们可以使用observe来数据内容变化监听来及时确保数据为最新。其实在多线程并发访问时这种全部通过一个对象锁实现效率很低,读写分离能够明显提高程序效率。
SqlLite一些问题:
1,使用多个SQLiteOpenHelper问题,例如:
// Thread 1 Context context = getApplicationContext(); DatabaseHelper helper = new DatabaseHelper(context); SQLiteDatabase database = helper.getWritableDatabase(); // Thread 2 Context context = getApplicationContext(); DatabaseHelper helper = new DatabaseHelper(context); SQLiteDatabase database = helper.getWritableDatabase();
使用多个DBHelper写入数据时,会发生写入失败问题,程序并不会报异常,Logcat会有一个输出
android.database.sqlite.SQLiteDatabaseLockedException: database is locked
由此可见DBHelper只能有一个实例对象存在,建议使用单例维护
2,关于在SQLite上数据库连接池的问题
学过java web都知道,数据库连接池可以提高程序性能,网站并发访问需要使用,但是在android只能同时存在一个DB connection,即one helper,one connection at the same time,otherwise one fail