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中的指定记录 |
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);
}
@Override
public 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());
}
@Override
public 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();
}
@Override
public 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);
}
@Override
public 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);
}
@Override
public void delete(Word word){
long id=word.getId();
database.delete(WordSQLiteHelper.TABLE_WORDS,WordSQLiteHelper.COLUMN_ID+"="+id,null);
}
@Override
public 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;
}
@Override
public 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;
}
@Override
public 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();
}
}