android應用之SQLite資料庫

android系統內嵌了SQLite資料庫，優點是處理速度快，佔用記憶體少

在android應用中，圍繞著資料庫的操作主要是通過SQLiteDatabase來完成的，該類封裝了以下方法：

方法	用途
execSQL(sql)	執行sql指令碼，通常用來建立或管理資料庫表格
rawQuery(sql,selectionArgs)	執行select查詢操作(sql語句的方式)
query(..)	執行select查詢操作(api的方式)
insert(table,nullColumnHack,values)	向指定table中新增記錄
update(table,values,whereClause,whereArgs)	更新指定table中的指定記錄
delete(table,whereClause,whereArgs)	刪除指定table中的指定記錄

通過這些方法便可完成對資料庫的增刪改查操作，同時SQLiteDatabase也引入了對事物的支援
beginTransaction()：開啟事物
endTransaction()：提交或復原事物
相對於web應用來講，移動用戶端的本質區別在於系統的訪問是單使用者的，沒有並行作業。因此，在資料庫不存在並發寫操作的前提下，事物的處理是可以不去考慮的。

SQLiteDatabase是通過SQLiteOpenHelper來擷取的，該類是一個抽象類別，開發基於資料庫儲存的android應用時，需要繼承該類，構建出自己的SQLiteOpenHelper執行個體並覆蓋其超類所聲明的抽象方法：
onCreate(SQLiteDatabase db)：用於建立資料庫表格
onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion)：用於更新資料庫表結構
有了SQLiteOpenHelper之後，便可通過其getWritableDatabase()方法擷取SQLiteDatabase對象，用以完成對資料庫的CURD操作。

public class WordSQLiteHelper extends SQLiteOpenHelper{public static final String TABLE_WORDS = "words";public static final String COLUMN_ID = "_id";public static final String COLUMN_ENGLISH = "english";public static final String COLUMN_CHINESE = "chinese";public static final String COLUMN_PHRASE = "phrase";public static final String COLUMN_TRANSLATION = "translation";public static final String COLUMN_LEVEL = "level";private static final String DATABASE_NAME = "words.db";private static final int DATABASE_VERSION = 1;public WordSQLiteHelper(Context context){super(context, DATABASE_NAME, null, DATABASE_VERSION);}@Overridepublic void onCreate(SQLiteDatabase db) {StringBuilder sql=new StringBuilder("create table ").append(TABLE_WORDS).append("(").append(COLUMN_ID).append(" integer primary key autoincrement,").append(COLUMN_ENGLISH).append(" text not null,").append(COLUMN_CHINESE).append(" text not null,").append(COLUMN_PHRASE).append(" text not null,").append(COLUMN_LEVEL).append(" real not null,").append(COLUMN_TRANSLATION).append(" text not null);");db.execSQL(sql.toString());}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {Log.w(WordSQLiteHelper.class.getName(),"Upgrading database from version " + oldVersion + " to "            + newVersion + ", which will destroy all old data");db.execSQL("DROP TABLE IF EXISTS "+TABLE_WORDS);onCreate(db);}}

在做web應用時，為了代碼層次能夠更加清晰，通常將其劃分為3層結構(entity、dao和service)
dao的建立使得Service的編寫變得更加容易，android應用可基於SQLiteDatabase對象構建出自己的Dao執行個體

public class WordDao implements IBaseDao<Word>{private SQLiteDatabase database;private WordSQLiteHelper dbHelper;private String[] allColumns={WordSQLiteHelper.COLUMN_ID,WordSQLiteHelper.COLUMN_ENGLISH,WordSQLiteHelper.COLUMN_CHINESE,WordSQLiteHelper.COLUMN_PHRASE,WordSQLiteHelper.COLUMN_TRANSLATION,WordSQLiteHelper.COLUMN_LEVEL};public WordDao(Context context){dbHelper=new WordSQLiteHelper(context);}public void open(){database=dbHelper.getWritableDatabase();}public void close(){dbHelper.close();}@Overridepublic void create(Word word){ContentValues values = new ContentValues();values.put(WordSQLiteHelper.COLUMN_ENGLISH,word.getEnglish());values.put(WordSQLiteHelper.COLUMN_CHINESE,word.getChinese());values.put(WordSQLiteHelper.COLUMN_PHRASE,word.getPhrase());values.put(WordSQLiteHelper.COLUMN_TRANSLATION,word.getTranslation());values.put(WordSQLiteHelper.COLUMN_LEVEL,word.getLevel());long insertId=database.insert(WordSQLiteHelper.TABLE_WORDS,null,values);word.setId(insertId);}@Overridepublic void update(Word word){ContentValues values = new ContentValues();values.put(WordSQLiteHelper.COLUMN_ENGLISH,word.getEnglish());values.put(WordSQLiteHelper.COLUMN_CHINESE,word.getChinese());values.put(WordSQLiteHelper.COLUMN_PHRASE,word.getPhrase());values.put(WordSQLiteHelper.COLUMN_TRANSLATION,word.getTranslation());values.put(WordSQLiteHelper.COLUMN_LEVEL,word.getLevel());database.update(WordSQLiteHelper.TABLE_WORDS,values,WordSQLiteHelper.COLUMN_ID+"="+word.getId(),null);}@Overridepublic void delete(Word word){long id=word.getId();database.delete(WordSQLiteHelper.TABLE_WORDS,WordSQLiteHelper.COLUMN_ID+"="+id,null);}@Overridepublic List<Word> getAll(){List<Word> words=new ArrayList<Word>();Cursor cursor = database.query(WordSQLiteHelper.TABLE_WORDS,        allColumns, null, null, null, null, null);//Cursor cursor=database.rawQuery("select * from "+WordSQLiteHelper.TABLE_WORDS,new String[]{});cursor.moveToFirst();while(!cursor.isAfterLast()){words.add(encapsulate(cursor));cursor.moveToNext();}cursor.close();return words;}@Overridepublic List<Word> findOnePage(int offset,int num){List<Word> words=new ArrayList<Word>();Cursor cursor = database.query(WordSQLiteHelper.TABLE_WORDS,        allColumns, null, null, null, null, null);cursor.moveToFirst();cursor.move(offset);int i=0;while(!cursor.isAfterLast()){if(i==num){break;}words.add(encapsulate(cursor));cursor.moveToNext();i++;}cursor.close();return words;}@Overridepublic int getCount(){Cursor cursor = database.query(WordSQLiteHelper.TABLE_WORDS,        allColumns, null, null, null, null, null);return cursor.getCount();}private Word encapsulate(Cursor cursor){Word word=new Word();word.setId(cursor.getLong(0));word.setEnglish(cursor.getString(1));word.setChinese(cursor.getString(2));word.setPhrase(cursor.getString(3));word.setTranslation(cursor.getString(4));word.setLevel(cursor.getFloat(5));return word;}}

不同於Hibenrate的處理方式，這裡的dao需要手動開啟和關閉session(通過執行open和close方法)，並且每個dao執行個體只封裝一個session對象。

在web應用中，dao執行個體一般通過spring容器來管理的，引入容器確保了dao在系統中只存在一個執行個體，由於每個dao都封裝了一個session，單一實例的dao也避免了session資源的浪費。
我們可效仿spring的管理方式，編寫自己的dao容器，同時，在容器中聲明clear方法，當系統APP退出時，執行該方法對容器進行銷毀，銷毀容器的同時，關閉dao對應的session。

public class DaoContext {private Map<String,IBaseDao<?>> daoMap;private static DaoContext instance=new DaoContext();private DaoContext(){daoMap=new HashMap<String,IBaseDao<?>>();}public static DaoContext getInstance(){return instance;}public void addDao(IBaseDao<?> dao){String daoName=dao.getClass().getSimpleName();removeDao(daoName);daoMap.put(dao.getClass().getSimpleName(),dao);dao.open();}public void removeDao(String daoName){if(daoMap.containsKey(daoName)){daoMap.remove(daoName);IBaseDao<?> dao=getDao(daoName);dao.close();}}public IBaseDao<?> getDao(String daoName){return daoMap.get(daoName);}public void clear(){for(Entry<String, IBaseDao<?>> entry:daoMap.entrySet()){IBaseDao<?> dao=entry.getValue();dao.close();}daoMap.clear();}}