With Android, SQLite comes as a default database installed for developing mobile applications that need database support. Android provides necessary support to SQLite and exposes its database management functions that allow you to store complex data collections wrapped into useful objects. It also ships along with SQLite3 database tool that enables browsing table contents, executing SQL commands and performing other functions on the database. With support for database programming included, the need to pack separate database libraries along with the application is not needed now.

The last couple of issues we had covered topics about Activity and Intent objects of the Android based application life cycle. In this article we look at how we can employ SQLite database in an Android based mobile application.

Address Book App
We would create an address book manager to demonstrate the integration of database functionality into an Android application. We would be using the default SQLite database for the application, but Android gives us liberty to use any other embedded database. JavaDB is another embedded database that can be used for such mobile applications. Another database engine that can be used is db4objects (PCQuest March 08, pg 106), which is an objects based database and eliminates the need of mapping Bean objects to the RDBMS.

In our AddressBook application we will store the information of a person such as name, mobile number, address, email and a note. In the note column, the user of the application can store some textual notes like secondary number of the person or anniversary or birth dates. The functioning of the application is simple. When the application starts a list of names and their mobile numbers is displayed from the address book database. When the user clicks on any list item, a new activity is triggered that displays the complete information of the person. The user can edit those details and save the changes. Also in the main activity we will be using a menu popup containing two options: to add a new entry into address book or delete one.

Getting started
To start up with building the AddressBook application, start a new Android project in Eclipse (refer to Febuary'08 issue to configure Eclipse for Android support). Name the project as AddressBookManager and on new android project window, give the properties values as:

• Package Name: com.pcq.android.addMgr
• Activity Name: AddressBookMgr
• App Name: AddressBook Manager

When the project opens in Eclipse, AddressBookMgr.java file opens. This class will be our main activity that will display the list of persons taken from the database by wrapping a database function, in our case DBHelper. Before we start up with our main activity class, we should first create the DBHelper class that will encapsulate the data access to SQLite database engine for storing address information and also to retrieve and update that information.

All methods for manipulating data in database like retrieving, creating, updating and deleting records will be handled from this DBHelper class. In this class, we would declare a database with the name as AddressBook and will create a table in the database with the same name. The table will be having name, mobile, address, email and notes as text fields while _id will be the primary key with auto increment. When the main activity class instantiates the DBHelper class with context as the parameter, the wrapper will check that the database with name AddressBook exists, else it would create the database. To create the database, Context.createDatabase() method is used which accepts four parameters, namely database name, version, mode and factory which is optional and is used to instantiate a cursor when a query is fired. While Context.openDatabase() method is used to open an existing database, it accepts two parameters, first is for database name and other is for cursor factory. The following code snippet shows the DBHelper class where we define the database and its parameters and also use the Context methods for creating and opening the database:

On startup, the application will display the list of persons and on clicking the Menu pop-up, the Add Details activity will appear from where new details can be added

public class DBHelper {
class Tuple extends Object {
public String name;
public String mobile;
public String address;
public String email;
public String notes;
public long rowId;
}
private SQLiteDatabase db;
public DBHelper(Context ctx) {
try {
db = ctx.openDatabase("AddressBook", null);
} catch (FileNotFoundException e) {
try {
db =ctx.createDatabase("AddressBook", 1, 0,null);
db.execSQL(DATABASE_CREATE);
} catch (FileNotFoundException e1) {
db = null;
}
}
}
private static final String DATABASE_CREATE =
"create table addressbook (_id integer primary key autoincrement, "
+ "name text not null,"
+ "mobile text not null,"
+ "address text not null,"
+ "email text not null,"
+ "notes text null"
+");";
public void close() {
db.close();
}
}

Defining activities
Once we have defined the database parameters in the DBHelper class, we can now work on the main activity class. The main activity class in our case is the AddressBookMgr. When the project is created this class gets created by Eclipse and is extending Activity class. As we have to display a list to the user, we will have to extend this class to ListActivity, so that list view can be used. The following code snippet does the same:

public class AddressBookMgr extends ListActivity {
private DBHelper dbHelper;
private Cursor c;
public void onCreate(Bundle icicle) {
super.onCreate(icicle); setContentView(R.layout.contact_list);
dbHelper = new DBHelper(this);
setupListStripes();
FillData();
}
private void FillData() {
c = dbHelper.GetAllRows();
startManagingCursor(c);
ListAdapter adapter = new SimpleCursorAdapter(
this,
R.layout.contact_row,
c,
new String[] {"name", "mobile", "_id"},
new int[] {R.id.col1, R.id.col2}
);
setListAdapter(adapter);
}

The onCreate method calls for a setupListStripes()method, which displays the alternate items in the list in different colors. The setupListStripes method's code and implementation can also be seen in the Android sample notepad project that is provided with the SDK. Now onCreate(), a FillData method, is called. This method accesses the database wrapper to call GetAllRows() method. The GetAllRows() method of the DBHelper class is shown in the following code snippet:

public Cursor GetAllRows() {
try {
return db.query("AddressBook", new String[] {
"_id", "name", "mobile", "address", "email", "notes"}, null, null, null, null, null);
} catch (SQLException sqle) {
Log.sqle ("Exception on query:-", sqle.toString());
return null;
}
}

Similarly the method calls for updation, deletion and selection of a database table can be created. On the main activity class, we have to create menu items and their following actions. By overriding the onCreateOptionsMenu method, we create menu items for the main activity. The following code snippet creates two menu items 'Add Details' and 'Delete selected.' The strings.xml file under res/values has the text for the menu items:

public boolean onCreateOptionsMenu(Menu menu) {
super.onCreateOptionsMenu(menu);
menu.add(0, Menu.FIRST, R.string.menu_insert);
menu.add(0, Menu.FIRST+1, R.string.menu_delete);
return true;
}
public boolean onMenuItemSelected(int featureId, Item item) {
super.onMenuItemSelected(featureId, item);
switch(item.getId()) {
case Menu.FIRST:
createDetail();
break;
case Menu.FIRST+1:
//call dbHelper's delete method
break;
}
return true;
}

When a particular item from menu is selected the onMenuItemSelected is executed. If the Add Detail menu is selected, which is denoted by Menu.FIRST having value as 1, the createDetail() method will be called. This createDetail() as in the following code snippet will call the sub activity AddressBookEdit. The sunActivity() takes Intent and request code as two parameters. The Intent on the other hand is the description of the action to be performed.

private void createDetail() {
Intent i = new Intent(this, AddressBookEdit.class);
startSubActivity(i, ACTIVITY_CREATE);
}

SubActivity description
As we are calling a sub activity from the main activity, it needs to be registered with Android so that at execution time it can find activities. For this the activity needs to be entered in the AndroidManifest.xml file, so that at execution of the AddressBook app, the system could know of all the activities that the application is going to need. The tag would register the AddressBookEdit class as an activity for the application and should be placed just before the tag in the xml file. In the following sub activity we show how the elements on the layout will be displayed and we will also extract the extras from the Bundle that were passed with the Intent.

public class AddressBookEdit extends Activity {
protected void onCreate(Bundle icicle) {
super.onCreate(icicle);
setContentView (R.layout.contact_edit);
nameText = (EditText) findViewById(R.id.txtName);
addressText = (EditText) findViewById(R.id.txtAddress);
mobileText = (EditText) findViewById(R.id.txtMobile);
homeText = (EditText) findViewById(R.id.txtHome);
Button confirmButton = (Button) findViewById(R.id.BtnSave);
rowId = null;

Bundle extras = getIntent().getExtras();
.
.
.
}

Now we have to write code for the event listener that will be triggered when the Save button is clicked. When the user clicks the Save button, the onClick() method will do some work and return the values of the task to the Intent caller. The following code template will allow us to add onClick() function for the event listening (click event). We can then return the specific result with a Bundle that maps the strings to various parcelable types.

confirmButton.setOnClickListener(new View.OnClickListener() {
public void onClick(View arg0) {
// return result });

As we can see, the Android application model is based around Activities calling each other. When we call an activity from another activity, the calling activity gets paused or killed to save system resources. So we must store the state of our activities to resume tasks and avoid restarting activity all over again.

Similarly we can fill the code for edit method of the subactivity also and complete the AddressBook application. You may look at the source code of the AddressBook application at forums.

pcquest.com under the current issue thread. Also if we use a database like db4objects for this kind of application, the implementation would be more or less the same but the library for database would have to be packaged with the app.