SQLite 是本地端的資料庫，把收集到的資料儲存成一個檔案，存放在本機的儲存裝置中。

SQLite 不是一般的文字檔，而是使用資料庫的格式來儲存，所以新增或查詢的效能比一般的文字檔高出甚多。

SQLite 跟其它的資料庫一樣，使用二元樹演算法，不必讀進全部的內容，就可以高速搜詢所有資料。

SQLite 只允許本機

在手機的世界中，網路時好時斷。當沒有網路時，總不能把手機即時收集到的資料，卻因為沒有網路不能傳送，就把資料丟掉吧!! 那該怎麼辦呢? 這時 SQLite 就有用處囉。

SQLite 只允許在本機存取，沒有網路斷線的問題，會與手機永遠連線。所以就可以把手機收集到的資訊先存在 SQLite 中，然後再用一個新的執行緒來檢查網路是否正常，若恢復連線，再由 SQLite 取得資料然後傳送到遠端伺服器。

會員登入

會員登入系統當然就不能使用 SQLite了。想想看，在你手機註冊會員資料，而且註冊成功了。那麼可以在別人的手機上登入嗎?? 當然不可能，因為註冊的會員資料只有你的手機才有。除非登入者用你的手機登入。

那~~~那麼多的 app 是怎麼作出來的? 在我的手機註冊成功，然後可以在別人的手機登入? 請看下圖

手機 A 註冊成功的資料，會經由網路傳送並儲存到 xxx 公司的 MySQL 伺服器。
然後在手機 B 登入時，也要由 xxx 公司的 MySQL 伺服器驗証並取得資料。

建立開啟資料庫

使用 context. openOrCreateDatabase() 建立資料庫。如果資料庫已存在，則只會進行開啟的動作。

第一個參數為資料庫的檔案名稱。

第二個參數為權限，權限有三種，分別如下

1. MODE_PRIVATE : 只有這個 app 可以存取。
2. MODE_WORLD_READABLE : 所有的 APP 都可以來讀取。
3. MODE_WORLD_WRITEABLE : 所有的 APP 都可以來寫入。底下傳入 Context.MODE_PRIVATE設定權限。

第三個參數為 ErrorHandler，如果不打算處理 Error 的情況，就塞 null 進去，或是不要放第三個參數。

底下的 db 變數為 SQLiteDatabase 型態

val db= this.openOrCreateDatabase("local.db", Context.MODE_PRIVATE, null )

請特別注意一點，當資料庫不再使用時，需使用 close()將資料庫關閉，否則會出現 IllegalStateException 例外。

db.close()

刪除資料庫

使用 context的 deleteDatabase(“資料庫檔名”)。如果原本並無此檔名，使用此方法刪除也不會出錯

this.deleteDatabase("local.db")

建立資料表

資料庫建立並開啟後，接下來就可以建立資料表了。使用 create table 的 SQL語法產生命令字串，再將此字串交由 db.execSQL()來執行即可。

建立資料表時，要指定每個欄位的資料型態，SQLite只有四種資料型態，分別為 INTEGER, REAL, TEXT, BLOB。

底下我們把 id 設為 autoincrement，每次新增一筆資料，其 id 值就會自動加 1。account 欄位設為 UNIQUE(唯一)，若有 account 的值重複，則會出現 SQLiteConstraintException 例外

val strMember=String.format(
    "create table member (" +
            "id integer primary key autoincrement, " +  //0
            "account TEXT UNIQUE, " +  //1
            "password TEXT, " +  //2
            "mail TEXT)"
)
db.execSQL(strMember)

刪除資料表

刪除資料表，同樣是使用SQL語法。底下是刪除 member 資料表的方法

db.execSQL("drop table if exists member")

CRUD

資料庫不外乎就是四個動作，分別為新增(Create)，查詢(Retrieve)，更新(Update) 和刪除(Delete)。

新增、更新及刪除三個動作的SQL語法，必需由 execSQL()方法來執行。至於查詢的SQL語法，需由 rawQuery()方法來執行。

execQuery

比如想新增一筆資料，可以先把 SQL 寫在 cmd 字串中，再交由 execQuery() 來執行。底下使用 try-catch 將 execSQL包含起來，是為了防止帳號重複所產生的 SQLiteConstraintException 例外。

val account="thomas"
val password="123456"
val mail="mahaljsp@gmail.com"
var cmd=String.format("insert into member (account, password, mail) values ('${account}', '${password}','${mail}')")
try {
    db.execSQL(cmd)
}
catch(e: SQLiteConstraintException){
    Log.d("Thomas", e.message.toString())
}

rawQuery

若要查詢資料，可以把查詢的SQL語法寫在 cmd字串中，再由 rawQuery執行。執行後的所有資料集會置於Cursor 資料型態物件 (cursor)，再由 cursor.moveToNext()一筆一筆讀取。每讀取一筆，再由 cursor.getString(欄位索引) 讀取每一個欄位的資料。第一個欄位的索引為 0。

var cmd = String.format("select * from member")
var cursor = db.rawQuery(cmd, null)
while (cursor.moveToNext()) {
    Log.d("Thomas",
        cursor.getString(1) + ":" + cursor.getString(2) + 
        ":" + cursor.getString(2)
    )
}

總整理

上述都是片斷的指令說明，實際上要讓初學者整合起來，其實並不容易，所以底下列出整合後的完整代碼

package com.asuscomm.mahaljsp.sqlite_test
import android.content.Context
import android.database.sqlite.SQLiteConstraintException
import android.database.sqlite.SQLiteDatabase
import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import android.util.Log

class MainActivity : AppCompatActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        //建立或開啟資料庫
        val db:SQLiteDatabase= this.openOrCreateDatabase("local.db", Context.MODE_PRIVATE, null )
        
        //建立資料表的SQL語法
        val strMember=String.format(
            "create table member (" +
                    "id integer primary key autoincrement, " +  //0
                    "account TEXT UNIQUE, " +  //1
                    "password TEXT, " +  //2
                    "mail TEXT)"
        )
        //防止第二次執行時，重複建立資料表
        var cursor=db.rawQuery(
            "select name from sqlite_master where type='table' and name='member' ", 
            null)
        if(!cursor.moveToNext()){
            db.execSQL(strMember)
        }

        //新增一筆資料
        val account="thomas"
        val password="123456"
        val mail="mahaljsp@gmail.com"
        var cmd=String.format("insert into member (account, password, mail) values ('${account}', '${password}','${mail}')")
        try {
            db.execSQL(cmd)
        }
        catch(e: SQLiteConstraintException){
            Log.d("Thomas", e.message.toString())
        }
        
        //查詢資料表
        cmd = String.format("select * from member")
        cursor = db.rawQuery(cmd, null)
        while (cursor.moveToNext()) {
            Log.d("Thomas",
                  cursor.getString(1) + ":" +
                  cursor.getString(2) + ":" + 
                  cursor.getString(3)
            )
        }
        db.close()
    }
}

防止重複建立資料表

每次執行app時，都要建立資料庫及資料表。重複建立資料庫是沒問題的，因為如果原先並沒有 local.db，就會建立新的資料庫。但如果經有local.db，再次下達 openOrCreateDatabase()只會開啟資料庫，並不會重新建立。

不過 create table member 建立 member 資料表時，第二次達行時又再重建一次，此時就會出現資料表已存在的例外。所以為了避免第二次重複建立 member，就必需使用如下SQL 語法查詢是否已存在 member。

"select name from sqlite_master where type='table' and name='member'"

經上述查詢後，如果 member 不存在，才會建立資料表。

自訂LocaldbHelper

前面的說明中，要開資料庫，建立資料表，還要檢查是否第二次執行重複建立資料表的問題，實在是煩人。所以Android幫我們建立了一個SQLiteHelper的類別，方便我們操作。

首先在專案下新增 LocaldbHelper 類別。

此類別繼承了 SQLiteOpenHelper 類別，裏面有三個重要的類別變數，分別為

DATABASE_NAME : 資料庫檔名
VERSION : 資料庫版本，系統會自動檢查版本，如果下次執行時的版本高於上一次，就會執行 onUpgrade方法，此方法會刪除原本的資料表，重新建立新的資料表。
strMember : 建立資料表的 SQL 語法字串

底下是 LocaldbHelper 的完整代碼，只要更改藍色及紅色的部份即可

package com.asuscomm.mahaljsp.sqlite_test
import android.content.Context
import android.database.SQLException
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper

class LocaldbHelper(context: Context, name: String, factory: SQLiteDatabase.CursorFactory?, version: Int) :
    SQLiteOpenHelper(context,name,factory,version) {
    companion object{
        val DATABASE_NAME = "local.db"
        val VERSION = 1
        var database:SQLiteDatabase? = null
        val strMember=String.format(
            "create table member (" +
                    "id integer primary key autoincrement, " +  //0
                    "account TEXT UNIQUE, " +  //1
                    "password TEXT, " +  //2
                    "mail TEXT)" //3
        )
        fun getDatabase(context: Context?): SQLiteDatabase? {
            if (database == null || !database!!.isOpen) {
                database = LocaldbHelper(context!!, DATABASE_NAME, null, VERSION).writableDatabase
            }
            return database
        }
    }

    override fun onCreate(db: SQLiteDatabase?) {
        try {
            db?.execSQL(strMember)
        }
        catch (e: SQLException){
            onUpgrade(db, 0, 0)
        }
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        db?.execSQL("DROP TABLE IF EXISTS member")
        onCreate(db)
    }
}

Localdb

請在專案下新增 Localdb類別。

Localdb 類別裏的方法都是類別方法，不需產生此物件就可以在任何地方執行裏面的類別方法。

package com.asuscomm.mahaljsp.sqlite_test

import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase

object Localdb {
    var db :SQLiteDatabase ?= null
    fun open(context: Context){
        db=LocaldbHelper.getDatabase(context)
    }
    fun execSQL(cmd:String){
        db?.execSQL(cmd)
    }
    fun rawQuery(cmd:String):Cursor?{
        val cursor=db?.rawQuery(cmd, null)
        return cursor
    }
    fun close(){
        db?.close()
    }
}

應用

上面二個類別都準備好了，就可以在 MainActivity 裏使用如下代碼。

1.  Localdb.open : 一定要先開啟資料庫
2. Localdb.execSQL/Localdb.rawQuery : 開始執行SQL命令
3. Localdb.close : 最後一定要關閉資料庫

package com.asuscomm.mahaljsp.sqlite_test
import android.database.sqlite.SQLiteConstraintException
import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import android.util.Log

class MainActivity : AppCompatActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        Localdb.open(this)
        val account="shenny"
        val password="123456"
        val mail="mahaljsp@gmail.com"
        var cmd=String.format("insert into member (account, password, mail) values ('${account}', '${password}','${mail}')")
        
        //新增一筆資料
        try {
            Localdb.execSQL(cmd)
        }
        catch(e: SQLiteConstraintException){
            Log.d("Thomas", e.message.toString())
        }

        //查詢資料表
        cmd = String.format("select * from member")
        var cursor = Localdb.rawQuery(cmd)
        while (cursor?.moveToNext()==true) {
            Log.d("Thomas",
                cursor.getString(1) + ":" +
                     cursor.getString(2) + ":" +
                     cursor.getString(3)
            )
        }
        Localdb.close()
    }
}

多執行緒

依官網的說明，一個數據庫連線不能被多個執行緒同時使用。

所以最好使用 Handler 交由 UI 主執行緒來操作，不然就是新增一個專門用來操作資料庫的新執行緒，千萬別把資料庫的操作分散在不同的執行緒中。

ContentValues

Android 提供 db.insert，db.update 等方法，這些方法必需搭配 ContentValues 物件，將資料置入 cv 中，再使用 Localdb.db?.update() 等方法。代碼如下

val cv = ContentValues()
val id=1
cv.put("name", "張大呆")
cv.put("account", "thomas")
cv.put("mail", "abc@gmail.com")
Localdb.db?.update("member", cv, "id=${id}", null)

在此不建議使用此方法，這些方法是讓不懂 SQL 語法的人用的。使用 ContentValues 不但靈活性不佳，且好像遇到錯誤時，無法攔截例外處理。