| 類/接口 | 作用 | 關鍵方法 |
| 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/update | put, get, remove, clear |
完整實戰:創建、增刪改查、事務、升級
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 使用避坑指南
- 檢查 null:
query/rawQuery 可能返回 null (極少見但規範要求)
- 檢查空結果:
cursor.getCount() == 0 或 !cursor.moveToFirst()
- 必須 moveToFirst/moveToNext:游標初始位置在 -1 (第一行之前)
- 使用 getColumnIndexOrThrow:列名拼寫錯誤直接拋異常,避免返回 -1 導致讀錯列
- 類型轉換注意:SQLite INTEGER 存 boolean/long,讀取用
getInt/getLong 後轉換
- 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");
});
性能優化關鍵點
- 索引策略:WHERE/JOIN/ORDER BY 涉及列建立索引,但過多索引拖慢寫入
- 預編譯語句:
SQLiteStatement / compileStatement 重複執行同類 SQL
- WAL 模式 (Write-Ahead Logging, API 16+):
db.enableWriteAheadLogging(),讀寫並發性能大幅提升
- 頁面大小:
PRAGMA page_size=4096 (默認 4KB,大數據量可調大)
- 異步寫入:大量寫入用
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