Night Dreaming (by Sudar)

This week in the Android course, Tony taught about the storing and retrieving information from database. Android has a bundled SQLite database and your app can store and retrieve information by creating a new database. The database that is created by an application is available only to that application and no other application can access it.

SQLiteOpenHelper

Android SDK provides a class called SQLiteOpenHelper which can be used for interfacing with this SQLite database that is associated with your application.

SQLiteOpenHelper has two methods which can be used for creating/updating the database. They are the following.

onCreate

The onCreate() method gets called when the app gets installed for the first time. The SQL code to create the database should go in this method. In addition to the SQL code we should also specify a version number for the database which will be used subsequently during upgrades.

onUpgrade

The onUpgrade() method gets called when the app is upgraded or if the version number specified in the app is greater than the one which is present in the database. Typically this function contains Alter table SQL code which will be used to upgrade the database.

In addition to the above two methods, the SQLiteOpenHelper also has other methods which can be used to access the database. One such method is getWritableDatabase()

getWritableDatabase

The getWritableDatabase() method will return a SQLiteDatabase object which has reference to the database. You can read more about this method from android documentation.

In addition to these methods, the SQLiteOpenHelper class other methods but the above there are the notable ones. You can read more about the SQLiteOpenHelper class from the android documentation.

Selecting data from the database

To selected data from the database, we have to call the query() method on the SQLiteDatabase object which is returned by the getWritableDatabase() method above.

The query() method returns an object of type Cursor, which can be iterated over to retrieve the resultset. The following code snippet explains the query() method.

private void loadTasks() {
	currentTasks = new ArrayList<Task>();
	Cursor tasksCursor = database.query(TASKS_TABLE,
			new String[] {TASK_ID, TASK_NAME, TASK_COMPLETE},
			null, null, null, null, String.format("%s,%s", TASK_COMPLETE, TASK_NAME));

	tasksCursor.moveToFirst();
	Task t;
	if (! tasksCursor.isAfterLast()) {
		do {
			int id = tasksCursor.getInt(0);
			String name = tasksCursor.getString(1);
			String boolValue = tasksCursor.getString(2);
			boolean complete = Boolean.parseBoolean(boolValue);
			t = new Task(name);
			t.setId(id);
			t.setComplete(complete);
			currentTasks.add(t);
		} while(tasksCursor.moveToNext());
	}
}

Inserting data into the database

In order to insert the data into the database we have to call the insert() method of the SQLiteDatabase. The data that needs to be inserted should be added to a ContextValues object and then passed to the insert() method. The ContextValues object is like a HashMap which contains the key and the value for each column of the row that will be inserted.

The insert() method returns the id of the row that was inserted. The following code snippet explains the insert() method.

public void addTask(Task t) {
	ContentValues values = new ContentValues();
	values.put(TASK_NAME, t.getName());
	values.put(TASK_COMPLETE, Boolean.toString(t.isComplete()));

	t.setId(database.insert(TASKS_TABLE, null, values));
	currentTasks.add(t);
}

Updating data in the database

To update data in the database we have to call the update() method of the SQLiteDatabase object. Like the insert() method, the data that needs to be updated should be passed in a ContextValues object.

The following code snippet explains the update() method.

public void saveTask(Task t) {

	ContentValues values = new ContentValues();
	values.put(TASK_NAME, t.getName());
	values.put(TASK_COMPLETE, Boolean.toString(t.isComplete()));

	long id = t.getId();
	String where = String.format("%s = %d", TASK_ID, id);
	database.update(TASKS_TABLE, values, where, null);
}

Deleting data from the database

To delete data from the database we have to call (guess what 😉 ) the delete() method of the SQLiteDatabase objet. The delete() method takes the where condition based on which the rows will be deleted.

public void deleteTasks(Long[] ids) {
	StringBuffer idList = new StringBuffer();
	for (int i =0; i< ids.length; i++) {
		idList.append(ids[i]);
		if (i < ids.length -1 ) {
			idList.append(",");
		}
	}

	String where = String.format("%s in (%s)", TASK_ID, idList);
	database.delete(TASKS_TABLE, where, null);
}

You can read more about this method from android documentation.

Demo App Sourcecode

The demo TaskManager app that we have been using in the previous classes was modified to store the tasks in the database. You can find the source code from my github page. I am also working in this week’s homework and will be posting the explanation and source code once I am done.

You can also subscribe to my blog’s RSS feed or follow me in Twitter to receive updates about my notes for the next sessions.