Kotlin for android學習十四（佈局篇）：資料庫

前言

kotlin官網和kotlin教程學習教程的筆記。
這一節，我們學習Anko庫中的anko-sqlite。主要用於運算元據庫。

引入庫

    compile "org.jetbrains.anko:anko-sqlite:$anko_version"

一、定義表

object UserContract {
    val TABLE_NAME = "myTable"
    val COLUMN_NAME_ID = "id"
    val COLUMN_NAME_NAME = "name"
}

二、建立ManagedSQLiteOpenHelper

class MyDatabaseOpenHelper(ctx: Context) : ManagedSQLiteOpenHelper(ctx, DATABASE_NAME, null, DATABASE_VERSION) {
    companion object {
        val DATABASE_VERSION = 1
        val DATABASE_NAME = "MyDataBase.db"

        private var instance: MyDatabaseOpenHelper? = null

        fun getInstance(ctx: Context): MyDatabaseOpenHelper {
            if (instance == null) {
                instance = MyDatabaseOpenHelper(ctx.applicationContext)
            }
            return instance!!
        }

    }

    override fun onCreate(db: SQLiteDatabase?) {
    }

    override fun onUpgrade(db: SQLiteDatabase?, p1: Int, p2: Int) {
    }
}

val Context.database: MyDatabaseOpenHelper
    get() = MyDatabaseOpenHelper.getInstance(applicationContext)

三、建立表

    override fun onCreate(db: SQLiteDatabase?) {
        db?.createTable(UserContract.TABLE_NAME, true,
                Pair(UserContract.COLUMN_NAME_ID, INTEGER + PRIMARY_KEY + UNIQUE), // + 會把多個修飾符組合起
                Pair(UserContract.COLUMN_NAME_NAME, TEXT))
    }

也可以這樣

    override fun onCreate(db: SQLiteDatabase?) {
  db?.createTable(UserContract.TABLE_NAME, true,
                UserContract.COLUMN_NAME_ID to INTEGER + PRIMARY_KEY + UNIQUE,
                UserContract.COLUMN_NAME_NAME to TEXT)
    }

四、重建表

  override fun onUpgrade(db: SQLiteDatabase?, p1: Int, p2: Int) {
        db?.dropTable(UserContract.TABLE_NAME, true)
        onCreate(db)
    }

五、建立資料庫model類

class User(var id: Long,var name: String)

六、寫入和查詢資料庫

 doAsync {
            var result: List<User> = listOf()
            database.use {
                for (i in 1..100) {
                    val values = ContentValues()
                    values.put(UserContract.COLUMN_NAME_ID, i*10)
                    values.put(UserContract.COLUMN_NAME_NAME, "user $i")
                    insert(UserContract.TABLE_NAME, null, values)
                }

                result = select(UserContract.TABLE_NAME)
                        .whereSimple("${UserContract.COLUMN_NAME_ID} %?=0", "7")
                        .parseList(classParser())
            }
            uiThread {
                var str = ""
                for (item in result) {
                    str += "${item.id} is ${item.name} \n"
                }
                txtView.text = str
            }
        }

七、使用高階函式與委託的另一種方式

User實體類這樣子

class User(map: MutableMap<String, Any?>) {
    var id: Long by map
    var name: String by map
}

讀取資料這樣子

......
      result = select(UserContract.TABLE_NAME)
                        .parseList<User> {
                            User(HashMap(it))
                        }

......

fun <T : Any> SelectQueryBuilder.parseList(parser: (Map<String, Any?>) -> T): List<T> =
        parseList(object : MapRowParser<T> {
            override fun parseRow(columns: Map<String, Any?>): T = parser(columns)
        })