Android SQLite Join Multiple Tables Example

Project Description:

We will take an example of Employee – Department tables to explain join in sqlite. In this Android SQLite tutorial we will do the following,

  • Create a new database with Employee and Department tables.
  • Write a SQLite Data Access Object (DAO) class for handling database create, read, update and delete (CRUD) operations on Employee and Department tables.
  • Add an Action bar menu item icon to insert employee details into database.
  • Retrieve employee details by joining Employee and Department tables and display 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.
  • Handle fragment orientation changes and retain the state of the fragment.
  • Asynchronously populate Spinner data from SQLite database.

Table Structure

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

FieldTypeKeyExtra
idintPrimary Keyauto_increment
nametext
salarydouble
dobdate
dept_idintForeign Key references department(id)

2.android-sqlite-multiple-tables-employee-table-structure

department table

FieldTypeKeyExtra
idintPrimary Keyauto_increment
nametext

1.android-sqlite-department-table-structure

Android Project

Create a new Android project and name it as AndroidSQLiteMultipleTables.

Download “Android SQLite Multiple Tables Example” AndroidSQLiteMultipleTables.zip – Downloaded 6792 times – 2 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 EmpAddFragmentwhich 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, department). It displays departments in Spinner which is asynchronously populated from SQLite database.

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 which is used by EmpListAdapter. It displays employee name, salary, date of birth and department name.

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. This class creates an “employeedb” with two tables “employee” and “department”.

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 EmployeeDAO and DepartmentDAO classes which will extend this class and use SQLiteDatabase object provides methods for SQLite CRUD (Create, Read, Update, Delete) operations.

Insert a new record in SQLite database

Department

In src folder, create a new class Department in the package com.androidopentutorials.sqlite.to. This class represents a single department in the database table.

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.
  • This class has Department object which defines foreign key constraints.

DepartmentDAO class

In src folder, create a new class DepartmentDAO in the package com.androidopentutorials.sqlite.db.
This class extends EmployeeDBDAO and implements database operations such as save, update, delete, get department. loadDepartments() method initially loads records into department table.

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.
  • Department id is stored as foreign key which is retrieved by calling employee.getDepartment().getId().

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, salary and department name as Spinner.A background task is executed which asynchronously populates department Spinner data from SQLite database. DatePickerDialog opens when date of birth EditText is clicked.
  • It gets employee details and executes a background task (AsyncTask) to save the employee record by calling save() method from 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. Here, we need to retrieve department name from department table and it involves joining two tables. We can use one of the following methods to build complex queries which involves multiple tables with column alias,

  1. By calling rawQuery() from SQLiteDatabase class which accepts SQL select statement as input. In the below code, Method 1 uses rawQuery().
  2. Using SQLiteQueryBuilder class which provides convenient methods for building complex queries, such as those that require column aliases, multiple tables. In the below code, Method 2 which is commented uses SQLiteQueryBuilder.

  • setTables() method from SQLiteQueryBuilder is used to set list of tables to query. Multiple tables can be specified to perform a join.

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 id, name, date of birth, salary and department name.

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.

CustomEmpDialogFragment class

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

  • This class receives an employee object in bundle as an argument and displays a dialog with employee details (name, salary, dob and department name) and updates employee details.
  • If the update is successful, it calls onFinishDialog() on MainActivity to notify the ListView (EmpListFragment). All communication between fragments should go via their activity class.

Delete a row from SQLite database

EmployeeDAO class

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

EmpListFragment calls this delete() method on list item long click event.

Complete EmployeeDAO class

MainActivity class

  • This is the main activity class.
  • When the app starts, it retrieves records from department table and if the size is zero, it loads departments by calling loadDepartments(). It begins a new FragmentTransaction and starts EmpListFragment. It displays a Toast message if there are no employee records.
  • This activity handles proper back navigation of fragments on back key pressed by overriding onBackPressed(). It shows an alert dialog on quit.
  • It handles fragment orientation changes and retain the fragment state by overriding onSaveInstanceState().

Output:

Insert a row – EmpAddFragment

1.android-sqlite-multiple-table-insert-row

Update a row – CustomEmpDialogFragment

2.android-sqlite-multiple-table-update-row

Fetch all rows – EmpListFragment

3.android-sqlite-multiple-table-read-rows-display-listview

  • apex

    can we get DAO value from datepicker ?
    if it possible can you show me how to make it. .

  • kumar

    nice

  • JOE

    hi very nice tutorial., but what i feel is why datepicker is not available for editing…??? if it available mean it will be a complete core package for multiple table tutorial..

  • Phil Webster

    Very good tutorial! I’m still struggling with some SQL, but this article helps a lot!

  • Devilsummer

    Hi very helpful tutorial! My question however is this: When we chose the department how can we make it so that the spinner shows only the name of the department and not the ID too?

    • RandomUser

      The easy way would be to change Deepartments toString Override to only return name instead of id:” + id + “, name:” + name;

  • Dragon

    Hi … the tutorial could have been structured better. It is difficult to follow and implement … poor tutorial on the whole

  • Rifqi Fardi Pradhana

    good code bro! nice share

  • Rifqi Fardi Pradhana

    Hello Nithya, I want to ask at entity model on method hashCode the line final int prime = 31; is for what? why should must 31? can you explain it? thanks for sharing again.

  • Pingback: android sqlite editor apk | Full APK Updates for You!

  • Marian Paździoch

    dept WHERE emp.”
    + DataBaseHelper.EMPLOYEE_DEPARTMENT_ID + ” = dept.”
    + DataBaseHelper.ID_COLUMN

    LOL

  • Dušan Dimitrijević

    I know this post is old, but it was and will be always helpful for beginners like me, so if anyone is following this tutorial or writer of this tutorial could help me with one error i’m getting. I have followed everything and basic i have copied the whole code. I have just changed instead double type, String type.

    Caused by: java.lang.NullPointerException: Attempt to invoke virtual method ‘android.database.Cursor android.database.sqlite.SQLiteDatabase.query(java.lang.String, java.lang.String[], java.lang.String, java.lang.String[], java.lang.String, java.lang.String, java.lang.String)’ on a null object reference

    This is the error i’m getting.

  • Purba Chakraborty

    Wonderful Post, really helpful. If possible post details on REST API , webservice.

    • Josué

      Bro do you have the code in android studio?

  • Pingback: Sqlite Update Set Multiple Values | Tips Insurance

  • FARWA BATOOL

    import com.androidopentutorials.sqlite.R;
    Error: Cannot resolve symbol R
    Most of my classes are not running due to this.

    • Dileep Sagar

      rebuilt it may be it helps

  • Shubham Jain

    i want to sort name with Department. how it is possible? please help

  • Wael Hassan

    Sorry what about if I have multi tables with different kind of join like many to many ???
    how we can distinct between tables and relations
    for ex: If we have customer table and order table and items table
    how can we by code join these tables ?????

  • Josué

    Hellow! I am new here. I was seeing this tutorial and is very good but the problem it looks the author made in eclipse IDE. So can someone write the code in Android Studio?? I try it but get a lot of errors. I want to learn :(
    Thanks

  • ankit

    is it good way to create 28 tables in sqlite database and also apply join and nested query?