實際測試
當
new MyDBHlper時,會自動判斷,帶入的
version是不是跟之前的一樣,如果不一樣,就會先執行
MyDBHlper的onUpgrade
執行順序為
MyContentProvider: onCreate:
MyDBHlper: onUpgrade: oldVersion:1newVersion:2
MyDBHlper: onCreate:
public class MyContentProvider extends ContentProvider { @Override public boolean onCreate() { Log.d(Tag, "onCreate: "); // MyDBHlper dbHper = new MyDBHlper(getContext(), DBname, null, 1); //FIXME:test version change MyDBHlper dbHper = new MyDBHlper(getContext(), DBname, null, 2); MyDB = dbHper.getWritableDatabase(); return true; } } public class MyDBHlper extends SQLiteOpenHelper { @Override public void onCreate(SQLiteDatabase db) { Log.d(Tag, "onCreate: "); db.execSQL(crTBsql); db.execSQL(initDBDate); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.d(Tag, "onUpgrade: oldVersion:" + oldVersion + "newVersion:" + newVersion); db.execSQL("DROP TABLE IF EXISTS " + TBname); onCreate(db); } }
***************************************************************************************************
轉載自http://mrraybox.blogspot.tw/2017/01/android-sqlite-onupgrade.html
本篇主要是示範:
1. 說明onUpgrade何時會啟用。
2. 示範如何使用onUpgrade。
3. 觀察onUpgrade使用後的前後差異。
相信開始接觸SQLite的大家都知道SQLiteOpenHelper中有一個方法叫onUpgrade,但是這個方法是做什麼用的呢?首先我們知道onCreate是在Android載入時,找不到對應的資料庫資料,就會觸發的一個方法。而onUpgrade呢,則是在資料庫的結構有所改變時,才會觸發的一個方法。
舉例來說,假如我在一月時,寫了一個App給其他人使用,在二月時,因改版需求,所以需要新增或刪減資料表的欄位。但是此時,那些使用我們所寫的App的用戶們,在這段期間已經儲存許多資料了。為了保存用戶舊的資料,此時我們就需要運用到onUpgrade來修改我們資料表的結構。
首先這裡示範我們一開始所寫的App。首先Layout部分的配置。
File Name:activity_main.xml
<ScrollView xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" > <LinearLayout android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" > <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="horizontal" > <Button android:id="@+id/btn_new" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="新增" /> <Button android:id="@+id/btn_delete" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="刪除" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:gravity="right" android:orientation="horizontal" > <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:hint="ID" android:id="@+id/edt1" android:layout_weight="1" /> <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:hint="Row1" android:id="@+id/edt2" android:layout_weight="1" /> <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:hint="Row2" android:id="@+id/edt3" android:layout_weight="1" /> <Button android:id="@+id/btn_alter" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="修改" /> </LinearLayout> <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="txt" android:id="@+id/txt"/> </LinearLayout> </ScrollView>
File Name:MainActivity
import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.TextView; public class MainActivity extends AppCompatActivity implements View.OnClickListener { private Button btnNew, btnDel, btnAlt; private EditText edtID, edtRow1, edtRow2; private TextView txt; private MyHelper helper; private SQLiteDatabase db; private Cursor c; private String result; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); btnNew = (Button) this.findViewById(R.id.btn_new); btnDel = (Button) this.findViewById(R.id.btn_delete); btnAlt = (Button) this.findViewById(R.id.btn_alter); edtID = (EditText) this.findViewById(R.id.edt1); edtRow1 = (EditText) this.findViewById(R.id.edt2); edtRow2 = (EditText) this.findViewById(R.id.edt3); txt = (TextView) this.findViewById(R.id.txt); btnNew.setOnClickListener(this); btnDel.setOnClickListener(this); btnAlt.setOnClickListener(this); helper = new MyHelper(this, "dbname", null, 1); db = helper.getWritableDatabase(); queryDB(); } private void queryDB() { c = db.query("tablename", null, null, null, null, null, null); result=""; for(int i=0; i<c.getColumnCount();i++){ result+=c.getColumnName(i)+", "; } result+="\n"; while (c.moveToNext()) { result += c.getString(c.getColumnIndex("_id")) + ", "+ c.getString(c.getColumnIndex("row1")) + ", "+ c.getString(c.getColumnIndex("row2")) +"\n"; } txt.setText(result); } @Override public void onClick(View view) { String id = edtID.getText().toString(); ContentValues values = new ContentValues(); switch (view.getId()){ case R.id.btn_new: values.put("row1", "Row1"); values.put("row2", "Row2"); db.insert("tablename", null, values); queryDB(); break; case R.id.btn_delete: db.delete("tablename", "_id="+id,null); queryDB(); break; case R.id.btn_alter: String row1 = edtRow1.getText().toString(); String row2 = edtRow2.getText().toString(); values.put("row1", row1); values.put("row2", row2); db.update("tablename", values, "_id=" + id, null); queryDB(); break; } } @Override public void finish() { c.close(); db.close(); super.finish(); } }
File Name:MyHelper
import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class MyHelper extends SQLiteOpenHelper { private String createTab; private String tableName = "tablename"; public MyHelper(Context context, String db_name, SQLiteDatabase.CursorFactory factory, int db_version) { super(context, db_name, factory, db_version); } @Override public void onCreate(SQLiteDatabase db) { createTab = "create table "+ tableName +" (_id integer primary key AUTOINCREMENT, row1 text, row2 text)"; db.execSQL(createTab); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }
在用戶新增完資料後,就會變成下面這樣。
接著是我們改版後的程式碼。
File Name:MainActivity
File Name:MainActivity
import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.TextView; public class MainActivity extends AppCompatActivity implements View.OnClickListener { private Button btnNew, btnDel, btnAlt; private EditText edtID, edtRow1, edtRow2; private TextView txt; private MyHelper helper; private SQLiteDatabase db; private Cursor c; private String result; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); btnNew = (Button) this.findViewById(R.id.btn_new); btnDel = (Button) this.findViewById(R.id.btn_delete); btnAlt = (Button) this.findViewById(R.id.btn_alter); edtID = (EditText) this.findViewById(R.id.edt1); edtRow1 = (EditText) this.findViewById(R.id.edt2); edtRow2 = (EditText) this.findViewById(R.id.edt3); txt = (TextView) this.findViewById(R.id.txt); btnNew.setOnClickListener(this); btnDel.setOnClickListener(this); btnAlt.setOnClickListener(this); helper = new MyHelper(this, "dbname", null, 2); db = helper.getWritableDatabase(); queryDB(); } private void queryDB() { c = db.query("tablename", null, null, null, null, null, null); result=""; for(int i=0; i<c.getColumnCount();i++){ result+=c.getColumnName(i)+", "; } result+="\n"; while (c.moveToNext()) { result += c.getString(c.getColumnIndex("_id")) + ", "+ c.getString(c.getColumnIndex("row1")) + ", "+ c.getString(c.getColumnIndex("row2")) +"\n"; } txt.setText(result); } @Override public void onClick(View view) { String id = edtID.getText().toString(); ContentValues values = new ContentValues(); switch (view.getId()){ case R.id.btn_new: values.put("row1", "Row1"); values.put("row2", "Row2"); db.insert("tablename", null, values); queryDB(); break; case R.id.btn_delete: db.delete("tablename", "_id="+id,null); queryDB(); break; case R.id.btn_alter: String row1 = edtRow1.getText().toString(); String row2 = edtRow2.getText().toString(); values.put("row1", row1); values.put("row2", row2); db.update("tablename", values, "_id=" + id, null); queryDB(); break; } } @Override public void finish() { c.close(); db.close(); super.finish(); } }
File Name:MyHelper
import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class MyHelper extends SQLiteOpenHelper { private String createTab; private String tableName = "tablename"; public MyHelper(Context context, String db_name, SQLiteDatabase.CursorFactory factory, int db_version) { super(context, db_name, factory, db_version); } @Override public void onCreate(SQLiteDatabase db) { createTab = "create table "+ tableName + " (_id integer primary key AUTOINCREMENT, row1 text, row2 text, row3 text)"; db.execSQL(createTab); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { if(oldVersion == 1 && newVersion == 2){ //Adding a row3 String sql = "alter table "+ tableName +" add row3 text"; db.execSQL(sql); } } }
實作後,則會變成下面這樣。有發現差異在哪嗎?
改版後的App不但保存了用戶的舊資料,同時還新增了一個row3的欄位,是不是很方便的一個功能呀!