【教學】Android SQLite Demo

本篇主要是示範：
1. 如何建立SQLite的資料庫。
2. 如何建立SQLite的資料表。
3. 如何對SQLite的資料表進行新增、刪除以及修改。

首先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>

再來是Java的部分。由於最近Ray小編接到新的Case的關係，稍微有點忙碌，因此關於程式碼說明的部分，Ray小編擇日再寫！程式碼的內容寫得很簡單，就麻煩各位自己稍微瀏覽一下囉！（趕緊閃人！）
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="_id, row1, row2\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;

    public MyHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, 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 i, int i1) {
        String dropTable = "drop table tablename";
        db.execSQL(dropTable);
    }
}