URL:http://mobdev.olin.edu/mobdevwiki/FrontPage/Tutorials/Databases

Database Helper Class

You need to create a special helper class for your specific application. This section will walk through an example helper class and what the components are.

Your database helper defines a couple housekeeping and useful functional methods. The main ones include open, close, createEntry, fetchEntry, fetchAllEntries, updateEntry, deleteEntry, and clear. I will start by going over some of the general creation portions, then go over each of these useful functions.

Parameters

To start you need to create a couple methods for functions you are using. In this case a simple phone number database:

Toggle line numbers

   1 private static final String TAG = "PhoneNumberAdaptor";
   2 private DatabaseHelper mDbHelper;
   3 private SQLiteDatabase mDb;

Next you will want to define all of the keys that you want in the database. In this case I am creating a simple contact database so you have 4 keys:

Toggle line numbers

   1 public static final String KEY_LASTNAME = "LastName";
   2 public static final String KEY_FIRSTNAME = "FirstName";
   3 public static final String KEY_NUMBER = "Number";
   4 public static final String KEY_ROWID = "_id";
   5 String[] KEYS = {KEY_ROWID,
   6                 KEY_LASTNAME, KEY_FIRSTNAME, KEY_NUMBER};

Now that you know what you want to store in the database it's time to create it. The first step is to organize all of the parameters for ease of programming. The last component tells the database what type of information you are putting in each entry. _id is for accessing the entries and the other three are all strings:

Toggle line numbers

   1 private static final String DATABASE_NAME = "mydb";
   2 private static final String DATABASE_TABLE = "numbers";
   3 private static final int DATABASE_VERSION = 1;
   4 private static final String DATABASE_CREATE = "CREATE TABLE " 
   5                                                 + DATABASE_TABLE 
   6                                                 + " (_id integer primary key autoincrement, "
   7                                                 + "LastName VARCHAR, FirstName VARCHAR, Number VARCHAR);";

Once you have all of these parameters taken care of it is time to create the context and the actual database. This creates the database helper and creates some constructs for when it is created and what happens if you need to upgrade if your code changes down the road. This example is pretty simple so it just deletes the old one and creates a new one:

Toggle line numbers

   1 private final Context mCtx;
   2 
   3 private static class DatabaseHelper extends SQLiteOpenHelper {
   4         DatabaseHelper(Context context) {
   5                 super(context, DATABASE_NAME, null, DATABASE_VERSION);
   6         }
   7         
   8         @Override
   9         public void onCreate(SQLiteDatabase db){
  10                 
  11                 db.execSQL(DATABASE_CREATE);
  12         }
  13         
  14         @Override
  15         public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
  16                 Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
  17                                 + newVersion + ", which will destroy all old data");
  18                 db.execSQL("DROP TABLE IF EXISTS "+ DATABASE_TABLE);
  19                 onCreate(db);
  20         }
  21     }
  22 
  23 /**
  24 * Constructor - takes the context to allow the database to be opened/created
  25 * 
  26 * @param ctx the Context within to work
  27 */
  28 public PhoneNumberAdaptor(Context ctx){
  29         this.mCtx = ctx;
  30 }

For the rest of the functions I'm just going to put in the code for them. The individual sections each have comments describing their interface and function:

Toggle line numbers

   1 /**
   2 * Open the numbers database.  If it cannot be opened, try to create a new
   3 * instance of the database.  If it cannot be created, throw an excepton to
   4 * signal the failure
   5 * 
   6 * @return this (self reference, allowing this to bechained in an initialized call)
   7 * @throws SQLException if the database could be neither be opened or created
   8 */
   9 
  10 public PhoneNumberAdaptor open() throws SQLException{
  11         mDbHelper = new DatabaseHelper(mCtx);
  12         mDb = mDbHelper.getWritableDatabase();
  13         return this;
  14 }
  15 
  16 /**
  17 * Closes the database.
  18 */
  19 
  20 public void close(){
  21         mDbHelper.close();
  22 }
  23 
  24 /**
  25 * Create a new entry using the last name, first name, and number provided.
  26 * If the entry is successfully created return the new rowid for that entry,
  27 * otherwise return a -1 to indicate failure.
  28 * 
  29 * @param LastName the persons last name
  30 * @param FirstName the persons first name
  31 * @param Number the persons phone number
  32 * @return rowid or -1 if failed
  33 */
  34 
  35 public long createEntry(String LastName, String FirstName, String Number){
  36         ContentValues initialValues = new ContentValues();
  37         initialValues.put(KEY_LASTNAME, LastName);
  38         initialValues.put(KEY_FIRSTNAME, FirstName);
  39         initialValues.put(KEY_NUMBER, Number);
  40         
  41         return mDb.insert(DATABASE_TABLE, null, initialValues);
  42 }
  43 
  44 /**
  45 * Return a Cursor positioned at the entry that matches the given rowid
  46 * 
  47 * @param rowid id of the entry to retreive
  48 * @return Cursor positioned to matching entry if found
  49 * @throws SQLException if entry could not be found/retrieved
  50 */
  51 
  52 public Cursor fetchEntry(long rowid) throws SQLException {
  53         Cursor mCursor = mDb.query(true, DATABASE_TABLE, KEYS, KEY_ROWID + "=" + rowid, null, null, null, null, null);
  54         if (mCursor != null){
  55                 mCursor.moveToFirst();
  56         }
  57         return mCursor;
  58 }
  59     
  60 /**
  61 * Return a Cursor over the list of all entries in the database
  62 * 
  63 * @return Cursor over all notes
  64 */
  65 
  66 public Cursor fetchAllEntries(){
  67         return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_LASTNAME, 
  68                         KEY_FIRSTNAME, KEY_NUMBER}, null, null, null, null, null);
  69         
  70 }  
  71     
  72 /**
  73 * Update the entry using the details provided.  The entry to be updated is
  74 * specified using the rowid, and it is altered to use the last name, first name,
  75 * and phone number passed in
  76 * 
  77 * @param rowid id of the note to update
  78 * @param LastName value to set the entry last name to
  79 * @param FirstName value to set the entry first name to
  80 * @param Number value to set the entry phone number to
  81 * @return true if the entry was successfully updated, false otherwise
  82 */
  83 
  84 public boolean updateEntry(long rowid, String LastName, String FirstName, String Number){
  85         ContentValues args = new ContentValues();
  86         args.put(KEY_LASTNAME, LastName);
  87         args.put(KEY_FIRSTNAME, FirstName);
  88         args.put(KEY_NUMBER, Number);
  89         
  90         return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowid, null)>0;
  91 }
  92     
  93 /**
  94 * Delete the entry with the given rowid
  95 * 
  96 * @param rowid id of entry to delete
  97 * @return true if deleted, false otherwise
  98 */
  99 public boolean deleteEntry(long rowid){
 100         return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowid, null)>0;
 101 }
 102     
 103 /**
 104 * Clears the database
 105 * 
 106 * Used mainly for reseting the values while developing
 107 * 
 108 * @return true if cleared, false otherwise
 109 */
 110 
 111 public boolean clear(){
 112         return mDb.delete(DATABASE_TABLE, KEY_ROWID, null)>0;
 113 }

Put all of this together inside a public class and you've got yourself a very simple Contact database.

Example application with Explanation

This section will go over how to implement a database in an application using the helper created in the previous section.

I will assume that you know how to create an xml layout and the principles associated with connecting to the widgets so I will just show the xml of the example and leave you to figure out the specifics(main.xml):

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent">
                
        <TextView android:id="@+id/last_name" 
            android:layout_width="fill_parent" 
            android:layout_height="wrap_content" 
            android:text="Enter Last Name:"/>
                
        <EditText android:id="@+id/last_name_entry"
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:background="@android:drawable/editbox_background"
                android:layout_below="@id/last_name"/>
                
        <TextView android:id="@+id/first_name_label" 
            android:layout_width="fill_parent" 
            android:layout_height="wrap_content" 
            android:text="Enter First Name:"
            android:layout_below="@id/last_name_entry"/>
                
        <EditText android:id="@+id/first_name_entry"
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:background="@android:drawable/editbox_background"
                android:layout_below="@id/first_name_label"/>
                
        <TextView android:id="@+id/number_label" 
            android:layout_width="fill_parent" 
            android:layout_height="wrap_content" 
            android:text="Enter Phone Number"
            android:layout_below="@id/first_name_entry"/>
                
        <EditText android:id="@+id/number_entry"
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:background="@android:drawable/editbox_background"
                android:layout_below="@id/number_label"/>
                
        <Button android:id="@+id/add_number"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="Add Number"
                android:layout_below="@id/number_entry"/>
        
        <!--
        This next section is a little interesting.  In order to display the information in a slightly more logical way
        I am using a ListView with the format pulled from another .xml layout file 
        -->
        
        <LinearLayout android:layout_width="wrap_content"
                android:layout_height="fill_parent"
                android:layout_below="@id/add_number">
                
                <ListView android:id="@+id/android:list"
                        android:layout_width="wrap_content"
                        android:layout_height="fill_parent"/>
                
                <TextView android:id="@+id/android:empty"
                        android:layout_width="wrap_content"
                        android:layout_height="wrap_content"
                        android:text="No Entries!"/>
        </LinearLayout>
</RelativeLayout>

The ListView pulls from another layout here (row.xml):

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content">
    
    <TextView android:id="@+id/last"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"/>
    
    <TextView android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text=", "/>
    
    <TextView android:id="@+id/first"
        android:layout_height="wrap_content"
        android:layout_width="wrap_content"/>
    
</LinearLayout>

This next portion will go over the main program. The first step again is to declare the constants.

Toggle line numbers

   1 private PhoneNumberAdaptor mDbHelper;
   2 
   3 private EditText last_name_entry;
   4 private EditText first_name_entry;
   5 private EditText number_entry;
   6 private Button add_number;

From here the next method is the onCreate method which pulls the layout, instantiates the adapter made in the previous section and calls the useful functions. init() hooks up the widgets and puts in a couple initial entries to show it works and fillData() populates the ListView with all of the numbers:

Toggle line numbers

   1 @Override
   2 public void onCreate(Bundle savedInstanceState) {
   3         super.onCreate(savedInstanceState);
   4         setContentView(R.layout.main);
   5         mDbHelper = new PhoneNumberAdaptor(this);
   6         mDbHelper.open();
   7         mDbHelper.clear();  //Remove this line to have persistance
   8         init();
   9         fillData();
  10     }

The next section of code is the init method. I like to pull out all of the general initiation portions and group them in one easy-to-read section:

Toggle line numbers

   1 /**
   2 * This runs to setup all of the simple GUI interface handles.
   3 */
   4     
   5 private void init(){
   6         
   7         /**
   8          * Find and create handles for the used GUI items.
   9          */
  10         
  11         last_name_entry = (EditText)findViewById(R.id.last_name_entry);
  12         first_name_entry = (EditText)findViewById(R.id.first_name_entry);
  13         number_entry = (EditText)findViewById(R.id.number_entry);
  14         add_number = (Button)findViewById(R.id.add_number);
  15         
  16         /**
  17          * Add the listener for the Add Number button.
  18          */
  19         
  20         add_number.setOnClickListener(new Button.OnClickListener(){public void onClick(View v){add_num();}});
  21         
  22         /**
  23          * Initializes the database with some entries.
  24          */
  25         
  26         mDbHelper.createEntry("Smith", "John", "666-123-4568");
  27         mDbHelper.createEntry("Smith", "Jane", "666-123-4567");
  28         
  29 }

This next function will populate the ListView, This should be called when the database is changed:

Toggle line numbers

   1 /**
   2 * Call this to populate the ListView with all of the entries
   3 */
   4     
   5 private void fillData(){
   6         Cursor entryCursor = mDbHelper.fetchAllEntries();
   7         startManagingCursor(entryCursor);
   8         
   9         /**
  10          * The columns in the database table to use as sources
  11          */
  12         
  13         String[] from = new String[]{PhoneNumberAdaptor.KEY_LASTNAME, PhoneNumberAdaptor.KEY_FIRSTNAME};
  14 
  15         /**
  16          * The destination textviews for the column's data
  17          */
  18         
  19         int[] to = new int[]{R.id.last, R.id.first};
  20         
  21         SimpleCursorAdapter entry_last = new SimpleCursorAdapter(this, R.layout.row, entryCursor, from, to);
  22         setListAdapter(entry_last);
  23 }

Finally the last method is the one called when the user adds an entry:

Toggle line numbers

   1 /**
   2 * Called when the user clicks the Add Number button.
   3 * 
   4 * It pulls the information and adds it to the database.
   5 * Once it adds the entry it updates the listview.
   6 */
   7     
   8 private void add_num(){
   9         String LastName = last_name_entry.getText().toString();
  10         String FirstName = first_name_entry.getText().toString();
  11         String Number = number_entry.getText().toString();
  12         
  13         mDbHelper.createEntry(LastName, FirstName, Number);
  14         fillData();
  15         
  16 }