[TQC+ Android] 2-4 SQLite 與 MENU 作業 Use SQLiteDatabase , SQLiteOpenHelper , SimpleCursorAdapter

提示使用者按MENU鍵 , 畫面會顯示MENU供使用者新增修改和刪除所選取的List item , 並且我們繼承SQLiteOpenHelper這個類別並override 其中幾個method , 幫助我們管理database和Table的操作 , 以下為程式碼。

package COM.TQC.GDD02;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

public class GDD02 extends Activity
{
  private mySQLiteOpenHelper gaaSQLiteOpenHelper;
  private Cursor myCursor;
  private ListView myListView;
  private EditText myEditText;
  private int _id;
  protected final static int MENU_ADD = Menu.FIRST;
  protected final static int MENU_EDIT = Menu.FIRST + 1;
  protected final static int MENU_DELETE = Menu.FIRST + 2;

  @Override
  public boolean onOptionsItemSelected(MenuItem item)
  {
    super.onOptionsItemSelected(item);
    
    switch (item.getItemId())
    {
      case MENU_ADD:
        this.addTodo();
        break;
      case MENU_EDIT:
        this.editTodo();
        break;
      case MENU_DELETE:
        this.deleteTodo();
        break;
    }
    return true;
  }

  @Override
  public boolean onCreateOptionsMenu(Menu menu)
  {
    super.onCreateOptionsMenu(menu);
    
    menu.add(0, MENU_ADD, 1, R.string.strAddButton);
    menu.add(0, MENU_EDIT, 2, R.string.strEditButton);
    menu.add(0, MENU_DELETE, 3, R.string.strDeleteButton);
    
    return true;
  }

  /** Called when the activity is first created. */
  @Override
  public void onCreate(Bundle savedInstanceState)
  {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    myListView = (ListView) this.findViewById(R.id.myListView);
    myEditText = (EditText) this.findViewById(R.id.myEditText);

    gaaSQLiteOpenHelper = new mySQLiteOpenHelper(this); 

    updateAdapter();
    
    myListView.setOnItemClickListener(new AdapterView.OnItemClickListener()
    {
      public void onItemClick(AdapterView arg0, View arg1, int postion,
          long rowID)
      {        
        myCursor.moveToPosition(postion);        
        _id = myCursor.getInt(0); // 取出所選物件的rowID
        //_id = (int) rowID;  rowID is the row id of the item that was clicked.
        myEditText.setText(myCursor.getString(1));
        //Log.d("Test", "postion:"+postion+" rowID:"+rowID+" _id:"+_id);
      }
    });
  }

  private void addTodo()
  {   
   gaaSQLiteOpenHelper.insert(myEditText.getText().toString());
   updateAdapter();
   myEditText.setText("");    
  }

  private void editTodo()
  {  
   gaaSQLiteOpenHelper.update(_id, myEditText.getText().toString());
   updateAdapter();
   myEditText.setText(""); 
  }

  private void deleteTodo()
  {
   gaaSQLiteOpenHelper.delete(_id);
   updateAdapter();
   myEditText.setText(""); 
  }
  
  private void updateAdapter()
  {
   myCursor = gaaSQLiteOpenHelper.select();
   SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.list,
          myCursor, new String[]
          {mySQLiteOpenHelper.FIELD_TEXT }, new int[]
          {R.id.listTextView1});
   /*
           使用SimpleCursorAdapter將我們所要的資料顯示在ListView中
     myCursor為我們query的結果 , 他可能有許多row
           而我們只需要顯示其中 FIELD_TEXT欄位的資料
           並且每行row的資料使用 我們事先在R.layout.list中定義好的rowView來顯示
           而FIELD_TEXT這欄資料對應到listTextView1 所定義的方式呈現
    */
   myListView.setAdapter(adapter);
  }
}

接下來是我們繼承SQLiteOpenHelper的類別。

package COM.TQC.GDD02;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class mySQLiteOpenHelper extends SQLiteOpenHelper
{
  private final static String DATABASE_NAME = "todo_db";
  private final static int DATABASE_VERSION = 1;
  private final static String TABLE_NAME = "todo_table";
  public final static String FIELD_id = "_id";
  public final static String FIELD_TEXT = "todo_text";

  public mySQLiteOpenHelper(Context context)
  {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase db)
  {    
    String sql = "CREATE TABLE " + TABLE_NAME + " (" + FIELD_id
        + " INTEGER primary key autoincrement, " + " " + FIELD_TEXT + " text)";
    db.execSQL(sql);    
  }

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

  public Cursor select()
  {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
    //query的結果顯示所有欄位的資料
    return cursor;
  }

  public long insert(String text)
  {
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL("INSERT INTO "+TABLE_NAME+" ("+FIELD_TEXT+") values ('"+text+"')");
    Cursor cursor = db.rawQuery("SELECT "+FIELD_id+" FROM "+TABLE_NAME+" WHERE "+FIELD_TEXT+"='"+text+"';", null);
    //rawQuery(String) 為另一種query的方法 , string內容為一般的 SQL語法
    cursor.moveToLast();
    /*
                也許有很多筆結果 , 因為text可能重複 , 但是FIELD_id是不會重複的
                跳置最後一個可以保證是剛剛所Insert的物件
     */
    return  cursor.getInt(0);    
  }

  public void delete(int id)
  {
    SQLiteDatabase db = this.getWritableDatabase();
    
    String where = FIELD_id + " = ?";
    String[] whereValue = { Integer.toString(id) };
    
    db.delete(TABLE_NAME, where, whereValue);
    
    // 或者使用 db.execSQL("DELETE FROM "+TABLE_NAME+" WHERE "+FIELD_id+"="+id);    
  }

  public void update(int id, String text)
  {
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL("UPDATE "+TABLE_NAME+" SET "+FIELD_TEXT+"='"+text+"' WHERE "+FIELD_id+"="+id);
  }
}

main.xml

再來是主程式GDD02.java中 , 在updateAdapter()時所用到的R.layout.list也就是 res/layout/list.xml

P.S. 題目中所要求的Variable和Method皆會保留 , 也會根據題目所要求的流程去實作 , 縱使題目要求繞遠路....