Android 范例-个人理财工具 之二 启动时初始化数据
Android 实例-个人理财工具 之二 启动时初始化数据sqlite是嵌入式SQL数据库引擎SQLite(SQLite Embeddable
Android 实例-个人理财工具 之二 启动时初始化数据
sqlite是嵌入式SQL数据库引擎SQLite(SQLite Embeddable SQL Database Engine)的一个扩展。SQLite是一个实现嵌入式SQL数据库引擎小型C语言库(C library),实现了独立的,可嵌入的,零配置的SQL数据库引擎。特性包括:事务操作是原子,一致,孤立,并且持久的,即使在系统崩溃和电源故障之后。 零配置——不需要安装和管理。 实现了绝大多数SQL92标准。
?
我在多年前就关注sqlite的发展,非常看好sqlite的前景,因为在移动,嵌入式的应用里面,sqlite具有非常好的特性来满足需求.
早在symbian 9.0 之前,openc 出来后,我就研究sqlite到symbian的移植.后来symbian9.3 nokia就已经集成了sqlite.
至今j2me还不支持sqlite,可以说是个遗憾.
?
现在我们来看看android sqlitedatabase 包里面的关键api
static??????SQLiteDatabase??openOrCreateDatabase(String?path,?SQLiteDatabase.CursorFactory?factory)- 打开数据库
- Cursor??query(String?table,?String[]?columns,?String?selection,?String[]?selectionArgs,?String?groupBy,?String?having,?String?orderBy,?String?limit)执行查询SQL
- void????execSQL(String?sql)
- 执行非查询sql
sdk 1.0 关于cursor和sqlite的相关api对于前面的版本改变很多.
我觉得关键是没了query(String sql)这个简单的方法了.很不爽.
?
不过如果你对新的query方法了解深入点,发现其实也就一样.
我们来看2个例子
//执行select?type,name?from?sqlite_master?where?name='colaconfig' - String?col[]?=?{"type",?"name"?};????????????Cursor?c?=db.query("sqlite_master",?col,?"name='colaconfig'",?null,?null,?null,?null);
- ????????????int?n=c.getCount();
//执行多表查询 - //select?fee,desc?from?acctite?a,bills?b?where?a.id=b.id? String?col2[]?=?{"fee",?"desc"?};
- ????????????Cursor?c2?=db.query("acctitem?a,bills?b",?col,?"a.id=b.id",?null,?null,?null,?null);????????????int?n2=c2.getCount();???????
- ????????????Log.v("cola","c2.getCount="+n2+"");????????????
- ????????????c2.moveToFirst();?????????????int?k?=?0;
- ????????????????while(!c2.isAfterLast()){????????????????String?ss?=?c2.getString(0)?+",?"+?c2.getString(1);??????????????
- ????????????????c2.moveToNext();?????????????????
- ????????????????Log.v("cola","ss="+ss+"");????????????}
?现在来看看我们如何在这个理财工具里面应用它.
我们需要在程序的第一次启动时,创建数据库,然后把基本的表创建好,并且初始化好账目表.
对于上一篇中的initapp方法 ,我们需要改造成
????public?void?initApp(){- ?????????BilldbHelper?billdb=new?BilldbHelper(this);?????????billdb.FirstStart();
- ?????????billdb.close();?????????
- ????}
下面我们给出BilldbHelper.java 代码
package?com.cola.ui;- import?android.content.Context;
- import?android.database.Cursor;import?android.database.sqlite.SQLiteDatabase;
- import?android.util.Log;
- /**?*?Provides?access?to?a?database?of?notes.?Each?note?has?a?title,?the?note
- ?*?itself,?a?creation?date?and?a?modified?data.?*/
- public?class?BilldbHelper?{
- ????private?static?final?String?TAG?=?"Cola_BilldbHelper";
- ????private?static?final?String?DATABASE_NAME?=?"cola.db";??????
- ????SQLiteDatabase?db;????Context?context;
- ????????BilldbHelper(Context?_context)?{
- ????????context=_context;????????db=context.openOrCreateDatabase(DATABASE_NAME,?0,?null);???//创建数据库
- ????????Log.v(TAG,"db?path="+db.getPath());????}
- ????????public?void?CreateTable_acctitem()?{
- ????????try{????????????db.execSQL("CREATE?TABLE?acctitem?("?//创建账目表
- ????????????????????+?"ID?INTEGER?PRIMARY?KEY,"????????????????????+?"PID?integer,"
- ????????????????????+?"NAME?TEXT,"????????????????????+?"TYPE?INTEGER"????????????????
- ????????????????????+?");");????????????Log.v("cola","Create?Table?acctitem?ok");
- ????????}catch(Exception?e){????????????Log.v("cola","Create?Table?acctitem?err,table?exists.");
- ????????}????}
- ????????public?void?CreateTable_bills()?{
- ????????try{????????????db.execSQL("CREATE?TABLE?bills?("
- ????????????????????+?"ID?INTEGER?PRIMARY?KEY,"????????????????????+?"fee?integer,"
- ????????????????????+?"userid?integer,"????????????????????+?"sdate?TEXT,"
- ????????????????????+?"stime?TEXT,"????????????????????+?"desc?TEXT"????????????????
- ????????????????????+?");");????????????Log.v("cola","Create?Table?acctitem?ok");
- ????????}catch(Exception?e){????????????Log.v("cola","Create?Table?acctitem?err,table?exists.");
- ????????}????}
- ????????public?void?CreateTable_colaconfig()?{
- ????????try{????????????db.execSQL("CREATE?TABLE?colaconfig?("
- ????????????????????+?"ID?INTEGER?PRIMARY?KEY,"????????????????????+?"NAME?TEXT"????????????
- ????????????????????+?");");????????????Log.v("cola","Create?Table?colaconfig?ok");
- ????????}catch(Exception?e){????????????Log.v("cola","Create?Table?acctitem?err,table?exists.");
- ????????}????}
- ????????public?void?InitAcctitem()?{
- ????????????????db.execSQL("insert?into?acctitem?values?(100,0,'收入',0)");
- ????????db.execSQL("insert?into?acctitem?values?(100100,100,'工资',0)");????????db.execSQL("insert?into?acctitem?values?(200,0,'支出',1)");
- ????????db.execSQL("insert?into?acctitem?values?(200100,200,'生活用品',1)");????????db.execSQL("insert?into?acctitem?values?(200101,200,'水电煤气费',1)");
- ????????db.execSQL("insert?into?acctitem?values?(200103,200,'汽油费',1)");????????Log.v("cola","insert?into?ok");
- ????????????}
- ????????
- ????public?void?QueryTable_acctitem(){????????
- ????}????
- ????public?void?FirstStart(){??????//如果是第一次启动,就不存在colaconfig这张表.
- ????????try{????????????String?col[]?=?{"type",?"name"?};
- ????????????Cursor?c?=db.query("sqlite_master",?col,?"name='colaconfig'",?null,?null,?null,?null);????????????int?n=c.getCount();
- ????????????if?(c.getCount()==0){????????????????CreateTable_acctitem();
- ????????????????CreateTable_colaconfig();????????????????CreateTable_bills();
- ????????????????InitAcctitem();????????????????
- ????????????}????????????
- ????????????Log.v("cola","c.getCount="+n+"");????????
- ????????????????????????
- ????????}catch(Exception?e){????????????Log.v("cola","e="+e.getMessage());
- ????????}???????????????
- ????}???????
- ????public?void?close(){????????db.close();
- ????}
- }