public class DBHelper extends SQLiteOpenHelper {
private final static String DATABASE_NAME = "price_app.db";
private final static int DATABASE_VERSION = 1;
public DBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
//需要建立的table SQL語法
String createTable = "CREATE TABLE IF NOT EXISTS GeoStatus " +
"(_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
" CountiesUpdateTime nvarchar(50) ,MRTUpdateTime nvarchar(50) )";
db.execSQL(createTable);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//oldVersion=舊的資料庫版本；newVersion=新的資料庫版本
//修改DATABASE_VERSION來改變
//資料庫版本異動 ex 修改某table欄位時
if (newVersion > oldVersion) {
db.beginTransaction();//建立交易
boolean success = false;//判斷參數
//由之前不用的版本，可做不同的動作
switch (oldVersion) {
case 1:
db.execSQL("ALTER TABLE GeoStatus ADD COLUMN reminder integer DEFAULT 0");
success = true;
break;
}
if (success) {
//正確交易才成功
db.setTransactionSuccessful();
}
db.endTransaction();
} else {
onCreate(db);
}
}
Insert / Update / Delete
DBHelper dbHelper = new DBHelper(conText);
SQLiteDatabase db = dbHelper.getWritableDatabase();
//insert
ContentValues cv = new ContentValues();
//cv.put(column ,value);
cv.put("city_code", "0000");
cv.put("district_code", "1");
//db.insert(table name ,nullColumnHack ,ContentValues values)
db.insert("District", null, cv);
//update
ContentValues cv = new ContentValues();
//cv.put(column ,value);
cv.put("city_name", "新北市");
//db.update(table name ,ContentValues values, whereClause ,whereArgs[])
db.update("City", cv, "city_code = '0000'", null);
//delete
//db.update(table name , whereClause ,whereArgs[])
db.delete("City", "city_code = '0000'", null);
db.close();
Query
DBHelper dbHelper = new DBHelper(conText);
SQLiteDatabase db = dbHelper.getReadableDatabase();
String SQL = "select city_code ,city_name from City order by sort asc";
Cursor cursor = db.rawQuery(SQL, null);
while (cursor.moveToNext()) {
String cityCode = cursor.getString(0); //第一個欄位
//或是 cursor.getString(cursor.getColumnIndex("city_code"));
}
cursor.close();
db.close();
