Android: Data --- Database

see http://developer.android.com/reference/android/database/package-summary.html

• Databases are useful for storing larger volumes of data that fit in the relational scheme of the supported SQLite Database

• Also, this lets you store relationships between data (like foreign keys concept)

• Only availabe to the application that created the database

• Stored in folder /data/data/<package_name>/database

Step 1: Create DBAdapter Helper Class

• encapsulates access of data

• can create, open and close SQLite Database

Step 1A: Setup static variables to represent fields in your database table

 

class DBAdapter {

static final String KEY_ROWID = "_id"; // field called _id

static final String KEY_NAME = "name"; //field called name

static final String KEY_EMAIL = "email"; //field called email

static final String TAG = "DBAdapter";

static final String DATABASE_NAME = "MyDB"; //name of database

static final String DATABASE_TABLE = "contacts"; //name of table

static final int DATABASE_VERSION = 1;

 

 

Step 1B: Setup static variable for SQL to create database table

 

class DBAdapter {

        //*******CODE FROM step 1A ******

        //SQL to create table contacts with 3 fields _id, name, email where _id is the primary key
       static final String DATABASE_CREATE = "create table contacts (_id integer primary key autoinclrement," +
                                                                          "name text not null, email text not null); "
                                         

 

 

 

Step 1C: Add methods open(), close() and insertContact(name, email), deleteContact(rowID), getAllContacts(), getContact(rowID), updateContact(rowID, name, email)

see example below that uses the private class we create in Step 1C to assist to get an instance of android.database.sqlite.SQLitesDatabase which has methods like insert, delete, query, update

 

class DBAdapter {

//******Code from STEPS 1A&1B *******

android.database.sqlite.SQLiteDatabase db;      //this is the handle to the underlying SQLiteDatabase
                                                                         // this class HELPER class uses (encapsulates)

//method that creates instace of actual database store in variable db, uses private class discussed in Step 1D
public DBAdapter open() throws SQLException
{
         this.db = DBHelper.getWriteableDatabase(); //see step 1D

         return this;

}

 

//method that uses the SQLiteDatabase instance db to store a new row in the Contact table
public long insertContact(String name, String email)
{

ContentValues intitialValues = new ContentValues();

intialValues.put(KEY_NAME, name);
intialValues.put(KEY_EMAIL, email);

return db.insert(DATABASE_TABLE, null, initialValues);

}

//method that uses the SQLiteDatabase instance db to remove a row in Contact table
public long deleteContact(long rowID)
{

return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowID, null) > 0;

}

//SEE EXAMPLE AT BOTTOM FOR OTHER METHODS

 

Step 1D: Add private class extends SQLiteOpenHelper to manage database creation and management

 

class DBAdapter {

        //*******CODE FROM steps 1A & 1B &1C ******

       DatabaseHelper DBHelper;

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

 

       private static class DatabaseHelper extends SQLiteOpenHelper
       {

DatabaseHelper(Context context)
   {   super (context, DATBASE_NAME, null, DATABASE_VERSION); }

 

//creates database table contacts
@Override
public void onCreate(SQLiteDatabase db)
   {

try{
        db.execSQL(DATABASE_CREATE);
} catch(SQLEXCEPTION e) {
        e.printStackTrace();
}

    }

 

//removes Database table contacts and recreates database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int NewVersion)
   {

Log.w(TAG, "upgrading from version" + oldVersion +" to " + newVersion);

db.execSQL("DROP TABLE IF EXISTS contacts");

onCreate(db); //will create the database if does not exist

    }

}

 

 

 

 

Step 2: In applicaiton's Activity create DBAdapter instance and use it

DPAdapter db = new DBAdapter(this);

db.open(); //call its open method

db.insertContact("Lynne Grewe", "lynne.grewe@csueastbay.edu"); //will return -1 if failure

//when you are done close it
db.close();

 

Example using above to create Database Table

Here we see the application cycling through the rows in the table and displaying them as Toast.makeText's temporary text at bottom

SEE BOOK for complete example