Sqlite，其實在很多平台上都支援使用，主要是其簡單、快速存取的優點。

但相對的，提供給USER的操作空間以及資料處理能力也是相對精簡。

Anko SQLite提供不少便利的資料庫操作以及連線安全的工具。

今天我們透過ManagedSQLiteOpenHelper來實作一個簡單的操作模板。

我們會建立一些模板

• onCreate : 檢查資料表存在，沒有的話就建立
• onUpgrade : 當資料表格式更新時，替換並保存舊版本
• query : query(資料表名稱,指定條件 可空)
• insert : insert(資料表名稱, 資料列表)
• delete : delete(資料表名稱,指定條件 可空)
• update : update(資料表名稱,對應欄位及資料, 指定條件)

class BookmarkDBHelper(var context: Context) :
        ManagedSQLiteOpenHelper(context,"Novel.db", null, 1) {
        var tableList: List<String> = listOf("bookmark")
        companion object {
            private val TAG = "BookmarkDB"
            private var instance: BookmarkDBHelper?=null
            @Synchronized
            fun getInstance(ctx: Context, version: Int = 0): BookmarkDBHelper {
                if (instance == null) {
                    instance =BookmarkDBHelper(ctx.applicationContext)
                }
                Log.d("TAG", "Instance RETURN")
                return instance!!
            }
        }
        override fun onCreate(db: SQLiteDatabase) {
            Log.d("TAG", "onCreate")
            DBInital(tableList,db)
        }

        override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
            Log.d("TAG", "onUpgrade")
            //要更新的動作
        }
        fun DBInital(listTable : List<String>, db: SQLiteDatabase){
            Log.d("TAG", "Inital start")
            //var db: SQLiteDatabase
            for(i : Int in 0..listTable.count()-1){
                when(listTable[i]){
                    "bookmark"->{

                        var create_sql = "CREATE TABLE IF NOT EXISTS "+listTable[i] +" ("
                        create_sql += "id INTEGER PRIMARY KEY  AUTOINCREMENT NOT NULL,"
                        create_sql += "title VARCHAR NOT NULL,"
                        create_sql += "html VARCHAR NOT NULL"
                        create_sql += ");"
                        Log.d("TAG", "create_sql:" + create_sql)
                        db.execSQL(create_sql)
                        Log.d("TAG", "create bookmark table Done")
                    }
                }
            }
        }
        fun query(tableName :String,condition: String): List<bookmarkInfo> {
            Log.d("TAG", "start query")
            var m_conditionJudge : String =
                if(condition=="") {
                    "1=1"
                }else {
                    condition
                }
            val sql = "select * from $tableName where $m_conditionJudge;"
            Log.d("TAG", "query sql: " + sql)
            var infoArray = mutableListOf<bookmarkInfo>()
            use {
                Log.d("TAG", "start get query")
                val cursor = rawQuery(sql, null)
                Log.d("TAG", "cursor: "+cursor.count)
                if(cursor.count>0) {
                    if (cursor.moveToFirst()) {
                        while (true) {
                            val info = bookmarkInfo()
                            info.id = cursor.getLong(cursor.getColumnIndexOrThrow("id"))
                            info.title = cursor.getString(cursor.getColumnIndexOrThrow("title"))
                            info.html = cursor.getString(cursor.getColumnIndexOrThrow("html"))
                            infoArray.add(info)
                            if (cursor.isLast) {
                                break
                            }
                            cursor.moveToNext()
                        }
                    }
                }
                cursor.close()
            }
            return infoArray
        }
        fun insert(tableName :String, infoArray: MutableList<bookmarkInfo>): Long {
            Log.d("TAG", "start insert")
            var result: Long = -1
            for (i in infoArray.indices) {
                val info = infoArray[i]
                //var tempArray: List<bookmarkInfo>
                val cv = ContentValues()
                cv.put("title", info.title)
                cv.put("html", info.html)
                use {
                    result = insert(tableName, "", cv)
                }
                if (result == -1L) {
                    return result
                }
            }
            return result
        }
        fun delete(tableName :String, condition: String): Int {
            Log.d("TAG", "start delete")
            var count = 0
            use {
                count = delete(tableName, condition, null)
            }
            return count
        }
        fun update(tableName :String,info: bookmarkInfo, condition: String = "id=${info.id}"): Int {
            val cv = ContentValues()
            cv.put("title", info.title)
            cv.put("html", info.html)
            var count = 0
            condition
            use {
                count = update(tableName, cv, condition, null)
            }
            return count
        }
        class bookmarkInfo{
            var id :Long = 0
            var title: String = ""
            var html: String = ""
        }
    }

好了，到這邊就設定完成了。

等等，怎麼好像沒看到連線設定，開啟連線跟關閉連線的語法呢?

其實在實作ManagedSQLiteOpenHelper時，已經將每個動作都設定為開啟連線，動作結束後就關閉連線。

使用者不需要自行設定相關動作，只需要專心處理資料庫操作就好。

以下是相關程式碼:

abstract class ManagedSQLiteOpenHelper(
    ctx: Context,
    name: String?,
    factory: SQLiteDatabase.CursorFactory? = null,
    version: Int = 1
): SQLiteOpenHelper(ctx, name, factory, version) {

    private val counter = AtomicInteger()
    private var db: SQLiteDatabase? = null

    fun <T> use(f: SQLiteDatabase.() -> T): T {
        try {
            return openDatabase().f()
        } finally {
            closeDatabase()
        }
    }

    @Synchronized
    private fun openDatabase(): SQLiteDatabase {
        if (counter.incrementAndGet() == 1) {
            db = writableDatabase
        }
        return db!!
    }

    @Synchronized
    private fun closeDatabase() {
        if (counter.decrementAndGet() == 0) {
            db?.close()
        }
    }
}