Database簡易使用:

在Android 資料的儲存方式有很多種, 常見的像是Shared Preferences, 讀寫sdcard or app local file等等, 當需要被儲存的資料有一定規模和一定規則的時候database就可以派上用場了

首先, 先建立一個繼承SQLiteOpenHelper的class, 並且定義database名稱以及需要的欄位, 在onCreate的callback初始化database

public class MyDatabaseHelper extends SQLiteOpenHelper{
	 private static final String DATABASE_NAME = "mydatabase.db";
	 private static final int DATABASE_VERSION = 2;
	 private static final String TABLE_NAME = "mydata";
	 public static final String COLUMN_TYPE = "type";
	 public static final String COLUMN_TIMESTAMP = "timestamp";
	 public static final String COLUMN_DATA = "data";
	 public static final String COLUMN_EXTRA1 = "extra1";
	 public static final String COLUMN_EXTRA2 = "extra2";
	 public static final String COLUMN_SYNC_STATUS = "syncstatus";
	 public static final String COLUMN_DEVICE_ID = "deviceid";
	 private static MyDatabaseHelper helper;
	 private static String mDeviceId = "";
	 /** Create a helper object for the Events database */
	 public MyDatabaseHelper (Context ctx) {
		 super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
	 }
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + TABLE_NAME + " ("
		        + BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
		        + COLUMN_TYPE + " TEXT,"
		        + COLUMN_TIMESTAMP + " TEXT,"
		        + COLUMN_DATA + " TEXT,"
		        + COLUMN_EXTRA1 + " TEXT,"
		        + COLUMN_EXTRA2 + " TEXT,"
		        + COLUMN_DEVICE_ID + " TEXT,"
		        + COLUMN_SYNC_STATUS + " TEXT"
		        + ");");
	}

如此一來, 當app MyDatabaseHelper 第一次被開起來後, 就會在app local files底下長出ㄧ個mydatabase.db的檔案, 之後除非版本有變動否則會一直沿用目前的db檔，因此我們還可以增加onUpgrage的callback來查看是否database有升級,

@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
	}

上述的callback重載會把目前的db檔裡的table刪除並處發onCreate再重建新的table, 如果想要保有舊的資料就必須很清楚版本變動的差異, 再用SQL語法逐一改變database table, 可能因為這個做法不容易, 所以網路上大部分都是直接刪除table砍掉重練這種做法

而如果進一步想把local端database上傳到web server可以參考這個link

http://programmerguru.com/android-tutorial/how-to-sync-sqlite-on-android-to-mysql-db/

這個link介紹了如何透過Android app把local database以JSON格式傳至server端的php, 接著php再把JSON資料存成server端的database, 最後回覆Android端app更新結果, 是個蠻簡單易懂的架構呢!