ContentProvider (part 3) – how to make it work with SQLite DB and CursorLoader

cursorloader contentprovider sqlite

So, it’s the final part of ContentProvider tutorial where I’ll explain how to display data from SQLite in a ListView using CursorLoader.

Part 1
Part 2

In the end, we should get this app:

ContentProvider CursorLoader

Let’s start with XML layout file for our Activity (/res/layout folder), which will show a ListView with the data filled in using CursorLoader.

activity_main.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <ListView
        android:id="@+id/lvItems"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1" >
    </ListView>

</LinearLayout> 

Now we need to create the Activity:

public class MainActivity extends ActionBarActivity {
	
	private ListView lvItems;
	//...
	
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		if(savedInstanceState != null) {
			return;
		}
		lvItems = (ListView)findViewById(R.id.lvItems);
		//...
	}

	//...
	
}

We will also need an adapter to link student data from SQLite to our ListView. Let’s create DataAdapter class extended from CursorAdapter (I used support version from android.support.v4 package):

public class DataAdapter extends CursorAdapter {

	private LayoutInflater mInflater; //нужен для создания объектов класса View

	public DataAdapter(Context context, Cursor c, int flags) {
		super(context, c, flags);
		mInflater = (LayoutInflater)context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
	}
	
	//...
	
}

Here we need to override two methods: newView() and bindView(). newView() return an object of View class which will be filled with data in bindView() method.

@Override
public View newView(Context ctx, Cursor cur, ViewGroup parent) {
	View root = mInflater.inflate(android.R.layout.simple_list_item_1, parent, false);
	ViewHolder holder = new ViewHolder();
	TextView tvClassName = (TextView)root.findViewById(android.R.id.text1);
	holder.tvClassName = tvClassName;
	root.setTag(holder);
	return root;
} 

Let’s look closely to newView() method. Here we set simple_list_item_1.xml as a layout for each list item. It’s stored in Android SDK files and we don’a have it in our project locally. It’s a usual TextView with id = ‘@+id/text1’.
Also, we use ViewHolder template in this adapter. It helps out ListView scroll smoothly because it reuses Views for each list element. bindView() method is called for each list item, and we save a pointer to TextView in a ViewHolder object so that there’s no need in finding TextView each time. Then we pass this ViewHolder object using setTag() method.

Her is ViewHolder class:

public static class ViewHolder {
	public TextView tvClassName;
	public long classID;
}

bindView() method:

@Override
public void bindView(View view, Context ctx, Cursor cur) {
	long id = cur.getLong(cur.getColumnIndex(ContractClass.Classes._ID));
	String classNumber = cur.getString(cur.getColumnIndex(ContractClass.Classes.COLUMN_NAME_CLASS_NUMBER));
	String classLetter = cur.getString(cur.getColumnIndex(ContractClass.Classes.COLUMN_NAME_CLASS_LETTER));
	ViewHolder holder = (ViewHolder) view.getTag();
	if(holder != null) {
		holder.tvClassName.setText(classNumber+"\""+classLetter+"\"");
		holder.classID = id;
	}
}

Here, as an argument, we have Cursor with the data from our sql request. This cursor is already set to the correct position, so for 0th item it was set to position 0, for 1st – position 1, etc.

In this line:

String classNumber = cur.getString(cur.getColumnIndex(ContractClass.Classes.COLUMN_NAME_CLASS_NUMBER));

we read value from the column named ‘class_number’. Method getString() takes column index from which we should read the value. You can find column index by it’s name using method getColumnIndex().

Next, we can read a ‘tag’ linked in newView():

ViewHolder holder = (ViewHolder) view.getTag();

And set the text into the TextView:

holder.tvClassName.setText(classNumber+"\""+classLetter+"\"");

We also save id of the SQLite row, so that it was possible to handle clicks on list items and display student information for each class:

holder.classID = id;

Adapter is ready, let’s link it with the ListView. Define a field in MainActivity class:

private DataAdapter mAdapter;

and initialize it in onCreate():

mAdapter = new DataAdapter(this, null, 0);

Take note that we pass ‘null’ into DataAdapter’s constructor as a Cursor argument. If we didn’t plan to use CursorLoader to fill our adapter, then we would need to get this Cursor object using, for example, getContentResolver().query(…), and then manually track changes int SQLite DB and update data in the adapter.

Now we link ListView and DataAdapter:

lvItems.setAdapter(mAdapter);

And now we can use CursorLoader. To do this, MainActivity class should implement LoaderCallbacks interface, and override 3 methods – onCreateLoader(), onLoaderReser() and onLoaderFinished():

public class MainActivity extends ActionBarActivity implements LoaderCallbacks {

	@Override
	public Loader onCreateLoader(int loaderID, Bundle arg1) {
		//in this method we create CursorLoader with particular sql request. In our case we need to select all rows from Classes table, and we can set selection query to 'null'.
		return new CursorLoader(
				this, 
				ContractClass.Classes.CONTENT_URI, //uri for Classes table
				ContractClass.Classes.DEFAULT_PROJECTION, //list of columns that should be presented in the selection
				null, // WHERE clause
				null, // WHERE clause's arguments
				null); // sort order
	}

	@Override
	public void onLoadFinished(Loader loader, Cursor newData) {
		//this method is called after receiving data from DB. Adapter
		//receives new data as a Cursor (from particular CursorLoader) 
                //and notification that data was updated 
		//and it needs to refresh the list.
		mAdapter.swapCursor(newData);
	}

	@Override
	public void onLoaderReset(Loader loader) {
		//this method is called if the CursorLoader was reset
		//and we should tell the adapter to clean the list
		mAdapter.swapCursor(null);
	}

}

Now we need to create CursorLoader in onCreate() method:

getSupportLoaderManager().initLoader(0, null, this);

and our ListView will be filled with the data.

Let’s add list item click handler to show students for each group. To do this, our MainActivity class implements OnItemClickListener interface, and overrides onItemClick() method as follows:

public void onItemClick(AdapterView parent, View v, int position, long id) {

	/**
	* When list item is clicked, there will be a dialog window
	* with list of student for the class. To do this let's create ArrayAdapter
	* and fill it with students. getStudents() method is described below.
	*/
	final ArrayAdapter arrayAdapter = new ArrayAdapter(
			MainActivity.this,
			android.R.layout.simple_list_item_1);
	// Get a ViewHolder, linked as a 'tag' earlier in newView() method of DataAdapter class
	ViewHolder holder = (ViewHolder) v.getTag();
	if(holder != null) {
		//Receive student's list with classID = holder.classID
		String[] students = getStudents(holder.classID);
		//Fill ArrayAdapter with the data
		if(students != null && students.length > 0) {
			arrayAdapter.addAll(students);
		}
		else {
			arrayAdapter.add("No students");
		}
	} else {
		return;
	}
	// And show dialog window with the list of students
	AlertDialog.Builder builderSingle = new AlertDialog.Builder(
			MainActivity.this);
	builderSingle.setTitle("Students");
	builderSingle.setNegativeButton("Cancel",
			new DialogInterface.OnClickListener() {

				@Override
				public void onClick(DialogInterface dialog, int which) {
					dialog.dismiss();
				}
			});

	builderSingle.setAdapter(arrayAdapter,
			new DialogInterface.OnClickListener() {

				@Override
				public void onClick(DialogInterface dialog, int which) {}
			});
	builderSingle.show();
}

getStudents() looks as follows:

private String[] getStudents(long classID) {
	String[] students = null;
	Cursor c = getContentResolver().query(
			ContractClass.Students.CONTENT_URI, 
			ContractClass.Students.DEFAULT_PROJECTION, 
			ContractClass.Students.COLUMN_NAME_FK_CLASS_ID+"=?", 
			new String[] {""+classID}, 
			null);
	if(c != null) {
		if(c.moveToFirst()) {
			students = new String;
			int i=0;
			do {
				String firstName = c.getString(c.getColumnIndex(ContractClass.Students.COLUMN_NAME_FIRST_NAME));
				String secondName = c.getString(c.getColumnIndex(ContractClass.Students.COLUMN_NAME_SECOND_NAME));
				double score = c.getDouble(c.getColumnIndex(ContractClass.Students.COLUMN_NAME_AVERAGE_SCORE));
				students[i] = firstName+" "+secondName+" | "+score;
				i++;
			} while (c.moveToNext());
		}
		c.close();
	}
	return students;
}

Here we initiate sql request to select all students for a class with particular id:

Cursor c = getContentResolver().query(
	ContractClass.Students.CONTENT_URI, 
	ContractClass.Students.DEFAULT_PROJECTION, 
	ContractClass.Students.COLUMN_NAME_FK_CLASS_ID+"=?", 
	new String[] {""+classID}, 
	null);

And after that, processing each row, we form an array of String objects:

if(c != null) {
	//set Cursor to the first position. It will return false, if Cursor is empty.
	if(c.moveToFirst()) {
		// set array's size
		students = new String;
		int i=0;
		do {
			// Read string from corresponding colemn and add it to 'students' array
			String firstName = c.getString(c.getColumnIndex(ContractClass.Students.COLUMN_NAME_FIRST_NAME));
			String secondName = c.getString(c.getColumnIndex(ContractClass.Students.COLUMN_NAME_SECOND_NAME));
			double score = c.getDouble(c.getColumnIndex(ContractClass.Students.COLUMN_NAME_AVERAGE_SCORE));
			students[i] = firstName+" "+secondName+" | "+score;
			i++;
		} while (c.moveToNext()); // and move Cursor to the next position
	}
	//always close Cursor after use!
	c.close();
}

That’s it, our app is ready. Add this snippet into onCreate() method of DataHelper class (after creating all tables) to fill DB with some initial data.

db.execSQL("insert into classes values (null, '11', 'A');");
db.execSQL("insert into classes values (null, '11', 'B');");
db.execSQL("insert into classes values (null, '11', 'C');");
db.execSQL("insert into classes values (null, '10', 'A');");
db.execSQL("insert into classes values (null, '10', 'B');");
db.execSQL("insert into classes values (null, '10', 'C');");

db.execSQL("insert into students values (null, 'Ivan', 'Petrov', 4.1, 0);");
db.execSQL("insert into students values (null, 'Petr', 'Ivanov', 3.5, 0);");
db.execSQL("insert into students values (null, 'Ivan', 'Sidorov', 4.9, 0);");

db.execSQL("insert into students values (null, 'Ivan', 'Petrov', 4.1, 1);");
db.execSQL("insert into students values (null, 'Petr', 'Ivanov', 3.5, 1);");
db.execSQL("insert into students values (null, 'Ivan', 'Sidorov', 4.9, 1);");

db.execSQL("insert into students values (null, 'Ivan', 'Petrov', 4.1, 2);");
db.execSQL("insert into students values (null, 'Petr', 'Ivanov', 3.5, 2);");
db.execSQL("insert into students values (null, 'Ivan', 'Sidorov', 4.9, 2);");

db.execSQL("insert into students values (null, 'Ivan', 'Petrov', 4.1, 3);");
db.execSQL("insert into students values (null, 'Petr', 'Ivanov', 3.5, 3);");
db.execSQL("insert into students values (null, 'Ivan', 'Sidorov', 4.9, 3);");

db.execSQL("insert into students values (null, 'Ivan', 'Petrov', 4.1, 4);");
db.execSQL("insert into students values (null, 'Petr', 'Ivanov', 3.5, 4);");
db.execSQL("insert into students values (null, 'Ivan', 'Sidorov', 4.9, 4);");

db.execSQL("insert into students values (null, 'Ivan', 'Petrov', 4.1, 5);");
db.execSQL("insert into students values (null, 'Petr', 'Ivanov', 3.5, 4);");
db.execSQL("insert into students values (null, 'Ivan', 'Sidorov', 4.9, 5);");

Full source code you can find here.

Leave a Reply

Your email address will not be published. Required fields are marked *