Android SQLite 入門：SQLiteOpenHelper 封裝增刪改查、事務、版本升級完整範例

從零開始在 Android 中使用 SQLite，核心類：`SQLiteOpenHelper`（建表/升級）、`SQLiteDatabase`（CRUD）、`ContentValues`（裝載數據）。

1. 定義契約類

public class DbContract {
    public static final String DB_NAME = "app.db";
    public static final int DB_VERSION = 1;
    
    public static class UserEntry implements BaseColumns {
        public static final String TABLE = "users";
        public static final String COL_NAME = "name";
        public static final String COL_EMAIL = "email";
        public static final String COL_AGE = "age";
    }
}

2. SQLiteOpenHelper 實現

public class AppDbHelper extends SQLiteOpenHelper {
    public AppDbHelper(Context context) {
        super(context, DbContract.DB_NAME, null, DbContract.DB_VERSION);
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE " + DbContract.UserEntry.TABLE + " (" +
                DbContract.UserEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                DbContract.UserEntry.COL_NAME + " TEXT NOT NULL, " +
                DbContract.UserEntry.COL_EMAIL + " TEXT UNIQUE, " +
                DbContract.UserEntry.COL_AGE + " INTEGER DEFAULT 0)";
        db.execSQL(sql);
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldV, int newV) {
        // 示例：重建表（生產環境應用 ALTER TABLE 遷移數據）
        db.execSQL("DROP TABLE IF EXISTS " + DbContract.UserEntry.TABLE);
        onCreate(db);
    }
}

3. DAO 封裝常用操作

public class UserDao {
    private final AppDbHelper helper;
    public UserDao(Context ctx) { helper = new AppDbHelper(ctx); }
    
    // 插入
    public long insert(String name, String email, int age) {
        SQLiteDatabase db = helper.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(DbContract.UserEntry.COL_NAME, name);
        cv.put(DbContract.UserEntry.COL_EMAIL, email);
        cv.put(DbContract.UserEntry.COL_AGE, age);
        return db.insert(DbContract.UserEntry.TABLE, null, cv);
    }
    
    // 查詢所有
    public List queryAll() {
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor c = db.query(DbContract.UserEntry.TABLE, null, null, null, null, null, "_id DESC");
        List list = new ArrayList<>();
        while (c.moveToNext()) {
            list.add(new User(
                c.getLong(c.getColumnIndexOrThrow("_id")),
                c.getString(c.getColumnIndexOrThrow("name")),
                c.getString(c.getColumnIndexOrThrow("email")),
                c.getInt(c.getColumnIndexOrThrow("age"))
            ));
        }
        c.close();
        return list;
    }
    
    // 事務批量插入
    public void batchInsert(List users) {
        SQLiteDatabase db = helper.getWritableDatabase();
        db.beginTransaction();
        try {
            for (User u : users) {
                ContentValues cv = new ContentValues();
                cv.put(DbContract.UserEntry.COL_NAME, u.name);
                cv.put(DbContract.UserEntry.COL_EMAIL, u.email);
                cv.put(DbContract.UserEntry.COL_AGE, u.age);
                db.insert(DbContract.UserEntry.TABLE, null, cv);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }
}

4. Activity 中調用

UserDao dao = new UserDao(this);
dao.insert("Alice", "alice@example.com", 25);
List users = dao.queryAll();
// 顯示到 RecyclerView...

避坑指南

• Cursor 用完必須 `close()`，否則洩漏導致 `CursorFinalizer` 崩潰。
• 寫操作用 `getWritableDatabase()`，讀操作用 `getReadableDatabase()`（若磁碟滿只讀，後者仍可讀）。
• 版本升級 `onUpgrade` 必須寫遷移 SQL，切勿直接 `DROP TABLE` 除非數據可丟棄。
• 大量寫入**必須用事務**，性能提升 10-100 倍。