SQLite

複寫SQLiteOpenHelper

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();