Android 開發 (七) DB 的使用介紹

在Android中要使用DataBase的功能，可以實作 SQLiteOpenHelper 或 SQLiteDatabase 接著只需在裡面使用相關的sql command即可 在這邊就稍微介紹一下該如何下指令

interface DAO_DB
{
 public void createTable();
 public void insertData(Object obj);
 public void deleteData(Object obj);
 public void updateData(Object obj);
 public void FindAllData();
 public void FindLastID();
 public void close();
}
public class DBHelper extends SQLiteOpenHelper implements DAO_DB{
 private SQLiteDatabase dbDatabase;
 private String TABLE_NAME = "MyTab";
 
 public DBHelper(Context context) {
  super(context, "TED_DE", null, 1);
  // TODO Auto-generated constructor stub
 }


 @Override
 public void onCreate(SQLiteDatabase db) {
  // TODO Auto-generated method stub
  dbDatabase = db;
  createTable();
 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  // TODO Auto-generated method stub
  
 }

 @Override
 public void createTable() {
  // TODO Auto-generated method stub
  String cmd = "CREATE TABLE " + TABLE_NAME + " ("
    + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
    DBColumns.NAME + " TEXT, " +
    DBColumns.TEL + " TEXT, " +
    DBColumns.EMAIL + " TEXT);"; 
  dbDatabase.execSQL(cmd);
 }

 @Override
 public void insertData(Object obj) {
  HashMap<String, String> map = (HashMap<String, String>)obj;
     String sql = "INSERT INTO "+ TABLE_NAME + 
       " values("
       +"\"" + map.get(DBColumns._ID) +"\","
       +"\"" + map.get(DBColumns.NAME) +"\","
       +"\"" + map.get(DBColumns.TEL) +"\","
       +"\"" + map.get(DBColumns.EMAIL) +"\""
       + ");";

     getWritableDatabase().execSQL(sql);
 }

 @Override
 public void deleteData(Object obj) {
  // TODO Auto-generated method stub
  HashMap<String, String> map = (HashMap<String, String>)obj;
  String sql = String.format("DELETE FROM "+ TABLE_NAME+" WHERE _id = '%s' ", map.get(DBColumns._ID));
  getWritableDatabase().execSQL(sql);
 }


 @Override
 public void updateData(Object obj) {
  HashMap<String, String> map = (HashMap<String, String>)obj;
     ContentValues values = new ContentValues();
     
     String sql = "UPDATE "+ TABLE_NAME + 
       " SET "+DBColumns.NAME+" = ?, "
       +DBColumns.TEL+" = ?, "
       +DBColumns.EMAIL+" = ? "+
       "WHERE _ID = ? ";
     Object[] bindArgs = new Object[]{map.get(DBColumns.NAME),map.get(DBColumns.TEL),map.get(DBColumns.EMAIL),map.get(DBColumns._ID)};
     getWritableDatabase().execSQL(sql,bindArgs);
 }


 @Override
 public void FindAllData() { 
   String query = "SELECT * FROM "+TABLE_NAME;
   Cursor cursor = getWritableDatabase().rawQuery(query, null);
    
    
    StringBuffer sf = new StringBuffer();
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
     sf.append(cursor.getInt(0)).append(" : ")
     .append(cursor.getString(1)).append(" : ")
     .append(cursor.getString(2)).append(" : ")
     .append(cursor.getString(3)).append("\n");
     cursor.moveToNext();
     }
    Log.d("Ted", sf.toString());
 }


 @Override
 public void FindLastID() {
  Cursor cur = getWritableDatabase().rawQuery("select LAST_INSERT_ROWID() ",null);
        cur.moveToFirst();
        Log.d("Ted","LAST ID"+ String.valueOf(cur.getLong(0)));
 }

}

上面實作了簡單的 insert ,update, del ,FindLastId , FindAllData的方法
當然其實也可以簡單的使用getWritableDatabase().insert getWritableDatabase().update getWritableDatabase().del  只是作者想順便練練execSQL的寫法而已 XD


若有興趣看看sampleCode 可以使用下面的網址

Sample Code