Android 內建 SQLite (輕量級關聯式資料庫),但原生 API 操作繁瑣、易出錯。現代開發強烈建議使用 Room (Google 官方 ORM),僅在特殊需求 (極簡依賴、極高效能調優、舊碼遷移) 使用原生 API。
| 特性 | 原生 SQLiteOpenHelper | Room (推薦) | SQLDelight (Kotlin 優先) |
|---|---|---|---|
| 型別安全 | 無 (Cursor 手動映射) | 編譯期驗證 SQL、實體映射 | 編譯期生成型別安全 API |
| 遷移管理 | 手寫 onUpgrade | 聲明式遷移、自動驗證 | 聲明式遷移 |
| 非同步/觀察 | 手動 AsyncTask/Thread | RxJava/Flow/LiveData 原生支援 | Flow/Coroutines 原生 |
| 編譯期檢查 | 無 | SQL 語法、實體對應驗證 | SQL 驗證、生成代碼 |
| 多平台 | 僅 Android | Android/JVM | Android/iOS/JVM/JS/Native |
| 學習曲線 | 低 (但易犯錯) | 中 (註解處理器) | 中高 |
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")
}
@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()
}
@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?
)
@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)")
}
}
}
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)
}
}
}
@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
}
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();
}
}
}
@Relation、@Embedded、JOIN 查詢一次取回關聯資料PagingSource + Pager,避免一次載入大量資料exportSchema = true + room-testing 自動驗證遷移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、善用索引與關聯查詢、批次操作用事務、遷移要有測試。