很高兴,这个系列的文章终于上了两位数,给自己点个赞;不过上篇文章给自己挖了个坑,说是这次介绍anko中的sqlite,
本来就是春节前的最后一周,完全无心看书啊,终于,终于,在参考了各种代码之后,算是做了一次初步的封装;
正如自己blog的描述,我们不生产代码,只是代码的搬运工,这次也一样,大部分的封装的代码参考自《Kotlin for Android Developers》,其中代码见传送门,只不过,我稍微简化修改了下,更适合自己的开发习惯,下面我们还是来看具体的代码:
step1
引入anko sqlite的库,这里我使用的版本是0.8.2,最新的是0.9.0,不过这次的封装不适用于最新版本,如果使用最新版本,请自行调整下:
添加以下代码到你的build.gradle文件中:
compile 'org.jetbrains.anko:anko-sqlite:0.8.2'
step2
创建表结构:
Tables.kt
package com.vslimit.kotlindemo.db
/**
* Created by vslimit on 17/1/25.
*/
object PersonTable {
val TABLE_NAME = "Person"
val ID = "_id"
val NAME = "name"
val ADDRESS = "address"
val COMPANY_ID = "companyId"
}
object CompanyTable{
val TABLE_NAME = "Company"
val ID = "_id"
val NAME = "name"
val ADDRESS = "address"
}
其实,最开始是打算,做个一对多的关系,后来,后来,因为时间不过,就做了CompanyTable的单表操作,有兴趣的可以把后续的做了。
step4
创建表对象:
DomainClasses.kt
package com.vslimit.kotlindemo.db
import java.util.*
/**
* Created by vslimit on 17/1/26.
*/
data class Company(val map: MutableMap<String, Any?>) {
var _id: Long by map
var name: String by map
var address: String by map
constructor() : this(HashMap()) {
}
constructor(id:Long,name: String,address:String) : this(HashMap()) {
this._id = id
this.name = name
this.address = address
}
}
step4
访问数据库,这里引用官方教程的一段话:
如果你使用了SQLiteOpenHelper,你可以调用getReadableDatabase()或者getWritableDatabase()(在生产环境的代码中产生的结果是一样的),但是之后你必须在接收到SQLiteDatabase的中调用close() 方法。你也必须在某个地方缓存助手类,同时,如果你从多个线程中使用了它,还得注意并发访问的问题。所有的这些要点都是难度相当大的。这就是为什么Android开发者并没有真正的使用默认SQLite API而宁愿使用代价昂贵的包装器,比如ORM库的原因。
Anko提供了一个特殊的类ManagedSQLiteOpenHelper无缝的替代了默认的类。这里的实现是参照《Kotlin for Android Developers》,所以可能与官方实现有些区别:
package com.vslimit.kotlindemo.db
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import com.vslimit.kotlindemo.App
import org.jetbrains.anko.db.*
/**
* Created by vslimit on 17/1/23.
*/
class DatabaseOpenHelper(ctx: Context = App.instance) : ManagedSQLiteOpenHelper(ctx, DB_NAME, null, DB_VERSION) {
companion object {
val DB_NAME = "person"
val DB_VERSION = 1
val instance by lazy { DatabaseOpenHelper() }
}
override fun onCreate(db: SQLiteDatabase) {
db.createTable(CompanyTable.TABLE_NAME, true,
CompanyTable.ID to INTEGER + PRIMARY_KEY + UNIQUE,
CompanyTable.NAME to TEXT,
CompanyTable.ADDRESS to TEXT)
// db.createTable(PersonTable.TABLE_NAME,true,
// PersonTable.ID to INTEGER + PRIMARY_KEY + UNIQUE,
// PersonTable.NAME to TEXT,
// PersonTable.ADDRESS to TEXT,
// PersonTable.COMPANY_ID to INTEGER)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
db.dropTable(CompanyTable.TABLE_NAME, true)
// db.dropTable(PersonTable.TABLE_NAME, true)
onCreate(db)
}
}
在ContextExtensions.kt扩展下:
val Context.database: DatabaseOpenHelper
get() = DatabaseOpenHelper.instance
step5
对原有的Sqlite操作进行扩展:
DatabaseExtensions.kt
/*
* Copyright 2015 Antonio Leiva
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.vslimit.kotlindemo.extensions
import android.database.sqlite.SQLiteDatabase
import org.jetbrains.anko.db.MapRowParser
import org.jetbrains.anko.db.SelectQueryBuilder
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)
})
fun <T : Any> SelectQueryBuilder.parseOpt(parser: (Map<String, Any>) -> T): T? =
parseOpt(object : MapRowParser<T> {
override fun parseRow(columns: Map<String, Any>): T = parser(columns)
})
fun SQLiteDatabase.clear(tableName: String){
execSQL("delete from $tableName")
}
fun SelectQueryBuilder.byId(id: Long) = whereSimple("_id = ?", id.toString())
大家看注释就晓得,这段代码是赤果果的照搬过来的,其实上面的代码也基本上是搬过来的,哈哈哈哈;
这里还需要有个Collections的扩展,我一起搬过来吧:
CollectionsExtensions.kt
/*
* Copyright 2015 Antonio Leiva
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.vslimit.kotlindemo.extensions
import java.util.*
fun <K, V : Any> MutableMap<K, V?>.toVarargArray(): Array<out Pair<K, V>> =
map({ Pair(it.key, it.value!!) }).toTypedArray()
inline fun <T, R : Any> Iterable<T>.firstResult(predicate: (T) -> R?): R {
for (element in this) {
val result = predicate(element)
if (result != null) return result
}
throw NoSuchElementException("No element matching predicate was found.")
}
至此,搬代码结果,其实原作者里,还进行了进一步的封装,但是,就我个人而言,我觉得到此封装已经足够我们使用了,下面我们来看看如何使用:
新建一个fragment,一个textview,3个button,具体代码我就不写了,我只写fragment中sqlite的操作:
package com.vslimit.kotlindemo.fragment
import android.os.Bundle
import android.view.View
import com.vslimit.kotlindemo.R
import com.vslimit.kotlindemo.db.Company
import com.vslimit.kotlindemo.db.CompanyTable
import com.vslimit.kotlindemo.extensions.*
import kotlinx.android.synthetic.main.fragment_companys.*
import org.jetbrains.anko.async
import org.jetbrains.anko.db.insert
import org.jetbrains.anko.db.select
import org.jetbrains.anko.db.update
import org.jetbrains.anko.onClick
import java.util.*
/**
* Created by vslimit on 16/12/31.
*/
class CompanyListFragment : BaseFragment() {
override val layoutResourceId: Int = R.layout.fragment_companys
companion object {
fun getInstance(): CompanyListFragment {
return CompanyListFragment()
}
}
override fun onViewCreated(view: View?, savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
init()
showBtn.onClick {
context.database.use {
val company = select(CompanyTable.TABLE_NAME).byId(1).parseOpt { Company(HashMap(it)) }
async() {
nameTv.text = company?.name
}
}
}
updateBtn.onClick {
context.database.use {
val company = select(CompanyTable.TABLE_NAME).byId(1).parseOpt { Company(HashMap(it)) }
company?.name = "update_name"
update(CompanyTable.TABLE_NAME, *company!!.map.toVarargArray()).where("_id = {id}", "id" to 1).exec()
}
}
insertBtn.onClick {
context.database.use {
val company = Company()
company.name = "demo_name"
company.address = "demo_address"
insert(CompanyTable.TABLE_NAME, *company.map.toVarargArray())
}
}
}
override fun onResume() {
super.onResume()
}
fun init() {
context.database.use {
val list = select(CompanyTable.TABLE_NAME)
.parseList { Company(HashMap(it)) }
async() {
nameTv.text = list.size.toString()
}
}
}
override fun onDestroy() {
super.onDestroy()
}
}
fragment中,init()方法是查询所有数据,三个button分别是显示详情,插入和更新。
如果要操作事务:
Anko中有一个叫做transaction()的特殊函数。它允许你将多个数据库操作放在一个闭合的单独的SQLite事务中。
transaction {
// Your transaction code
}
如果大括号代码块中如果没有抛出任何的异常,事务将会被标记为成功!
备注: 如果你出于某些原因想中止事务,只需抛出TransactionAbortException异常。这种情况下,你不需要手动处理这些异常。
至此,基于Kotlin封装的的Sqlite已经实现,对于一些简单的sqlite操作,这样已经足够了,当然了,跟greendao相比,好处在于不用另外生成dao类的文件,便利性上,可能稍微差些,毕竟greendao是专职的orm;如果有更复杂的数据库操作,大家可以考虑Realm,而不是Sqlite
本文中的所有代码已经提交到git上了,大家如果喜欢就去git下star下吧。
(关于anko中的sqlite更多的内容,请查看官方文档:英文传送门、中文传送门)
下期预告:下期春节,就不预告了,上周的预告差点没完成,不过,还是要有个方向,下周可能是Realm,也可能是其他的,只能到时想到什么写什么了,在这里给各位朋友拜个早年,祝大家新年快乐,鸡年大吉!
Sqlite的代码详见:https://github.com/vslimit/kotlindemo
网友评论
package com.xxxx.demo
import android.database.sqlite.SQLiteDatabase
import org.jetbrains.anko.db.MapRowParser
import org.jetbrains.anko.db.SelectQueryBuilder
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)
})
fun <T : Any> SelectQueryBuilder.parseOpt(parser: (Map<String, Any?>) -> T): T? = parseOpt(object : MapRowParser<T> {
override fun parseRow(columns: Map<String, Any?>) = parser(columns)
})
fun SQLiteDatabase.clear(tableName: String) {
execSQL("delete from $tableName")
}
fun SelectQueryBuilder.byId(id: Long) = whereSimple("_id = ?", id.toString())
下面是方法调用链:
1.fun SQLiteDatabase.select(tableName: String): SelectQueryBuilder{} // 构造SQB对象
2.fun <T: Any> parseList(parser: RowParser<T>): List<T> = AnkoInternals.useCursor(execInternal()) {} // 进行sql查询
3.private fun execInternal(): Cursor {} // 查询结果保存在cursor里
4.fun <T: Any> Cursor.parseList(parser: RowParser<T>): List<T> = AnkoInternals.useCursor(this) {} // 遍历cursor, 把结果保存在一个ArrayList<T>对象里
5.fun parseRow(columns: Array<Any>): T // 把泛型List转为具体对象的List