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

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