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)
        })