萬盛學電腦網

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

Android SQLite 數據庫開發完整指南:從建表到查詢優化

SQLite 在 Android 中的地位

  • 輕量級嵌入式關係數據庫,零配置、單文件、跨平台
  • Android 運行時內置完整 SQLite 引擎 (通常版本滯後於最新版)
  • 每個 App 擁有私有數據庫目錄:/data/data/包名/databases/
  • 不支持存儲過程、觸發器功能受限、ALTER TABLE 功能有限、無 RIGHT/FULL OUTER JOIN
  • 類型系統:動態類型/顯著類型 — 列類型僅為建議,可存入任意類型數據

核心 API 體系

完整實戰:創建、增刪改查、事務、升級

1. 數據庫輔助類

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "app.db";
    private static final int DB_VERSION = 2; // 版本號只能遞增
    
    // 表定義常量
    public static final String TABLE_USER = "user";
    public static final String COL_ID = "_id"; // 必須有 _id 列配合 CursorAdapter
    public static final String COL_NAME = "name";
    public static final String COL_AGE = "age";
    public static final String COL_EMAIL = "email";
    public static final String COL_CREATE_TIME = "create_time";

    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 創建表
        String sql = "CREATE TABLE " + TABLE_USER + " (" +
                COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COL_NAME + " TEXT NOT NULL, " +
                COL_AGE + " INTEGER DEFAULT 0, " +
                COL_EMAIL + " TEXT UNIQUE, " +
                COL_CREATE_TIME + " INTEGER DEFAULT (strftime('%s', 'now'))" +
                ");";
        db.execSQL(sql);
        
        // 創建索引
        db.execSQL("CREATE INDEX idx_user_name ON " + TABLE_USER + "(" + COL_NAME + ");");
        db.execSQL("CREATE INDEX idx_user_email ON " + TABLE_USER + "(" + COL_EMAIL + ");");
        
        // 初始化數據
        ContentValues cv = new ContentValues();
        cv.put(COL_NAME, "Admin");
        cv.put(COL_AGE, 25);
        cv.put(COL_EMAIL, "admin@example.com");
        db.insert(TABLE_USER, null, cv);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 版本遷移邏輯:必須處理所有中間版本
        if (oldVersion < 2) {
            // v1 -> v2: 添加 email 欄位與索引
            db.execSQL("ALTER TABLE " + TABLE_USER + " ADD COLUMN " + COL_EMAIL + " TEXT UNIQUE;");
            db.execSQL("CREATE INDEX idx_user_email ON " + TABLE_USER + "(" + COL_EMAIL + ");");
        }
        // 如有 v3, v4... 繼續添加 if (oldVersion < 3) ...
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        // 可選:啟用外鍵約束 (SQLite 默認關閉)
        if (!db.isReadOnly()) {
            db.execSQL("PRAGMA foreign_keys=ON;");
        }
    }
}

2. DAO 操作類 (封裝 CRUD)

public class UserDao {
    private final DatabaseHelper dbHelper;

    public UserDao(Context context) {
        dbHelper = new DatabaseHelper(context.getApplicationContext());
    }

    // 獲取可寫/讀數據庫 (建議長連接,Application 級單例持有)
    private SQLiteDatabase getDb(boolean writable) {
        return writable ? dbHelper.getWritableDatabase() : dbHelper.getReadableDatabase();
    }

    // 插入
    public long insert(User user) {
        SQLiteDatabase db = getDb(true);
        ContentValues cv = toContentValues(user);
        return db.insert(DatabaseHelper.TABLE_USER, null, cv);
    }

    // 批量插入 (事務優化)
    public void insertBatch(List users) {
        SQLiteDatabase db = getDb(true);
        db.beginTransaction();
        try {
            for (User user : users) {
                ContentValues cv = toContentValues(user);
                db.insert(DatabaseHelper.TABLE_USER, null, cv);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    // 更新
    public int update(User user) {
        SQLiteDatabase db = getDb(true);
        ContentValues cv = toContentValues(user);
        String where = DatabaseHelper.COL_ID + " = ?";
        String[] args = {String.valueOf(user.getId())};
        return db.update(DatabaseHelper.TABLE_USER, cv, where, args);
    }

    // 刪除
    public int deleteById(long id) {
        SQLiteDatabase db = getDb(true);
        String where = DatabaseHelper.COL_ID + " = ?";
        String[] args = {String.valueOf(id)};
        return db.delete(DatabaseHelper.TABLE_USER, where, args);
    }

    // 查詢單個
    public User queryById(long id) {
        SQLiteDatabase db = getDb(false);
        String[] columns = {DatabaseHelper.COL_ID, DatabaseHelper.COL_NAME, 
                           DatabaseHelper.COL_AGE, DatabaseHelper.COL_EMAIL, DatabaseHelper.COL_CREATE_TIME};
        String where = DatabaseHelper.COL_ID + " = ?";
        String[] args = {String.valueOf(id)};
        
        Cursor cursor = db.query(DatabaseHelper.TABLE_USER, columns, where, args, null, null, null);
        try {
            if (cursor.moveToFirst()) return cursorToUser(cursor);
            return null;
        } finally {
            cursor.close();
        }
    }

    // 分頁查詢
    public List queryPage(int offset, int limit, String orderBy) {
        SQLiteDatabase db = getDb(false);
        String[] columns = {DatabaseHelper.COL_ID, DatabaseHelper.COL_NAME, 
                           DatabaseHelper.COL_AGE, DatabaseHelper.COL_EMAIL, DatabaseHelper.COL_CREATE_TIME};
        Cursor cursor = db.query(DatabaseHelper.TABLE_USER, columns, null, null, null, null, 
                                orderBy, limit + " OFFSET " + offset);
        try {
            List list = new ArrayList<>();
            while (cursor.moveToNext()) {
                list.add(cursorToUser(cursor));
            }
            return list;
        } finally {
            cursor.close();
        }
    }

    // 原生 SQL 查詢 (複雜查詢)
    public List searchByName(String keyword) {
        SQLiteDatabase db = getDb(false);
        String sql = "SELECT * FROM " + DatabaseHelper.TABLE_USER + 
                     " WHERE " + DatabaseHelper.COL_NAME + " LIKE ? ORDER BY " + DatabaseHelper.COL_CREATE_TIME + " DESC";
        String[] args = {"%" + keyword + "%"};
        Cursor cursor = db.rawQuery(sql, args);
        try {
            List list = new ArrayList<>();
            while (cursor.moveToNext()) {
                list.add(cursorToUser(cursor));
            }
            return list;
        } finally {
            cursor.close();
        }
    }

    private ContentValues toContentValues(User user) {
        ContentValues cv = new ContentValues();
        cv.put(DatabaseHelper.COL_NAME, user.getName());
        cv.put(DatabaseHelper.COL_AGE, user.getAge());
        cv.put(DatabaseHelper.COL_EMAIL, user.getEmail());
        return cv;
    }

    private User cursorToUser(Cursor cursor) {
        User user = new User();
        user.setId(cursor.getLong(cursor.getColumnIndexOrThrow(DatabaseHelper.COL_ID)));
        user.setName(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COL_NAME)));
        user.setAge(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COL_AGE)));
        user.setEmail(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COL_EMAIL)));
        user.setCreateTime(cursor.getLong(cursor.getColumnIndexOrThrow(DatabaseHelper.COL_CREATE_TIME)));
        return user;
    }

    public void close() {
        dbHelper.close();
    }
}

3. 實體類

public class User {
    private long id;
    private String name;
    private int age;
    private String email;
    private long createTime;
    // Getter/Setter 省略
}

Cursor 使用避坑指南

  1. 檢查 null:query/rawQuery 可能返回 null (極少見但規範要求)
  2. 檢查空結果:cursor.getCount() == 0 或 !cursor.moveToFirst()
  3. 必須 moveToFirst/moveToNext:游標初始位置在 -1 (第一行之前)
  4. 使用 getColumnIndexOrThrow:列名拼寫錯誤直接拋異常,避免返回 -1 導致讀錯列
  5. 類型轉換注意:SQLite INTEGER 存 boolean/long,讀取用 getInt/getLong 後轉換
  6. finally 中 close():防止 Cursor 洩漏導致內存/文件句柄耗盡

事務管理最佳實踐

// 模板方法
public void executeInTransaction(TransactionCallback callback) {
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    db.beginTransaction();
    try {
        callback.execute(db);
        db.setTransactionSuccessful();
    } catch (Exception e) {
        // 可選:記錄錯誤、拋出運行時異常
        throw new RuntimeException("Transaction failed", e);
    } finally {
        db.endTransaction();
    }
}

@FunctionalInterface
interface TransactionCallback {
    void execute(SQLiteDatabase db) throws Exception;
}

// 使用
userDao.executeInTransaction(db -> {
    db.delete(TABLE_USER, "age < ?", new String[]{"18"});
    db.execSQL("UPDATE " + TABLE_USER + " SET age = age + 1");
});

性能優化關鍵點

  1. 索引策略:WHERE/JOIN/ORDER BY 涉及列建立索引,但過多索引拖慢寫入
  2. 預編譯語句:SQLiteStatement / compileStatement 重複執行同類 SQL
  3. WAL 模式 (Write-Ahead Logging, API 16+):db.enableWriteAheadLogging(),讀寫並發性能大幅提升
  4. 頁面大小:PRAGMA page_size=4096 (默認 4KB,大數據量可調大)
  5. 異步寫入:大量寫入用 AsyncTask/協程/線程池,避免阻塞主線程

現代替代方案:Room (Jetpack 官方 ORM)

強烈建議新項目直接使用 Room,優勢:

  • 編譯期 SQL 驗證,杜絕運行時語法錯誤
  • 自動生成 DAO 實現、Entity 映射、遷移腳本
  • 原生支持 LiveData/Flow/RxJava 觀察數據變化
  • 內置遷移測試、TypeConverter 處理複雜類型
  • 編譯時生成的代碼性能接近手寫 SQLite
@Entity(tableName = "user", indices = {@Index("name"), @Index(value = "email", unique = true)})
public class User {
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "_id") public long id;
    @ColumnInfo(name = "name") public String name;
    @ColumnInfo(name = "age") public int age;
    @ColumnInfo(name = "email") public String email;
    @ColumnInfo(name = "create_time") public long createTime;
}

@Dao
public interface UserDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE) long insert(User user);
    @Insert void insertAll(List users);
    @Update int update(User user);
    @Delete int delete(User user);
    @Query("SELECT * FROM user WHERE _id = :id") User getById(long id);
    @Query("SELECT * FROM user ORDER BY create_time DESC LIMIT :limit OFFSET :offset") 
    List getPage(int limit, int offset);
    @Query("SELECT * FROM user WHERE name LIKE :keyword") 
    List search(String keyword);
    @RawQuery(observedEntities = User.class) 
    List rawQuery(SupportSQLiteQuery query);
}

@Database(entities = {User.class}, version = 2, exportSchema = true)
public abstract class AppDatabase extends RoomDatabase {
    public abstract UserDao userDao();
}
copyright © 萬盛學電腦網 all rights reserved
類/接口作用關鍵方法
SQLiteOpenHelper數據庫創建/升級/版本管理輔助類onCreate, onUpgrade, onOpen, getReadableDatabase, getWritableDatabase
SQLiteDatabase數據庫操作核心類execSQL, query, rawQuery, insert, update, delete, beginTransaction, setTransactionSuccessful, endTransaction
Cursor查詢結果游標moveToFirst, moveToNext, getInt, getString, getColumnIndex, close, getCount
ContentValues鍵值對容器,用於 insert/updateput, get, remove, clear