Exploring Android's Embedded Database

One of the stand-apart functionalities of Android is support for an embedded

database, SQLite. It's a software library collection that implements a

self-contained, server independent, zero-configuration, transactional SQL

database engine. Because of this, and its small code footprint it has found use

in widespread embedded applications. SQLite can be used as an embedded database

for desktop applications as well as for electronic devices ranging from mobile

phones, PDAs, MP3 players to smart security devices used in automobiles.

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.