Android Open Tutorials

  • Android
    • Dialog
    • ImageView
    • ListView
    • SharedPreferences
    • Spinner
    • SQLite
    • ViewPager
Menu
Home > SQLite > Android SQLite Join Multiple Tables Example

Android SQLite Join Multiple Tables Example

Nithya Vasudevan + April 20, 2014 2014-04-20T19:36:02+00:00 2014-04-21T10:16:35+00:00 SQLite 21 Comments

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

Field Type Key Extra
id int Primary Key auto_increment
name text
salary double
dob date
dept_id int Foreign Key references department(id)

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

department table

Field Type Key Extra
id int Primary Key auto_increment
name text

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 9392 times – 2 MB

Resources

colors.xml

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

1
2
3
4
5
6
<? xml version = "1.0" encoding = "utf-8" ?>
< resources >
< color name = "transparent" > #00000000</color>
< color name = "list_item_bg" > #ffffff</color>
< color name = "view_divider_color" > #BABABA</color>
< / resources >

strings.xml

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<? xml version = "1.0" encoding = "utf-8" ?>
< resources >
< string name = "app_name" > AndroidSQLiteMultipleTables < / string >
< string name = "action_settings" > Settings < / string >
< string name = "add_emp" > Add Employee < / string >
< string name = "update_emp" > Update Employee < / string >
< string name = "name" > Employee Name < / string >
< string name = "dob" > Employee DOB < / string >
< string name = "salary" > Employee Salary < / string >
< string name = "update" > Update < / string >
< string name = "add" > Add < / string >
< string name = "reset" > Reset < / string >
< string name = "cancel" > Cancel < / string >
< / resources >

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.

1
2
3
4
5
6
7
8
9
10
< menu xmlns : android = "http://schemas.android.com/apk/res/android" >
< item
android : id = "@+id/action_add"
android : icon = "@android:drawable/ic_menu_add"
android : orderInCategory = "100"
android : showAsAction = "ifRoom|withText"
android : title = "@string/add_emp" / >
< / menu >

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.

1
2
3
4
5
6
7
8
9
10
11
12
< 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"
tools : context = ".MainActivity" >
< FrameLayout
android : id = "@+id/content_frame"
android : layout_width = "match_parent"
android : layout_height = "match_parent" / >
< / RelativeLayout >

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
<? xml version = "1.0" encoding = "utf-8" ?>
< ScrollView xmlns : android = "http://schemas.android.com/apk/res/android"
android : layout_width = "match_parent"
android : layout_height = "match_parent" >
< RelativeLayout
android : layout_width = "wrap_content"
android : layout_height = "wrap_content"
android : layout_margin = "5dp" >
< EditText
android : id = "@+id/etxt_name"
android : layout_width = "wrap_content"
android : layout_height = "wrap_content"
android : ems = "10"
android : hint = "@string/name"
android : nextFocusDown = "@+id/etxt_dob"
android : singleLine = "true" / >
< EditText
android : id = "@+id/etxt_dob"
android : layout_width = "wrap_content"
android : layout_height = "wrap_content"
android : layout_below = "@+id/etxt_name"
android : hint = "@string/dob"
android : nextFocusDown = "@+id/etxt_salary"
android : singleLine = "true" / >
< EditText
android : id = "@+id/etxt_salary"
android : layout_width = "wrap_content"
android : layout_height = "wrap_content"
android : layout_below = "@+id/etxt_dob"
android : hint = "@string/salary"
android : inputType = "numberDecimal"
android : singleLine = "true" / >
< Spinner
android : id = "@+id/spinner_dept"
android : layout_width = "wrap_content"
android : layout_height = "wrap_content"
android : layout_below = "@+id/etxt_salary" / >
< LinearLayout
android : id = "@+id/layout_submit"
android : layout_width = "fill_parent"
android : layout_height = "wrap_content"
android : layout_below = "@+id/spinner_dept"
android : layout_margin = "5dp"
android : orientation = "horizontal"
android : weightSum = "2" >
< Button
android : id = "@+id/button_add"
android : layout_width = "0dp"
android : layout_height = "wrap_content"
android : layout_weight = "1"
android : enabled = "false"
android : text = "@string/add" / >
< Button
android : id = "@+id/button_reset"
android : layout_width = "0dp"
android : layout_height = "wrap_content"
android : layout_weight = "1"
android : text = "@string/reset" / >
< / LinearLayout >
< / RelativeLayout >
< / ScrollView >

fragment_emp_list.xml

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
< 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 : background = "#EDEDED" >
< ListView
android : id = "@+id/list_emp"
android : layout_width = "match_parent"
android : layout_height = "match_parent"
android : divider = "@color/transparent"
android : dividerHeight = "10dp"
android : drawSelectorOnTop = "true"
android : footerDividersEnabled = "false"
android : padding = "10dp"
android : scrollbarStyle = "outsideOverlay" / >
< / RelativeLayout >

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
<? xml version = "1.0" encoding = "utf-8" ?>
< RelativeLayout xmlns : android = "http://schemas.android.com/apk/res/android"
android : layout_width = "fill_parent"
android : layout_height = "wrap_content"
android : background = "@color/list_item_bg"
android : descendantFocusability = "blocksDescendants" >
< RelativeLayout
android : id = "@+id/layout_item"
android : layout_width = "fill_parent"
android : layout_height = "wrap_content" >
< TextView
android : id = "@+id/txt_emp_id"
android : layout_width = "wrap_content"
android : layout_height = "wrap_content"
android : padding = "6dp" / >
< TextView
android : id = "@+id/txt_emp_name"
android : layout_width = "wrap_content"
android : layout_height = "wrap_content"
android : layout_toRightOf = "@+id/txt_emp_id"
android : padding = "6dp" / >
< TextView
android : id = "@+id/txt_emp_dob"
android : layout_width = "wrap_content"
android : layout_height = "wrap_content"
android : layout_below = "@+id/txt_emp_name"
android : padding = "6dp" / >
< TextView
android : id = "@+id/txt_emp_salary"
android : layout_width = "wrap_content"
android : layout_height = "wrap_content"
android : layout_below = "@+id/txt_emp_dob"
android : padding = "6dp" / >
< TextView
android : id = "@+id/txt_emp_dept"
android : layout_width = "wrap_content"
android : layout_height = "wrap_content"
android : layout_below = "@+id/txt_emp_salary"
android : padding = "6dp"
android : textColor = "#0000FF" / >
< / RelativeLayout >
< View
android : layout_width = "match_parent"
android : layout_height = "1dp"
android : layout_below = "@+id/layout_item"
android : background = "@color/view_divider_color" / >
< / RelativeLayout >

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”.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
package com . androidopentutorials . sqlite . db ;
import android . content . Context ;
import android . database . sqlite . SQLiteDatabase ;
import android . database . sqlite . SQLiteOpenHelper ;
public class DataBaseHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "employeedb" ;
private static final int DATABASE_VERSION = 1 ;
public static final String EMPLOYEE_TABLE = "employee" ;
public static final String DEPARTMENT_TABLE = "department" ;
public static final String ID_COLUMN = "id" ;
public static final String NAME_COLUMN = "name" ;
public static final String EMPLOYEE_DOB = "dob" ;
public static final String EMPLOYEE_SALARY = "salary" ;
public static final String EMPLOYEE_DEPARTMENT_ID = "dept_id" ;
public static final String CREATE_EMPLOYEE_TABLE = "CREATE TABLE "
+ EMPLOYEE_TABLE + "(" + ID_COLUMN + " INTEGER PRIMARY KEY, "
+ NAME_COLUMN + " TEXT, " + EMPLOYEE_SALARY + " DOUBLE, "
+ EMPLOYEE_DOB + " DATE, " + EMPLOYEE_DEPARTMENT_ID + " INT, "
+ "FOREIGN KEY(" + EMPLOYEE_DEPARTMENT_ID + ") REFERENCES "
+ DEPARTMENT_TABLE + "(id) " + ")" ;
public static final String CREATE_DEPARTMENT_TABLE = "CREATE TABLE "
+ DEPARTMENT_TABLE + "(" + ID_COLUMN + " INTEGER PRIMARY KEY,"
+ NAME_COLUMN + ")" ;
private static DataBaseHelper instance ;
public static synchronized DataBaseHelper getHelper ( Context context ) {
if ( instance == null )
instance = new DataBaseHelper ( context ) ;
return instance ;
}
private DataBaseHelper ( Context context ) {
super ( context , DATABASE_NAME , null , DATABASE_VERSION ) ;
}
@ Override
public void onOpen ( SQLiteDatabase db ) {
super . onOpen ( db ) ;
if ( ! db . isReadOnly ( ) ) {
// Enable foreign key constraints
db . execSQL ( "PRAGMA foreign_keys=ON;" ) ;
}
}
@ Override
public void onCreate ( SQLiteDatabase db ) {
db . execSQL ( CREATE_DEPARTMENT_TABLE ) ;
db . execSQL ( CREATE_EMPLOYEE_TABLE ) ;
}
@ Override
public void onUpgrade ( SQLiteDatabase db , int oldVersion , int newVersion ) {
}
}

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package com . androidopentutorials . sqlite . db ;
import android . content . Context ;
import android . database . SQLException ;
import android . database . sqlite . SQLiteDatabase ;
public class EmployeeDBDAO {
protected SQLiteDatabase database ;
private DataBaseHelper dbHelper ;
private Context mContext ;
public EmployeeDBDAO ( Context context ) {
this . mContext = context ;
dbHelper = DataBaseHelper . getHelper ( mContext ) ;
open ( ) ;
}
public void open ( ) throws SQLException {
if ( dbHelper == null )
dbHelper = DataBaseHelper . getHelper ( mContext ) ;
database = dbHelper . getWritableDatabase ( ) ;
}
/*public void close() {
dbHelper.close();
database = null;
}*/
}

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
package com . androidopentutorials . sqlite . to ;
import android . os . Parcel ;
import android . os . Parcelable ;
public class Department implements Parcelable {
private int id ;
private String name ;
public Department ( ) {
super ( ) ;
}
public Department ( int id , String name ) {
super ( ) ;
this . id = id ;
this . name = name ;
}
public Department ( String name ) {
this . name = name ;
}
private Department ( Parcel in ) {
super ( ) ;
this . id = in . readInt ( ) ;
this . name = in . readString ( ) ;
}
public int getId ( ) {
return id ;
}
public void setId ( int id ) {
this . id = id ;
}
public String getName ( ) {
return name ;
}
public void setName ( String name ) {
this . name = name ;
}
@ Override
public String toString ( ) {
return "id:" + id + ", name:" + name ;
}
@ Override
public int describeContents ( ) {
return 0 ;
}
@ Override
public void writeToParcel ( Parcel parcel , int flags ) {
parcel . writeInt ( getId ( ) ) ;
parcel . writeString ( getName ( ) ) ;
}
public static final Parcelable . Creator < Department > CREATOR = new Parcelable . Creator < Department > ( ) {
public Department createFromParcel ( Parcel in ) {
return new Department ( in ) ;
}
public Department [ ] newArray ( int size ) {
return new Department [ size ] ;
}
} ;
@ Override
public int hashCode ( ) {
final int prime = 31 ;
int result = 1 ;
result = prime * result + id ;
return result ;
}
@ Override
public boolean equals ( Object obj ) {
if ( this == obj )
return true ;
if ( obj == null )
return false ;
if ( getClass ( ) != obj . getClass ( ) )
return false ;
Department other = ( Department ) obj ;
if ( id != other . id )
return false ;
return true ;
}
}

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
package com . androidopentutorials . sqlite . to ;
import java . util . Date ;
import android . os . Parcel ;
import android . os . Parcelable ;
public class Employee implements Parcelable {
private int id ;
private String name ;
private Date dateOfBirth ;
private double salary ;
private Department department ;
public Employee ( ) {
super ( ) ;
}
private Employee ( Parcel in ) {
super ( ) ;
this . id = in . readInt ( ) ;
this . name = in . readString ( ) ;
this . dateOfBirth = new Date ( in . readLong ( ) ) ;
this . salary = in . readDouble ( ) ;
this . department = in . readParcelable ( Department . class . getClassLoader ( ) ) ;
}
public int getId ( ) {
return id ;
}
public void setId ( int id ) {
this . id = id ;
}
public String getName ( ) {
return name ;
}
public void setName ( String name ) {
this . name = name ;
}
public Date getDateOfBirth ( ) {
return dateOfBirth ;
}
public void setDateOfBirth ( Date dateOfBirth ) {
this . dateOfBirth = dateOfBirth ;
}
public double getSalary ( ) {
return salary ;
}
public void setSalary ( double salary ) {
this . salary = salary ;
}
public Department getDepartment ( ) {
return department ;
}
public void setDepartment ( Department department ) {
this . department = department ;
}
@ Override
public String toString ( ) {
return "Employee [id=" + id + ", name=" + name + ", dateOfBirth="
+ dateOfBirth + ", salary=" + salary + ", department="
+ department + "]" ;
}
@ Override
public int hashCode ( ) {
final int prime = 31 ;
int result = 1 ;
result = prime * result + id ;
return result ;
}
@ Override
public boolean equals ( Object obj ) {
if ( this == obj )
return true ;
if ( obj == null )
return false ;
if ( getClass ( ) != obj . getClass ( ) )
return false ;
Employee other = ( Employee ) obj ;
if ( id != other . id )
return false ;
return true ;
}
@ Override
public int describeContents ( ) {
return 0 ;
}
@ Override
public void writeToParcel ( Parcel parcel , int flags ) {
parcel . writeInt ( getId ( ) ) ;
parcel . writeString ( getName ( ) ) ;
parcel . writeLong ( getDateOfBirth ( ) . getTime ( ) ) ;
parcel . writeDouble ( getSalary ( ) ) ;
parcel . writeParcelable ( getDepartment ( ) , flags ) ;
}
public static final Parcelable . Creator < Employee > CREATOR = new Parcelable . Creator < Employee > ( ) {
public Employee createFromParcel ( Parcel in ) {
return new Employee ( in ) ;
}
public Employee [ ] newArray ( int size ) {
return new Employee [ size ] ;
}
} ;
}

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
package com . androidopentutorials . sqlite . db ;
import java . util . ArrayList ;
import java . util . List ;
import com . androidopentutorials . sqlite . to . Department ;
import android . content . ContentValues ;
import android . content . Context ;
import android . database . Cursor ;
import android . util . Log ;
public class DepartmentDAO extends EmployeeDBDAO {
private static final String WHERE_ID_EQUALS = DataBaseHelper . ID_COLUMN
+ " =?" ;
public DepartmentDAO ( Context context ) {
super ( context ) ;
}
public long save ( Department department ) {
ContentValues values = new ContentValues ( ) ;
values . put ( DataBaseHelper . NAME_COLUMN , department . getName ( ) ) ;
return database . insert ( DataBaseHelper . DEPARTMENT_TABLE , null , values ) ;
}
public long update ( Department department ) {
ContentValues values = new ContentValues ( ) ;
values . put ( DataBaseHelper . NAME_COLUMN , department . getName ( ) ) ;
long result = database . update ( DataBaseHelper . DEPARTMENT_TABLE , values ,
WHERE_ID_EQUALS ,
new String [ ] { String . valueOf ( department . getId ( ) ) } ) ;
Log . d ( "Update Result:" , "=" + result ) ;
return result ;
}
public int deleteDept ( Department department ) {
return database . delete ( DataBaseHelper . DEPARTMENT_TABLE ,
WHERE_ID_EQUALS , new String [ ] { department . getId ( ) + "" } ) ;
}
public List < Department > getDepartments ( ) {
List < Department > departments = new ArrayList < Department > ( ) ;
Cursor cursor = database . query ( DataBaseHelper . DEPARTMENT_TABLE ,
new String [ ] { DataBaseHelper . ID_COLUMN ,
DataBaseHelper . NAME _ COLUMN } , null , null , null , null ,
null ) ;
while ( cursor . moveToNext ( ) ) {
Department department = new Department ( ) ;
department . setId ( cursor . getInt ( 0 ) ) ;
department . setName ( cursor . getString ( 1 ) ) ;
departments . add ( department ) ;
}
return departments ;
}
public void loadDepartments ( ) {
Department department = new Department ( "Development" ) ;
Department department1 = new Department ( "R and D" ) ;
Department department2 = new Department ( "Human Resource" ) ;
Department department3 = new Department ( "Financial" ) ;
Department department4 = new Department ( "Marketing" ) ;
Department department5 = new Department ( "Sales" ) ;
List < Department > departments = new ArrayList < Department > ( ) ;
departments . add ( department ) ;
departments . add ( department1 ) ;
departments . add ( department2 ) ;
departments . add ( department3 ) ;
departments . add ( department4 ) ;
departments . add ( department5 ) ;
for ( Department dept : departments ) {
ContentValues values = new ContentValues ( ) ;
values . put ( DataBaseHelper . NAME_COLUMN , dept . getName ( ) ) ;
database . insert ( DataBaseHelper . DEPARTMENT_TABLE , null , values ) ;
}
}
}

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package com . androidopentutorials . sqlite . db ;
import android . content . ContentValues ;
import android . content . Context ;
import java . text . SimpleDateFormat ;
import com . androidopentutorials . sqlite . to . Employee ;
public class EmployeeDAO extends EmployeeDBDAO {
private static final SimpleDateFormat formatter = new SimpleDateFormat (
"yyyy-MM-dd" , Locale . ENGLISH ) ;
public EmployeeDAO ( Context context ) {
super ( context ) ;
}
. . . .
public long save ( Employee employee ) {
ContentValues values = new ContentValues ( ) ;
values . put ( DataBaseHelper . NAME_COLUMN , employee . getName ( ) ) ;
values . put ( DataBaseHelper . EMPLOYEE_DOB , formatter . format ( employee . getDateOfBirth ( ) ) ) ;
values . put ( DataBaseHelper . EMPLOYEE_SALARY , employee . getSalary ( ) ) ;
values . put ( DataBaseHelper . EMPLOYEE_DEPARTMENT_ID , employee . getDepartment ( ) . getId ( ) ) ;
return database . insert ( DataBaseHelper . EMPLOYEE_TABLE , null , values ) ;
}
. . . .
}

  • 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
package com . androidopentutorials . sqlite . fragment ;
import java . lang . ref . WeakReference ;
import java . text . SimpleDateFormat ;
import java . util . Calendar ;
import java . util . Date ;
import java . util . List ;
import java . util . Locale ;
import android . app . Activity ;
import android . app . DatePickerDialog ;
import android . app . DatePickerDialog . OnDateSetListener ;
import android . os . AsyncTask ;
import android . os . Bundle ;
import android . support . v4 . app . Fragment ;
import android . text . InputType ;
import android . view . LayoutInflater ;
import android . view . View ;
import android . view . View . OnClickListener ;
import android . view . ViewGroup ;
import android . widget . ArrayAdapter ;
import android . widget . Button ;
import android . widget . DatePicker ;
import android . widget . EditText ;
import android . widget . Spinner ;
import android . widget . Toast ;
import com . androidopentutorials . sqlite . R ;
import com . androidopentutorials . sqlite . db . DepartmentDAO ;
import com . androidopentutorials . sqlite . db . EmployeeDAO ;
import com . androidopentutorials . sqlite . to . Department ;
import com . androidopentutorials . sqlite . to . Employee ;
public class EmpAddFragment extends Fragment implements OnClickListener {
// UI references
private EditText empNameEtxt ;
private EditText empSalaryEtxt ;
private EditText empDobEtxt ;
private Spinner deptSpinner ;
private Button addButton ;
private Button resetButton ;
private static final SimpleDateFormat formatter = new SimpleDateFormat (
"yyyy-MM-dd" , Locale . ENGLISH ) ;
DatePickerDialog datePickerDialog ;
Calendar dateCalendar ;
Employee employee = null ;
private EmployeeDAO employeeDAO ;
private DepartmentDAO departmentDAO ;
private GetDeptTask task ;
private AddEmpTask addEmpTask ;
public static final String ARG_ITEM_ID = "emp_add_fragment" ;
@ Override
public void onCreate ( Bundle savedInstanceState ) {
super . onCreate ( savedInstanceState ) ;
employeeDAO = new EmployeeDAO ( getActivity ( ) ) ;
departmentDAO = new DepartmentDAO ( getActivity ( ) ) ;
}
@ Override
public View onCreateView ( LayoutInflater inflater , ViewGroup container ,
Bundle savedInstanceState ) {
View rootView = inflater . inflate ( R . layout . fragment_add_emp , container ,
false ) ;
findViewsById ( rootView ) ;
setListeners ( ) ;
// Used for orientation change
/*
* After entering the fields, change the orientation.
* NullPointerException occurs for date. This piece of code avoids it.
*/
if ( savedInstanceState != null ) {
dateCalendar = Calendar . getInstance ( ) ;
if ( savedInstanceState . getLong ( "dateCalendar" ) != 0 )
dateCalendar . setTime ( new Date ( savedInstanceState
. getLong ( "dateCalendar" ) ) ) ;
}
// asynchronously retrieves department from table and sets it in Spinner
task = new GetDeptTask ( getActivity ( ) ) ;
task . execute ( ( Void ) null ) ;
return rootView ;
}
private void setListeners ( ) {
empDobEtxt . setOnClickListener ( this ) ;
Calendar newCalendar = Calendar . getInstance ( ) ;
datePickerDialog = new DatePickerDialog ( getActivity ( ) ,
new OnDateSetListener ( ) {
public void onDateSet ( DatePicker view , int year ,
int monthOfYear , int dayOfMonth ) {
dateCalendar = Calendar . getInstance ( ) ;
dateCalendar . set ( year , monthOfYear , dayOfMonth ) ;
empDobEtxt . setText ( formatter . format ( dateCalendar
. getTime ( ) ) ) ;
}
} , newCalendar . get ( Calendar . YEAR ) ,
newCalendar . get ( Calendar . MONTH ) ,
newCalendar . get ( Calendar . DAY_OF_MONTH ) ) ;
addButton . setOnClickListener ( this ) ;
resetButton . setOnClickListener ( this ) ;
}
protected void resetAllFields ( ) {
empNameEtxt . setText ( "" ) ;
empSalaryEtxt . setText ( "" ) ;
empDobEtxt . setText ( "" ) ;
if ( deptSpinner . getAdapter ( ) . getCount ( ) > 0 )
deptSpinner . setSelection ( 0 ) ;
}
private void setEmployee ( ) {
employee = new Employee ( ) ;
employee . setName ( empNameEtxt . getText ( ) . toString ( ) ) ;
employee . setSalary ( Double . parseDouble ( empSalaryEtxt . getText ( )
. toString ( ) ) ) ;
if ( dateCalendar != null )
employee . setDateOfBirth ( dateCalendar . getTime ( ) ) ;
Department selectedDept = ( Department ) deptSpinner . getSelectedItem ( ) ;
employee . setDepartment ( selectedDept ) ;
}
@ Override
public void onResume ( ) {
getActivity ( ) . setTitle ( R . string . add_emp ) ;
getActivity ( ) . getActionBar ( ) . setTitle ( R . string . add_emp ) ;
super . onResume ( ) ;
}
@ Override
public void onSaveInstanceState ( Bundle outState ) {
if ( dateCalendar != null )
outState . putLong ( "dateCalendar" , dateCalendar . getTime ( ) . getTime ( ) ) ;
}
private void findViewsById ( View rootView ) {
empNameEtxt = ( EditText ) rootView . findViewById ( R . id . etxt_name ) ;
empSalaryEtxt = ( EditText ) rootView . findViewById ( R . id . etxt_salary ) ;
empDobEtxt = ( EditText ) rootView . findViewById ( R . id . etxt_dob ) ;
empDobEtxt . setInputType ( InputType . TYPE_NULL ) ;
deptSpinner = ( Spinner ) rootView . findViewById ( R . id . spinner_dept ) ;
addButton = ( Button ) rootView . findViewById ( R . id . button_add ) ;
resetButton = ( Button ) rootView . findViewById ( R . id . button_reset ) ;
}
@ Override
public void onClick ( View view ) {
if ( view == empDobEtxt ) {
datePickerDialog . show ( ) ;
} else if ( view == addButton ) {
setEmployee ( ) ;
addEmpTask = new AddEmpTask ( getActivity ( ) ) ;
addEmpTask . execute ( ( Void ) null ) ;
} else if ( view == resetButton ) {
resetAllFields ( ) ;
}
}
public class GetDeptTask extends AsyncTask < Void , Void , Void > {
private final WeakReference < Activity > activityWeakRef ;
private List < Department > departments ;
public GetDeptTask ( Activity context ) {
this . activityWeakRef = new WeakReference < Activity > ( context ) ;
}
@ Override
protected Void doInBackground ( Void . . . arg0 ) {
departments = departmentDAO . getDepartments ( ) ;
return null ;
}
@ Override
protected void onPostExecute ( Void v ) {
if ( activityWeakRef . get ( ) != null
&& ! activityWeakRef . get ( ) . isFinishing ( ) ) {
ArrayAdapter < Department > adapter = new ArrayAdapter < Department > (
activityWeakRef . get ( ) ,
android . R . layout . simple_list_item_1 , departments ) ;
deptSpinner . setAdapter ( adapter ) ;
addButton . setEnabled ( true ) ;
}
}
}
public class AddEmpTask extends AsyncTask < Void , Void , Long > {
private final WeakReference < Activity > activityWeakRef ;
public AddEmpTask ( Activity context ) {
this . activityWeakRef = new WeakReference < Activity > ( context ) ;
}
@ Override
protected Long doInBackground ( Void . . . arg0 ) {
long result = employeeDAO . save ( employee ) ;
return result ;
}
@ Override
protected void onPostExecute ( Long result ) {
if ( activityWeakRef . get ( ) != null
&& ! activityWeakRef . get ( ) . isFinishing ( ) ) {
if ( result != - 1 )
Toast . makeText ( activityWeakRef . get ( ) , "Employee Saved" ,
Toast . LENGTH_LONG ) . show ( ) ;
}
}
}
}

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
package com . androidopentutorials . sqlite . db ;
import java . util . ArrayList ;
import java . util . Date ;
import android . content . Context ;
import android . database . Cursor ;
import android . database . sqlite . SQLiteQueryBuilder ;
import android . util . Log ;
import com . androidopentutorials . sqlite . to . Department ;
import com . androidopentutorials . sqlite . to . Employee ;
public class EmployeeDAO extends EmployeeDBDAO {
public static final String EMPLOYEE_ID_WITH_PREFIX = "emp.id" ;
public static final String EMPLOYEE_NAME_WITH_PREFIX = "emp.name" ;
public static final String DEPT_NAME_WITH_PREFIX = "dept.name" ;
private static final String WHERE_ID_EQUALS = DataBaseHelper . ID_COLUMN + " =?" ;
public EmployeeDAO ( Context context ) {
super ( context ) ;
}
// METHOD 1
// Uses rawQuery() to query multiple tables
public ArrayList < Employee > getEmployees ( ) {
ArrayList < Employee > employees = new ArrayList < Employee > ( ) ;
String query = "SELECT " + EMPLOYEE_ID_WITH_PREFIX + ","
+ EMPLOYEE_NAME_WITH_PREFIX + "," + DataBaseHelper . EMPLOYEE_DOB
+ "," + DataBaseHelper . EMPLOYEE_SALARY + ","
+ DataBaseHelper . EMPLOYEE_DEPARTMENT_ID + ","
+ DEPT_NAME_WITH_PREFIX + " FROM "
+ DataBaseHelper . EMPLOYEE_TABLE + " emp, "
+ DataBaseHelper . DEPARTMENT_TABLE + " dept WHERE emp."
+ DataBaseHelper . EMPLOYEE_DEPARTMENT_ID + " = dept."
+ DataBaseHelper . ID_COLUMN ;
// Building query using INNER JOIN keyword
/*String query = "SELECT " + EMPLOYEE_ID_WITH_PREFIX + ","
+ EMPLOYEE_NAME_WITH_PREFIX + "," + DataBaseHelper.EMPLOYEE_DOB
+ "," + DataBaseHelper.EMPLOYEE_SALARY + ","
+ DataBaseHelper.EMPLOYEE_DEPARTMENT_ID + ","
+ DEPT_NAME_WITH_PREFIX + " FROM "
+ DataBaseHelper.EMPLOYEE_TABLE + " emp INNER JOIN "
+ DataBaseHelper.DEPARTMENT_TABLE + " dept ON emp."
+ DataBaseHelper.EMPLOYEE_DEPARTMENT_ID + " = dept."
+ DataBaseHelper.ID_COLUMN;*/
Log . d ( "query" , query ) ;
Cursor cursor = database . rawQuery ( query , null ) ;
while ( cursor . moveToNext ( ) ) {
Employee employee = new Employee ( ) ;
employee . setId ( cursor . getInt ( 0 ) ) ;
employee . setName ( cursor . getString ( 1 ) ) ;
try {
employee . setDateOfBirth ( formatter . parse ( cursor . getString ( 2 ) ) ) ;
} catch ( ParseException e ) {
employee . setDateOfBirth ( null ) ;
}
employee . setSalary ( cursor . getDouble ( 3 ) ) ;
Department department = new Department ( ) ;
department . setId ( cursor . getInt ( 4 ) ) ;
department . setName ( cursor . getString ( 5 ) ) ;
employee . setDepartment ( department ) ;
employees . add ( employee ) ;
}
return employees ;
}
// METHOD 2
// Uses SQLiteQueryBuilder to query multiple tables
/*public ArrayList<Employee> getEmployees() {
ArrayList<Employee> employees = new ArrayList<Employee>();
SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
queryBuilder
.setTables(DataBaseHelper.EMPLOYEE_TABLE
+ " INNER JOIN "
+ DataBaseHelper.DEPARTMENT_TABLE
+ " ON "
+ DataBaseHelper.EMPLOYEE_DEPARTMENT_ID
+ " = "
+ (DataBaseHelper.DEPARTMENT_TABLE + "." + DataBaseHelper.ID_COLUMN));
// Get cursor
Cursor cursor = queryBuilder.query(database, new String[] {
EMPLOYEE_ID_WITH_PREFIX,
DataBaseHelper.EMPLOYEE_TABLE + "."
+ DataBaseHelper.NAME_COLUMN,
DataBaseHelper.EMPLOYEE_DOB,
DataBaseHelper.EMPLOYEE_SALARY,
DataBaseHelper.EMPLOYEE_DEPARTMENT_ID,
DataBaseHelper.DEPARTMENT_TABLE + "."
+ DataBaseHelper.NAME_COLUMN }, null, null, null, null,
null);
while (cursor.moveToNext()) {
Employee employee = new Employee();
employee.setId(cursor.getInt(0));
employee.setName(cursor.getString(1));
try {
employee.setDateOfBirth(formatter.parse(cursor.getString(2)));
} catch (ParseException e) {
employee.setDateOfBirth(null);
}
employee.setSalary(cursor.getDouble(3));
Department department = new Department();
department.setId(cursor.getInt(4));
department.setName(cursor.getString(5));
employee.setDepartment(department);
employees.add(employee);
}
return employees;
}*/
}

  • 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
package com . androidopentutorials . sqlite . adapter ;
import java . text . SimpleDateFormat ;
import java . util . List ;
import java . util . Locale ;
import android . app . Activity ;
import android . content . Context ;
import android . view . LayoutInflater ;
import android . view . View ;
import android . view . ViewGroup ;
import android . widget . ArrayAdapter ;
import android . widget . TextView ;
import com . androidopentutorials . sqlite . R ;
import com . androidopentutorials . sqlite . to . Employee ;
public class EmpListAdapter extends ArrayAdapter < Employee > {
private Context context ;
List < Employee > employees ;
private static final SimpleDateFormat formatter = new SimpleDateFormat (
"yyyy-MM-dd" , Locale . ENGLISH ) ;
public EmpListAdapter ( Context context , List < Employee > employees ) {
super ( context , R . layout . list_item , employees ) ;
this . context = context ;
this . employees = employees ;
}
private class ViewHolder {
TextView empIdTxt ;
TextView empNameTxt ;
TextView empDobTxt ;
TextView empSalaryTxt ;
TextView empDeptNameTxt ;
}
@ Override
public int getCount ( ) {
return employees . size ( ) ;
}
@ Override
public Employee getItem ( int position ) {
return employees . get ( position ) ;
}
@ Override
public long getItemId ( int position ) {
return 0 ;
}
@ Override
public View getView ( int position , View convertView , ViewGroup parent ) {
ViewHolder holder = null ;
if ( convertView == null ) {
LayoutInflater inflater = ( LayoutInflater ) context
. getSystemService ( Activity . LAYOUT_INFLATER_SERVICE ) ;
convertView = inflater . inflate ( R . layout . list_item , null ) ;
holder = new ViewHolder ( ) ;
holder . empIdTxt = ( TextView ) convertView
. findViewById ( R . id . txt_emp_id ) ;
holder . empNameTxt = ( TextView ) convertView
. findViewById ( R . id . txt_emp_name ) ;
holder . empDobTxt = ( TextView ) convertView
. findViewById ( R . id . txt_emp_dob ) ;
holder . empSalaryTxt = ( TextView ) convertView
. findViewById ( R . id . txt_emp_salary ) ;
holder . empDeptNameTxt = ( TextView ) convertView
. findViewById ( R . id . txt_emp_dept ) ;
convertView . setTag ( holder ) ;
} else {
holder = ( ViewHolder ) convertView . getTag ( ) ;
}
Employee employee = ( Employee ) getItem ( position ) ;
holder . empIdTxt . setText ( employee . getId ( ) + "" ) ;
holder . empNameTxt . setText ( employee . getName ( ) ) ;
holder . empSalaryTxt . setText ( employee . getSalary ( ) + "" ) ;
holder . empDeptNameTxt . setText ( employee . getDepartment ( ) . getName ( ) ) ;
holder . empDobTxt . setText ( formatter . format ( employee . getDateOfBirth ( ) ) ) ;
return convertView ;
}
@ Override
public void add ( Employee employee ) {
employees . add ( employee ) ;
notifyDataSetChanged ( ) ;
super . add ( employee ) ;
}
@ Override
public void remove ( Employee employee ) {
employees . remove ( employee ) ;
notifyDataSetChanged ( ) ;
super . remove ( employee ) ;
}
}

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
package com . androidopentutorials . sqlite . fragment ;
import java . lang . ref . WeakReference ;
import java . util . ArrayList ;
import android . app . Activity ;
import android . os . AsyncTask ;
import android . os . Bundle ;
import android . support . v4 . app . Fragment ;
import android . view . LayoutInflater ;
import android . view . View ;
import android . view . ViewGroup ;
import android . widget . AdapterView ;
import android . widget . AdapterView . OnItemClickListener ;
import android . widget . AdapterView . OnItemLongClickListener ;
import android . widget . ListView ;
import android . widget . Toast ;
import com . androidopentutorials . sqlite . R ;
import com . androidopentutorials . sqlite . adapter . EmpListAdapter ;
import com . androidopentutorials . sqlite . db . EmployeeDAO ;
import com . androidopentutorials . sqlite . to . Employee ;
public class EmpListFragment extends Fragment implements OnItemClickListener ,
OnItemLongClickListener {
public static final String ARG_ITEM_ID = "employee_list" ;
Activity activity ;
ListView employeeListView ;
ArrayList < Employee > employees ;
EmpListAdapter employeeListAdapter ;
EmployeeDAO employeeDAO ;
private GetEmpTask task ;
@ Override
public void onCreate ( Bundle savedInstanceState ) {
super . onCreate ( savedInstanceState ) ;
activity = getActivity ( ) ;
employeeDAO = new EmployeeDAO ( activity ) ;
}
@ Override
public View onCreateView ( LayoutInflater inflater , ViewGroup container ,
Bundle savedInstanceState ) {
View view = inflater . inflate ( R . layout . fragment_emp_list , container ,
false ) ;
findViewsById ( view ) ;
task = new GetEmpTask ( activity ) ;
task . execute ( ( Void ) null ) ;
employeeListView . setOnItemClickListener ( this ) ;
employeeListView . setOnItemLongClickListener ( this ) ;
return view ;
}
private void findViewsById ( View view ) {
employeeListView = ( ListView ) view . findViewById ( R . id . list_emp ) ;
}
@ Override
public void onItemClick ( AdapterView <? > list , View view , int position ,
long id ) {
Employee employee = ( Employee ) list . getItemAtPosition ( position ) ;
if ( employee != null ) {
Bundle arguments = new Bundle ( ) ;
arguments . putParcelable ( "selectedEmployee" , employee ) ;
CustomEmpDialogFragment customEmpDialogFragment = new CustomEmpDialogFragment ( ) ;
customEmpDialogFragment . setArguments ( arguments ) ;
customEmpDialogFragment . show ( getFragmentManager ( ) ,
CustomEmpDialogFragment . ARG_ITEM_ID ) ;
}
}
@ Override
public boolean onItemLongClick ( AdapterView <? > parent , View view ,
int position , long id ) {
Employee employee = ( Employee ) parent . getItemAtPosition ( position ) ;
// Use AsyncTask to delete from database
employeeDAO . deleteEmployee ( employee ) ;
employeeListAdapter . remove ( employee ) ;
return true ;
}
public class GetEmpTask extends AsyncTask < Void , Void , ArrayList < Employee >> {
private final WeakReference < Activity > activityWeakRef ;
public GetEmpTask ( Activity context ) {
this . activityWeakRef = new WeakReference < Activity > ( context ) ;
}
@ Override
protected ArrayList < Employee > doInBackground ( Void . . . arg0 ) {
ArrayList < Employee > employeeList = employeeDAO . getEmployees ( ) ;
return employeeList ;
}
@ Override
protected void onPostExecute ( ArrayList < Employee > empList ) {
if ( activityWeakRef . get ( ) != null
&& ! activityWeakRef . get ( ) . isFinishing ( ) ) {
employees = empList ;
if ( empList != null ) {
if ( empList . size ( ) != 0 ) {
employeeListAdapter = new EmpListAdapter ( activity ,
empList ) ;
employeeListView . setAdapter ( employeeListAdapter ) ;
} else {
Toast . makeText ( activity , "No Employee Records" ,
Toast . LENGTH_LONG ) . show ( ) ;
}
}
}
}
}
/*
* This method is invoked from MainActivity onFinishDialog() method. It is
* called from CustomEmpDialogFragment when an employee record is updated.
* This is used for communicating between fragments.
*/
public void updateView ( ) {
task = new GetEmpTask ( activity ) ;
task . execute ( ( Void ) null ) ;
}
@ Override
public void onResume ( ) {
getActivity ( ) . setTitle ( R . string . app_name ) ;
getActivity ( ) . getActionBar ( ) . setTitle ( R . string . app_name ) ;
super . onResume ( ) ;
}
}

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
package com . androidopentutorials . sqlite . db ;
import android . content . ContentValues ;
import android . content . Context ;
import android . util . Log ;
import com . androidopentutorials . sqlite . to . Employee ;
public class EmployeeDAO extends EmployeeDBDAO {
private static final String WHERE_ID_EQUALS = DataBaseHelper . ID_COLUMN + " =?" ;
public EmployeeDAO ( Context context ) {
super ( context ) ;
}
. . . .
public long update ( Employee employee ) {
ContentValues values = new ContentValues ( ) ;
values . put ( DataBaseHelper . NAME_COLUMN , employee . getName ( ) ) ;
values . put ( DataBaseHelper . EMPLOYEE_DOB , formatter . format ( employee . getDateOfBirth ( ) ) ) ;
values . put ( DataBaseHelper . EMPLOYEE_SALARY , employee . getSalary ( ) ) ;
values . put ( DataBaseHelper . EMPLOYEE_DEPARTMENT_ID , employee . getDepartment ( ) . getId ( ) ) ;
long result = database . update ( DataBaseHelper . EMPLOYEE_TABLE , values ,
WHERE_ID_EQUALS ,
new String [ ] { String . valueOf ( employee . getId ( ) ) } ) ;
Log . d ( "Update Result:" , "=" + result ) ;
return result ;
}
. . . .
}

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
package com . androidopentutorials . sqlite . fragment ;
import java . text . ParseException ;
import java . text . SimpleDateFormat ;
import java . util . List ;
import java . util . Locale ;
import android . app . AlertDialog ;
import android . app . Dialog ;
import android . content . DialogInterface ;
import android . os . Bundle ;
import android . support . v4 . app . DialogFragment ;
import android . view . LayoutInflater ;
import android . view . View ;
import android . widget . ArrayAdapter ;
import android . widget . EditText ;
import android . widget . LinearLayout ;
import android . widget . Spinner ;
import android . widget . Toast ;
import com . androidopentutorials . sqlite . MainActivity ;
import com . androidopentutorials . sqlite . R ;
import com . androidopentutorials . sqlite . db . DepartmentDAO ;
import com . androidopentutorials . sqlite . db . EmployeeDAO ;
import com . androidopentutorials . sqlite . to . Department ;
import com . androidopentutorials . sqlite . to . Employee ;
public class CustomEmpDialogFragment extends DialogFragment {
// UI references
private EditText empNameEtxt ;
private EditText empSalaryEtxt ;
private EditText empDobEtxt ;
private Spinner deptSpinner ;
private LinearLayout submitLayout ;
private Employee employee ;
EmployeeDAO employeeDAO ;
ArrayAdapter < Department > adapter ;
public static final String ARG_ITEM_ID = "emp_dialog_fragment" ;
private static final SimpleDateFormat formatter = new SimpleDateFormat (
"yyyy-MM-dd" , Locale . ENGLISH ) ;
/*
* Callback used to communicate with EmpListFragment to notify the list adapter.
* MainActivity implements this interface and communicates with EmpListFragment.
*/
public interface CustomEmpDialogFragmentListener {
void onFinishDialog ( ) ;
}
public CustomEmpDialogFragment ( ) {
}
@ Override
public Dialog onCreateDialog ( Bundle savedInstanceState ) {
employeeDAO = new EmployeeDAO ( getActivity ( ) ) ;
Bundle bundle = this . getArguments ( ) ;
employee = bundle . getParcelable ( "selectedEmployee" ) ;
AlertDialog . Builder builder = new AlertDialog . Builder ( getActivity ( ) ) ;
LayoutInflater inflater = getActivity ( ) . getLayoutInflater ( ) ;
View customDialogView = inflater . inflate ( R . layout . fragment_add_emp ,
null ) ;
builder . setView ( customDialogView ) ;
empNameEtxt = ( EditText ) customDialogView . findViewById ( R . id . etxt_name ) ;
empSalaryEtxt = ( EditText ) customDialogView
. findViewById ( R . id . etxt_salary ) ;
empDobEtxt = ( EditText ) customDialogView . findViewById ( R . id . etxt_dob ) ;
deptSpinner = ( Spinner ) customDialogView
. findViewById ( R . id . spinner_dept ) ;
submitLayout = ( LinearLayout ) customDialogView
. findViewById ( R . id . layout_submit ) ;
submitLayout . setVisibility ( View . GONE ) ;
setValue ( ) ;
builder . setTitle ( R . string . update_emp ) ;
builder . setCancelable ( false ) ;
builder . setPositiveButton ( R . string . update ,
new DialogInterface . OnClickListener ( ) {
public void onClick ( DialogInterface dialog , int id ) {
try {
employee . setDateOfBirth ( formatter . parse ( empDobEtxt . getText ( ) . toString ( ) ) ) ;
} catch ( ParseException e ) {
Toast . makeText ( getActivity ( ) ,
"Invalid date format!" ,
Toast . LENGTH_SHORT ) . show ( ) ;
return ;
}
employee . setName ( empNameEtxt . getText ( ) . toString ( ) ) ;
employee . setSalary ( Double . parseDouble ( empSalaryEtxt
. getText ( ) . toString ( ) ) ) ;
Department dept = ( Department ) adapter
. getItem ( deptSpinner . getSelectedItemPosition ( ) ) ;
employee . setDepartment ( dept ) ;
long result = employeeDAO . update ( employee ) ;
if ( result > 0 ) {
MainActivity activity = ( MainActivity ) getActivity ( ) ;
activity . onFinishDialog ( ) ;
} else {
Toast . makeText ( getActivity ( ) ,
"Unable to update employee" ,
Toast . LENGTH_SHORT ) . show ( ) ;
}
}
} ) ;
builder . setNegativeButton ( R . string . cancel ,
new DialogInterface . OnClickListener ( ) {
public void onClick ( DialogInterface dialog , int id ) {
dialog . cancel ( ) ;
}
} ) ;
AlertDialog alertDialog = builder . create ( ) ;
return alertDialog ;
}
private void setValue ( ) {
DepartmentDAO departmentDAO = new DepartmentDAO ( getActivity ( ) ) ;
List < Department > departments = departmentDAO . getDepartments ( ) ;
adapter = new ArrayAdapter < Department > ( getActivity ( ) ,
android . R . layout . simple_list_item_1 , departments ) ;
deptSpinner . setAdapter ( adapter ) ;
int pos = adapter . getPosition ( employee . getDepartment ( ) ) ;
if ( employee != null ) {
empNameEtxt . setText ( employee . getName ( ) ) ;
empSalaryEtxt . setText ( employee . getSalary ( ) + "" ) ;
empDobEtxt . setText ( formatter . format ( employee . getDateOfBirth ( ) ) ) ;
deptSpinner . setSelection ( pos ) ;
}
}
}

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package com . androidopentutorials . sqlite . db ;
import android . content . Context ;
import com . androidopentutorials . sqlite . to . Employee ;
public class EmployeeDAO extends EmployeeDBDAO {
. . .
private static final String WHERE_ID_EQUALS = DataBaseHelper . ID_COLUMN + " =?" ;
public int deleteEmployee ( Employee employee ) {
return database . delete ( DataBaseHelper . EMPLOYEE_TABLE , WHERE_ID_EQUALS ,
new String [ ] { employee . getId ( ) + "" } ) ;
}
. . .
}

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

Complete EmployeeDAO class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
package com . androidopentutorials . sqlite . db ;
import java . text . ParseException ;
import java . text . SimpleDateFormat ;
import java . util . ArrayList ;
import java . util . Locale ;
import android . content . ContentValues ;
import android . content . Context ;
import android . database . Cursor ;
//import android.database.sqlite.SQLiteQueryBuilder;
import android . util . Log ;
import com . androidopentutorials . sqlite . to . Department ;
import com . androidopentutorials . sqlite . to . Employee ;
public class EmployeeDAO extends EmployeeDBDAO {
public static final String EMPLOYEE_ID_WITH_PREFIX = "emp.id" ;
public static final String EMPLOYEE_NAME_WITH_PREFIX = "emp.name" ;
public static final String DEPT_NAME_WITH_PREFIX = "dept.name" ;
private static final String WHERE_ID_EQUALS = DataBaseHelper . ID_COLUMN + " =?" ;
private static final SimpleDateFormat formatter = new SimpleDateFormat (
"yyyy-MM-dd" , Locale . ENGLISH ) ;
public EmployeeDAO ( Context context ) {
super ( context ) ;
}
public long save ( Employee employee ) {
ContentValues values = new ContentValues ( ) ;
values . put ( DataBaseHelper . NAME_COLUMN , employee . getName ( ) ) ;
values . put ( DataBaseHelper . EMPLOYEE_DOB , formatter . format ( employee . getDateOfBirth ( ) ) ) ;
values . put ( DataBaseHelper . EMPLOYEE_SALARY , employee . getSalary ( ) ) ;
values . put ( DataBaseHelper . EMPLOYEE_DEPARTMENT_ID , employee . getDepartment ( ) . getId ( ) ) ;
return database . insert ( DataBaseHelper . EMPLOYEE_TABLE , null , values ) ;
}
public long update ( Employee employee ) {
ContentValues values = new ContentValues ( ) ;
values . put ( DataBaseHelper . NAME_COLUMN , employee . getName ( ) ) ;
values . put ( DataBaseHelper . EMPLOYEE_DOB , formatter . format ( employee . getDateOfBirth ( ) ) ) ;
values . put ( DataBaseHelper . EMPLOYEE_SALARY , employee . getSalary ( ) ) ;
values . put ( DataBaseHelper . EMPLOYEE_DEPARTMENT_ID , employee . getDepartment ( ) . getId ( ) ) ;
long result = database . update ( DataBaseHelper . EMPLOYEE_TABLE , values ,
WHERE_ID_EQUALS ,
new String [ ] { String . valueOf ( employee . getId ( ) ) } ) ;
Log . d ( "Update Result:" , "=" + result ) ;
return result ;
}
public int deleteEmployee ( Employee employee ) {
return database . delete ( DataBaseHelper . EMPLOYEE_TABLE , WHERE_ID_EQUALS ,
new String [ ] { employee . getId ( ) + "" } ) ;
}
// METHOD 1
// Uses rawQuery() to query multiple tables
public ArrayList < Employee > getEmployees ( ) {
ArrayList < Employee > employees = new ArrayList < Employee > ( ) ;
String query = "SELECT " + EMPLOYEE_ID_WITH_PREFIX + ","
+ EMPLOYEE_NAME_WITH_PREFIX + "," + DataBaseHelper . EMPLOYEE_DOB
+ "," + DataBaseHelper . EMPLOYEE_SALARY + ","
+ DataBaseHelper . EMPLOYEE_DEPARTMENT_ID + ","
+ DEPT_NAME_WITH_PREFIX + " FROM "
+ DataBaseHelper . EMPLOYEE_TABLE + " emp, "
+ DataBaseHelper . DEPARTMENT_TABLE + " dept WHERE emp."
+ DataBaseHelper . EMPLOYEE_DEPARTMENT_ID + " = dept."
+ DataBaseHelper . ID_COLUMN ;
// Building query using INNER JOIN keyword
/*String query = "SELECT " + EMPLOYEE_ID_WITH_PREFIX + ","
+ EMPLOYEE_NAME_WITH_PREFIX + "," + DataBaseHelper.EMPLOYEE_DOB
+ "," + DataBaseHelper.EMPLOYEE_SALARY + ","
+ DataBaseHelper.EMPLOYEE_DEPARTMENT_ID + ","
+ DEPT_NAME_WITH_PREFIX + " FROM "
+ DataBaseHelper.EMPLOYEE_TABLE + " emp INNER JOIN "
+ DataBaseHelper.DEPARTMENT_TABLE + " dept ON emp."
+ DataBaseHelper.EMPLOYEE_DEPARTMENT_ID + " = dept."
+ DataBaseHelper.ID_COLUMN;*/
Log . d ( "query" , query ) ;
Cursor cursor = database . rawQuery ( query , null ) ;
while ( cursor . moveToNext ( ) ) {
Employee employee = new Employee ( ) ;
employee . setId ( cursor . getInt ( 0 ) ) ;
employee . setName ( cursor . getString ( 1 ) ) ;
try {
employee . setDateOfBirth ( formatter . parse ( cursor . getString ( 2 ) ) ) ;
} catch ( ParseException e ) {
employee . setDateOfBirth ( null ) ;
}
employee . setSalary ( cursor . getDouble ( 3 ) ) ;
Department department = new Department ( ) ;
department . setId ( cursor . getInt ( 4 ) ) ;
department . setName ( cursor . getString ( 5 ) ) ;
employee . setDepartment ( department ) ;
employees . add ( employee ) ;
}
return employees ;
}
// METHOD 2
// Uses SQLiteQueryBuilder to query multiple tables
/*public ArrayList<Employee> getEmployees() {
ArrayList<Employee> employees = new ArrayList<Employee>();
SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
queryBuilder
.setTables(DataBaseHelper.EMPLOYEE_TABLE
+ " INNER JOIN "
+ DataBaseHelper.DEPARTMENT_TABLE
+ " ON "
+ DataBaseHelper.EMPLOYEE_DEPARTMENT_ID
+ " = "
+ (DataBaseHelper.DEPARTMENT_TABLE + "." + DataBaseHelper.ID_COLUMN));
// Get cursor
Cursor cursor = queryBuilder.query(database, new String[] {
EMPLOYEE_ID_WITH_PREFIX,
DataBaseHelper.EMPLOYEE_TABLE + "."
+ DataBaseHelper.NAME_COLUMN,
DataBaseHelper.EMPLOYEE_DOB,
DataBaseHelper.EMPLOYEE_SALARY,
DataBaseHelper.EMPLOYEE_DEPARTMENT_ID,
DataBaseHelper.DEPARTMENT_TABLE + "."
+ DataBaseHelper.NAME_COLUMN }, null, null, null, null,
null);
while (cursor.moveToNext()) {
Employee employee = new Employee();
employee.setId(cursor.getInt(0));
employee.setName(cursor.getString(1));
try {
employee.setDateOfBirth(formatter.parse(cursor.getString(2)));
} catch (ParseException e) {
employee.setDateOfBirth(null);
}
employee.setSalary(cursor.getDouble(3));
Department department = new Department();
department.setId(cursor.getInt(4));
department.setName(cursor.getString(5));
employee.setDepartment(department);
employees.add(employee);
}
return employees;
}*/
}

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() .

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
package com . androidopentutorials . sqlite ;
import android . app . AlertDialog ;
import android . content . DialogInterface ;
import android . os . Bundle ;
import android . support . v4 . app . Fragment ;
import android . support . v4 . app . FragmentActivity ;
import android . support . v4 . app . FragmentManager ;
import android . support . v4 . app . FragmentTransaction ;
import android . view . Menu ;
import android . view . MenuItem ;
import com . androidopentutorials . sqlite . db . DepartmentDAO ;
import com . androidopentutorials . sqlite . fragment . CustomEmpDialogFragment . CustomEmpDialogFragmentListener ;
import com . androidopentutorials . sqlite . fragment . EmpAddFragment ;
import com . androidopentutorials . sqlite . fragment . EmpListFragment ;
public class MainActivity extends FragmentActivity implements
CustomEmpDialogFragmentListener {
private Fragment contentFragment ;
private EmpListFragment employeeListFragment ;
private EmpAddFragment employeeAddFragment ;
@ Override
protected void onCreate ( Bundle savedInstanceState ) {
super . onCreate ( savedInstanceState ) ;
setContentView ( R . layout . activity_main ) ;
FragmentManager fragmentManager = getSupportFragmentManager ( ) ;
DepartmentDAO deptDAO = new DepartmentDAO ( this ) ;
//Initially loads departments
if ( deptDAO . getDepartments ( ) . size ( ) <= 0 )
deptDAO . loadDepartments ( ) ;
/*
* This is called when orientation is changed.
*/
if ( savedInstanceState != null ) {
if ( savedInstanceState . containsKey ( "content" ) ) {
String content = savedInstanceState . getString ( "content" ) ;
if ( content . equals ( EmpAddFragment . ARG_ITEM_ID ) ) {
if ( fragmentManager
. findFragmentByTag ( EmpAddFragment . ARG_ITEM_ID ) != null ) {
setFragmentTitle ( R . string . add_emp ) ;
contentFragment = fragmentManager
. findFragmentByTag ( EmpAddFragment . ARG_ITEM_ID ) ;
}
}
}
if ( fragmentManager . findFragmentByTag ( EmpListFragment . ARG_ITEM_ID ) != null ) {
employeeListFragment = ( EmpListFragment ) fragmentManager
. findFragmentByTag ( EmpListFragment . ARG_ITEM_ID ) ;
contentFragment = employeeListFragment ;
}
} else {
employeeListFragment = new EmpListFragment ( ) ;
setFragmentTitle ( R . string . app_name ) ;
switchContent ( employeeListFragment , EmpListFragment . ARG_ITEM_ID ) ;
}
}
@ Override
public boolean onCreateOptionsMenu ( Menu menu ) {
getMenuInflater ( ) . inflate ( R . menu . main , menu ) ;
return true ;
}
@ Override
public boolean onOptionsItemSelected ( MenuItem item ) {
switch ( item . getItemId ( ) ) {
case R . id . action_add :
setFragmentTitle ( R . string . add_emp ) ;
employeeAddFragment = new EmpAddFragment ( ) ;
switchContent ( employeeAddFragment , EmpAddFragment . ARG_ITEM_ID ) ;
return true ;
}
return super . onOptionsItemSelected ( item ) ;
}
@ Override
protected void onSaveInstanceState ( Bundle outState ) {
if ( contentFragment instanceof EmpAddFragment ) {
outState . putString ( "content" , EmpAddFragment . ARG_ITEM_ID ) ;
} else {
outState . putString ( "content" , EmpListFragment . ARG_ITEM_ID ) ;
}
super . onSaveInstanceState ( outState ) ;
}
/*
* We consider EmpListFragment as the home fragment and it is not added to
* the back stack.
*/
public void switchContent ( Fragment fragment , String tag ) {
FragmentManager fragmentManager = getSupportFragmentManager ( ) ;
while ( fragmentManager . popBackStackImmediate ( ) )
;
if ( fragment != null ) {
FragmentTransaction transaction = fragmentManager
. beginTransaction ( ) ;
transaction . replace ( R . id . content_frame , fragment , tag ) ;
// Only EmpAddFragment is added to the back stack.
if ( ! ( fragment instanceof EmpListFragment ) ) {
transaction . addToBackStack ( tag ) ;
}
transaction . commit ( ) ;
contentFragment = fragment ;
}
}
protected void setFragmentTitle ( int resourseId ) {
setTitle ( resourseId ) ;
getActionBar ( ) . setTitle ( resourseId ) ;
}
/*
* We call super.onBackPressed(); when the stack entry count is > 0. if it
* is instanceof EmpListFragment or if the stack entry count is == 0, then
* we prompt the user whether to quit the app or not by displaying dialog.
* In other words, from EmpListFragment on back press it quits the app.
*/
@ Override
public void onBackPressed ( ) {
FragmentManager fm = getSupportFragmentManager ( ) ;
if ( fm . getBackStackEntryCount ( ) > 0 ) {
super . onBackPressed ( ) ;
} else if ( contentFragment instanceof EmpListFragment
|| fm . getBackStackEntryCount ( ) == 0 ) {
//finish();
//Shows an alert dialog on quit
onShowQuitDialog ( ) ;
}
}
public void onShowQuitDialog ( ) {
AlertDialog . Builder builder = new AlertDialog . Builder ( this ) ;
builder . setCancelable ( false ) ;
builder . setMessage ( "Do You Want To Quit?" ) ;
builder . setPositiveButton ( android . R . string . yes ,
new DialogInterface . OnClickListener ( ) {
@ Override
public void onClick ( DialogInterface dialog , int id ) {
finish ( ) ;
}
} ) ;
builder . setNegativeButton ( android . R . string . no ,
new DialogInterface . OnClickListener ( ) {
public void onClick ( DialogInterface dialog , int id ) {
dialog . cancel ( ) ;
}
} ) ;
builder . create ( ) . show ( ) ;
}
/*
* Callback used to communicate with EmpListFragment to notify the list adapter.
* Communication between fragments goes via their Activity class.
*/
@ Override
public void onFinishDialog ( ) {
if ( employeeListFragment != null ) {
employeeListFragment . updateView ( ) ;
}
}
}

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

Tags: android back navigation of fragments , Android Examples , Android orientation changes , Android Spinner Data from SQLite , Android SQLite , Android SQLite Date , Android SQLite Examples , android sqlite join , android sqlite multiple table join , Android Sqlite Multiple tables example , Android SQLiteQueryBuilder multiple table column alias , SQLite

About The Author

nithya

Related Posts

  • android-sqlite-featured-image Android SQLite Example
  • 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

    • Tan Woon How

      check this out http://instinctcoder.com/android-studio-sqlite-database-example/

  • ankit

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

  • avni patel

    I have followed everything and basic i have copied the whole code, bt i m getting error..
    java.lang.NullPointerException: Attempt to invoke virtual method ‘void android.app.ActionBar.setTitle(int)’ on a null object reference at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2416)
    this is the error i m getting

    Find us on Facebook

    Find us on Google Plus

    Top Posts

    • Android Image Slideshow using ViewPager - 280,287 views
    • Android: DatePickerDialog on EditText Click Event - 215,359 views
    • Android SharedPreferences Tutorial and Example - 156,358 views
    • Android: How to store list of values in SharedPreferences - 137,290 views
    • Android SQLite Join Multiple Tables Example - 125,155 views
    • Android Vertical ScrollBar Styling - 96,236 views
    • Android: How to get Holo Spinner Theme in Android 2.x? - 84,686 views
    • Android SQLite Example - 69,512 views
    • Android: ListView Fastscroll and its styling - 56,424 views
    • Android: ListView with Alphabetical side index - 54,741 views

    Categories

    • Android (2)
    • Dialog (1)
    • ImageView (1)
    • ListView (2)
    • SharedPreferences (2)
    • Spinner (1)
    • SQLite (2)
    • ViewPager (1)
    July 2020
    M T W T F S S
    « Mar
    1 2 3 4 5
    6 7 8 9 10 11 12
    13 14 15 16 17 18 19
    20 21 22 23 24 25 26
    27 28 29 30 31
  • Follow @opentutorials
  • Popular
  • Recent
  • Facebook
  • Google+
  • Twitter

Tags

11 Android Examples 2 Android ListView Examples 2 Android SharedPreferences example 2 Android SQLite Examples 2 SharedPreferences 2 Android SharedPreferences Tutorial 2 SQLite 2 Android SQLite 2 Android SQLite Date 2 Android orientation changes 2 android back navigation of fragments 2 imageview 1 android edittext onclick datepicker 1 Android ListView with index 1 Listview with alphabetic index 1 Android ListView Fastscroll 1 Android ListView Fastscroll styling 1 Android change background color of spinner 1 Android change spinner item highlight color 1 Android customize spinner styling 1 Android highlight selected item in spinner 1 Android holo theme for android 2.x 1 Android spinner change default highlight color 1 Android spinner styles 1 Android spinner styling 1 Android Styles and Themes example 1 Android Spinner Examples 1 Android SectionIndexer example 1 calendar datepickerdialog 1 edittext click event 1 edittext 1 calendar 1 datepickerdialog 1 json shared preferences 1 gson 1 Android Sqlite Multiple tables example 1 Android SQLiteQueryBuilder multiple table column alias 1 android animation 1 android imageview animation 1 android imageview blink animation 1 SharedPreferences store list 1 SharedPreferences store arraylist 1 Android SharedPreferences favorites 1 android sqlite join 1 android sqlite multiple table join 1 Android Spinner Data from SQLite 1 Android ScrollView 1 Android Scrollbar 1 ScrollView 1 Scrollbar 1 Android Scrollbar styling 1 styling 1 Android Vertical ScrollBar Styling 1 Android ViewPager Example 1 ViewPager 1 Android ViewPager 1 Android ViewPager Image Slideshow 1 Android ImageView Slideshow 1 universal image loader 1 viewpagerindicator 1 viewpager slideshow
Android Open Tutorials Copyright © 2020.
Back to Top ↑
  • Android
    • Dialog
    • ImageView
    • ListView
    • SharedPreferences
    • Spinner
    • SQLite
    • ViewPager