Ch 18 SQLite資料庫應用

Android以SQLite資料庫引擎管理資料庫，並提供有sqlite3命令列工具與資料庫相關的API。SQLite與一般資料庫引擎一樣屬於關連性資料庫管理系統 (Relational DataBase Management System，RDBMS)的一種，但它將一些非絕對必要的功能取消，同時是執行於本機的資料庫引擎，不需要透過伺服器提供服務。

一、關連性資料庫與SQLite 關連性資料庫以資料表提供有效及結構化的資料保存與管理，一個資料庫可以擁有多個資料表。資料表由列與欄組成，每一列為一筆單獨的記錄，含有不同的資料欄內容，而一個資料欄是所有記錄共同類型的資料。 SQLite並非出自Google，而是由一個SQLite聯盟持續的開發和維護，它可以使用大部份標準SQL語言。

SQLite屬於嵌入式的SQL資料庫引擎，不需依賴伺服器而能自己工作，它所有的資料表都包含在同一個資料庫檔案中，且此資料庫檔案為跨平台的格式。 SQLite的函式庫相當小，它能在很小的堆疊(約4KB)與堆積(約100KB)空間中執行，加上它的可靠行相當高，這使得它非常適合在類似行動裝置這類資源有限的設備上使用。 SQLite對於資料庫管理採取交易的模式，它交易的方式是所有動作完全成功，或是任何動作都不作用，因此就算應用程式在執行中被意外中止，對於處理中的資料庫也不會造成損害，這更提升了資料的正確與安全。

經由SQLite的協助，資料庫就如同一般的檔案一樣，可以存在行動裝置的內建記憶體，或外加的擴充SD卡上，但一般都存於行動裝置內建記憶體的「/data/data/<專案套件>/databases」目錄中，此目錄由專案自動建立。

1、SQL語法 所有SQL的指令大致可以分成2類： 資料定義語言(Data Definition Language，DDL)，這類的指令主要是用於建立資料表與資料欄。 資料操作語言(Data Manipulation Language，DML)，這類的指令則用於資料表內的資料，包括新增(insert)、更新(update)、刪除(delete)、查詢(query)等。

資料定義語言(DDL)： CREATE TABLE DROP TABLE 在資料庫中建立新的資料表。建立時需提供資料表名稱，與此資料表所含的資料欄與資料欄的資料型式。 以CREATE TABLE建立的資料表與資料欄定義稱為資料庫的概要圖。 DROP TABLE 移除用CREATE TABLE建立的資料表。 執行此指令時需要提供要移除的資料表名稱，移除的資料表會從資料庫概要圖及儲存位置移除，移除後不能再恢復，所含的資料也一併刪除。

資料操作語言(DML)： ALTER TABLE 資料表中設定的約束 SELECT 改變資料表的結構，但不能更改已經存在的資料欄名稱或移除資料欄，也不能從資料表中移除約束。 資料表中設定的約束 約束是一種資料欄的屬性，可以用來限制這資料欄所能存在的內容。 資料操作語言(DML)： SELECT 資料查詢的主要工具，執行的結果會傳回0筆或多筆記錄，並可指定傳回記錄所包含的資料欄。 SELECT指令中可以含有以下的子句： FROM子句：指定對那一個資料表執行查詢，可以指定回傳記錄所包括的資料欄；如果要包含原資料表所有資料欄，則以「*」表示資料欄名稱。

WHERE子句：指定查詢的條件，只有滿足指定條件的記錄才會回傳，也可以指定回傳記錄所包括的資料欄。在WHERE子句中可以使用「LIKE」及萬用字「%」作為篩選條件。 ORDER BY子句：將查詢回傳的記錄以指定的資料欄為標準排序。排序方式可以設定為「ASC」，表示以升冪方式排序，或「DESC」表示以降冪方式排序。 GROUP BY子句：查詢回傳的記錄以指定的資料欄進行分組統計。 HAVING子句：將分組統計過的查詢結果，再進行篩選。與WHERE的差別是，WHERE篩選條件發生在分組統計之前；而HAVING子句的篩選，發生在分組統計之後。 LIMIT子句：用以設定查詢回傳記錄筆數的上限，可以使用任何產生正整數的表示式來限定回傳記錄筆數。

交易(Transaction) INSERT UPDATE DELETE 用以修改滿足指定條件記錄的指定資料欄內容，執行時需提供資料表與要更新的資料欄名稱，以及新的資料欄內容。 DELETE 從資料表中移除滿足指定條件的記錄，執行時需提供資料表名稱與篩選的WHERE條件子句。 交易(Transaction) 資料庫的交易是由一系列的SQL指令構成，且交易的結果為所有指令都完成，或沒有一個指令對資料庫產生作用。

對於SQLite資料庫引擎，每一個影響資料庫的操作是在自己的交易上自動進行，這表示程式開發者可以確認在SQL指令成功執行後，它對資料庫的影響立即生效，這些自動開始的交易會在完成最後一個SQL指令後交付給SQLite資料庫引擎。 也可以用「BEGIN」明確的開始一個交易，這交易會在下一個「COMMIT」命令才交付出去執行，且SQLite資料庫引擎將在確認交易中所有指令都順利完成後，才會真正修改資料庫的內容。

二、sqlite3命令列工具 對於SQLite資料庫相關的開發過程，經常會先以「sqlite3」命令列工具，檢查資料庫應用程式執行結果或除錯。 使用sqlite3需透過「adb shell」，而且必須先啟動模擬器，步驟如下： 啟動模擬器 建立資料庫存放的資料夾 adb shell # cd /data/data/

建立資料庫存放的資料夾 adb shell # cd /data/data/ #mkdir com.example.sqlite3test (在「/data/data/」目錄下建立新的「com.example.sqlite3test」目錄) #cd com.example.sqlite3test #mkdir databases (在「/data/data/com.example.sqlite3test」目錄下建立新的「databases」目錄)

建立資料庫並進入sqlite3命令列 #sqlite3 company.db sqlite3接受2種指令，資料庫操作的SQL指令，或sqlite3本身的以「.」開始的單字指令。 SQL指令必須以「;」為指令結束，未鍵入「;」前，sqlite3會視為是同一個指令，以「…>」提示符號提醒指令尚未完成(也就是說SQL指令可以分成多行鍵入) 單字指令不必以「;」結束，常用的有「.help」列出sqlite3所有單字命令說明，與「.exit」離開sqlite3(按「Ctrl+C」鍵也可以離開sqlite3)。

建立資料表 sqlite>CREATE TABLE employee ( …>_id integer primary key,name text UNIQUE NOT NULL …>,age INTEGER, salary INTEGER); sqlite>

在目前資料庫中建立「employee」資料表，此資料表有4個資料欄， 「_id」資料欄資料型式為整數，且為此資料表的主鍵欄(主鍵可用與和其它資料表關連)，Android用這個特殊_id名稱是為了要讓資料表能工作於它的游標(Cursor)系統 「name」資料欄資料型式為字串，且設定它的資料不能重覆，也不能是NULL 「age」資料欄資料型式為整數 「salary」資料欄資料型式也是整數。 注意對於SQL指令是不區分大小寫，因此「INTEGER」與「integer」意義相同。

新增記錄 sqlite>INSERT INTO employee(_id,name,age,salary) VALUES(1,”Smith”,28,40000); VALUES(2,”John”,32,38000); VALUES(3,”Helen”,27,35000); VALUES(4,”Joe”,41,42000); 用「INSERT」指令在employee資料表新增4筆記錄；並設定各資料欄內容，注意字串要使用雙引號「”」。

查詢記錄 sqlite>SELECT * FROM employee; 查詢employee資料表所有的記錄，並取得所有資料欄；注意，sqlite3以「|」分隔各資料欄。 sqlite>SELECT name,age,salary FROM employee WHERE salary >= 38000 ORDER BY salary; 查詢employee資料表中「salary」資料欄內容大於(含)38000的所有記錄，只取資料表中「name」、「age」、「salary」3個資料欄，並以「salary」資料欄排序。

更新記錄 刪除記錄 sqlite>UPDATE employee SET salary=41000 WHERE name=”Smith”; sqlite>SELECT * FROM employee; 將姓名為”Smith”的記錄的「salary」資料更新為「41000」，然後查詢employee資料表中所有記錄。 刪除記錄 sqlite>DELETE FROM employee WHERE salary=35000; sqlite>SELECT * FROM employee ORDER BY salary DESC; 將資料表中「salary」資料欄為35000的記錄刪除，然後查詢employee資料表中所有記錄，並以「salary」資料欄降冪排序。

三、資料庫應用相關類別 SQLiteOpenHelper 負責建立資料庫與管理版本的輔助類別，在應用程式中必須建立它的延伸類別，並於應用資料庫之前以此延伸類別取得對指定資料庫的參考。 如果指定的資料庫不存在，此延伸類別會自動建立此資料庫與事先設計的資料表。 建構函式定義如下： public SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)

onCreate(SQLiteDatabase)方法在資料庫第一次建立時呼叫，這是建立資料表與初始化資料表的地方。 onUpgrade(SQLiteDatabase, int, int)方法是當資料庫概要圖需要更改時呼叫，要增加或刪除資料表都應在此方法內實作。 onOpen(SQLiteDatabase)方法在資料庫開啟後呼叫，一般是在這個方法內檢查資料庫是否是唯讀的，避免更新資料表時產生意外。

其他常用的方法： getReadableDatabase()：建立或開啟唯讀資料庫。 getWritableDatabase()：建立或開啟可以讀寫的資料庫。 getDatabaseName()：取得已開啟資料庫名稱。 close()：關閉資料庫。

SQLiteDatabase SQLiteDatabase的實體物件參考到開啟的資料庫，提供了建立、新增、更新、刪除、執行SQL命令，以及其他常用的資料庫管理方法。 常用的方法有： beginTransaction()：以EXCLUSIVE模式開始一個交易的範圍定義。 beginTransactionNonExclusive()：以IMMEDIATE模式開始一個交易範圍定義。 endTransaction()：結束交易範圍定義。 create ()：建立一個記憶體備份的資料庫，此資料庫在關閉時自動刪除。

openDatabase()或openOrCreateDatabase()：開啟資料庫。 insert()：在指定資料表中新增一筆記錄。 query()：依設定的條件查詢資料表，執行結果會回傳包含查詢結果的Cursor物件。 replace()：在指定資料表中以新值取代或新增一筆記錄。 update()：更新資料表中滿足指定條件的記錄，可以一次更新多筆記錄。 delete()：刪除資料表中符合指定條件的記錄，可以一次刪除多筆記錄。 execSQL()：執行一個不會回傳資料的SQL指令。 deleteDatabase()：刪除指定的資料庫。

Cursor Cursor是存放資料查詢回傳結果的容器，透過Cursor可以指定要從回傳結果中讀取那一筆記錄。 常用的方法有： close()：關閉Cursor物件並釋放其所有資源，執行後此物件無法再使用。 getBlob(int columnIndex)：以位元組陣列回傳指定資料欄的內容，資料欄索引從0開始。 getFloat(int columnIndex)：以浮點數回傳指定資料欄的內容。 getInt(int columnIndex)：以整數回傳指定資料欄的內容。 getString(int columnIndex)：以字串回傳指定資料欄的內容。

getType(int columnIndex)：傳回指定資料欄的資料型式。 getColumnCount()：回傳查詢結果含有幾個欄位。 getCount()：回傳查詢結果含有幾筆記錄。 getPosition()：回傳游標目前在查詢結果的位置(第幾筆記錄)。 isAfterLast()：回傳目前游標是否位於查詢結果最後一筆記錄之後。 isBeforeFirst()：回傳目前游標是否位於查詢結果第一筆記錄之前。 isFirst()：回傳目前游標是否位於查詢結果的第一筆記錄。 isLast()：回傳目前游標是否位於查詢結果的最後一筆記錄。

isNull (int columnIndex)：回傳指定資料欄的內容是否為NULL。 move(int offset)：從目前位置將游標往後(正值)或往前(負值)移動指定的記錄筆數。 moveToFirst()：將游標移到第一筆記錄。 moveToLast()：將游標移到最後一筆記錄。 moveToNext()：將游標移到下一筆記錄。 moveToPrevious()：將游標移到前一筆記錄。 moveToPosition(int position)：將游標移到指定位置的記錄。

ContentValues 此類別的作用是用來儲存一組鍵/值對，常用的方法有： put(String key, dataType value)：將鍵/值對存至ContentValues物件。 clear()：清除ContentValues物件內部所有儲存的值。

四、資料庫應用範例 DatabaseApp2 Test範例 範例將建立「people.db」資料庫與「employee」資料表，資料表每一筆記錄有「_id」、「name」、「age」、「salary」4個資料欄，其中「_id」為主鍵欄，與「age」、「salary」資料欄的資料型式都是整數；而「name」資料欄資料型式為字串，且設定有資料不能重覆及不能是null的約束。 活動佈局中以EditText元件顯示記錄中「_id」以外的內容，並有3個主要功能按鈕。

範例也自建一個延伸自SQLiteOpenHelper類別的子類別「DatabaseHelper.java」。 「全部」鈕取得資料表內所有記錄，並可透過「下一筆」、「前一筆」鈕逐筆顯示記錄。 「刪除」鈕刪除目前顯示的記錄。 「新增」鈕在各EditText元件中輸入資料後，按「確認」鈕將輸入的資料新增為資料表的一筆記錄。 範例也自建一個延伸自SQLiteOpenHelper類別的子類別「DatabaseHelper.java」。 範例詳細內容請參考原書 code 18-2/3。