【教學】Android SQLite onUpgrade方法的運用

本篇主要是示範：
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
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的欄位，是不是很方便的一個功能呀！

關於SQLite資料庫、資料表的建立，以及新增、刪除、修改的功能運用，麻煩請看這篇「【教學】Android SQLite Demo」。