Android SQLite Example

Project Description

This Android SQLite tutorial explains the following,

• How to create a new database with a table (Employee table) in your application.
• Write SQLite Data Access Object (DAO) class for handling database create, read, update and delete (CRUD) operations on the table.
• Add an Action bar menu item icon to insert employee details into database.
• Retrieve employee details from database and display it in ListView.
• Custom dialog fragment to update employee details.
• Communication between dialog fragment and list fragment ( via main activity using an interface) when employee details are updated.
• Delete employee details on list item long click event.
• Proper back navigation of fragments on back key press.
• How to handle fragment orientation changes and retain the state of the fragment.

Employee Table

This Android SQLite example uses an employee table with the following structure.

Android Project

Create a new Android project and name it as AndroidSQLite .

Download “Android SQLite Example” AndroidSQLite.zip – Downloaded 9398 times – 1 MB

Resources

colors.xml

Create a new file res/values/colors.xml and copy paste the following content.

strings.xml

Open res/values/strings.xml and edit to have the content as shown below.

Action Bar Menu Item (main.xml)

Open res/menu/main.xml and edit to have the content as shown below. This file defines the menu item in action bar for adding an employee. When this menu item is clicked, it displays EmpAddFragment which is used to add an employee record to SQLite database.

Layout files

activity_main.xml

This is the main layout file. Here we define a Framelayout which is used to hold all the fragments.
Open res/layout/activity_main.xml and edit to have the content as shown below.

fragment_add_emp.xml

This layout file is used by EmpAddFragment to add employee details (name, dob, salary).

fragment_emp_list.xml

This layout file defines a ListView which is used by EmpListFragment to display list of employees.

list_item.xml

This file defines custom layout for ListView item (employee row) which is used by EmpListAdapter .

Create a SQLite database in Android

DataBaseHelper class

In src folder, create a new class DataBaseHelper in the package com.androidopentutorials.sqlite.db . This class extends SQLiteOpenHelper which manages database creation and version management.

Employee DB DAO class

Create a new class EmployeeDBDAO in the package com.androidopentutorials.sqlite.db . To write to and read from the database we need a SQLiteDatabase object (that represents the database) which can be obtained by calling getWritableDatabase() on the database helper.

Then we will create an EmployeeDAO class which will extend this class and using SQLiteDatabase object provides methods for SQLite CRUD (Create, Read, Update, Delete) operations.

Insert a new record in SQLite database

Employee

In src folder, create a new class Employee in the package com.androidopentutorials.sqlite.to .

• This class represents an Employee stored in a database.
• This class implements Parcelable interface because we need to bundle the employee object as argument when showing custom dialog fragment to update employee details.

Save Employee

In src folder, create a new class EmployeeDAO in the package com.androidopentutorials.sqlite.db . This class extends EmployeeDBDAO and implements database operations such as retrieve, save, update, and delete employee object.

Here we are formatting ‘date of birth’ as ‘yyyy-MM-dd’ by using SimpleDateFormatter to be compatible with SQLite date type. To insert a new row in SQLite, we use ContentValues which define key-value pairs where key represents table column name and the value represents the data for the column for a particular record.

Add Employee Fragment

In src folder, create a new class EmpAddFragment in the package com.androidopentutorials.sqlite.fragment .

• This fragment displays a form to enter employee details such as name, date of birth and salary. DatePickerDialog opens when date of birth EditText is clicked.
• We execute a background task (AsyncTask) to save the employee record by calling save() method of EmployeeDAO class.

Fetch records from SQLite database

EmployeeDAO class

In the above mentioned EmployeeDAO class, add the below getEmployees() method to get all records from database. This class uses query method which accepts the following parameters and returns a Cursor over the result set,

public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)

Employee List Adapter

In src folder, create a new class EmpListAdapter in the package com.androidopentutorials.sqlite.adapter . This is the custom list adapter class which displays employee details – id, name, date of birth and salary.

The add() and remove() method adds and removes item from ListView and notifies the Listview adapter.

Display Database Records in ListView – Employee List Fragment

In src folder, create a new class EmpListFragment in the package com.androidopentutorials.sqlite.fragment .

• This fragment class executes a background task (AsyncTask) where we call getEmployees() method in EmployeeDAO and display list of employees in a ListView.
• When a list item is clicked we display a custom dialog fragment to update employee details.
• On list item long click event we delete a particular employee record from the database and remove it from the list adapter.

Update a row in SQLite database

EmployeeDAO class

In the above mentioned EmployeeDAO class, add the following method to update a particular record from database.