Building a Mobile Employee Directory – Step 3: Save (Persist) the data into a SQLite Database & Load a ListView from a SQLite Database

For the source code relating to this post, checkout this Github repository.

Continuing with the mobile employee directory example, I’ve added persistent storage with an SQLite database.

MainActivity.java

package com.himebaugh.employeedirectory;

import java.util.List;

import android.app.ListActivity;
import android.database.Cursor;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.v4.widget.SimpleCursorAdapter;
import android.view.Menu;

//	GOAL: Build a native android Mobile Employee Directory 

//	** The result is similar to the sample with Flex and Flash Builder 
//	see http://www.adobe.com/devnet/flex/articles/employee-directory-android-flex.html

//	PURPOSE: Learning how to build an Android App.

//	Step 3: Save (Persist) the data into a SQLite Database & Load a ListView from a SQLite Database
//			1) Modify LoadEmployeesTask to load the database. 
//			2) The database is created when called for the first time. This will also call the EmployeeXmlParser from within.
//			3) A Cursor is returned that exposes results from a query on a SQLiteDatabase.
//			4) The SimpleCursorAdapter displays the data from the Cursor.

public class MainActivity extends ListActivity {

	public List<Employee> employees = null;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);

        // Load xml data in a non-ui thread
        new LoadEmployeesTask().execute();
	}

	private class LoadEmployeesTask extends AsyncTask<String, Void, Cursor> {

		@Override
		protected Cursor doInBackground(String... args) {

			// query the database and return a cursor of employees.
			EmployeeDatabase employeeDatabase = new EmployeeDatabase(getApplicationContext());

			Cursor cursor = employeeDatabase.getAllEmployeesCursor();

			return cursor;
		}

		@Override
		protected void onPostExecute(Cursor cursor) {

			String[] dataColumns = { EmployeeDatabase.COLUMN_FIRSTNAME, EmployeeDatabase.COLUMN_TITLE, EmployeeDatabase.COLUMN_DEPARTMENT };
			int[] viewIDs = { R.id.list_item_name, R.id.list_item_title, R.id.list_item_department };

			SimpleCursorAdapter records = new SimpleCursorAdapter(getBaseContext(), R.layout.list_item, cursor, dataColumns, viewIDs, 0);

			setListAdapter(records);

		}

	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.main, menu);
		return true;
	}

}

EmployeeDatabase.java

package com.himebaugh.employeedirectory;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class EmployeeDatabase extends SQLiteOpenHelper {

	private Context context;
	ArrayList<Employee> employeeList = new ArrayList<Employee>();

	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "employee_directory";
	public static final String TABLE_EMPLOYEES = "employees";
	public static final String COLUMN_ID = "_id";
	public static final String COLUMN_FIRSTNAME = "first_name";
	public static final String COLUMN_LASTNAME = "last_name";
	public static final String COLUMN_TITLE = "title";
	public static final String COLUMN_DEPARTMENT = "department";
	public static final String COLUMN_CITY = "city";
	public static final String COLUMN_OFFICE_PHONE = "office_phone";
	public static final String COLUMN_MOBILE_PHONE = "mobile_phone";
	public static final String COLUMN_EMAIL = "email";
	public static final String COLUMN_PICTURE = "picture";

	private static final String CREATE_TABLE_EMPLOYEES = "CREATE TABLE " + TABLE_EMPLOYEES + " (" 
		+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
		+ COLUMN_FIRSTNAME + " TEXT NOT NULL, "
		+ COLUMN_LASTNAME + " TEXT NOT NULL, " 
		+ COLUMN_TITLE + " TEXT NOT NULL, " 
		+ COLUMN_DEPARTMENT + " TEXT NOT NULL, " 
		+ COLUMN_CITY + " TEXT NOT NULL, " 
		+ COLUMN_OFFICE_PHONE + " TEXT NOT NULL, " 
		+ COLUMN_MOBILE_PHONE + " TEXT NOT NULL, " 
		+ COLUMN_EMAIL + " TEXT NOT NULL, " 
		+ COLUMN_PICTURE + " TEXT NOT NULL);";

	public EmployeeDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;  
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// Called when the database is created for the first time. 
		// This is where the creation of tables and the initial population of the tables happens.
		db.execSQL(CREATE_TABLE_EMPLOYEES);
		seedData(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_EMPLOYEES);
		onCreate(db);
	}

	public Cursor getAllEmployeesCursor() {

		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_EMPLOYEES, new String[] { COLUMN_ID, COLUMN_FIRSTNAME, COLUMN_LASTNAME, COLUMN_TITLE, COLUMN_DEPARTMENT, COLUMN_CITY, COLUMN_OFFICE_PHONE, COLUMN_MOBILE_PHONE, COLUMN_EMAIL, COLUMN_PICTURE }, null, null, null, null, null );

		if (cursor != null) {
			cursor.moveToFirst();
		}

		// cursor.close();
		db.close();

		return cursor;
	}

	private void seedData(SQLiteDatabase db) {

		// CALL XMLPULLPARSER & RETURN AN ArrayList OF EMPLOYEES
		EmployeeXmlParser parser = new EmployeeXmlParser();
		employeeList = parser.parse(context);

		// INSERT EACH EMPLOYEE TO THE DATABASE
		for (Employee employee : employeeList) {
			db.execSQL("INSERT INTO employees (" 
					+ COLUMN_FIRSTNAME + ", " 
					+ COLUMN_LASTNAME + ", " 
					+ COLUMN_TITLE + ", " 
					+ COLUMN_DEPARTMENT + ", " 
					+ COLUMN_CITY + ", " 
					+ COLUMN_OFFICE_PHONE + ", "
					+ COLUMN_MOBILE_PHONE + ", " 
					+ COLUMN_EMAIL + ", " 
					+ COLUMN_PICTURE + ")" 
					+ " values (\"" 
					+ String.valueOf(employee._firstName) 
					+ "\", \"" + String.valueOf(employee._lastName)
					+ "\", \"" + String.valueOf(employee._title) + "\", \"" + String.valueOf(employee._department) + "\", \"" + String.valueOf(employee._city) + "\", \""
					+ String.valueOf(employee._officePhone) + "\", \"" + String.valueOf(employee._mobilePhone) + "\", \"" + String.valueOf(employee._email) + "\", \""
					+ String.valueOf(employee._picture) + "\");");
		}

	}

}

EmployeeXmlParser.java

package com.himebaugh.employeedirectory;

import java.io.IOException;
import java.util.ArrayList;

import org.xmlpull.v1.XmlPullParser;
import org.xmlpull.v1.XmlPullParserException;

import android.content.Context;

public class EmployeeXmlParser {

	// names of the XML tags
	static final String EMPLOYEES = "employees";
	static final String EMPLOYEE = "employee";
	static final String ID = "id";
	static final String FIRSTNAME = "firstName";
	static final String LASTNAME = "lastName";
	static final String TITLE = "title";
	static final String DEPARTMENT = "department";
	static final String CITY = "city";
	static final String OFFICEPHONE = "officePhone";
	static final String MOBILEPHONE = "mobilePhone";
	static final String EMAIL = "email";
	static final String PICTURE = "picture";

	ArrayList<Employee> employeeList = null;
	private Employee currentEmployee = null;
	private boolean done = false;
	private String currentTag = null;

	public ArrayList<Employee> parse(Context context) {

		XmlPullParser parser = context.getResources().getXml(R.xml.employee_list);

		try {

			int eventType = parser.getEventType();

			// Following logic modified from http://www.ibm.com/developerworks/library/x-android/
			// Also look at http://developer.android.com/training/basics/network-ops/xml.html

			while (eventType != XmlPullParser.END_DOCUMENT && !done) {

				switch (eventType) {
				case XmlPullParser.START_DOCUMENT:
					employeeList = new ArrayList<Employee>();
					break;
				case XmlPullParser.START_TAG:
					currentTag = parser.getName();
					if (currentTag.equalsIgnoreCase(EMPLOYEE)) {
						currentEmployee = new Employee();
					} else if (currentEmployee != null) {
						if (currentTag.equalsIgnoreCase(ID)) {
							currentEmployee.setId(Integer.parseInt(parser.nextText())); 	
							// currentEmployee.setId(parser.nextText());
						} else if (currentTag.equalsIgnoreCase(FIRSTNAME)) {
							currentEmployee.setFirstName(parser.nextText());
						} else if (currentTag.equalsIgnoreCase(LASTNAME)) {
							currentEmployee.setLastName(parser.nextText());
						} else if (currentTag.equalsIgnoreCase(TITLE)) {
							currentEmployee.setTitle(parser.nextText());
						} else if (currentTag.equalsIgnoreCase(DEPARTMENT)) {
							currentEmployee.setDepartment(parser.nextText());
						} else if (currentTag.equalsIgnoreCase(CITY)) {
							currentEmployee.setCity(parser.nextText());
						} else if (currentTag.equalsIgnoreCase(OFFICEPHONE)) {
							currentEmployee.setOfficePhone(parser.nextText());
						} else if (currentTag.equalsIgnoreCase(MOBILEPHONE)) {
							currentEmployee.setMobilePhone(parser.nextText());
						} else if (currentTag.equalsIgnoreCase(EMAIL)) {
							currentEmployee.setEmail(parser.nextText());
						} else if (currentTag.equalsIgnoreCase(PICTURE)) {
							currentEmployee.setPicture(parser.nextText());
						}
					}
					break;
				case XmlPullParser.END_TAG:
					currentTag = parser.getName();
					if (currentTag.equalsIgnoreCase(EMPLOYEE) && currentEmployee != null) {
						employeeList.add(currentEmployee);
					} else if (currentTag.equalsIgnoreCase(EMPLOYEES)) {
						done = true;
					}
					break;
				}
				eventType = parser.next();
			}

		} catch (XmlPullParserException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return employeeList;

	}

}

Employee.java

package com.himebaugh.employeedirectory;

public class Employee {
	Integer _empID;
	String _firstName;
	String _lastName;
	String _title;
	String _department;
	String _city;
	String _officePhone;
	String _mobilePhone;
	String _email;
	String _picture;

	// constructor
	public Employee() {

	}

	// constructor with parameters
	public Employee(Integer empID, String firstName, String lastName, String title, String department, String city, String officePhone, String mobilePhone, String email, String picture) {
		this._empID = empID;
		this._firstName = firstName;
		this._lastName = lastName;
		this._title = title;
		this._department = department;
		this._city = city;
		this._officePhone = officePhone;
		this._mobilePhone = mobilePhone;
		this._email = email;
		this._picture = picture;
	}

	// All set methods

	public void setId(Integer empID) {
		this._empID = empID;
	}

	public void setFirstName(String firstName) {
		this._firstName = firstName;
	}

	public void setLastName(String lastName) {
		this._lastName = lastName;
	}

	public void setTitle(String title) {
		this._title = title;
	}

	public void setDepartment(String department) {
		this._department = department;
	}

	public void setCity(String city) {
		this._city = city;
	}

	public void setOfficePhone(String officePhone) {
		this._officePhone = officePhone;
	}

	public void setMobilePhone(String mobilePhone) {
		this._mobilePhone = mobilePhone;
	}

	public void setEmail(String email) {
		this._email = email;
	}

	public void setPicture(String picture) {
		this._picture = picture;
	}

	// All get methods

	public Integer getId() {
		return this._empID;
	}

	public String getFirstName() {
		return this._firstName;
	}

	public String getLastName() {
		return this._lastName;
	}

	public String getTitle() {
		return this._title;
	}

	public String getDepartment() {
		return this._department;
	}

	public String getCity() {
		return this._city;
	}

	public String getOfficePhone() {
		return this._officePhone;
	}

	public String getMobilePhone() {
		return this._mobilePhone;
	}

	public String getPicture() {
		return this._picture;
	}

	//
	@Override
	public String toString() {
		return _firstName + " " + _lastName + "\n" + _title;
	}

}

activity_main.xml (in res/layout)

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".MainActivity" >

    <ListView
        android:id="@android:id/list"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_alignParentLeft="true"
        android:layout_alignParentTop="true" >
    </ListView>

</RelativeLayout>

list_item.xml (in res/layout)

<?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" >

    <!-- android:maxLines="3" -->
    <!-- android:visibility="gone" -->

    <TextView
        android:id="@+id/list_item_emp_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:visibility="gone" />

    <TextView
        android:id="@+id/list_item_name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:textSize="16sp" />

    <TextView
        android:id="@+id/list_item_title"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:paddingRight="10dp" />

    <TextView
        android:id="@+id/list_item_department"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:gravity="right"
        android:paddingRight="10dp" />

    <TextView
        android:id="@+id/list_item_city"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:visibility="gone" />

    <TextView
        android:id="@+id/list_item_office_phone"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:visibility="gone" />

    <TextView
        android:id="@+id/list_item_mobile_phone"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:visibility="gone" />

    <TextView
        android:id="@+id/list_item_email"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:visibility="gone" />

    <TextView
        android:id="@+id/list_item_picture"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:visibility="gone" />

</LinearLayout>

employee_list.xml (in res/xml)

<?xml version="1.0" encoding="utf-8"?>
<employees>
    <employee>
        <id>1</id>
        <firstName>John</firstName>
        <lastName>Doe</lastName>
        <title>CEO</title>
        <city>San Francisco, CA</city>
        <managerId>0</managerId>
        <department>Corporate</department>
        <officePhone>123-456-0001</officePhone>
        <mobilePhone>987-654-1234</mobilePhone>
        <email>John@mail.com</email>
        <picture>placeholder.jpg</picture>
    </employee>
    <employee>
        <id>2</id>
        <firstName>Barack</firstName>
        <lastName>Obama</lastName>
        <title>President</title>
        <city>Washington DC</city>
        <department>Corporate</department>
        <managerId>0</managerId>
        <officePhone>123-456-0002</officePhone>
        <mobilePhone>781-000-0002</mobilePhone>
        <email>Barack@mail.com</email>
        <picture>barack_obama.jpg</picture>
    </employee>
    <employee>
        <id>3</id>
        <firstName>Joe</firstName>
        <lastName>Biden</lastName>
        <title>VP</title>
        <city>Washington DC</city>
        <managerId>2</managerId>
        <department>Corporate</department>
        <officePhone>123-456-0003</officePhone>
        <mobilePhone>987-654-1234</mobilePhone>
        <email>Joe@mail.com</email>
        <picture>joe_biden.jpg</picture>
    </employee>
    <employee>
        <id>4</id>
        <firstName>Hillary</firstName>
        <lastName>Clinton</lastName>
        <title>Secretary of State</title>
        <city>Washington DC</city>
        <managerId>2</managerId>
        <department>Corporate</department>
        <officePhone>123-456-0004</officePhone>
        <mobilePhone>987-654-1234</mobilePhone>
        <email>Hillary@mail.com</email>
        <picture>hillary_clinton.jpg</picture>
    </employee>
</employees>

Screenshot of the application

 

Tags: Employee Directory Example, Persistent storage

Category: Android Application Development