Allen 的學習筆記

SQLite 是 Android 內建的一個輕量級資料庫系統。

要在程式中使用SQLite，需要利用SQLiteOpenHelper類別及SQLiteDatabase類別。

 

SQLiteOpenHelper類別:主要是要取得操作資料庫的物件，需要傳入資料庫檔案名稱，如果該檔案不存在會自動建立。

SQLiteDatabase類別: SQLiteOpenHelper類別的getWritableDatabase()方法會回傳一個SQLiteDatabase型態物件，該物件提供操作資料庫的各種方法，ex:insert, delete,query,replace, update, execSQL...等。

 

STEP1 : 建立一個Helper 類別繼承自SQLiteOpenHelper 並Override onCreate&onUpgrade

public class DBHelper extends SQLiteOpenHelper {

    private String _CreateTableCommand;

    public DBHelper(Context context, String name, String command, SQLiteDatabase.CursorFactory factory,int version) {
        super(context, name, factory, version);
        _CreateTableCommand = command;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub

        if (_CreateTableCommand.isEmpty())
            return;

        db.execSQL(_CreateTableCommand);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVer, int newVer) {
        // TODO Auto-generated method stub

    }
}

STEP2:  建立DBHelper物件並利用getWritableDatabase 取得SQLiteDatabase 物件即可利用該物件進行資料存取。

_DBHelper = new DBHelper(getApplicationContext(), _DB_FILE, _CreateTableCommand, null, 1);

_DbRW = _DBHelper.getWritableDatabase();

public class MyActivity extends Activity {
    private Button _BtnAdd;
    private Button _BtnList;
    private EditText _EdtName;

    public static final String _DB_FILE = "friends.db";
    private String _DB_TABLE = "friends";

    private DBHelper _DBHelper;
    private SQLiteDatabase _DbRW;
    private String _CreateTableCommand="CREATE TABLE friends(" +
            "_id INTEGER PRIMARY KEY," +
            "name TEXT NOT NULL," +
            "sex TEXT," +
            "address TEXT);";
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_my);

        _BtnAdd = (Button)findViewById(R.id.btnAdd);
        _BtnList =  (Button)findViewById(R.id.btnList);
        _EdtName = (EditText)findViewById(R.id.edtName);
        _BtnAdd.setOnClickListener(onClickBtnAdd);
        _BtnList.setOnClickListener(onClickBtnListAdd);

        _DBHelper = new DBHelper(getApplicationContext(), _DB_FILE, _CreateTableCommand, null, 1);
        _DbRW = _DBHelper.getWritableDatabase();
    }

    private Button.OnClickListener onClickBtnListAdd = new Button.OnClickListener() {
        @Override
        public void onClick(View v) {
            Cursor c = null;

            if (_EdtName.getText().toString().isEmpty() == false) {
                c = _DbRW.query(true, _DB_TABLE, new String[]{"name"}, null, null, null, null, null, null);
            }
            if (c == null)
                return;

            if (c.getCount() == 0) {
                Log.i("MyApp", "No Data");
            }
            else {
                c.moveToFirst();
                Log.i("MyApp", c.getString(0));

                while (c.moveToNext())
                    Log.i("MyApp", c.getString(0));
            }
        }
    };

    private Button.OnClickListener onClickBtnAdd = new Button.OnClickListener() {
        @Override
        public void onClick(View v) {
            //Log.i("MyApp", "Add Click");
            ContentValues newRow = new ContentValues();
            newRow.put("name", _EdtName.getText().toString());
            _DbRW.insert(_DB_TABLE, null, newRow);
        }
    };
    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.my, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        // Handle action bar item clicks here. The action bar will
        // automatically handle clicks on the Home/Up button, so long
        // as you specify a parent activity in AndroidManifest.xml.
        int id = item.getItemId();
        if (id == R.id.action_settings) {
            return true;
        }
        return super.onOptionsItemSelected(item);
    }
}