预计阅读7分钟

Kotlin 封装 DB 操作举例

移动开发

首先继承SQLiteOpenHelper类，建一个自己的BaseDbHelper.kt

class BaseDbHelper(context: Context) : SQLiteOpenHelper(context, DB_NAME, null, DB_VERSION) {

    private val TAG = BaseDbHelper::class.java!!.getSimpleName()

    companion object {
        private val DB_NAME = "account.db"
        private val DB_VERSION = 6
    }

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(UserTable.TABLE_SQL)
    }

    /**
     * onUpgrade()方法在数据库版本每次发生变化时都会把用户手机上的数据库表删除，然后再重新创建。<br></br>
     * 一般在实际项目中是不能这样做的，正确的做法是在更新数据库表结构时，还要考虑用户存放于数据库中的数据不会丢失,从版本几更新到版本几。(非
     * Javadoc)
     */
    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        if (db != null && newVersion > oldVersion) {
            try {
                when (oldVersion) {
                    1 -> {
                        run {
                            addAccountAvatarColumn(db)
                            Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] 1 update db, oldVersion = " + oldVersion)
                        }
                        run {
                            //CREATE TABLE Account ( userId TEXT PRIMARY KEY, flyme TEXT, phone TEXT, nickName TEXT NOT NULL, icon TEXT);
                            db.execSQL("drop table if exists account_address")
                            Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] 2 update db, oldVersion = " + oldVersion)
                        }
                        run {
                            addAccountAreaCodeColumn(db)//添加国家码一栏
                            db.execSQL("drop table if exists account_address")
                            Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] 3 update db, oldVersion = " + oldVersion)
                        }
                        run {
                            addAccountEmailColumn(db)//添加邮件
                            db.execSQL("drop table if exists account_address")
                            Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] 4 update db, oldVersion = " + oldVersion)
                        }
                        run { addBackgroundImageColumn(db) }
                        Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] update db, oldVersion = " + oldVersion)
                    }
                    2 -> {
                        run {
                            db.execSQL("drop table if exists account_address")
                            Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] 2 update db, oldVersion = " + oldVersion)
                        }
                        run {
                            addAccountAreaCodeColumn(db)
                            db.execSQL("drop table if exists account_address")
                            Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] 3 update db, oldVersion = " + oldVersion)
                        }
                        run {
                            addAccountEmailColumn(db)
                            db.execSQL("drop table if exists account_address")
                            Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] 4 update db, oldVersion = " + oldVersion)
                        }
                        run { addBackgroundImageColumn(db) }
                        Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] update db, oldVersion = " + oldVersion)
                    }
                    3 -> {
                        run {
                            addAccountAreaCodeColumn(db)
                            db.execSQL("drop table if exists account_address")
                            Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] 3 update db, oldVersion = " + oldVersion)
                        }
                        run {
                            addAccountEmailColumn(db)
                            db.execSQL("drop table if exists account_address")
                            Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] 4 update db, oldVersion = " + oldVersion)
                        }
                        run { addBackgroundImageColumn(db) }
                        Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] update db, oldVersion = " + oldVersion)
                    }
                    4 -> {
                        run {
                            addAccountEmailColumn(db)
                            db.execSQL("drop table if exists account_address")
                            Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] 4 update db, oldVersion = " + oldVersion)
                        }
                        run { addBackgroundImageColumn(db) }
                        Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] update db, oldVersion = " + oldVersion)
                    }
                    5 -> {
                        run { addBackgroundImageColumn(db) }
                        Log.d(BaseDbHelper::class.java!!.getSimpleName(), "[onUpgrade] update db, oldVersion = " + oldVersion)
                    }
                }
            } catch (e: Exception) {
            }

        }
    }

    override fun onDowngrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        if (db != null && oldVersion > newVersion) {
            var cursor: Cursor? = null
            try {
                cursor = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null)
                val tableNames = ArrayList<String>()
                if (cursor != null && cursor.moveToFirst()) {
                    do {
                        val name = cursor.getString(0)
                        if (!TextUtils.isEmpty(name) && !name.equals("android_metadata", ignoreCase = true)) {
                            tableNames.add(name)
                        }
                    } while (cursor.moveToNext())
                }
                if (tableNames.size > 0) {
                    for (name in tableNames) {
                        db.execSQL("DROP TABLE IF EXISTS " + name)
                    }
                }
                onCreate(db)
            } catch (e: Exception) {
            } finally {
                if (cursor != null) {
                    cursor.close()
                }
            }
        }
    }

    private fun addAccountAvatarColumn(db: SQLiteDatabase) {
        try {
            db.execSQL("ALTER TABLE " + UserTable.TABLE_NAME + " ADD COLUMN "
                    + UserTable.AVATAR_ICON + " TEXT;")
        } catch (e: Exception) {
            Log.e(TAG, "[addAccountAvatarColumn] error = " + e.message)
        }

    }

    private fun addAccountAreaCodeColumn(db: SQLiteDatabase) {
        try {
            db.execSQL("ALTER TABLE " + UserTable.TABLE_NAME + " ADD COLUMN "
                    + UserTable.COUNTRY_CODE + " TEXT;")
        } catch (e: Exception) {
            Log.e(TAG, "[addAccountAreaCodeColumn] error = " + e.message)
        }

    }

    private fun addAccountEmailColumn(db: SQLiteDatabase) {
        try {
            db.execSQL("ALTER TABLE " + UserTable.TABLE_NAME + " ADD COLUMN "
                    + UserTable.EMAIL + " TEXT;")
        } catch (e: Exception) {
            Log.e(TAG, "[addAccountEmailColumn] error = " + e.message)
        }

    }

    private fun addBackgroundImageColumn(db: SQLiteDatabase) {
        try {
            db.execSQL("ALTER TABLE " + UserTable.TABLE_NAME + " ADD COLUMN "
                    + UserTable.BACKGROUND_IMAGE + " TEXT;")
        } catch (e: Exception) {
            Log.e(TAG, "[addBackgroundImageColumn] error = " + e.message)
        }

    }
}

然后是新建一个单例CoreDBManager.kt，用来业务调用操作数据库增删改查等

class CoreDBManager private constructor(context: Context) {

    private var mDataBase: SQLiteDatabase? = null
    private val mBaseDbHelper: BaseDbHelper
    private val mOpenCounter = AtomicInteger()

    companion object {

        private val TAG = CoreDBManager::class.java!!.getSimpleName()
        @Volatile private var mInstance: CoreDBManager? = null

        fun getInstance(context: Context): CoreDBManager? {
            if (mInstance == null) {
                synchronized(CoreDBManager::class.java) {
                    if (mInstance == null) {
                        mInstance = CoreDBManager(context)
                    }
                }
            }
            return mInstance
        }
    }

    private val writableDatabase: SQLiteDatabase?
        get() {
            if (mOpenCounter.incrementAndGet() == 1) {
                mDataBase = mBaseDbHelper.writableDatabase
            }
            return mDataBase
        }

    init {
        mBaseDbHelper = BaseDbHelper(context)
    }

    private fun closeDataBase() {
        if (mOpenCounter.decrementAndGet() == 0) {
            if (mDataBase != null) {
                mDataBase!!.close()
            }
        }
    }

    /**
     * 查询指定用户
     *
     * @param userId
     * @return
     */
    fun queryUserInfo(userId: String): UserInfo? {
        //如果Realm关闭，所有查询得到的RealmObject都不能使用了
        val db = writableDatabase
        var result: UserInfo? = null
        try {
            val selection = UserTable.USER_ID + "=?"
            val selectionArgs = arrayOf(userId)
            var cursor: Cursor? = null
            try {
                cursor = db!!.query(UserTable.TABLE_NAME, null, if (TextUtils.isEmpty(userId)) null else selection,
                        if (TextUtils.isEmpty(userId)) null else selectionArgs, null, null, null)
                if (cursor != null) {
                    if (cursor.moveToFirst()) {
                        result = UserInfo()
                        result.parseCursor(cursor)
                    }
                }
            } catch (e: Exception) {
                Log.e(TAG, "[queryUserInfo] error = " + e.message)
            } finally {
                cursor?.close()
            }
        } finally {
            closeDataBase()
        }
        return result
    }

    /**
     * 查询指定用户
     *
     * @return
     */
    fun queryUserInfo(projections: Array<String>, where: String, selectionArgs: Array<String>, sortOrder: String): UserInfo? {
        val db = writableDatabase
        var result: UserInfo? = null
        try {
            var cursor: Cursor? = null
            try {
                cursor = db!!.query(UserTable.TABLE_NAME, projections, where, selectionArgs, null, null, sortOrder)
                if (cursor != null) {
                    if (cursor.moveToFirst()) {
                        result = UserInfo()
                        result.parseCursor(cursor)
                    }
                }
            } catch (e: Exception) {
            } finally {
                cursor?.close()
            }
        } catch (e: Exception) {
            Log.e(TAG, "[queryUserInfo] error = " + e.message)
        } finally {
            closeDataBase()
        }
        return result
    }

    /**
     * 用户信息写入数据库
     *
     * @param userInfo
     */
    fun insertOrUpdate(userInfo: UserInfo): Boolean {
        val db = writableDatabase
        val cursor = db!!.query(
                UserTable.TABLE_NAME,
                arrayOf(UserTable.USER_ID),
                UserTable.USER_ID + " = ?",
                arrayOf(userInfo.userId), null, null, null)
        var result: Long = 0
        try {
            val values = userInfo.contentValues
            if (cursor != null && cursor.count > 0) {
                //更新操作
                cursor.moveToFirst()
                val userId = cursor.getString(cursor.getColumnIndex(UserTable.USER_ID))
                val newId = userInfo.userId
                if (!TextUtils.isEmpty(newId) && newId != userId) {//容错，数据库只有一个用户信息
                    result = db.update(UserTable.TABLE_NAME, values, UserTable.USER_ID + " = ?", arrayOf(userId)).toLong()
                } else {
                    result = db.update(UserTable.TABLE_NAME, values, UserTable.USER_ID + " = ?", arrayOf(userInfo.userId)).toLong()
                }
            } else {
                //插入操作
                result = db.insert(UserTable.TABLE_NAME, null, values)
            }
        } catch (e: Exception) {
            Log.e(TAG, "[insertOrUpdate] error = " + e.message)
        } finally {
            cursor?.close()
            closeDataBase()
        }
        return result > 0
    }

    /**
     * 删除数据库全部数据
     */
    fun deleteAllDatas() {
        val db = writableDatabase
        try {
            db!!.delete(UserTable.TABLE_NAME, null, null)
        } catch (e: Exception) {
            Log.e(TAG, "[deleteAllDatas] error = " + e.message)
        } finally {
            closeDataBase()
        }
    }
}

本文标题:Kotlin 封装 DB 操作举例

文章作者:ngudream

发布时间:2017-08-29, 21:02:46

原始链接:http://ngudream.com/2017/08/29/kotlin-database-helper/

许可协议:"署名-非商用-相同方式共享 3.0" 转载请保留原文链接及作者。

文章目录
|