Create, delete, update : SQLiteOpenHelper « Database « Android

Create, delete, update

     

package app.test;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;
import android.widget.Toast;

class DBAdapter {
  public static final String KEY_ROWID = "_id";
  public static final String KEY_NAME = "name";
  public static final String KEY_EMAIL = "email";
  private static final String TAG = "DBAdapter";

  private static final String DATABASE_NAME = "MyDB";
  private static final String DATABASE_TABLE = "contacts";
  private static final int DATABASE_VERSION = 2;

  private static final String DATABASE_CREATE = "create table contacts (_id integer primary key autoincrement, "
      + "name text not null, email text not null);";

  private final Context context;

  private DatabaseHelper DBHelper;
  private SQLiteDatabase db;

  public DBAdapter(Context ctx) {
    this.context = ctx;
    DBHelper = new DatabaseHelper(context);
  }

  private static class DatabaseHelper extends SQLiteOpenHelper {
    DatabaseHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
      try {
        db.execSQL(DATABASE_CREATE);
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      Log.w(TAG, oldVersion + " to " + newVersion
          + ", which will destroy all old data");
      db.execSQL("DROP TABLE IF EXISTS contacts");
      onCreate(db);
    }
  }

  public DBAdapter open() throws SQLException {
    db = DBHelper.getWritableDatabase();
    return this;
  }

  public void close() {
    DBHelper.close();
  }

  public long insertContact(String name, String email) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_NAME, name);
    initialValues.put(KEY_EMAIL, email);
    return db.insert(DATABASE_TABLE, null, initialValues);
  }

  public boolean deleteContact(long rowId) {
    return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
  }

  public Cursor getAllContacts() {
    return db.query(DATABASE_TABLE, new String[] { KEY_ROWID, KEY_NAME,
        KEY_EMAIL }, null, null, null, null, null);
  }

  public Cursor getContact(long rowId) throws SQLException {
    Cursor mCursor = db.query(true, DATABASE_TABLE, new String[] {
        KEY_ROWID, KEY_NAME, KEY_EMAIL }, KEY_ROWID + "=" + rowId,
        null, null, null, null, null);
    if (mCursor != null) {
      mCursor.moveToFirst();
    }
    return mCursor;
  }

  public boolean updateContact(long rowId, String name, String email) {
    ContentValues args = new ContentValues();
    args.put(KEY_NAME, name);
    args.put(KEY_EMAIL, email);
    return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
  }
}

public class Test extends Activity {
  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    try {
      String destPath = "/data/data/" + getPackageName()
          + "/databases/MyDB";
      File f = new File(destPath);
      if (!f.exists()) {
        CopyDB(getBaseContext().getAssets().open("mydb"),
            new FileOutputStream(destPath));
      }
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }

    DBAdapter db = new DBAdapter(this);
    // ---add a contact---
    db.open();
    long id = db.insertContact("Wei-Meng Lee",
        "weimenglee@learn2develop.net");
    id = db.insertContact("Mary Jackson", "mary@jackson.com");
    db.close();
    // ---get all contacts---
    db.open();
    Cursor c = db.getAllContacts();
    if (c.moveToFirst()) {
      do {
        DisplayContact(c);
      } while (c.moveToNext());
    }
    db.close();

    // ---get a contact---
    db.open();
    c = db.getContact(2);
    if (c.moveToFirst())
      DisplayContact(c);
    else
      Toast.makeText(this, "No contact found", Toast.LENGTH_LONG).show();
    db.close();

    // ---update contact---
    db.open();
    if (db.updateContact(1, "Wei-Meng Lee", "weimenglee@gmail.com"))
      Toast.makeText(this, "Update successful.", Toast.LENGTH_LONG)
          .show();
    else
      Toast.makeText(this, "Update failed.", Toast.LENGTH_LONG).show();
    db.close();

    // ---delete a contact---
    db.open();
    if (db.deleteContact(1))
      Toast.makeText(this, "Delete successful.", Toast.LENGTH_LONG)
          .show();
    else
      Toast.makeText(this, "Delete failed.", Toast.LENGTH_LONG).show();
    db.close();

  }

  public void CopyDB(InputStream inputStream, OutputStream outputStream)
      throws IOException {
    byte[] buffer = new byte[1024];
    int length;
    while ((length = inputStream.read(buffer)) > 0) {
      outputStream.write(buffer, 0, length);
    }
    inputStream.close();
    outputStream.close();
  }

  public void DisplayContact(Cursor c) {
    Toast.makeText(
        this,
        "id: " + c.getString(0) + "\n" + "Name: " + c.getString(1)
            + "\n" + "Email:  " + c.getString(2), Toast.LENGTH_LONG)
        .show();
  }
}
//main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    >
<TextView  
    android:layout_width="fill_parent" 
    android:layout_height="wrap_content" 
    android:text="@string/hello"
    />
</LinearLayout>