SQLite 採用動態類型,主要儲存類別:
NULL、INTEGER (有號整數)、REAL (浮點數)、TEXT (UTF 字串)、BLOB (二進位)。INTEGER PRIMARY KEY AUTOINCREMENT 作為主鍵。| 類別 | 用途 |
|---|---|
SQLiteOpenHelper | 抽象類,管理資料庫建立/升級/開啟,必須繼承實作。 |
SQLiteDatabase | 實際執行 SQL、查詢、交易的物件。 |
ContentValues | 鍵值對容器,用於 insert/update 避免 SQL 拼接。 |
Cursor | 查詢結果游標,逐列讀取資料。 |
public class DBHelper extends SQLiteOpenHelper {
private static final String DB_NAME = "my.db";
private static final int VERSION = 1;
private static final String TABLE_USER = "user";
public DBHelper(Context context) {
super(context, DB_NAME, null, VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "CREATE TABLE " + TABLE_USER + " (" +
"_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"name TEXT NOT NULL, " +
"age INTEGER, " +
"email TEXT UNIQUE)";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldV, int newV) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
onCreate(db);
}
}
DBHelper helper = new DBHelper(this);
SQLiteDatabase db = helper.getWritableDatabase(); // 讀寫模式
// --- Insert ---
ContentValues cv = new ContentValues();
cv.put("name", "張三");
cv.put("age", 25);
cv.put("email", "zhang@example.com");
long rowId = db.insert(TABLE_USER, null, cv);
// --- Query ---
Cursor cursor = db.query(TABLE_USER,
new String[]{"_id", "name", "age", "email"}, // columns
"age > ?", new String[]{"20"}, // selection, args
null, null, "age DESC", "10"); // groupBy, having, orderBy, limit
while (cursor.moveToNext()) {
int id = cursor.getInt(0);
String name = cursor.getString(1);
int age = cursor.getInt(2);
}
cursor.close();
// --- Update ---
ContentValues cv2 = new ContentValues();
cv2.put("age", 26);
int count = db.update(TABLE_USER, cv2, "_id=?", new String[]{String.valueOf(rowId)});
// --- Delete ---
int delCount = db.delete(TABLE_USER, "age < ?", new String[]{"18"});
db.close();
helper.close();
db.beginTransaction() / setTransactionSuccessful() / endTransaction() 批次操作加速。? 佔位符防止 SQL 注入。CREATE INDEX idx_name ON user(name); 加速查詢。