SQLite数据库 #

一、SQLite概述 #

SQLite是Android内置的轻量级关系型数据库，支持标准的SQL语法，适合存储结构化数据。

1.1 SQLite特点 #

轻量级：占用资源少
零配置：无需安装和配置
单文件：整个数据库存储在一个文件中
ACID事务：支持事务操作
跨平台：支持多种操作系统

1.2 数据类型 #

类型	说明
INTEGER	整数
REAL	浮点数
TEXT	文本字符串
BLOB	二进制数据
NULL	空值

二、创建数据库 #

2.1 SQLiteOpenHelper #

kotlin

class DatabaseHelper(context: Context) : SQLiteOpenHelper(
    context,
    DATABASE_NAME,
    null,
    DATABASE_VERSION
) {
    
    companion object {
        private const val DATABASE_NAME = "my_app.db"
        private const val DATABASE_VERSION = 1
        
        private const val TABLE_USERS = "users"
        private const val COLUMN_ID = "_id"
        private const val COLUMN_NAME = "name"
        private const val COLUMN_EMAIL = "email"
        private const val COLUMN_AGE = "age"
        
        private const val CREATE_TABLE_USERS = """
            CREATE TABLE $TABLE_USERS (
                $COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                $COLUMN_NAME TEXT NOT NULL,
                $COLUMN_EMAIL TEXT UNIQUE,
                $COLUMN_AGE INTEGER
            )
        """
    }
    
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(CREATE_TABLE_USERS)
    }
    
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS $TABLE_USERS")
        onCreate(db)
    }
}

2.2 使用数据库 #

kotlin

class MainActivity : AppCompatActivity() {
    
    private lateinit var databaseHelper: DatabaseHelper
    private lateinit var database: SQLiteDatabase
    
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        
        databaseHelper = DatabaseHelper(this)
        database = databaseHelper.writableDatabase
    }
    
    override fun onDestroy() {
        super.onDestroy()
        database.close()
        databaseHelper.close()
    }
}

三、CRUD操作 #

3.1 插入数据 #

kotlin

fun insertUser(name: String, email: String, age: Int): Long {
    val values = ContentValues().apply {
        put(COLUMN_NAME, name)
        put(COLUMN_EMAIL, email)
        put(COLUMN_AGE, age)
    }
    
    return database.insert(TABLE_USERS, null, values)
}

// 使用
val id = insertUser("张三", "zhangsan@example.com", 25)
if (id != -1L) {
    Log.d("Database", "插入成功，ID: $id")
}

3.2 查询数据 #

kotlin

fun getAllUsers(): List<User> {
    val users = mutableListOf<User>()
    
    val cursor = database.query(
        TABLE_USERS,
        null,  // 所有列
        null,  // selection
        null,  // selectionArgs
        null,  // groupBy
        null,  // having
        "$COLUMN_NAME ASC"  // orderBy
    )
    
    cursor.use {
        while (it.moveToNext()) {
            val id = it.getLong(it.getColumnIndexOrThrow(COLUMN_ID))
            val name = it.getString(it.getColumnIndexOrThrow(COLUMN_NAME))
            val email = it.getString(it.getColumnIndexOrThrow(COLUMN_EMAIL))
            val age = it.getInt(it.getColumnIndexOrThrow(COLUMN_AGE))
            
            users.add(User(id, name, email, age))
        }
    }
    
    return users
}

// 条件查询
fun getUserById(id: Long): User? {
    val cursor = database.query(
        TABLE_USERS,
        null,
        "$COLUMN_ID = ?",
        arrayOf(id.toString()),
        null,
        null,
        null
    )
    
    cursor.use {
        if (it.moveToFirst()) {
            val name = it.getString(it.getColumnIndexOrThrow(COLUMN_NAME))
            val email = it.getString(it.getColumnIndexOrThrow(COLUMN_EMAIL))
            val age = it.getInt(it.getColumnIndexOrThrow(COLUMN_AGE))
            return User(id, name, email, age)
        }
    }
    
    return null
}

// 使用rawQuery
fun searchUsers(keyword: String): List<User> {
    val users = mutableListOf<User>()
    
    val cursor = database.rawQuery(
        "SELECT * FROM $TABLE_USERS WHERE $COLUMN_NAME LIKE ?",
        arrayOf("%$keyword%")
    )
    
    cursor.use {
        while (it.moveToNext()) {
            // 解析数据
        }
    }
    
    return users
}

3.3 更新数据 #

kotlin

fun updateUser(id: Long, name: String, email: String, age: Int): Int {
    val values = ContentValues().apply {
        put(COLUMN_NAME, name)
        put(COLUMN_EMAIL, email)
        put(COLUMN_AGE, age)
    }
    
    return database.update(
        TABLE_USERS,
        values,
        "$COLUMN_ID = ?",
        arrayOf(id.toString())
    )
}

// 更新部分字段
fun updateUserName(id: Long, newName: String): Int {
    val values = ContentValues().apply {
        put(COLUMN_NAME, newName)
    }
    
    return database.update(
        TABLE_USERS,
        values,
        "$COLUMN_ID = ?",
        arrayOf(id.toString())
    )
}

3.4 删除数据 #

kotlin

fun deleteUser(id: Long): Int {
    return database.delete(
        TABLE_USERS,
        "$COLUMN_ID = ?",
        arrayOf(id.toString())
    )
}

// 删除所有数据
fun deleteAllUsers(): Int {
    return database.delete(TABLE_USERS, null, null)
}

四、事务处理 #

kotlin

fun transferMoney(fromId: Long, toId: Long, amount: Double): Boolean {
    database.beginTransaction()
    
    try {
        // 扣款
        val fromValues = ContentValues().apply {
            put("balance", "balance - $amount")
        }
        val fromUpdated = database.update(
            "accounts",
            fromValues,
            "id = ? AND balance >= ?",
            arrayOf(fromId.toString(), amount.toString())
        )
        
        if (fromUpdated == 0) {
            return false
        }
        
        // 入账
        val toValues = ContentValues().apply {
            put("balance", "balance + $amount")
        }
        database.update("accounts", toValues, "id = ?", arrayOf(toId.toString()))
        
        database.setTransactionSuccessful()
        return true
    } finally {
        database.endTransaction()
    }
}

五、数据库升级 #

kotlin

class DatabaseHelper(context: Context) : SQLiteOpenHelper(
    context,
    DATABASE_NAME,
    null,
    DATABASE_VERSION
) {
    
    companion object {
        private const val DATABASE_VERSION = 2
    }
    
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(CREATE_TABLE_USERS)
        db.execSQL(CREATE_TABLE_ORDERS)  // 版本2新增
    }
    
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        when (oldVersion) {
            1 -> {
                // 从版本1升级到版本2
                db.execSQL(CREATE_TABLE_ORDERS)
                db.execSQL("ALTER TABLE $TABLE_USERS ADD COLUMN phone TEXT")
            }
        }
    }
}

六、封装DAO #

kotlin

data class User(
    val id: Long = 0,
    val name: String,
    val email: String,
    val age: Int
)

interface UserDao {
    fun insert(user: User): Long
    fun update(user: User): Int
    fun delete(id: Long): Int
    fun getById(id: Long): User?
    fun getAll(): List<User>
}

class UserDaoImpl(private val database: SQLiteDatabase) : UserDao {
    
    override fun insert(user: User): Long {
        val values = ContentValues().apply {
            put(COLUMN_NAME, user.name)
            put(COLUMN_EMAIL, user.email)
            put(COLUMN_AGE, user.age)
        }
        return database.insert(TABLE_USERS, null, values)
    }
    
    override fun update(user: User): Int {
        val values = ContentValues().apply {
            put(COLUMN_NAME, user.name)
            put(COLUMN_EMAIL, user.email)
            put(COLUMN_AGE, user.age)
        }
        return database.update(
            TABLE_USERS,
            values,
            "$COLUMN_ID = ?",
            arrayOf(user.id.toString())
        )
    }
    
    override fun delete(id: Long): Int {
        return database.delete(
            TABLE_USERS,
            "$COLUMN_ID = ?",
            arrayOf(id.toString())
        )
    }
    
    override fun getById(id: Long): User? {
        val cursor = database.query(
            TABLE_USERS,
            null,
            "$COLUMN_ID = ?",
            arrayOf(id.toString()),
            null,
            null,
            null
        )
        
        cursor.use {
            if (it.moveToFirst()) {
                return it.toUser()
            }
        }
        return null
    }
    
    override fun getAll(): List<User> {
        val users = mutableListOf<User>()
        val cursor = database.query(TABLE_USERS, null, null, null, null, null, null)
        
        cursor.use {
            while (it.moveToNext()) {
                users.add(it.toUser())
            }
        }
        return users
    }
    
    private fun Cursor.toUser(): User {
        return User(
            id = getLong(getColumnIndexOrThrow(COLUMN_ID)),
            name = getString(getColumnIndexOrThrow(COLUMN_NAME)),
            email = getString(getColumnIndexOrThrow(COLUMN_EMAIL)),
            age = getInt(getColumnIndexOrThrow(COLUMN_AGE))
        )
    }
}

七、使用协程 #

kotlin

class UserRepository(private val context: Context) {
    
    private val databaseHelper by lazy { DatabaseHelper(context) }
    
    suspend fun getAllUsers(): List<User> = withContext(Dispatchers.IO) {
        val database = databaseHelper.readableDatabase
        val userDao = UserDaoImpl(database)
        val users = userDao.getAll()
        database.close()
        users
    }
    
    suspend fun insertUser(user: User): Long = withContext(Dispatchers.IO) {
        val database = databaseHelper.writableDatabase
        val userDao = UserDaoImpl(database)
        val id = userDao.insert(user)
        database.close()
        id
    }
}

八、总结 #

本章详细介绍了SQLite数据库：

SQLite的基本概念和特点
使用SQLiteOpenHelper创建数据库
CRUD操作的实现
事务处理
数据库升级策略
DAO封装模式

SQLite是Android本地数据存储的重要方式，但对于复杂项目，推荐使用Room等ORM框架来简化数据库操作。