Android學習筆記(12)————利用SQLiteOpenHelper來管理SQLite資料庫

/********************************************************************************************
 * author：conowen@大鐘                                                                                                                          
 * E-mail：conowen@hotmail.com                                                                                                             
 * http://blog.csdn.net/conowen                                                                                                              
 * 註：本文為原創，僅作為學習交流使用，轉載請標明作者及出處。     

 ********************************************************************************************/

1、SQLiteOpenHelper介紹

通過上篇博文，http://blog.csdn.net/conowen/article/details/7276417，瞭解了SQLite資料庫的相關操作方法，但是一般在實際開發中，為了更加方便地管理、維護、升級資料庫，需要通過繼承SQLiteOpenHelper類來管理SQLite資料庫。

關於SQLiteOpenHelper的官方說明如下：

A helper class to manage database creation and version management.

You create a subclass implementing onCreate(SQLiteDatabase),onUpgrade(SQLiteDatabase,
int, int) and optionallyonOpen(SQLiteDatabase), and this class takes care of opening
the database if it exists, creating it if it does not, and upgrading it as necessary. Transactions are used to make sure the database is always in a sensible state.

This class makes it easy for ContentProvider implementations to defer opening and upgrading the database until first use, to avoid blocking application
startup with long-running database upgrades.

For an example, see the NotePadProvider class in the NotePad sample application, in thesamples/ directory of the SDK.

簡單翻譯：SQLiteOpenHelper可以建立資料庫，和管理資料庫的版本。

在繼承SQLiteOpenHelper的類（extends
SQLiteOpenHelper）裡面，通過複寫onCreate(SQLiteDatabase),onUpgrade(SQLiteDatabase,
int, int) 和onOpen(SQLiteDatabase)（可選）來操作資料庫。

2、SQLiteOpenHelper()的具體用法

建立一個新的class如下所示，onCreate(SQLiteDatabase db)和onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)方法會被自動添加。

/* * @author:conowen * @date:12.2.29  */package com.conowen.sqlite;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class DbHelper extends SQLiteOpenHelper{public DbHelper(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);// TODO Auto-generated constructor stub}@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stub}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stub}}

方法詳解

public SQLiteOpenHelper (Context context, String name, SQLiteDatabase.CursorFactory factory, int version) 

Since: API Level 1

Create a helper object to create, open, and/or manage a database. This method always returns very quickly. The database is not actually created or opened until one ofgetWritableDatabase()
orgetReadableDatabase() is called.

Parameters

context	to use to open or create the database
name	of the database file, or null for an in-memory database
factory	to use for creating cursor objects, or null for the default
version	number of the database (starting at 1); if the database is older, onUpgrade(SQLiteDatabase, int, int) will be used to upgrade the database; if the database is newer, onDowngrade(SQLiteDatabase, int, int) will be used to downgrade the database

參數簡述：

name————表示資料庫檔案名（不包括檔案路徑），SQLiteOpenHelper類會根據這個檔案名稱來建立資料庫檔案。

version————表示資料庫的版本號碼。如果當前傳入的資料庫版本號碼比上一次建立的版本高，SQLiteOpenHelper就會調用onUpgrade()方法。

public DbHelper(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);// TODO Auto-generated constructor stub}

以上是SQLiteOpenHelper 的建構函式，當資料庫不存在時，就會建立資料庫，然後開啟資料庫（過程已經被封裝起來了），再調用onCreate (SQLiteDatabase db)方法來執行建立表之類的操作。當資料庫存在時，SQLiteOpenHelper 就不會調用onCreate (SQLiteDatabase db)方法了，它會檢測版本號碼，若傳入的版本號碼高於當前的，就會執行onUpgrade()方法來更新資料庫和版本號碼。

3、SQLiteOpenHelper的兩個主要方法

3.1、onCreate方法

public abstract void onCreate (SQLiteDatabase db)

Since:
API Level 1

Called when the database is created for the first time. This is where the creation of tables and the initial population of the tables should happen.

Parameters

db	The database.

//這樣就建立一個一個table@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stub String sql = "CREATE  TABLE table_name(_id INTEGER PRIMARY KEY , filename VARCHAR, data TEXT)";db.execSQL(sql);}

3.2、onUpgrade方法

public abstract void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)

Since:
API Level 1

Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.

The SQLite ALTER TABLE documentation can be found
here. If you add new columns you can use ALTER TABLE to insert them into a live table. If you rename or remove columns you can use ALTER TABLE to rename the old table, then create the new table and then populate the new table with the contents of the old
table.

Parameters

db	The database.
oldVersion	The old database version.
newVersion	The new database version.

        更新資料庫，包括刪除表，添加表等各種操作。若版本是第一版，也就是剛剛建立資料庫，onUpgrade()方法裡面就不用寫東西，因為第一版資料庫何來更新之說，以後發布的版本，資料庫更新的話，可以在onUpgrade()方法添加各種更新的操作。

4、注意事項

建立完SQLiteOpenHelper 類之後，在主activity裡面就可以通過SQLiteOpenHelper.getWritableDatabase()或者getReadableDatabase()方法來擷取在SQLiteOpenHelper 類裡面建立的資料庫執行個體。（也就是說只有調用這兩種方法才真正地執行個體化資料庫）

getWritableDatabase() 方法————以讀寫方式開啟資料庫，如果資料庫所在磁碟空間滿了，而使用的又是getWritableDatabase() 方法就會出錯。

                                                                         因為此時資料庫就只能讀而不能寫，

getReadableDatabase()方法————則是先以讀寫方式開啟資料庫，如果資料庫的磁碟空間滿了，就會開啟失敗，但是當開啟失敗後會繼續嘗試以唯讀

                                                                          方式開啟資料庫。而不會報錯

=========================================================================================================

下面示範一個以SQLite的資料庫為adapter的listview例子（也可以當做通訊錄小工具）

如下

/*主activity * @author:conowen * @date:12.3.1 */package com.conowen.sqlite;import android.app.Activity;import android.content.ContentValues;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;import android.widget.EditText;import android.widget.ListAdapter;import android.widget.ListView;import android.widget.SimpleCursorAdapter;import android.widget.Toast;public class SqliteActivity extends Activity {SQLiteDatabase sqldb;public String DB_NAME = "sql.db";public String DB_TABLE = "num";public int DB_VERSION = 1;final DbHelper helper = new DbHelper(this, DB_NAME, null, DB_VERSION);// DbHelper類在DbHelper.java檔案裡面建立的/** Called when the activity is first created. */@Overridepublic void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.main);sqldb = helper.getWritableDatabase();// 通過helper的getWritableDatabase()得到SQLiteOpenHelper所建立的資料庫Button insert = (Button) findViewById(R.id.insert);Button delete = (Button) findViewById(R.id.delete);Button update = (Button) findViewById(R.id.update);Button query = (Button) findViewById(R.id.query);final ContentValues cv = new ContentValues();// ContentValues是“添加”和“更新”兩個操作的資料載體updatelistview();// 更新listview// 添加insertinsert.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubEditText et_name = (EditText) findViewById(R.id.name);EditText et_phone = (EditText) findViewById(R.id.phone);cv.put("name", et_name.getText().toString());cv.put("phone", et_phone.getText().toString());// name和phone為列名long res = sqldb.insert("addressbook", null, cv);// 插入資料if (res == -1) {Toast.makeText(SqliteActivity.this, "添加失敗",Toast.LENGTH_SHORT).show();} else {Toast.makeText(SqliteActivity.this, "添加成功",Toast.LENGTH_SHORT).show();}updatelistview();// 更新listview}});// 刪除delete.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubint res = sqldb.delete("addressbook", "name='大鐘'", null);// 刪除列名name，行名為“大鐘”的，這一行的所有資料，null表示這一行的所有資料// 若第二個參數為null，則刪除表中所有列對應的所有行的資料，也就是把table清空了。// name='大鐘'，大鐘要單引號的// 傳回值為刪除的行數if (res == 0) {Toast.makeText(SqliteActivity.this, "刪除失敗",Toast.LENGTH_SHORT).show();} else {Toast.makeText(SqliteActivity.this, "成刪除了" + res + "行的資料",Toast.LENGTH_SHORT).show();}updatelistview();// 更新listview}});// 更改update.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubcv.put("name", "大鐘");cv.put("phone", "1361234567");int res = sqldb.update("addressbook", cv, "name='張三'", null);// 把name=張三所在行的資料，全部更新為ContentValues所對應的資料// 返回時為成功更新的行數Toast.makeText(SqliteActivity.this, "成功更新了" + res + "行的資料",Toast.LENGTH_SHORT).show();updatelistview();// 更新listview}});// 查詢query.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubCursor cr = sqldb.query("addressbook", null, null, null, null,null, null);// 返回名為addressbook的表的所有資料Toast.makeText(SqliteActivity.this,"一共有" + cr.getCount() + "條記錄", Toast.LENGTH_SHORT).show();updatelistview();// 更新listview}});}// 更新listviewpublic void updatelistview() {ListView lv = (ListView) findViewById(R.id.lv);final Cursor cr = sqldb.query("addressbook", null, null, null, null,null, null);String[] ColumnNames = cr.getColumnNames();// ColumnNames為資料庫的表的列名，getColumnNames()為得到指定table的所有列名ListAdapter adapter = new SimpleCursorAdapter(this, R.layout.layout,cr, ColumnNames, new int[] { R.id.tv1, R.id.tv2, R.id.tv3 });// layout為listView的布局檔案，包括三個TextView，用來顯示三個列名所對應的值// ColumnNames為資料庫的表的列名// 最後一個參數是int[]類型的，為view類型的id，用來顯示ColumnNames列名所對應的值。view的類型為TextViewlv.setAdapter(adapter);}}

/*SQLiteOpenHelper類 * @author:conowen * @date:12.3.1 */package com.conowen.sqlite;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class DbHelper extends SQLiteOpenHelper {public DbHelper(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);// TODO Auto-generated constructor stub}@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stubString sql = "CREATE  TABLE addressbook (_id INTEGER PRIMARY KEY , name VARCHAR, phone VARCHAR)";db.execSQL(sql);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stub}}

main.xml

<?xml version="1.0" encoding="utf-8"?><LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"    android:layout_width="fill_parent"    android:layout_height="fill_parent"    android:orientation="vertical" >    <EditText        android:id="@+id/name"        android:layout_width="fill_parent"        android:layout_height="wrap_content" />    <EditText        android:id="@+id/phone"        android:layout_width="fill_parent"        android:layout_height="wrap_content" />    <LinearLayout        android:id="@+id/linearLayout1"        android:layout_width="fill_parent"        android:layout_height="wrap_content" >        <Button            android:id="@+id/insert"            android:layout_width="wrap_content"            android:layout_height="wrap_content"            android:text="增加" />        <Button            android:id="@+id/delete"            android:layout_width="wrap_content"            android:layout_height="wrap_content"            android:text="刪除" />        <Button            android:id="@+id/update"            android:layout_width="wrap_content"            android:layout_height="wrap_content"            android:text="更改" />        <Button            android:id="@+id/query"            android:layout_width="wrap_content"            android:layout_height="wrap_content"            android:text="查詢" />    </LinearLayout>    <ListView        android:id="@+id/lv"        android:layout_width="fill_parent"        android:layout_height="wrap_content" >    </ListView></LinearLayout>

ListView的布局檔案layout.xml

<?xml version="1.0" encoding="utf-8"?><LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"    android:layout_width="fill_parent"    android:layout_height="fill_parent"    android:orientation="horizontal" ><TextView        android:id="@+id/tv1"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:textSize="20sp"         android:width="50px" /><TextView        android:id="@+id/tv2"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:textSize="20sp"         android:width="50px"        /><TextView        android:id="@+id/tv3"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:textSize="20sp"         android:width="150px" /></LinearLayout>