Hdelossantos.com

This tutorial will demonstrate how to use the database adapter created in “Creating a SQLite Database in Android” to add and get data to and from the database to populate a ListView.

DatabaseQuery.java :

We must first create a class to handle the formatting for the queries. The database adapter takes ArrayLists of String objetcs to add rows to the database, however you will probably want a method to add one cell at a time, then when the contents of the row are completed, add the entire row to the database. We begin by declaring the variables for the ArrayLists.

import java.util.ArrayList;
import android.content.Context;
import android.database.Cursor;

/**
 * This class adds multiple entries to the database and pulls them back
 * out.
 */
public class DatabaseQuery {
	// Variables area
	private ArrayList<String> arrayKeys = null;
	private ArrayList<String> arrayValues = null;
	private ArrayList<String> databaseKeys = null;
	private ArrayList<String> databaseKeyOptions = null;
	private DBAdapter database;

We then create the constructor which will call the DBAdapter class and create the database.

/**
	 * Initialize the ArrayList
	 * @param context Pass context from calling class.
	 */
	public DatabaseQuery(Context context) {
		// Create an ArrayList of keys and one of the options/parameters
		// for the keys.
		databaseKeys = new ArrayList<String>();
		databaseKeyOptions = new ArrayList<String>();
		databaseKeys.add("Title");
		databaseKeyOptions.add("text not null");

		// Call the database adapter to create the database
		database = new DBAdapter(context, "testTable", databaseKeys, databaseKeyOptions);
        database.open();
		arrayKeys = new ArrayList<String>();
		arrayValues = new ArrayList<String>();

	}

databaseKeys is an ArrayList of all of the column names the database will contain.
databaseKeyOptions is an ArrayList of the options for the column names. This can be a field type such as “text” or “integer”.

In this example I have only added one key here. The DBAdapter class also adds a timeStamp column when a new row is inserted. This makes it easy to sort by time later on. The context is passed from the applications main activity, which we will create at the end. After the DBAdapter object has been created, we then use it to open the pipeline to the database. Now we can query the database.

In the constructor we also initialized two arrays “arrayKeys” and “arrayValues” which will hold all of the data that constitutes one row. This data is appended to the arrays in the appendData method. Once all of the keys and their values have been appended, then the addRow method is called to insert the row to the database.

/**
	 * Append data to an ArrayList to then submit to the database
	 * @param key Key of the value being appended to the Array.
	 * @param value Value to be appended to Array.
	 */
	public void appendData(String key, String value){
		arrayKeys.add(key);
		arrayValues.add(value);
	}

	/**
	 * This method adds the row created by appending data to the database.
	 * The parameters constitute one row of data.
	 */
	public void addRow(){
		database.insertEntry(arrayKeys, arrayValues);
	}

To run a query and get data from the database, we can specify parameters to sort all of the data in the database.

keys is a String[] of the column headers to return in the results
selection is a String to search for in the columns. Only columns with matching string are returned.
selectionArgs is a String[] with arguments for the selection.
groupBy is the String to group results by.
having is a filter to declare which row groups to include in the cursor.
sortBy is a key to sort the results by.
sortOption specifies the way to sort the data.

/**
	 * Get data from the table.
	 */
	public ArrayList<String> getData(String[] keys, String selection, String[]
	  selectionArgs, String groupBy, String having, String sortBy, String sortOption){

		ArrayList<String> list = new ArrayList<String>();
		Cursor results = database.getAllEntries(keys, selection,
				selectionArgs, groupBy, having, sortBy, sortOption);
		while(results.moveToNext())
			list.add(results.getString(results.getColumnIndex(sortBy)));
		return list;

	}

In this example I only wanted the results to include one field, “Title”. I set the parameters for this in my main class. This returns an ArrayList, with only the column title. This is obtained by selecting the string to add to the ArrayList.

list.add(results.getString(results.getColumnIndex(sortBy)));

You can use results.get[String, Integer, etc.] in order to obtain the data in a specific format. You must supply it with a column index number, which can be obtained by results.getColumnIndex(“Title”). In my case sortBy has “Title” since I will be sorting by that key value as well. As it is this code will only return the results of one column of data for many rows. Modifications can be made to return more. One way to achieve this is to create an object and return an array of those objects.

Once we are all done, the pipe to the database must be closed.

/**
	 * Destroy the reporter.
	 * @throws Throwable
	 */
	public void destroy() throws Throwable{
        database.close();
	}
}

This attempts to close the database and returns an exception if it encounters an error.

DBAdapterTest.java :

This is the main class and will extend ListActivity in order to populate a list of the elements obtained from the database.

import java.util.ArrayList;

import android.app.ListActivity;
import android.os.Bundle;
import android.widget.ArrayAdapter;

public class DBAdapterTest extends ListActivity {
	private ArrayList<String> queryString;
	private DatabaseQuery query;
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        query = new DatabaseQuery(this);

        // Populate the database
        query.appendData("Title", "Row One");
        query.addRow();
        query.appendData("Title", "Row Two");
        query.addRow();

        // Pull the data from the database
        queryString = query.getData(new String[] {"Title"}, null, null, null, null, "Title", " ASC");
        try {
			query.destroy();
		} catch (Throwable e) {
			e.printStackTrace();
		}

        // Set the ListView
        setListAdapter(new ArrayAdapter<String>(this,
                android.R.layout.simple_list_item_1, queryString));
        getListView().setTextFilterEnabled(true);

    }
}

A new instance of DatabaseQuery is instantiated and data is added to the database. Once the data has been added, a query is run to pull the data out of the database and populate a ListView. This application adds the same two entries every time it is run. This is simply an quick example of how to use the DBAdapter and changes should be made depending on the needs of your application.

You can download the full Eclipse project folder below.

Tags: android, database, database adapter, howto, sqlite, step-by-step, tutorial, using database adapter, using dbadapter

Related posts

• Creating a SQLite database in Android (8)
• Quick and easy Android HTTP POST of JSON string (2)
• Talking Twitter (5)
• I’ve earned my Android Badge :) (0)