Kotlin入门(26)数据库ManagedSQLiteOpenHelper

共享参数毕竟只能存储简单的键值对数据,如果需要存取更复杂的关系型数据,就要用到数据库SQLite了。尽管SQLite只是手机上的轻量级数据库,但它麻雀虽小、五脏俱全,与Oracle一样存在数据库的创建、变更、删除、连接等DDL操作,以及数据表的增删改查等DML操作,因此开发者对SQLite的使用编码一点都不能含糊。当然,Android为了方便开发者的工作,已经提供了一个操作SQLite的工具类即SQLiteOpenHelper,在App开发时可由SQLiteOpenHelper派生出具体的业务表管理类。
但是,系统自带的SQLiteOpenHelper有个先天缺陷,就是它并未封装数据库管理类SQLiteDatabase,这造成一个后果:开发者需要在操作表之前中手工打开数据库连接,然后在操作结束后手工关闭数据库连接。可是手工开关数据库连接存在着诸多问题,比如数据库连接是否重复打开了?数据库连接是否忘记关闭了?在A处打开数据库却在B处关闭数据是否造成业务异常?以上的种种问题都制约了SQLiteOpenHelper的安全性。
有鉴于此,Kotlin结合Anko库推出了改良版的SQLite管理工具,名叫ManagedSQLiteOpenHelper,该工具封装了数据库连接的开关操作,使得开发者完全无需关心SQLiteDatabase在何时在何处调用,也就避免了手工开关数据库连接可能导致的各种异常。同时ManagedSQLiteOpenHelper的用法与SQLiteOpenHelper几乎一模一样,唯一的区别是:数据表的增删改查语句需要放在use语句块之中,具体格式如下:

1
2
3
4
5
6
7
8
9
10
use {
    //1、插入记录
    //insert(...)
    //2、更新记录
    //update(...)
    //3、删除记录
    //delete(...)
    //4、查询记录
    //query(...)或者rawQuery(...)
}

 

其中表的查询操作还要借助于SQLite已有的游标类Cursor来实现,上述代码中的query和rawQuery方法,返回的都是Cursor对象,那么获取查询结果就得根据游标的指示一条一条遍历结果集合。下面是Cursor类的常用方法:
1、游标控制类方法,用于指定游标的状态:
close : 关闭游标
isClosed : 判断游标是否关闭
isFirst : 判断游标是否在开头
isLast : 判断游标是否在末尾
2、游标移动类方法,把游标移动到指定位置:
moveToFirst : 移动游标到开头
moveToLast : 移动游标到末尾
moveToNext : 移动游标到下一个
moveToPrevious : 移动游标到上一个
move : 往后移动游标若干偏移量
moveToPosition : 移动游标到指定位置
3、获取记录类方法,可获取记录的数量、类型以及取值。
getCount : 获取记录数
getInt : 获取指定字段的整型值
getFloat : 获取指定字段的浮点数值
getString : 获取指定字段的字符串值
getType : 获取指定字段的字段类型
接下来以用户注册信息数据库为例,看看Kotlin的数据库操作代码是怎样实现的,具体的实现代码示例如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
class UserDBHelper(var context: Context, private var DB_VERSION: Int=CURRENT_VERSION) : ManagedSQLiteOpenHelper(context, DB_NAME, null, DB_VERSION) {
    companion object {
        private val TAG = "UserDBHelper"
        var DB_NAME = "user.db" //数据库名称
        var TABLE_NAME = "user_info" //表名称
        var CURRENT_VERSION = 1 //当前的最新版本,如有表结构变更,该版本号要加一
        private var instance: UserDBHelper? = null
        @Synchronized
        fun getInstance(ctx: Context, version: Int=0): UserDBHelper {
            if (instance == null) {
                //如果调用时没传版本号,就使用默认的最新版本号
                instance = if (version>0) UserDBHelper(ctx.applicationContext, version)
                            else UserDBHelper(ctx.applicationContext)
            }
            return instance!!
        }
    }
 
    override fun onCreate(db: SQLiteDatabase) {
        Log.d(TAG, "onCreate")
        val drop_sql = "DROP TABLE IF EXISTS $TABLE_NAME;"
        Log.d(TAG, "drop_sql:" + drop_sql)
        db.execSQL(drop_sql)
        val create_sql = "CREATE TABLE IF NOT EXISTS $TABLE_NAME (" +
            "_id INTEGER PRIMARY KEY  AUTOINCREMENT NOT NULL," +
            "name VARCHAR NOT NULL," + "age INTEGER NOT NULL," +
            "height LONG NOT NULL," + "weight FLOAT NOT NULL," +
            "married INTEGER NOT NULL," + "update_time VARCHAR NOT NULL" +
            //演示数据库升级时要先把下面这行注释
            ",phone VARCHAR" + ",password VARCHAR" + ");"
        Log.d(TAG, "create_sql:" + create_sql)
        db.execSQL(create_sql)
    }
 
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        Log.d(TAG, "onUpgrade oldVersion=$oldVersion, newVersion=$newVersion")
        if (newVersion > 1) {
            //Android的ALTER命令不支持一次添加多列,只能分多次添加
            var alter_sql = "ALTER TABLE $TABLE_NAME ADD COLUMN phone VARCHAR;"
            Log.d(TAG, "alter_sql:" + alter_sql)
            db.execSQL(alter_sql)
            alter_sql = "ALTER TABLE $TABLE_NAME ADD COLUMN password VARCHAR;"
            Log.d(TAG, "alter_sql:" + alter_sql)
            db.execSQL(alter_sql)
        }
    }
 
    fun delete(condition: String): Int {
        var count = 0
        use {
            count = delete(TABLE_NAME, condition, null)
        }
        return count
    }
 
    fun insert(info: UserInfo): Long {
        val infoArray = mutableListOf(info)
        return insert(infoArray)
    }
 
    fun insert(infoArray: MutableList<UserInfo>): Long {
        var result: Long = -1
        for (i in infoArray.indices) {
            val info = infoArray[i]
            var tempArray: List<UserInfo>
            // 如果存在同名记录,则更新记录
            // 注意条件语句的等号后面要用单引号括起来
            if (info.name.isNotEmpty()) {
                val condition = "name='${info.name}'"
                tempArray = query(condition)
                if (tempArray.size > 0) {
                    update(info, condition)
                    result = tempArray[0].rowid
                    continue
                }
            }
            // 如果存在同样的手机号码,则更新记录
            if (info.phone.isNotEmpty()) {
                val condition = "phone='${info.phone}'"
                tempArray = query(condition)
                if (tempArray.size > 0) {
                    update(info, condition)
                    result = tempArray[0].rowid
                    continue
                }
            }
            // 不存在唯一性重复的记录,则插入新记录
            val cv = ContentValues()
            cv.put("name", info.name)
            cv.put("age", info.age)
            cv.put("height", info.height)
            cv.put("weight", info.weight)
            cv.put("married", info.married)
            cv.put("update_time", info.update_time)
            cv.put("phone", info.phone)
            cv.put("password", info.password)
            use {
                result = insert(TABLE_NAME, "", cv)
            }
            // 添加成功后返回行号,失败后返回-1
            if (result == -1L) {
                return result
            }
        }
        return result
    }
 
    @JvmOverloads
    fun update(info: UserInfo, condition: String = "rowid=${info.rowid}"): Int {
        val cv = ContentValues()
        cv.put("name", info.name)
        cv.put("age", info.age)
        cv.put("height", info.height)
        cv.put("weight", info.weight)
        cv.put("married", info.married)
        cv.put("update_time", info.update_time)
        cv.put("phone", info.phone)
        cv.put("password", info.password)
        var count = 0
        use {
            count = update(TABLE_NAME, cv, condition, null)
        }
        return count
    }
 
    fun query(condition: String): List<UserInfo> {
        val sql = "select rowid,_id,name,age,height,weight,married,update_time,phone,password from $TABLE_NAME where $condition;"
        Log.d(TAG, "query sql: " + sql)
        var infoArray = mutableListOf<UserInfo>()
        use {
            val cursor = rawQuery(sql, null)
            if (cursor.moveToFirst()) {
                while (true) {
                    val info = UserInfo()
                    info.rowid = cursor.getLong(0)
                    info.xuhao = cursor.getInt(1)
                    info.name = cursor.getString(2)
                    info.age = cursor.getInt(3)
                    info.height = cursor.getLong(4)
                    info.weight = cursor.getFloat(5)
                    //SQLite没有布尔型,用0表示false,用1表示true
                    info.married = if (cursor.getInt(6) == 0) false else true
                    info.update_time = cursor.getString(7)
                    info.phone = cursor.getString(8)
                    info.password = cursor.getString(9)
                    infoArray.add(info)
                    if (cursor.isLast) {
                        break
                    }
                    cursor.moveToNext()
                }
            }
            cursor.close()
        }
        return infoArray
    }
 
    fun queryByPhone(phone: String): UserInfo {
        val infoArray = query("phone='$phone'")
        val info: UserInfo = if (infoArray.size>0) infoArray[0] else UserInfo()
        return info
    }
 
    fun deleteAll(): Int = delete("1=1")
 
    fun queryAll(): List<UserInfo> = query("1=1")
 
}

因为ManagedSQLiteOpenHelper来自于Anko库,所以记得在UserDBHelper文件头部加上下面一行导入语句:

1
import org.jetbrains.anko.db.ManagedSQLiteOpenHelper

 

另外,有别于常见的anko-common包,Anko库把跟数据库有关的部分放到了anko-sqlite包中,故而还需修改模块的build.gradle文件,在dependencies节点中补充下述的anko-sqlite包编译配置:

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

 

现在有了用户信息表的管理类,在Activity代码中存取用户信息就方便多了,下面是往数据库存储用户信息和从数据库读取用户信息的代码片段:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
var helper: UserDBHelper = UserDBHelper.getInstance(this)
//往数据库存储用户信息
btn_save.setOnClickListener {
    when (true) {
        et_name.text.isEmpty() -> toast("请先填写姓名")
        et_age.text.isEmpty() -> toast("请先填写年龄")
        et_height.text.isEmpty() -> toast("请先填写身高")
        et_weight.text.isEmpty() -> toast("请先填写体重")
        else -> {
            val info = UserInfo(name = et_name.text.toString(),
            age = et_age.text.toString().toInt(),
            height = et_height.text.toString().toLong(),
            weight = et_weight.text.toString().toFloat(),
            married = bMarried,
            update_time = DateUtil.nowDateTime)
            helper.insert(info)
            toast("数据已写入SQLite数据库")
        }
    }
}
 
//从数据库读取用户信息
private fun readSQLite() {
    val userArray = helper.queryAll()
    var desc = "数据库查询到${userArray.size}条记录,详情如下:"
    for (i in userArray.indices) {
        val item = userArray[i]
        desc = "$desc\n第${i+1}条记录信息如下:" +
                "\n 姓名为${item.name}" +
                "\n 年龄为${item.age}" +
                "\n 身高为${item.height}" +
                "\n 体重为${item.weight}" +
                "\n 婚否为${item.married}" +
                "\n 更新时间为${item.update_time}"
    }
    if (userArray.isEmpty()) {
        desc = "数据库查询到的记录为空"
    }
    tv_sqlite.text = desc
}

  

posted @   aqi00  阅读(781)  评论(0编辑  收藏  举报
编辑推荐:
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 【.NET】调用本地 Deepseek 模型
点击右上角即可分享
微信分享提示