Android soup with SQLiteDatabase, ListActivity and Options Menu

Have some Android soup made with SQLiteDatabase, ListActivity and Options Menu as ingredients! In this latest encounter with Android I create a small database that holds a list of Train numbers and names. The contents of this list is displayed to the user. The user is also given a menu to “add”, “delete” and “deleteAll” the records.

To get started I create a SqlOpenHelper class which extends the SQLiteOpenHelper. This class has the methods to create the SQLiteDatabase.

publicvoid onCreate(SQLiteDatabase db) {



publicvoid createDatabase(SQLiteDatabase db) {




TRAIN_NO + ” INTEGER , ” + TRAIN_NAME + ” TEXT” + “)”;

Log.d(“Creating: ” , CREATE_TRAIN_TABLE);



Besides there are methods to

1) To add a train record

// Add a train method

publicvoid addTrain(int trainNo,String trainName) {

SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();

values.put(TRAIN_NO, trainNo);

values.put(TRAIN_NAME, trainName);

// Insert a new row

db.insert(TRAINS_TABLE, null, values);

db.close(); // Closing database connection


2) To delete a train record

// Delete a single train

publicvoid deleteTrain(Train train) {

SQLiteDatabase db = this.getWritableDatabase();

db.delete(TRAINS_TABLE, TRAIN_ID + ” = ?”,

new String[] { String.valueOf(train.getID()) });



and to display all the trains in the database. The entire list is returned as a List.

3) Display all trains

// Getting all trains in SQLiteDB

public List<Train> getAllTrains() {

//Create a List

List<Train> trainList = new ArrayList<Train>();

// Create the “Select query”

String selectQuery = “SELECT * FROM ” + TRAINS_TABLE;

//Open the DB in read mode

SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db.rawQuery(selectQuery, null);

// loop through all rows and add trains to the List

if (cursor.moveToFirst()) {

do {

Train train = new Train();




// Adding contact to list


} while (cursor.moveToNext());


//Close DB


// return the train list

return trainList;


To get started the DB has to be created with the following method in the MainActivity class

SqlOpenHelper helper = new SqlOpenHelper(this);

//Open SQLiteDB

db = this.openOrCreateDatabase(“train_no.db”, MODE_PRIVATE, null);

After creating the DB the MainActivity switches to the displayTrains activity which display the current list of of trains in the DB as follows.


SqlOpenHelper helper = new SqlOpenHelper(this);

int count = helper.getTrainCount();

Log.d(“Count:”, “value =” + count);

results = populateResults();

listAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,results);


The list is displayed by creating displayTrains as a ListActivity using the simple_list_item_1

In order to provide a add,delete & deleteAll function I added an Options Menu. To do this create an Option Menu in XML under res/menu


<item android:id=“@id/add”


android:title=“@string/add” />

<item android:id=“@id/delete”


android:title=“@string/delete” />

<item android:id=“@id/deleteAll”




This is then inflated in displayTrains activity in which I wanted the Options Menu to appear as follows

publicboolean onCreateOptionsMenu(Menu menu) {

MenuInflater inflater = getMenuInflater();

inflater.inflate(, menu);



Icons for the Options Menu have to added in the drawable folder.


For some reason icons do not show up in my ADT but when I debugged on my phone it shows.

When the Add Option is clicked it takes it to the addTrains activity which inputs the Train Number & Train Name. The user is then given the option to save.


This returns the activity to display Trains where the entire list is redisplayed.

The delete option was a little tricky. The selected record is deleted as follows


where the selectedItem selects a row in which we have to determine the trainID and delete the record with that ID. This is done as below

publicvoid deleteTrain(Train train) {

SQLiteDatabase db = this.getWritableDatabase();

db.delete(TRAINS_TABLE, TRAIN_ID + ” = ?”,

new String[] { String.valueOf(train.getID()) });



After deleting the record from the DB the list has to be refreshed. I repopulate the listAdapter and pass it to the ListActivity as bwlow. I am not sure if this is the most efficient methid.

r = populateResults();

listAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,r);



Finally the deleteAll deletes all the records in the DB as shown below

publicvoid deleteAllTrains() {

SQLiteDatabase db = this.getWritableDatabase();

db.delete(TRAINS_TABLE, null, null);



This was some fun. The entire code can be downloaded at

Find me on Google+

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s