[安藤]Android 一個可在DB中隨時增加Table的欄位，也會自動升級並保留原本資料的Content Provider + DB 架構

在這邊提出一個android用的DB+content provider的架構供參考。
設計理念：

• 簡單增加欄位
• 使用較少程式碼
• 資料穩定度
• 全自動升級資料庫(重要！)

所有的Table設計都繼承此class:

public abstract class DBTable {

    public static final String SYS_CONTENT_TYPE = "vnd.android.cursor.dir";

    public static final String SYS_CONTENT_ITEM_TYPE = "vnd.android.cursor.item";

    public static final String COLUMN_TYPE_TEXT = "TEXT DEFAULT '' ";  //所有的資料欄位都加上預設值。

    public static final String COLUMN_TYPE__ID = "INTEGER PRIMARY KEY AUTOINCREMENT ";  //此欄位為android的DB table所必需。資料格式也是固定。勿動。

    public static final String COLUMN_TYPE_INT = "INTEGER DEFAULT 0 ";

    public static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS ";

    public static final String URL_PREFIX_CONTENT = "content://";

    public static final String COLUMN_TYPE_REAL = "REAL DEFAULT 0 ";

    public abstract String getTableName();

    public abstract int getTotalColumns();

    public abstract String getColumnName(int columnIndex);

    public abstract String getColumnType(int columnIndex);

    public abstract String getTableCreateCmd();



}

接下來是各個table的定義。這邊只用一個來舉例，其他的請如法炮製：

/**
 * When you define columns, first column name must be "_id", data type
 * must be "INTEGER PRIMARY KEY AUTOINCREMENT", other column name use lower case is better.
 *
 */
public class Table1 extends DBTable {
public static String AUTHORITY;  //android 的content provider所需要的authorities。 跟AndroidManifest.xml裡面宣告Provider的時候的字串要相同。我的建議是直接取App的packagename來用。
public static String TABLENAME;  //table名。不可以跟其他table名稱重複。本例直接拿class name來用。
public static Uri CONTENT_URI;  //android 的content provider所需要的uri。
public static String CONTENT_TYPE;  //給content provider Uri matche使用。
public static String CONTENT_ITEM_TYPE;  //給content provider Uri matche使用。
public static enum eColumns {_id, column1, column2, column3};  //欄位名稱加在這裡
private static eColumns[] arEColumns;  //方便以index取得欄位名稱。(跑迴圈用)

public Table1(String authority)
{
    AUTHORITY = authority;
    CONTENT_URI = Uri.parse(URL_PREFIX_CONTENT + AUTHORITY + "/" +   TABLENAME);
    CONTENT_TYPE = SYS_CONTENT_TYPE + "/" + AUTHORITY + "." + TABLENAME;
    CONTENT_ITEM_TYPE = SYS_CONTENT_ITEM_TYPE + "/" + AUTHORITY + "." + TABLENAME;
    arEColumns = eColumns.values();
    TABLENAME = this.getClass().getSimpleName();
}

@Override
public int getTotalColumns() {
    return arEColumns.length;
}

@Override
public String getColumnName(int columnIndex) {
if ((columnIndex >= 0) && (columnIndex < arEColumns.length))
    return arEColumns[columnIndex].name();
else
    return null;
}

@Override
public String getTableName() {
    return TABLENAME;
}

/**
* 定義各欄位的資料格式
**/
@Override
    public String getColumnType(int columnIndex)
    {
    if (
         columnIndex == eColumns. column1.ordinal()
         || columnIndex == eColumns. column2.ordinal()
     )
        return COLUMN_TYPE_TEXT;
    else if (  //別動。
         columnIndex == eColumns._id.ordinal()
    )
        return COLUMN_TYPE__ID;
    
    return COLUMN_TYPE_INT;
    }

/**
* 取得建立此table的完整sql指令。
**/
@Override
    public String getTableCreateCmd()
    {
        StringBuilder strbuilder = new StringBuilder();
        strbuilder.append(CREATE_TABLE).append(TABLENAME);
        for (eColumns eTmp: arEColumns)
        {
            if (eTmp.ordinal() == 0)
                strbuilder.append(" (");
            else
                strbuilder.append(", ");
            strbuilder.append(eTmp.name())
            .append(" ")
            .append(getColumnType(eTmp.ordinal()));
        }
        strbuilder.append(")");
        return strbuilder.toString();
    }

}

==================================
第二步：建立DBHelper。主要負責DB的table建立跟版本管理。

public class MyDBHelper extends SQLiteOpenHelper {

 private final static int DATABASE_VERSION = 1;  //有新增欄位的時候必須改動此編號，DB才會自動upgrade。
 public ArrayList alDbList;
 public static MyDBHelper dbHelper;
  
 public MyDBHelper(Context mContext, String dbName)
 {
  super(mContext, dbName, null, DATABASE_VERSION);
  dbHelper = this;
  String strAuthority = mContext.getPackageName();
  alDbList = new ArrayList();

  //新增db的話請加入alDbList裡面。
  alDbList.add(new Table1(strAuthority));
 }
 
 @Override
 public void onCreate(SQLiteDatabase db) {

  for (StoreAppTable table : alDbList)
  {
            try{
       db.execSQL(table.getTableCreateCmd());
            }
   catch(SQLiteException e) 
   {e.printStackTrace();}
  }
 }

 
 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

  //第一步：把目前DB裡面的所有table更名。
  for (StoreAppTable table : alDbList)
  {
   StringBuilder strb = new StringBuilder();
   strb.append("ALTER TABLE ")
   .append(table.getTableName())
   .append(" RENAME TO ")
   .append(table.getTableName() + "_");
   try{
       db.execSQL(strb.toString());
            }
   catch(SQLiteException e) 
   {e.printStackTrace();}
  }
  
  //第二步：建立新的table。
  onCreate(db);
  
  //第三步：從舊的table搬資料進新的table。
  //先把舊table填上新的欄位。
  for (StoreAppTable table : alDbList)
  {
   Cursor cursor = null;
   
   try{            
    cursor = db.rawQuery("SELECT * FROM " + table.getTableName() + "_" + " LIMIT 1", null);
    if (cursor != null)
    {
     for (int i = 0; i < table.getTotalColumns(); i++)
     {
      if (cursor.getColumnIndex(table.getColumnName(i)) < 0)
      {
       StringBuilder strb = new StringBuilder();
       strb.append("ALTER TABLE ")
       .append(table.getTableName() + "_")
       .append(" ADD ")
       .append(table.getColumnName(i)).append(" ")
       .append(table.getColumnType(i));
       try {
           db.execSQL(strb.toString());
                }
       catch(SQLiteException e) 
       {e.printStackTrace();}
      }
     }
     cursor.close();
    }
   }
   catch(SQLiteException e) 
   {e.printStackTrace();}
  }
  //利用insert [newtable] select (columns) in [oldtable] 一口氣灌入新table。
  for (StoreAppTable table : alDbList)
  {
   StringBuilder strb = new StringBuilder();
   strb.append("INSERT INTO ")
   .append(table.getTableName())
   .append(" (");

   for (int i = 1; i < table.getTotalColumns(); i++)  //_id欄位是自動增加，不需要insert。
   {
    if (i != 1)
     strb.append(",");
    strb.append(table.getColumnName(i));
   }
   strb.append(")").append(" SELECT ");
   for (int i = 1; i < table.getTotalColumns(); i++)
   {
    if (i != 1)
     strb.append(",");
    strb.append(table.getColumnName(i));
   }
   strb.append(" FROM ")
   .append(table.getTableName() + "_");
   try {
       db.execSQL(strb.toString());
   }
   catch(SQLiteException e) 
   {e.printStackTrace();}
  }
  
 
  
  //第三步：刪除舊table。
  for (StoreAppTable table : alDbList)
  {
   StringBuilder strb = new StringBuilder();
   strb.append("DROP TABLE ")
   .append(table.getTableName() + "_");
   try {
       db.execSQL(strb.toString());
            }
   catch(SQLiteException e) {;}
  }
 }
 
}

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

接著是content provider class:

public class StoreAppContentProvider extends ContentProvider {
private StoreAppDBHelper databaseHelper;
private UriMatcher sUriMatcher; //URi 
public ArrayList alDbList;  //儲存所有的table. 方便回圈使用。
    private String AUTHORITY = null;
    private static StoreAppContentProvider storeAppContentProvider;  //singleton設計
    public static ContentResolver resolver;
    public static long lSipServerTimeDelta = 0;
    
@Override
public boolean onCreate() {
// TODO Auto-generated method stub
storeAppContentProvider = this;
resolver = getContext().getContentResolver();
AUTHORITY = getContext().getPackageName();  //之前提到建議authority使用package name. 這樣要沿用到其他app的時候只要直接取代packagename的字串即可。

databaseHelper = new StoreAppDBHelper(getContext(), AUTHORITY.substring(AUTHORITY.lastIndexOf(".") + 1));  //DB的名稱，各個app是可以重複的。就是靠Authority去分辨... 在這邊取packagename的最後一串字。

if (databaseHelper == null)
return false;
else
{
alDbList = databaseHelper.alDbList;  //從dbhelper裡面取得所有db的列表。
sUriMatcher = new UriMatcher(UriMatcher.NO_MATCH);  //開始建立所有table的Uri matcher
for (int i = 0; i < alDbList.size(); i++)  //for entire table
{
sUriMatcher.addURI(AUTHORITY, alDbList.get(i).getTableName(), i);
}
for (int i = 0; i < alDbList.size(); i++)  //for specified position item
{
sUriMatcher.addURI(AUTHORITY, alDbList.get(i).getTableName() + "/#", alDbList.size() + i);
}
return true;
}
}


public static StoreAppContentProvider getInstance()
{
    return storeAppContentProvider;
}

/**
* DB比較用不到getType，意義不大。
**/

public String getType(Uri uri) {
// TODO Auto-generated method stub
int index = sUriMatcher.match(uri);
if ((index >= (alDbList.size() * 2)) || (index < 0))
{
            throw new IllegalArgumentException("**** get type Unknown URI " + uri); 
}
else
{
if (index <= alDbList.size())
return StoreAppTable.SYS_CONTENT_TYPE + "/" + AUTHORITY + "." + alDbList.get(index);
else
return StoreAppTable.SYS_CONTENT_ITEM_TYPE + "/" + AUTHORITY + "." + alDbList.get(index - alDbList.size());
}
}


@Override
public int delete(Uri uri, String selection, String[] selectionArgs) {
// TODO Auto-generated method stub
try {
SQLiteDatabase db = databaseHelper.getWritableDatabase(); 
int index = sUriMatcher.match(uri);
int count = 0;
if ((index >= (alDbList.size() * 2)) || (index < 0))
{
            throw new IllegalArgumentException("**** get type Unknown URI " + uri); 
}
else
{
if (index < alDbList.size())
count = db.delete(alDbList.get(index).getTableName(), selection, selectionArgs);
else
count =  db.delete(alDbList.get(index - alDbList.size()).getTableName(), BaseColumns._ID + " = " + uri.getPathSegments().get(1)    
+ (!TextUtils.isEmpty(selection) ? " AND (" + selection + ')' : ""), selectionArgs);
}
if (count > 0)
    resolver.notifyChange(uri, null);
return count;
}
catch(SQLiteException e)
{
if (BuildConfig.DEBUG) Log.d("Jeff", "Failed to delete data in db " + uri);
e.printStackTrace();
}
return 0;
}

@Override
public Uri insert(Uri uri, ContentValues values) {
// TODO Auto-generated method stub
try {
SQLiteDatabase db = databaseHelper.getWritableDatabase(); 
int index = sUriMatcher.match(uri);
//int count = 0;
long rowId;
if (values == null) {
return null;
}
if ((index >= alDbList.size()) || (index < 0))
{
            throw new IllegalArgumentException("**** get type Unknown URI " + uri); 
}
else
{
rowId = db.insert(alDbList.get(index).getTableName(), null, values);
if(rowId > 0){
Uri uriNew = ContentUris.withAppendedId(Uri.parse("content://" + AUTHORITY + "/" + alDbList.get(index)), rowId); 
resolver.notifyChange(uri, null);
return uriNew;
}
}
if (rowId >= 0)
    resolver.notifyChange(uri, null);
}
catch(SQLiteException e)
{
if (BuildConfig.DEBUG) Log.d("Jeff", "Failed to insert data in db " + uri);
e.printStackTrace();
}

return null;
}


@Override
public Cursor query(Uri uri, String[] projection, String selection,
String[] selectionArgs, String sortOrder) {
// TODO Auto-generated method stub
try {
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
int index = sUriMatcher.match(uri);
//int count = 0;
if ((index >= (alDbList.size() * 2)) || (index < 0))
{
            throw new IllegalArgumentException("**** get type Unknown URI " + uri); 
}
else
{
if (index <= alDbList.size())
{
qb.setTables(alDbList.get(index).getTableName());
}
else
{
qb.setTables(alDbList.get(index - alDbList.size()).getTableName());
qb.appendWhere(BaseColumns._ID + "=" + uri.getPathSegments().get(1));
}
}

if (projection != null)
{
Cursor c = qb.query(databaseHelper.getReadableDatabase(), projection, 
selection, selectionArgs, null, null, sortOrder);
if (c != null) {
c.setNotificationUri(resolver, uri);
return c;
}
}
else
{
Cursor c = qb.query(databaseHelper.getReadableDatabase(), new String[]{"*"}, 
selection, selectionArgs, null, null, sortOrder);
if (c != null) {
c.setNotificationUri(resolver, uri);
return c;
}
}

}
catch(SQLiteException e)
{
if (BuildConfig.DEBUG) Log.d("Jeff", "Failed to query data in db " + uri);
e.printStackTrace();
}
return null;
}

@Override
public int update(Uri uri, ContentValues values, String selection,
String[] selectionArgs) {
// TODO Auto-generated method stub
try {
SQLiteDatabase db = databaseHelper.getWritableDatabase(); 
int index = sUriMatcher.match(uri);
int count = 0;
if ((index >= (alDbList.size() * 2)) || (index < 0))
{
            throw new IllegalArgumentException("**** get type Unknown URI " + uri); 
}
else
{
if (index < alDbList.size())
count = db.update(alDbList.get(index).getTableName(), values, selection, selectionArgs);
}
if (count > 0)
    resolver.notifyChange(uri, null);
return count;
}
catch(SQLiteException e)
{
if (BuildConfig.DEBUG) Log.d("Jeff", "Failed to update data in db " + uri);
e.printStackTrace();
}
return 0;
}


}

==========================
使用方法：
讀：

Cursor cursor = this.getContentResolver().query(

Table1.CONTENT_URI, null

, Table1.eColumns.column1.name() + " =? " 

   + " AND " + Table1.eColumns.column2.name() + " =? " 

, new String[1] {"text", "text2"}

, Table1.eColumns.column3.name());

寫：

ContentValues values = new ContentValues();

values.put(Table1.eColumns.column1.name(), "data");

this.getContentResolver().insert(Table1.CONTENT_URI, values);