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.
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”>
<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.
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
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
”
Giga thoughts ... 