IT_Programming/Android_Java

[펌] Android SQLite Join Multiple Tables Example

JJun ™ 2014. 4. 28. 23:22

* 출처

: http://androidopentutorials.com/android-sqlite-join-multiple-tables-example/

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

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

department table

Field	Type	Key	Extra
id	int	Primary Key	auto_increment
name	text

Android Project

Create a new Android project and name it as AndroidSQLiteMultipleTables.

Download “Android SQLite Multiple Tables Example” AndroidSQLiteMultipleTables.zip – Downloaded 9252 times – 2 MB

Resources

colors.xml

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

<?xml version="1.0" encoding="utf-8"?>

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

<?xml version="1.0" encoding="utf-8"?>

<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="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 EmpAddFragmentwhich is used to add an employee record to SQLite database.

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

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

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

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

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

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.

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.

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.

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.

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.

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.

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,

By calling rawQuery() from SQLiteDatabase class which accepts SQL select statement as input. In the below code, Method 1 uses rawQuery().
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.

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.

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.

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.

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.

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.

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

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

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

Update a row – CustomEmpDialogFragment

Fetch all rows – EmpListFragment

저작자표시 비영리 변경금지

'IT_Programming > Android_Java' 카테고리의 다른 글

[펌] Android application 설치 완료 Broadcast 받기 (0)	2014.05.06
[android] CheckBox에서 image와 text 간격 (0)	2014.05.02
[안드로이드 라이브러리] 앱이 크래시가 일어 났을 때 로그를 저장하는 법 (0)	2014.04.22
[펌] 웹뷰 테스트는 하고 릴리즈하는거니? (0)	2014.04.20
android 버전 4.x 이상 버전에서 발생하는 WebView에서 동영상 재생시 전체화면모드에서 발생하는 오류 해결법 (0)	2014.04.20

현재글[펌] Android SQLite Join Multiple Tables Example

삶의 모든 순간을 즐겨라!

Today :
Yesterday :

« 2025/01 »
일	월	화	수	목	금	토
			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

[펌] Android SQLite Join Multiple Tables Example

Android SQLite Join Multiple Tables Example

Project Description:

Table Structure

Android Project

Resources

colors.xml

strings.xml

Action Bar Menu Item (main.xml)

Layout files

activity_main.xml

fragment_add_emp.xml

fragment_emp_list.xml

list_item.xml

Create a SQLite database in Android

DataBaseHelper class

Employee DB DAO class

Insert a new record in SQLite database

Department

Employee

DepartmentDAO class

Save Employee

Add Employee Fragment

Fetch records from SQLite database

EmployeeDAO class

Employee List Adapter

Display Database Records in ListView – Employee List Fragment

Update a row in SQLite database

EmployeeDAO class

CustomEmpDialogFragment class

Delete a row from SQLite database

EmployeeDAO class

Complete EmployeeDAO class

MainActivity class

Output:

Insert a row – EmpAddFragment

Update a row – CustomEmpDialogFragment

Fetch all rows – EmpListFragment

'IT_Programming > Android_Java' 카테고리의 다른 글

'IT_Programming/Android_Java'의 다른글

관련글

티스토리툴바