Android SQLite总结

SQLite

概述

• SQLite是一款轻量级的关系型数据库，运行速度非常快，占用资源很少，通常只有几百KB内存，
• 数据库文件存放路径：/data/data/<package name>/databases/目录下

数据类型

• NULL：空值
• VARCHAR(n)：长度不固定，最大长度为n的字符串，n不能操作4000
• CHAR(n)：长度固定为n的字符串，n不能超过254
• INTEGER：整型
• REAL：浮动型
• TEXT：文本类型
• BLOB：二进制类型
• DATA：年月日
• TIME：时分秒

使用

创建数据库

• 需要继承SQLiteOpenHelper类，并实现三个方法
• 构造函数：依次传入参数（上下文Context、数据库名、null、数据库版本号）
• onCreate()：创建表
• onUpgrade()：升级表

package com.example.myapplication.sqlite

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper


class MySQLiteOpenHelper(
    context: Context?
) : SQLiteOpenHelper(context, DB_NAME, null, VERSION) {

    companion object {
        const val DB_NAME = "mysqlite.db"
        const val VERSION = 1
    }

    private val CREATE_COURSE = """
        create table if not exists course(
        id integer primary key autoincrement,
        name text,
        teacher text,
        price real
        )
    """.trimIndent()

    private val CREATE_TEACHER = """
        create table if not exists teacher(
        id integer primary key autoincrement,
        name text,
        age integer,
        desc text
        )
    """.trimIndent()

    override fun onCreate(db: SQLiteDatabase?) {
        db?.execSQL(CREATE_COURSE)
        db?.execSQL(CREATE_TEACHER)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        //TODO 升级
    }
}

初始化

val dbHelper = MySQLiteOpenHelper(this)
val db = dbHelper.writableDatabase

增加数据

private fun addData(): Boolean {
    val values = ContentValues().apply {
        put(COURSE_NAME, "Java")
        put(COURSE_TEACHER, "小黑")
        put(COURSE_PRICE, 12.04)
    }
    val success = db.insert(TABLE_COURSE, null, values) != -1L
    return success
}

private fun addData2() {
    val sql =
    "insert into $TABLE_COURSE (${COURSE_NAME}, ${COURSE_TEACHER}, ${COURSE_PRICE}) values(?,?,?)"
    db.execSQL(sql, arrayOf("Android", "小白", "12.03"))
}

查询数据

private fun queryData() {
    val builder = StringBuilder()
    val cursor = db.query("course", null, null, null, null, null, null)
    with(cursor) {
        while (moveToNext()) {
            val id: String = cursor.getString(cursor.getColumnIndex("id"))
            val name: String = cursor.getString(cursor.getColumnIndex("name"))
            val teacher: String = cursor.getString(cursor.getColumnIndex("teacher"))
            val price: String = cursor.getString(cursor.getColumnIndex("price"))
            builder.append("id:${id},name:$name,teacher:$teacher,price:$price \n")
        }
    }
    cursor.close()
    binding.tvContent.text = builder.toString()
}

private fun queryData2() {
    val builder = StringBuilder()
    val sql =
    "select $COURSE_ID,$COURSE_NAME,$COURSE_TEACHER,$COURSE_PRICE from $TABLE_COURSE where $COURSE_ID>10"
    val cursor = db.rawQuery(sql, null)
    with(cursor) {
        while (moveToNext()) {
            val id: String = cursor.getString(cursor.getColumnIndex("id"))
            val name: String = cursor.getString(cursor.getColumnIndex("name"))
            val teacher: String = cursor.getString(cursor.getColumnIndex("teacher"))
            val price: String = cursor.getString(cursor.getColumnIndex("price"))
            builder.append("id:${id},name:$name,teacher:$teacher,price:$price \n")
        }
    }
    cursor.close()
    binding.tvContent.text = builder.toString()
}

删除数据

private fun deleteData(): Boolean {
    val success = db.delete(TABLE_COURSE, "teacher=?", arrayOf<String>("小黑")) != -1
    return success
}

private fun deleteData2() {
    val sql = "delete from $TABLE_COURSE where teacher=?"
    db.execSQL(sql, arrayOf("小白"))
}

修改数据

private fun modifyData(): Boolean {
    val values = ContentValues().apply {
        put("teacher", "小红")
    }
    val success = db.update(TABLE_COURSE, values, "name=?", arrayOf("Java")) != -1
    return success
}

private fun modifyData2() {
    val sql = "update $TABLE_COURSE set teacher=? where name=?"
    db.execSQL(sql, arrayOf("小花", "Java"))
}

数据库升级

override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
    if (oldVersion <= 1) {
        updateVersion2(db)
    }
    if (oldVersion <= 2) {
        updateVersion3(db)
    }
}

private fun updateVersion2(db: SQLiteDatabase) {
    val sql = "alter table course add column teacher_id integer"
    db.execSQL(sql)
}

private fun updateVersion3(db: SQLiteDatabase) {
    val values = ContentValues().apply {
        put("price", "0.00")
    }
    db.update(TABLE_COURSE, values, null, null)
}

源码下载