萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> Android 開發

Android SQLite 整合完整指南:Room、原生 API 與最佳實踐

Android 資料庫選擇指南

Android 內建 SQLite (輕量級關聯式資料庫),但原生 API 操作繁瑣、易出錯。現代開發強烈建議使用 Room (Google 官方 ORM),僅在特殊需求 (極簡依賴、極高效能調優、舊碼遷移) 使用原生 API。

方案對比

特性原生 SQLiteOpenHelperRoom (推薦)SQLDelight (Kotlin 優先)
型別安全無 (Cursor 手動映射)編譯期驗證 SQL、實體映射編譯期生成型別安全 API
遷移管理手寫 onUpgrade聲明式遷移、自動驗證聲明式遷移
非同步/觀察手動 AsyncTask/ThreadRxJava/Flow/LiveData 原生支援Flow/Coroutines 原生
編譯期檢查無SQL 語法、實體對應驗證SQL 驗證、生成代碼
多平台僅 AndroidAndroid/JVMAndroid/iOS/JVM/JS/Native
學習曲線低 (但易犯錯)中 (註解處理器)中高

Room 完整實作指南 (現代標準)

1. 依賴配置 (build.gradle.kts)

dependencies {
    val room_version = "2.6.1"
    implementation("androidx.room:room-runtime:$room_version")
    implementation("androidx.room:room-ktx:$room_version") // Flow/LiveData 擴展
    kapt("androidx.room:room-compiler:$room_version")     // Java/KAPT
    // 或 KSP (更快): ksp("androidx.room:room-compiler:$room_version")
    
    // 可選:Paging 3 整合
    implementation("androidx.room:room-paging:$room_version")
    // 可選:RxJava 支援
    implementation("androidx.room:room-rxjava3:$room_version")
    // 可選:自動遷移測試
    testImplementation("androidx.room:room-testing:$room_version")
}

2. 實體定義

@Entity(
    tableName = "users",
    indices = [
        Index(value = ["email"], unique = true),
        Index(value = ["last_name", "first_name"])
    ],
    foreignKeys = [
        ForeignKey(
            entity = Department::class,
            parentColumns = ["id"],
            childColumns = ["department_id"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class User(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    @ColumnInfo(name = "first_name") val firstName: String,
    @ColumnInfo(name = "last_name") val lastName: String,
    @ColumnInfo(name = "email") val email: String,
    @ColumnInfo(name = "age") val age: Int,
    @ColumnInfo(name = "department_id") val departmentId: Long? = null,
    @ColumnInfo(name = "created_at") val createdAt: Long = System.currentTimeMillis(),
    @ColumnInfo(name = "is_active") val isActive: Boolean = true
)

// 嵌入物件 (複雜型別)
@Embedded
data class Address(
    val street: String,
    val city: String,
    @ColumnInfo(name = "zip_code") val zipCode: String,
    val country: String
)

// 型別轉換器 (TypeConverter)
class Converters {
    @TypeConverter
    fun fromTimestamp(value: Long?): Date? = value?.let { Date(it) }
    
    @TypeConverter
    fun toTimestamp(date: Date?): Long? = date?.time
    
    @TypeConverter
    fun fromList(list: List?): String? = list?.joinToString(",")
    
    @TypeConverter
    fun toList(data: String?): List = data?.split(",") ?: emptyList()
}

3. DAO (Data Access Object) 介面


@Dao
interface UserDao {
    // 插入
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(user: User): Long
    
    @Insert
    suspend fun insertAll(users: List): List
    
    // 更新
    @Update
    suspend fun update(user: User): Int
    
    // 刪除
    @Delete
    suspend fun delete(user: User): Int
    
    @Query("DELETE FROM users WHERE id = :id")
    suspend fun deleteById(id: Long): Int
    
    // 查詢 - 返回 Flow (生命週期感知、自動重查)
    @Query("SELECT * FROM users WHERE id = :id")
    fun getUserById(id: Long): Flow
    
    @Query("SELECT * FROM users WHERE is_active = 1 ORDER BY last_name, first_name")
    fun getAllActiveUsers(): Flow>
    
    @Query("SELECT * FROM users WHERE email LIKE :pattern")
    fun searchByEmail(pattern: String): Flow>
    
    // 複雜查詢:關聯查詢
    @Query("""
        SELECT u.*, d.name as department_name 
        FROM users u 
        LEFT JOIN departments d ON u.department_id = d.id 
        WHERE u.is_active = 1
    """)
    fun getUsersWithDepartment(): Flow>
    
    // 原始查詢 (RawQuery) - 動態 SQL
    @RawQuery
    fun rawQuery(query: SimpleSQLiteQuery): Flow>
    
    // 批次操作 (事務)
    @Transaction
    suspend fun insertWithDepartment(user: User, department: Department) {
        departmentDao.insert(department)
        insert(user.copy(departmentId = department.id))
    }
}

// 關聯查詢結果類別
data class UserWithDepartment(
    @Embedded val user: User,
    @Relation(
        parentColumn = "department_id",
        entityColumn = "id",
        entity = Department::class
    ) val department: Department?
)

4. Database 抽象類別


@Database(
    entities = [User::class, Department::class],
    version = 3,
    exportSchema = true  // 生成 schemas/ 目錄供遷移測試
)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
    abstract fun departmentDao(): DepartmentDao
    
    companion object {
        @Volatile private var INSTANCE: AppDatabase? = null
        
        fun getInstance(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                INSTANCE ?: buildDatabase(context).also { INSTANCE = it }
            }
        }
        
        private fun buildDatabase(context: Context): AppDatabase {
            return Room.databaseBuilder(context.applicationContext, AppDatabase::class.java, "app_db")
                .addMigrations(MIGRATION_1_2, MIGRATION_2_3)
                .fallbackToDestructiveMigration() // 開發階段允許破壞性遷移
                .setQueryExecutor(Executors.newFixedThreadPool(4))
                .setTransactionExecutor(Executors.newSingleThreadExecutor())
                .addCallback(object : RoomDatabase.Callback() {
                    override fun onCreate(db: SupportSQLiteDatabase) {
                        super.onCreate(db)
                        // 預填資料
                        scope.launch { seedDatabase() }
                    }
                })
                .build()
        }
    }
    
    // 遷移範例:v1 → v2 新增 age 欄位
    val MIGRATION_1_2 = object : Migration(1, 2) {
        override fun migrate(database: SupportSQLiteDatabase) {
            database.execSQL("ALTER TABLE users ADD COLUMN age INTEGER NOT NULL DEFAULT 0")
        }
    }
    
    // 遷移範例:v2 → v3 新增 department 表與外鍵
    val MIGRATION_2_3 = object : Migration(2, 3) {
        override fun migrate(database: SupportSQLiteDatabase) {
            database.execSQL("""
                CREATE TABLE departments (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL UNIQUE
                )
            """.trimIndent())
            database.execSQL("ALTER TABLE users ADD COLUMN department_id INTEGER")
            database.execSQL("CREATE INDEX idx_users_dept ON users(department_id)")
        }
    }
}

5. Repository 模式封裝


class UserRepository @Inject constructor(
    private val db: AppDatabase,
    private val api: UserApiService
) {
    // 公開 Flow 給 UI 層觀察
    val allUsers: Flow> = db.userDao().getAllActiveUsers()
        .map { users -> users.sortedBy { it.lastName + it.firstName } }
        .distinctUntilChanged()
    
    suspend fun searchUsers(query: String): Result> {
        return try {
            // 先查本地
            val local = db.userDao().searchByEmail("%$query%").first()
            if (local.isNotEmpty()) return Result.success(local)
            
            // 本地無資料,查遠端並同步
            val remote = api.searchUsers(query).await()
            if (remote.isSuccessful) {
                db.userDao().insertAll(remote.body()!!)
                return Result.success(remote.body()!!)
            }
            Result.failure(Exception("Search failed"))
        } catch (e: Exception) {
            Result.failure(e)
        }
    }
    
    suspend fun addUser(user: User): Result {
        return try {
            val id = db.userDao().insert(user)
            Result.success(id)
        } catch (e: Exception) {
            Result.failure(e)
        }
    }
}

6. 在 ViewModel 中使用


@HiltViewModel
class UserViewModel @Inject constructor(
    private val repository: UserRepository
) : ViewModel() {
    
    // 使用 stateIn 轉為 StateFlow,避免重複訂閱
    val usersUiState: StateFlow>> = repository.allUsers
        .map { UiState.Success(it) }
        .onStart { emit(UiState.Loading) }
        .catch { emit(UiState.Error(it.message)) }
        .stateIn(viewModelScope, SharingStarted.WhileSubscribed(5000), UiState.Empty)
    
    fun search(query: String) {
        viewModelScope.launch {
            _searchState.value = UiState.Loading
            repository.searchUsers(query)
                .onSuccess { _searchState.value = UiState.Success(it) }
                .onFailure { _searchState.value = UiState.Error(it.message) }
        }
    }
}

sealed interface UiState {
    data class Success(val data: T) : UiState
    data class Error(val message: String?) : UiState
    object Loading : UiState
    object Empty : UiState
}

原生 SQLite API 參考 (僅供維舊專案/特殊需求)


public class LegacyDatabaseHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "legacy.db";
    private static final int DB_VERSION = 3;
    
    // 建表 SQL
    private static final String CREATE_USERS = 
        "CREATE TABLE users (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
        "name TEXT NOT NULL, email TEXT UNIQUE, age INTEGER, created_at INTEGER)";
    
    public LegacyDatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_USERS);
        db.execSQL("CREATE INDEX idx_users_email ON users(email)");
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            db.execSQL("ALTER TABLE users ADD COLUMN phone TEXT");
        }
        if (oldVersion < 3) {
            db.execSQL("CREATE TABLE logs (id INTEGER PRIMARY KEY, message TEXT, time INTEGER)");
        }
    }
    
    // 插入
    public long insertUser(String name, String email, int age) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("email", email);
        values.put("age", age);
        values.put("created_at", System.currentTimeMillis());
        return db.insert("users", null, values);
    }
    
    // 查詢 (務必關閉 Cursor!)
    public List getAllUsers() {
        List list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = null;
        try {
            cursor = db.query("users", null, null, null, null, null, "name ASC");
            while (cursor.moveToNext()) {
                User u = new User();
                u.id = cursor.getLong(cursor.getColumnIndexOrThrow("_id"));
                u.name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
                u.email = cursor.getString(cursor.getColumnIndexOrThrow("email"));
                u.age = cursor.getInt(cursor.getColumnIndexOrThrow("age"));
                list.add(u);
            }
        } finally {
            if (cursor != null) cursor.close();
        }
        return list;
    }
    
    // 事務範本
    public void executeInTransaction(Runnable action) {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        try {
            action.run();
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }
}

效能優化與避坑指南

  1. 索引策略:WHERE/JOIN/ORDER BY 欄位建立索引,避免過度索引影響寫入
  2. 避免 N+1 查詢:使用 @Relation、@Embedded、JOIN 查詢一次取回關聯資料
  3. 分頁載入:PagingSource + Pager,避免一次載入大量資料
  4. 非同步執行:所有 DB 操作在 IO 執行緒 (Room 自動處理,原生需手動)
  5. WAL 模式:Room 預設啟用 Write-Ahead Logging,並發讀寫效能更好
  6. 遷移測試:exportSchema = true + room-testing 自動驗證遷移
  7. 加密:敏感資料使用 SQLCipher (Room 支援 SupportSQLiteOpenHelper.Factory 自定)

測試策略


// 測試 DAO (使用 in-memory database)
@RunWith(AndroidJUnit4::class)
class UserDaoTest {
    private AppDatabase db;
    private UserDao dao;
    
    @Before
    fun setup() {
        db = Room.inMemoryDatabaseBuilder(InstrumentationRegistry.getInstrumentation().targetContext, AppDatabase::class.java)
            .allowMainThreadQueries() // 測試時允許主執行緒
            .build()
        dao = db.userDao()
    }
    
    @After
    fun tearDown() { db.close() }
    
    @Test
    fun insertAndQuery() = runBlocking {
        val user = User(firstName = "John", lastName = "Doe", email = "john@example.com", age = 30)
        val id = dao.insert(user)
        assertTrue(id > 0)
        
        val found = dao.getUserById(id).first()
        assertEquals("John", found?.firstName)
    }
}

總結

Android 資料庫開發首選 Room:型別安全、編譯期驗證、生命週期感知流、遷移管理、測試友善。原生 SQLite 僅用於極簡場景或極致效能調優。核心原則:主執行緒禁用 IO、善用索引與關聯查詢、批次操作用事務、遷移要有測試。

copyright © 萬盛學電腦網 all rights reserved