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) {

createDatabase(db);

}

publicvoid createDatabase(SQLiteDatabase db) {

//Create TRAIN_TABLE

String CREATE_TRAIN_TABLE = “CREATE TABLE IF NOT EXISTS ” +

TRAINS_TABLE + “(” + TRAIN_ID + ” INTEGER PRIMARY KEY,” +

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

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

db.execSQL(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()) });

db.close();

}

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();

train.setID(Integer.parseInt(cursor.getString(cursor.getColumnIndex(TRAIN_ID))));

train.setTrainNo(cursor.getString(cursor.getColumnIndex(TRAIN_NO)));

train.setTrainName(cursor.getString(cursor.getColumnIndex(TRAIN_NAME)));

// Adding contact to list

trainList.add(train);

} while (cursor.moveToNext());

}

//Close DB

db.close();

// 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.

 list

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);

this.setListAdapter(listAdapter);

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

<menuxmlns:android=http://schemas.android.com/apk/res/android&#8221;>

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

android:icon=“@drawable/add”

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

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

android:icon=“@drawable/delete”

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

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

android:title=“@string/deleteAll”

android:icon=“@drawable/deleteall”/>

</menu>

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(R.menu.options_menu, menu);

returntrue;

}

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

options

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.

add

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

helper.deleteTrain(selectedItem);

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()) });

db.close();

}

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);

this.setListAdapter(listAdapter);

listAdapter.notifyDataSetChanged();

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);

db.close();

}

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

Find me on Google+

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s