4.17 一个SQLite的小例子

 一个使用Sqlite数据库示例

本例中，使用SQLite数据库作为数据存储仓库。

示例中，使用DBHelper继承SQLiteOpenHelper进行数据库中数据表的新建删除及数据库的获得，另外利用DBHelper进行数据在数据表中的CRUD。

示例中，界面上提供ListView+SimpleCursorAdapter来呈现数据表中的数据

示例中，提供对数据库文件的备份和恢复。当备份/恢复结束或过程中出现异常，会以通知的形式提示用户。

示例中，数据库文件的备份恢复会采用异步方式进行，具体的IO操作采用nio的channel进行

示例中，也可以使用ContentProvider来对数据库的数据表进行CRUD操作

示例中，也使用了CursorLoader这种方式来加载数据库。对于一些大型数据库的加载，如果在主线程上使用getReadableDatabase/getWriteableDatabase方式打开数据库时，可能造成ANR，所以利用CursorLoader的方式来加载数据库，加载完毕后将内容填充到ListView中进行显示

代码展示：

MainActivity的界面：

<LinearLayoutxmlns:android="http://schemas.android.com/apk/res/android"

   xmlns:tools="http://schemas.android.com/tools"

   android:layout_width="match_parent"

   android:layout_height="match_parent"

   tools:context=".MainActivity"

   android:orientation="vertical">

   

   <LinearLayout

       android:layout_width="match_parent"

       android:layout_height="wrap_content"

       >

       <TextView

           android:layout_width="wrap_content"

           android:layout_height="wrap_content"

           android:text="暱稱："

           android:typeface="monospace"

           android:layout_marginTop="10dp"

           android:layout_marginLeft="5dp"

           />

       <EditText

           android:layout_width="0dp"

           android:layout_height="wrap_content"

           android:layout_weight="100"

           android:hint="新的暱稱"

           android:typeface="monospace"

           android:layout_marginTop="10dp"

           android:layout_marginLeft="5dp"

            android:layout_marginRight="5dp"

           android:paddingLeft="5dp"

           />

   </LinearLayout>

   <Button

       android:id="@+id/btn_add"

       android:layout_width="match_parent"

       android:layout_height="wrap_content"

        android:layout_marginTop="10dp"

       android:text="增加新的暱稱"

       android:padding="5dp"

       />

          <Button

       android:id="@+id/btn_save"

       android:layout_width="match_parent"

       android:layout_height="wrap_content"

       android:layout_marginTop="10dp"

       android:text="備份數據庫"

       android:padding="5dp"

       />

           <Button

       android:id="@+id/btn_provider"

       android:layout_width="match_parent"

       android:layout_height="wrap_content"

       android:layout_marginTop="10dp"

       android:text="試試provider"

       android:padding="5dp"

       />

           <ListView

               android:id="@+id/lv_person"

                     android:layout_width="match_parent"

                    android:layout_height="wrap_content"

                     android:layout_marginTop="10dp"

               ></ListView>

   

</LinearLayout>

界面如图所示：

在EditText中输入内容后，点击“增加新的昵称”，会将EditText中的内容添加到Sqlite数据库，并且新增数据会刷新到ListView中。

点击“备份数据库”后，将数据库文件保存到SD卡上的指定文件夹下

点击“试试Provider”按钮后，会显示ProviderActivity。在ProviderActivity中，会使用CursorLoader加载数据库文件，并使用ContentProvider对数据表进行CRUD

在进行MainActivity进行具体编码之前，因为MainActivity中需要用到大量的其它相关自定义类内容，所以先从其它自定义类开始。

常量接口com.example.constant.Constant：

/**

 *

 * 該接口中會定義若干關於數據庫的常量值，方便調用使用

 *

 *@author piglite

 *

 */

public interface Constant {

         StringDB_NAME="mydb";//数据库名称

         intDB_VERSION=1;//数据库版本

         StringTABLE_NAME="t_person";//数据表名称

         StringCOLUMN_ID="_id";//列名

         StringCOLUMN_NAME="name";//列名

         StringCOLUMN_DATE="date";//列名

         StringCREATE_TABLE="CREATE TABLE IF NOT EXISTS "+TABLE_NAME+ " ("+

                            COLUMN_ID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+

                            COLUMN_NAME+ " TEXT NOT NULL, "+

                            COLUMN_DATE+ " DATE NOT NULL" +

                            ") "; //建表语句

         StringDROP_TABLE="DROP TABLE IF EXISTS "+TABLE_NAME;//删表语句

}

创建SqliteOpenHelper的继承类DBHelper，作为数据库操作的辅助类：

/**

 *

 *SQLite數據庫的輔助類。負責數據庫中數據表的新建、刪除以及數據庫示例的獲得等操作

 *@author piglite

 *

 */

public class DBHelper extendsSQLiteOpenHelper implements Constant{

        

         publicDBHelper(Context context){

                   super(context,DB_NAME, null, DB_VERSION);

         }

 

         @Override

         publicvoid onCreate(SQLiteDatabase db) {

                   db.execSQL(CREATE_TABLE);

                   //創建數據表后插入兩條數據作為測試數據

                   ContentValuescv1=new ContentValues();

                   cv1.put(COLUMN_NAME,"大寶貝貓");

                   cv1.put(COLUMN_DATE,getNow());

                   db.insert(TABLE_NAME,null, cv1);

                   ContentValuescv2=new ContentValues();

                   cv2.put(COLUMN_NAME,"金絲貓");

                   cv2.put(COLUMN_DATE,getNow());

                   db.insert(TABLE_NAME,null, cv2);

         }

 

         @Override

         publicvoid onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

                   db.execSQL(DROP_TABLE);

                   onCreate(db);

         }

         /**

          * 將ContentValues插入數據表時，計算插入時間的工具方法

          * @return ContentValues插入數據表時的日期，該日期用字符串形式表示

          */

         publicString getNow(){

                   returnnew SimpleDateFormat("yyyy-MM-dd").format(new Date());

         }

}

创建一个进行数据库文件IO操作的工具类IOTask。因为IO操作是典型的耗时操作应该异步执行，所以让IOTask继承AsyncTask类：

 

/**

 * 負責數據庫文件的備份和恢復

 * 因為IO操作是耗時操作，因此要異步進行

 * 當執行完IO操作后，根據執行結構回調OnCompleteListener中的對應方法

 *@author piglite

 *

 */

public class IOTask extendsAsyncTask<String, Void, Integer> implements Constant{

         //在後台進行IO操作后的可能結果

         privatestatic final int BACKUP_OK=0;//備份成功

         privatestatic final int RESOTRE_OK=1;//恢復成功

   private static final int ERROR=2;//有異常

   //指定IOTask執行備份（BACKUP）還是恢復（RESTORE）操作

   public static final String BACKUP="backupdbfile";

   public static final String RESTORE="restoredbfile";

   

   private Context mContext;

   private OnCompleteListener mListener;

   /**

    * 當IO操作完成后，根據執行結果，進行回調處理

    * @author piglite

    *

    */

   public interface OnCompleteListener{

             void onBackSuccess();

             void onRestoreSuccess();

             void onError(int errorcode);

    }

   //為IOTask提供回調實例

   public void setOnCompleteListener(OnCompleteListener listener){

             mListener=listener;

    }

         publicIOTask(Context ctx) {

                   super();

                   this.mContext=ctx;

         }

        

         @Override

         protectedInteger doInBackground(String... params) {

                   //判斷是要執行備份還是恢復操作

                   Stringcommand=params[0];

                   //IO執行結果的默認值

                   intresult=ERROR;

                   //獲得指定數據庫的路徑

                   FiledbPath=mContext.getDatabasePath(DB_NAME);

                   //獲得SD上存放目標文件的文件夾

                   FiletargetPath=newFile(Environment.getExternalStorageDirectory(),"dbbak");

                   if(!targetPath.exists())targetPath.mkdirs();

                   //獲得目標文件

                   FiletargetFile=new File(targetPath,dbPath.getName());

                  

                   if(BACKUP.equals(command)){

                            try{

                                     //注意，createNewFile可能返回true也可能返回false

                                     //返回true意味著目標文件夾中沒有該文件，此時新建并準備被寫入

                                     //返回false意味著目標文件夾中有該文件，此時準備將原文件覆蓋

                                     targetFile.createNewFile();

                                     fileCopy(dbPath,targetFile);

                                     result=0;

                            }catch (Exception e) {

                                     e.printStackTrace();

                                     result=2;

                            }

                           

                   }elseif(RESTORE.equals(command)){

                            try{

                                     if(!targetFile.exists()){

                                               thrownew RuntimeException("沒有存儲的文件");

                                     }

                                     dbPath.createNewFile();

                                     fileCopy(targetFile,dbPath);

                                     result=1;

                            }catch (Exception e) {

                                     e.printStackTrace();

                                     result=2;

                            }

                   }else{

                            result=2;

                   }

                  

                   returnresult;

         }

         /**

          * 利用nio的Channel來進行實際IO操作

          * @param dbPath 源文件（如果是備份數據庫的操作，源文件就是數據庫文件）

          * @param targetFile 目標文件（如果是備份數據庫的操作，目標文件就是SD卡上的文件）

          * @throws Exception IO過程錯誤

          */

         privatevoid fileCopy(File dbPath, File targetFile) throws Exception {

                   FileChannelout=new FileOutputStream(dbPath).getChannel();

                   FileChannelin=new FileInputStream(targetFile).getChannel();

                   in.transferTo(0,in.size(), out);

                   out.close();

                   in.close();

         }

         /**

          * 當異步的IO操作完成后，會返回代表操作結果的result值

          * 根據result值來調用回調接口的相關方法

          */

         @Override

         protectedvoid onPostExecute(Integer result) {

                   super.onPostExecute(result);

                   if(mListener==null){

                            thrownew RuntimeException("未指定有效的回調接口");

                   }

                   switch(result) {

                   caseBACKUP_OK:

                            mListener.onBackSuccess();

                            break;

                   caseRESOTRE_OK:

                            mListener.onRestoreSuccess();

                            break;

                   default:

                            mListener.onError(result);

                            break;

                   }

         }

 

}

在使用IOTask的时候，要传入OnCompleteListener实例，否则会引发异常。

写一个ContentProvider的继承者，用来查询我们的数据库：

/**

 *

 * 用來提供對數據庫訪問的“接口”

 *@author piglite

 *

 */

public class MyDBProvider extendsContentProvider implements Constant {

         //數據庫的uri（注意，必須要有content://前綴）

         publicstatic final Uri CONTENT_URI =

                   Uri.parse("content://com.example.mydbprovider/person");

         privatestatic final int PERSON = 1;

         privatestatic final int PERSON_ID = 2;

         //添加一個UriMatcher，用來判斷用戶查詢的uri是什麼

         //如果是content://com.example.mydbprovider/person，就認為用戶是想對多條數據發起訪問

         //如果是content://com.example.mydbprovider/person/xxxid，就認為用戶是想對指定的數據發起訪問

         privatestatic final UriMatcher matcher = new UriMatcher(UriMatcher.NO_MATCH);

         static{

                   //設定matcher可匹配的uri后，利用Matcher的match方法

                   //就根據用戶提供的uri，判斷用戶欲訪問的數據內容是多條數據還是特定某條數據

                   matcher.addURI(CONTENT_URI.getAuthority(),"person", PERSON);

                   matcher.addURI(CONTENT_URI.getAuthority(),"person/#", PERSON_ID);

         }

         privateSQLiteDatabase mDb;

 

         @Override

         publicboolean onCreate() {

                   DBHelper_helper = new DBHelper(getContext());

                   mDb= _helper.getWritableDatabase();

                   returntrue;

         }

 

         @Override

         publicCursor query(Uri uri, String[] projection, String selection,

                            String[]selectionArgs, String sortOrder) {

                   //根據用戶query時提供的uri來判斷用戶是想訪問多條數據還是特定的某條數據

                   intresult = matcher.match(uri);

                   switch(result) {

                   //如果是訪問多條數據時

                   casePERSON:

                            returnmDb.query(TABLE_NAME, projection, selection, selectionArgs,

                                               null,null, sortOrder);

                   //如果是訪問指定的id的數據時

                   casePERSON_ID:

                            returnmDb

                                               .query(TABLE_NAME,projection, "_id=?",

                                                                 newString[] { uri.getLastPathSegment() }, null,

                                                                 null,null);

                   //如果查詢提供的uri不符合matcher裡面添加的模式，則直接返回null

                   default:

                            returnnull;

                   }

 

         }

 

         @Override

         publicString getType(Uri uri) {

                   returnnull;

         }

 

         @Override

         publicUri insert(Uri uri, ContentValues values) {

                   long_id = mDb.insert(TABLE_NAME, null, values);

                   //如果數據庫數據表插入數據成功，將該條數據的_id值拼接為一個uri作為方法的返回值返回

                   if(_id > 0)

                            returnuri.withAppendedPath(uri, String.valueOf(_id));

                   else

                            returnnull;

         }

 

         @Override

         publicint delete(Uri uri, String selection, String[] selectionArgs) {

                   intresult = matcher.match(uri);

                   switch(result) {

                   //刪除整張數據表的數據

                   casePERSON:

                            returnmDb.delete(TABLE_NAME, selection, selectionArgs);

                   //刪除數據表中指定的某條數據

                   casePERSON_ID:

                            returnmDb.delete(TABLE_NAME, "_id=?",

                                               newString[] { uri.getLastPathSegment() });

                   //提供的uri不符合matcher中添加的uri模式

                   default:

                            return0;

                   }

         }

 

         @Override

         publicint update(Uri uri, ContentValues values, String selection,

                            String[]selectionArgs) {

                   intresult = matcher.match(uri);

                   switch(result) {

                   //更新數據表中的多條數據

                   casePERSON:

                            returnmDb.update(TABLE_NAME, values, selection, selectionArgs);

                   //更新數據表中的某條數據

                   casePERSON_ID:

                            returnmDb.update(TABLE_NAME, values, "_id=?",

                                               newString[] { uri.getLastPathSegment() });

                   //提供的uri不符合matcher中添加的uri模式                    

                   default:

                            return0;

                   }

         }

 

}

Provider要在AndroidManifest文件中进行注册，注册的时候必须要提供name和authorities两个属性值

<provider

           android:name="com.example.util.MyDBProvider"

           android:authorities="com.example.mydbprovider" >

       </provider>

这个Provider的authorities与类中定义的CONTENT_URI常量的内容要对应上。

最后是在两个Activity中加载呈现数据。

MainActivity

MainActivity提供3个按钮，第一个按钮为向数据表中添加新的数据；第二个按钮为备份整个数据库；第三个按钮为跳转到另一个Activity。它还有一个ListView，利用SimpleCursorAdapter来加载数据库得到的Cursor对象到ListView中显示。

public class MainActivity extends Activityimplements Constant, OnItemClickListener{

         @ViewInject(R.id.et_name)

         privateEditText mEtName;//輸入新的暱稱

         @ViewInject(R.id.btn_add)

         privateButton mBtnAdd;//添加新暱稱到數據庫中

         @ViewInject(R.id.btn_save)

         privateButton mBtnSave;//保存數據庫文件到SD卡

         @ViewInject(R.id.btn_provider)

         privateButton mBtnProvider;//跳轉到另一個Activity

         @ViewInject(R.id.lv_person)

         privateListView mListView;//將Cursor中的數據加載到ListView中顯示

        

         privateSQLiteDatabase mDb;

         privateDBHelper mHelper;

         privateSimpleCursorAdapter mAdapter;

         privateCursor mCursor;

 

         @Override

         protectedvoid onCreate(Bundle savedInstanceState) {

                   super.onCreate(savedInstanceState);

                   setContentView(R.layout.activity_main);

                   ViewUtils.inject(this);

                   mHelper=newDBHelper(this);

         }

        

         @Override

         protectedvoid onResume() {

                   super.onResume();

                   mDb=mHelper.getWritableDatabase();

                   String[]columns=new String[]{COLUMN_ID,COLUMN_NAME,COLUMN_DATE};

                   //直接利用SqliteDatebase提供的方法來查詢獲得結果集

                   mCursor=mDb.query(TABLE_NAME,null,null,null,null,null,null);

                   //將結果集中每一條數據中的指定列信息加載到item的指定佈局中

                   mAdapter=newSimpleCursorAdapter(this, android.R.layout.simple_list_item_1,

                                     mCursor,

                                     newString[]{COLUMN_NAME,COLUMN_DATE},

                                     newint[]{android.R.id.text1,android.R.id.text2});

                   //為ListView綁定適配器

                   mListView.setAdapter(mAdapter);

                   //為ListView的條目添加點擊事件監聽器

                   mListView.setOnItemClickListener(this);

         }

        

         @OnClick({R.id.btn_add,R.id.btn_provider,R.id.btn_save})

         publicvoid doClick(View v){

                   switch(v.getId()) {

                   //將EditText中輸入的內容添加到數據庫中

                   caseR.id.btn_add:

                            ContentValuescv=new ContentValues();

                            cv.put(COLUMN_NAME,mEtName.getText().toString());

                            cv.put(COLUMN_DATE,mHelper.getNow());

                            mDb.insert(TABLE_NAME,null, cv);

                            //插入完畢后，重新獲得最新的結果集

                            mCursor.requery();

                            //通知綁定該結果集的適配器，去刷新與該適配器綁定的ListView

                            mAdapter.notifyDataSetChanged();

                            mEtName.setText("");

                            break;

                   caseR.id.btn_save:

                            IOTasktask=new IOTask(this);

                            //在使用IOTask的時候要指定OnCompleteListener實例

                            task.setOnCompleteListener(newOnCompleteListener() {

                                    

                                     @Override

                                     publicvoid onRestoreSuccess() {

                                              

                                     }

                                    

                                     @Override

                                     publicvoid onError(int errorcode) {

                                               makeNotification("數據庫操作錯誤");

                                     }

                                    

                                     @Override

                                     publicvoid onBackSuccess() {

                                               makeNotification("數據庫備份完畢");

                                     }

                            });

                            task.execute(IOTask.BACKUP);

                            break;

                   //點擊跳轉到OtherActivity界面。OtherListView將採用LoaderManager異步加載數據庫文件，并使用ContentResolver

                   //訪問數據庫綁定的ContentProvider來獲得數據

                   caseR.id.btn_provider:

                            startActivity(newIntent(this,OtherActivity.class));

                            break;

                   }

         }

         //利用通知來提示用戶

         protectedvoid makeNotification(String string) {

                   /**

                    * 使用通知的步驟：

                    * 1 獲得NotificationManager

                    * 2 構建Notification對象

                    * 3 如果有必要，設置Notification的相關屬性（flag）

                    * 4 設置Notification用的PendingIntent

                    * 5 用NotificationManager呈現Notification

                    */

                   //1

                   NotificationManagermanager=(NotificationManager) getSystemService(Context.NOTIFICATION_SERVICE);

                   //2

                   Notificationn = new Notification(R.drawable.ic_launcher, "數據庫操作信息",System.currentTimeMillis());

                   //4

                   n.setLatestEventInfo(this,"提示消息", string, PendingIntent.getActivity(

                                     this,0, new Intent(this,MainActivity.class), 0));

                   //5

                   manager.notify(0,n);

         }

 

         @Override

         publicvoid onItemClick(AdapterView<?> parent, View view, int position,

                            longid) {

                   //因為數據集中的數據是一條條按順序顯示在ListView中的，因此點擊的item在ListView中的位置也就是數據在數據集中的位置

                   mCursor.moveToPosition(position);

                   //獲得該位置數據的_id值

                   int_id=mCursor.getInt(mCursor.getColumnIndex(COLUMN_ID));

                   //刪除該條數據

                   mDb.delete(TABLE_NAME,"_id=?", new String[]{String.valueOf(_id)});

                   //刪除完畢后，重新查詢獲得結果集

                   mCursor.requery();

                   //通知Adaptercursor發生了變化，去刷新ListView的內容

                   mAdapter.notifyDataSetChanged();

         }

}

最后是利用Loader来加载数据和利用ContentResolver来访问数据的OtherActivity

public class OtherActivity extendsFragmentActivity implementsConstant,OnItemClickListener,LoaderCallbacks<Cursor>{

         @ViewInject(R.id.lv_other)

         privateListView mListView;//用來呈現數據表中的數據

         privateSimpleCursorAdapter mAdapter;//用來管理數據表中的數據

         privateContentResolver mCr;//用來通過數據庫的ContentProvider來實現數據的CRUD

         @Override

         protectedvoid onCreate(Bundle savedInstanceState) {

                   super.onCreate(savedInstanceState);

                   setContentView(R.layout.activity_other);

                   ViewUtils.inject(this);

                   //初始化Loader時需要提供一個該Loader的id值，以及實現一個LoaderManager的LoaderCalbacks<Cursor>的實例

                   getSupportLoaderManager().initLoader(1001,null, this);

                   //獲得ContentResolver，用來通過ContentProvider來訪問數據表中的數據

                   mCr=getContentResolver();

                   //獲得一個SimpleCursorAdapter對象。

                   //注意該SimpleCursorAdapter的Cursor參數為null。

                   //初次與ListView進行綁定時，ListView中呈現的數據並不來自SimpleCursorAdapter適配器

                   //但是以後依然可以通過適配器獲得與ListView綁定的數據集，儘管這個數據集是來自CursorLoader

                   mAdapter=newSimpleCursorAdapter(this, android.R.layout.simple_list_item_1, null, newString[]{COLUMN_NAME}, new int[]{android.R.id.text1});

                   mListView.setAdapter(mAdapter);

                   mListView.setOnItemClickListener(this);

         }

         @Override

         publicLoader<Cursor> onCreateLoader(int arg0, Bundle arg1) {

                   CursorLoaderloader = new CursorLoader(this,

                                     MyDBProvider.CONTENT_URI,

                                     newString[]{COLUMN_ID,COLUMN_NAME,COLUMN_DATE},

                                     null,

                                     null,

                                     null);

                   returnloader;

         }

         @Override

         publicvoid onLoadFinished(Loader<Cursor> arg0, Cursor arg1) {

                   mAdapter.swapCursor(arg1);

         }

         @Override

         publicvoid onLoaderReset(Loader<Cursor> arg0) {

                   mAdapter.swapCursor(null);

         }

         @Override

         publicvoid onItemClick(AdapterView<?> parent, View view, int position,

                            long id) {

                   Cursorcursor = mAdapter.getCursor();

                   cursor.moveToPosition(position);

                   int_id=cursor.getInt(cursor.getColumnIndex(COLUMN_ID));

                   Cursorresult=mCr.query(Uri.withAppendedPath(MyDBProvider.CONTENT_URI,String.valueOf(_id)),

                                     newString[]{COLUMN_ID,COLUMN_NAME,COLUMN_DATE}, 

                                     "_id=?",

                                     newString[]{String.valueOf(_id)},

                                     null);

                   result.moveToFirst();

                   Stringstr=String.format("您點擊的內容是,%s %s %s",

                            result.getInt(0),result.getString(1),result.getString(2));

                   Toast.makeText(this,str, 0).show();

         }

 

}