How to retrieve data from a SQLite database in Android

Hello Android fellows… let us Retrieve today 😉 I mean lets learn how to retrieve data from a SQLite database in Android. This is the second part of the SQLite blog series. In my previous post I have shown you how to insert data to a SQLite database. I am continuing from there. So please have a look on my previous post before you go through this post directly.

If you guys can remember, you did not able to see anything when we started our undergraduate GPA Android application last time. But after coding to retrieve data form the SQLite database, you will be able to see the existing or the registered undergraduates’ names in our first UI (User Interface) as shown below.

Figure 1

Hey… you can only see names of the undergraduates. What happen to the other details? :O Don’t worry and don’t be hurry, you can see those details when you click on a name of an undergraduate as in Figure 2

Figure 2

Enough fancy stories. Let me explain the logic and the techniques behind this. The basic logic is similar to what we have done when we inserting data. That means to contact the database we need a helper class which extends the SQLiteOpenHelper class. Then we need a readable SQLite database. If you can remember my previous post, there we have used a writable database because we wanted to insert data. But today we are going to retrieve data.

OK, now we have a SQLite database that can be read. How are we going to read it? Cursor class will give you a hand to fulfill your target. By using a while loop, we can traverse each and every raw of the particular table. Lets look at the code now. The comment in the code is more clear than the explanation here, because I have explain almost all the lines with comments.

package com.anuja.sqlite;

import java.util.ArrayList;
import java.util.List;

import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.Toast;

public class UndergraduateListActivity extends Activity implements OnClickListener, OnItemClickListener {

	private ListView uGraduateNamesListView;
	private Button addNewUndergraduateButton;

	// We need some kind of Adapter to made the connection between ListView UI component and SQLite data set.
	private ListAdapter uGraduateListAdapter;

	// We need this while we read the query using Cursor and pass data
	private ArrayList<UndergraduateDetailsPojo> pojoArrayList;

    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.names_listview);

        // Initialize UI components
        uGraduateNamesListView = (ListView) findViewById(R.id.uGraduateListView);
        uGraduateNamesListView.setOnItemClickListener(this);

        addNewUndergraduateButton = (Button) findViewById(R.id.namesListViewAddButton);
        addNewUndergraduateButton.setOnClickListener(this);

        pojoArrayList = new ArrayList<UndergraduateDetailsPojo>();

        // For the third argument, we need a List that contains Strings.
        //We decided to display undergraduates names on the ListView.
        //Therefore we need to create List that contains undergraduates names
        uGraduateListAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, populateList());

        uGraduateNamesListView.setAdapter(uGraduateListAdapter);

    }

	@Override
	public void onClick(View v) {
		Intent addNewUndergraduateIntent = new Intent(this, AddNewUndergraduateActivity.class);
		startActivity(addNewUndergraduateIntent);
	}

	// To create a List that contains undergraduate names, we have to read the SQLite database
	//We are going to do it in the separate method
	public List<String> populateList(){

		// We have to return a List which contains only String values. Lets create a List first
		List<String> uGraduateNamesList = new ArrayList<String>();

		// First we need to make contact with the database we have created using the DbHelper class
		AndroidOpenDbHelper openHelperClass = new AndroidOpenDbHelper(this);

		// Then we need to get a readable database
		SQLiteDatabase sqliteDatabase = openHelperClass.getReadableDatabase();

		// We need a a guy to read the database query. Cursor interface will do it for us
		//(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
		Cursor cursor = sqliteDatabase.query(AndroidOpenDbHelper.TABLE_NAME_GPA, null, null, null, null, null, null);
		// Above given query, read all the columns and fields of the table

		startManagingCursor(cursor);

		// Cursor object read all the fields. So we make sure to check it will not miss any by looping through a while loop
		while (cursor.moveToNext()) {
			// In one loop, cursor read one undergraduate all details
			// Assume, we also need to see all the details of each and every undergraduate
			// What we have to do is in each loop, read all the values, pass them to the POJO class
			//and create a ArrayList of undergraduates

			String ugName = cursor.getString(cursor.getColumnIndex(AndroidOpenDbHelper.COLUMN_NAME_UNDERGRADUATE_NAME));
			String ugUniId = cursor.getString(cursor.getColumnIndex(AndroidOpenDbHelper.COLUMN_NAME_UNDERGRADUATE_UNI_ID));
			double ugGpa = cursor.getDouble(cursor.getColumnIndex(AndroidOpenDbHelper.COLLUMN_NAME_UNDERGRADUATE_GPA));

			// Finish reading one raw, now we have to pass them to the POJO
			UndergraduateDetailsPojo ugPojoClass = new UndergraduateDetailsPojo();
			ugPojoClass.setuGraduateName(ugName);
			ugPojoClass.setuGraduateUniId(ugUniId);
			ugPojoClass.setuGraduateGpa(ugGpa);

			// Lets pass that POJO to our ArrayList which contains undergraduates as type
			pojoArrayList.add(ugPojoClass);

			// But we need a List of String to display in the ListView also.
			//That is why we create "uGraduateNamesList"
			uGraduateNamesList.add(ugName);
		}

		// If you don't close the database, you will get an error
		sqliteDatabase.close();

		return uGraduateNamesList;
	}

	// If you don't write the following code, you wont be able to see what you have just insert to the database
	@Override
	protected void onResume() {
		super.onResume();
        uGraduateListAdapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, populateList());
        uGraduateNamesListView.setAdapter(uGraduateListAdapter);
	}

	// On ListView you just see the name of the undergraduate, not any other details
	// Here we provide the solution to that. When the user click on a list item, he will redirect to a page where
	//he can see all the details of the undergraduate
	@Override
	public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {

		Toast.makeText(getApplicationContext(), "Clicked on :" + arg2, Toast.LENGTH_SHORT).show();

		// We want to redirect to another Activity when the user click an item on the ListView
		Intent updateDeleteUgraduateIntent = new Intent(this, UpdateDeleteUndergraduateActivity.class);

		// We have to identify what object, does the user clicked, because we are going to pass only clicked object details to the next activity
		// What we are going to do is, get the ID of the clicked item and get the values from the ArrayList which has
		//same array id.
		UndergraduateDetailsPojo clickedObject =  pojoArrayList.get(arg2);

		// We have to bundle the data, which we want to pass to the other activity from this activity
		Bundle dataBundle = new Bundle();
		dataBundle.putString("clickedUgraduateName", clickedObject.getuGraduateName());
		dataBundle.putString("clickedUgraduateUniId", clickedObject.getuGraduateUniId());
		dataBundle.putDouble("clickedUgraduateGpa", clickedObject.getuGraduateGpa());

		// Attach the bundled data to the intent
		updateDeleteUgraduateIntent.putExtras(dataBundle);

		// Start the Activity
		startActivity(updateDeleteUgraduateIntent);

	}
}

Above is the full code for retrieve. I will explain the scenario from where I have stopped. Now you have the data set which we have read from the SQLite database. You have a ListView that you can visualize the out put to the user. You can combine the UI component and the data set by using an Adapter. Here I have used an ArrayAdapter.

Figure 2 relate with a new Activity. You can see, that I have call a new Activity with in the OnItemClick method. Those things I have covered in my previous blogs. Therefore don’t ask me to repeat it here 😛

I think this is more than enough for today’s blog post. I will upload the source code to GitHub after completing Update and Delete functions as well.

Wish all of you a Merry Christmas…!!! 🙂