Android SQlite Database Tutorial

SQLite is an Open-Source embedded SQL database engine. This provides relational database management structure for storing user defined records in the form of tables. SQLite is light weight when it comes to resource usage and it doesn’t need to have any server setup like other RDBMS systems. It is an cross platform and self-contained database. SQLite is one way of storing user data. SQLite is a very light weight database which comes with Android OS .
Android is shipped with SQLite version 3.4.0.
In this tutorial I am taking an example of storing Employee record in SQLite database. I am using a table called Employee to store Employee record. This table contains three columns emp_id (INTEGER), emp_name (TEXT), emp_phone_number (TEXT).

Step 1) Create Employee model class

write Employee class with all getter and setter methods to maintain single contact as an object.

package com.androidtutorialshub.sqlitedatabaseexample; public class Employee { private int empId; private String empName; private String empPhoneNo; public Employee() { } public Employee(String empName, String empPhoneNo) { this.empName = empName; this.empPhoneNo = empPhoneNo; } public int getEmpId() { return empId; } public void setEmpId(int empId) { this.empId = empId; } public String getEmpName() { return empName; } public void setEmpName(String empName) { this.empName = empName; } public String getEmpPhoneNo() { return empPhoneNo; } public void setEmpPhoneNo(String empPhoneNo) { this.empPhoneNo = empPhoneNo; } }

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

package com.androidtutorialshub.sqlitedatabaseexample; public class Employee {     private int empId;     private String empName;     private String empPhoneNo;     public Employee() {     }     public Employee(String empName, String empPhoneNo) {         this.empName = empName;         this.empPhoneNo = empPhoneNo;     }     public int getEmpId() {         return empId;     }     public void setEmpId(int empId) {         this.empId = empId;     }     public String getEmpName() {         return empName;     }     public void setEmpName(String empName) {         this.empName = empName;     }     public String getEmpPhoneNo() {         return empPhoneNo;     }     public void setEmpPhoneNo(String empPhoneNo) {         this.empPhoneNo = empPhoneNo;     } }

Step 2) Create DatabaseHelper class

Create a class named DatabaseHelper and extends it with SQLiteOpenHelper .After extending your class from SQLiteOpenHelper you need to override two methods onCreate() and onUpgrade()
onCreate() – This is called when database is created and in this method we create the employee table
onUpgrade() – This is called when database is upgraded like modifying the table structure, adding constraints to database etc.

public class DatabaseHelper extends SQLiteOpenHelper { // Database Version private static final int DATABASE_VERSION = 1; // Database Name private static final String DATABASE_NAME = "EmployeeManager"; // Employee table name private static final String TABLE_EMPLOYEE = "employee"; // Employee Table Columns names private static final String KEY_EMP_ID = "emp_id"; private static final String KEY_EMP_NAME = "emp_name"; private static final String KEY_EMP_PH_NO = "emp_phone_number"; private String CREATE_EMPLOYEE_TABLE = "CREATE TABLE " + TABLE_EMPLOYEE + "(" + KEY_EMP_ID + " INTEGER PRIMARY KEY," + KEY_EMP_NAME + " TEXT," + KEY_EMP_PH_NO + " TEXT" + ")"; private String DROP_EMPLOYEE_TABLE = "DROP TABLE IF EXISTS " + CREATE_EMPLOYEE_TABLE; public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_EMPLOYEE_TABLE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //Drop Employee Table if exist db.execSQL(DROP_EMPLOYEE_TABLE); // Create tables again onCreate(db); } }

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

public class DatabaseHelper extends SQLiteOpenHelper {     // Database Version     private static final int DATABASE_VERSION = 1;     // Database Name     private static final String DATABASE_NAME = "EmployeeManager";     // Employee table name     private static final String TABLE_EMPLOYEE = "employee";     // Employee Table Columns names     private static final String KEY_EMP_ID = "emp_id";     private static final String KEY_EMP_NAME = "emp_name";     private static final String KEY_EMP_PH_NO = "emp_phone_number";     private String CREATE_EMPLOYEE_TABLE = "CREATE TABLE " + TABLE_EMPLOYEE + "("             + KEY_EMP_ID + " INTEGER PRIMARY KEY," + KEY_EMP_NAME + " TEXT,"             + KEY_EMP_PH_NO + " TEXT" + ")";     private String DROP_EMPLOYEE_TABLE = "DROP TABLE IF EXISTS " + CREATE_EMPLOYEE_TABLE;     public DatabaseHelper(Context context) {         super(context, DATABASE_NAME, null, DATABASE_VERSION);     }     @Override     public void onCreate(SQLiteDatabase db) {         db.execSQL(CREATE_EMPLOYEE_TABLE);     }     @Override     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {         //Drop Employee Table if exist         db.execSQL(DROP_EMPLOYEE_TABLE);         // Create tables again         onCreate(db);     } }

Step 3) Add All CRUD Operations (Create, Read, Update and Delete)

We need to write all operations that are required to read and write database .Add the following code snippets in DatabaseHelper class
-> Insterting New Employee Record

//Create employee record public void addEmployee(Employee employee){ SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_EMP_NAME, employee.getEmpName()); values.put(KEY_EMP_PH_NO, employee.getEmpPhoneNo()); // Inserting Row db.insert(TABLE_EMPLOYEE, null, values); db.close(); }

1 2 3 4 5 6 7 8 9 10 11 12

//Create employee record     public void addEmployee(Employee employee){         SQLiteDatabase db = this.getWritableDatabase();         ContentValues values = new ContentValues();         values.put(KEY_EMP_NAME, employee.getEmpName());         values.put(KEY_EMP_PH_NO, employee.getEmpPhoneNo());         // Inserting Row         db.insert(TABLE_EMPLOYEE, null, values);         db.close();     }

-> Reading All Employee Records

//Read employee record //Read employee record public ArrayList<employee> getAllEmployee() { ArrayList<employee> employeeList = new ArrayList<employee>(); // Select All Query String selectQuery = "SELECT * FROM " + TABLE_EMPLOYEE; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); // Traversing through all rows and adding to list if (cursor.moveToFirst()) { do { Employee employee = new Employee(); employee.setEmpId(Integer.parseInt(cursor.getString(0))); employee.setEmpName(cursor.getString(1)); employee.setEmpPhoneNo(cursor.getString(2)); // Adding employee record to list employeeList.add(employee); } while (cursor.moveToNext()); } // return employee list return employeeList; }

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

//Read employee record     //Read employee record     public ArrayList<Employee> getAllEmployee() {         ArrayList<Employee> employeeList = new ArrayList<Employee>();         // Select All Query         String selectQuery = "SELECT  * FROM " + TABLE_EMPLOYEE;         SQLiteDatabase db = this.getWritableDatabase();         Cursor cursor = db.rawQuery(selectQuery, null);         // Traversing through all rows and adding to list         if (cursor.moveToFirst()) {             do {                 Employee employee = new Employee();                 employee.setEmpId(Integer.parseInt(cursor.getString(0)));                 employee.setEmpName(cursor.getString(1));                 employee.setEmpPhoneNo(cursor.getString(2));                 // Adding employee record to list                 employeeList.add(employee);             } while (cursor.moveToNext());         }         // return employee list         return employeeList;     }

-> Update Employee Record

//Update employee record public int updateEmployee(Employee employee) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_EMP_NAME, employee.getEmpName()); values.put(KEY_EMP_PH_NO, employee.getEmpPhoneNo()); // updating row return db.update(TABLE_EMPLOYEE, values, KEY_EMP_ID + " = ?", new String[] { String.valueOf(employee.getEmpId()) }); }

1 2 3 4 5 6 7 8 9 10 11 12

//Update employee record     public int updateEmployee(Employee employee) {         SQLiteDatabase db = this.getWritableDatabase();         ContentValues values = new ContentValues();         values.put(KEY_EMP_NAME, employee.getEmpName());         values.put(KEY_EMP_PH_NO, employee.getEmpPhoneNo());         // updating row         return db.update(TABLE_EMPLOYEE, values, KEY_EMP_ID + " = ?",                 new String[] { String.valueOf(employee.getEmpId()) });     }

-> Delete Employee Record

//Delete employee record public void deleteEmployee(Employee employee) { SQLiteDatabase db = this.getWritableDatabase(); db.delete(TABLE_EMPLOYEE, KEY_EMP_ID + " = ?", new String[]{String.valueOf(employee.getEmpId())}); db.close(); }

1 2 3 4 5 6 7

//Delete employee record     public void deleteEmployee(Employee employee) {         SQLiteDatabase db = this.getWritableDatabase();         db.delete(TABLE_EMPLOYEE, KEY_EMP_ID + " = ?",                 new String[]{String.valueOf(employee.getEmpId())});         db.close();     }

Complete DatabaseHelper Class

package com.androidtutorialshub.sqlitedatabaseexample; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import java.util.ArrayList; public class DatabaseHelper extends SQLiteOpenHelper { // Database Version private static final int DATABASE_VERSION = 1; // Database Name private static final String DATABASE_NAME = "EmployeeManager"; // Employee table name private static final String TABLE_EMPLOYEE = "employee"; // Employee Table Columns names private static final String KEY_EMP_ID = "emp_id"; private static final String KEY_EMP_NAME = "emp_name"; private static final String KEY_EMP_PH_NO = "emp_phone_number"; private String CREATE_EMPLOYEE_TABLE = "CREATE TABLE " + TABLE_EMPLOYEE + "(" + KEY_EMP_ID + " INTEGER PRIMARY KEY," + KEY_EMP_NAME + " TEXT," + KEY_EMP_PH_NO + " TEXT" + ")"; private String DROP_EMPLOYEE_TABLE = "DROP TABLE IF EXISTS " + CREATE_EMPLOYEE_TABLE; public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_EMPLOYEE_TABLE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //Drop Employee Table if exist db.execSQL(DROP_EMPLOYEE_TABLE); // Create tables again onCreate(db); } //Create employee record public void addEmployee(Employee employee){ SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_EMP_NAME, employee.getEmpName()); values.put(KEY_EMP_PH_NO, employee.getEmpPhoneNo()); // Inserting Row db.insert(TABLE_EMPLOYEE, null, values); db.close(); } //Read employee record public ArrayList<employee> getAllEmployee() { ArrayList<employee> employeeList = new ArrayList<employee>(); // Select All Query String selectQuery = "SELECT * FROM " + TABLE_EMPLOYEE; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); // Traversing through all rows and adding to list if (cursor.moveToFirst()) { do { Employee employee = new Employee(); employee.setEmpId(Integer.parseInt(cursor.getString(0))); employee.setEmpName(cursor.getString(1)); employee.setEmpPhoneNo(cursor.getString(2)); // Adding employee record to list employeeList.add(employee); } while (cursor.moveToNext()); } // return employee list return employeeList; } //Update employee record public int updateEmployee(Employee employee) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_EMP_NAME, employee.getEmpName()); values.put(KEY_EMP_PH_NO, employee.getEmpPhoneNo()); // updating row return db.update(TABLE_EMPLOYEE, values, KEY_EMP_ID + " = ?", new String[] { String.valueOf(employee.getEmpId()) }); } //Delete employee record public void deleteEmployee(Employee employee) { SQLiteDatabase db = this.getWritableDatabase(); db.delete(TABLE_EMPLOYEE, KEY_EMP_ID + " = ?", new String[]{String.valueOf(employee.getEmpId())}); db.close(); } }

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

package com.androidtutorialshub.sqlitedatabaseexample; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import java.util.ArrayList; public class DatabaseHelper extends SQLiteOpenHelper {     // Database Version     private static final int DATABASE_VERSION = 1;     // Database Name     private static final String DATABASE_NAME = "EmployeeManager";     // Employee table name     private static final String TABLE_EMPLOYEE = "employee";     // Employee Table Columns names     private static final String KEY_EMP_ID = "emp_id";     private static final String KEY_EMP_NAME = "emp_name";     private static final String KEY_EMP_PH_NO = "emp_phone_number";     private String CREATE_EMPLOYEE_TABLE = "CREATE TABLE " + TABLE_EMPLOYEE + "("             + KEY_EMP_ID + " INTEGER PRIMARY KEY," + KEY_EMP_NAME + " TEXT,"             + KEY_EMP_PH_NO + " TEXT" + ")";     private String DROP_EMPLOYEE_TABLE = "DROP TABLE IF EXISTS " + CREATE_EMPLOYEE_TABLE;     public DatabaseHelper(Context context) {         super(context, DATABASE_NAME, null, DATABASE_VERSION);     }     @Override     public void onCreate(SQLiteDatabase db) {         db.execSQL(CREATE_EMPLOYEE_TABLE);     }     @Override     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {         //Drop Employee Table if exist         db.execSQL(DROP_EMPLOYEE_TABLE);         // Create tables again         onCreate(db);     }    //Create employee record     public void addEmployee(Employee employee){         SQLiteDatabase db = this.getWritableDatabase();         ContentValues values = new ContentValues();         values.put(KEY_EMP_NAME, employee.getEmpName());         values.put(KEY_EMP_PH_NO, employee.getEmpPhoneNo());         // Inserting Row         db.insert(TABLE_EMPLOYEE, null, values);         db.close();     }    //Read employee record     public ArrayList<Employee> getAllEmployee() {         ArrayList<Employee> employeeList = new ArrayList<Employee>();         // Select All Query         String selectQuery = "SELECT  * FROM " + TABLE_EMPLOYEE;         SQLiteDatabase db = this.getWritableDatabase();         Cursor cursor = db.rawQuery(selectQuery, null);         // Traversing through all rows and adding to list         if (cursor.moveToFirst()) {             do {                 Employee employee = new Employee();                 employee.setEmpId(Integer.parseInt(cursor.getString(0)));                 employee.setEmpName(cursor.getString(1));                 employee.setEmpPhoneNo(cursor.getString(2));                 // Adding employee record to list                 employeeList.add(employee);             } while (cursor.moveToNext());         }         // return employee list         return employeeList;     }     //Update employee record     public int updateEmployee(Employee employee) {         SQLiteDatabase db = this.getWritableDatabase();         ContentValues values = new ContentValues();         values.put(KEY_EMP_NAME, employee.getEmpName());         values.put(KEY_EMP_PH_NO, employee.getEmpPhoneNo());         // updating row         return db.update(TABLE_EMPLOYEE, values, KEY_EMP_ID + " = ?",                 new String[] { String.valueOf(employee.getEmpId()) });     }     //Delete employee record     public void deleteEmployee(Employee employee) {         SQLiteDatabase db = this.getWritableDatabase();         db.delete(TABLE_EMPLOYEE, KEY_EMP_ID + " = ?",                 new String[]{String.valueOf(employee.getEmpId())});         db.close();     } }

Step 4) Using DatabaseHelper in MainActivity.class

package com.androidtutorialshub.sqlitedatabaseexample; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.util.Log; import java.util.List; public class MainActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); DatabaseHelper db = new DatabaseHelper(this); // Inserting Contacts Log.d("Insert: ", "Inserting."); db.addEmployee(new Employee("Akanksha", "9988998899")); db.addEmployee(new Employee("Lalit", "888888888")); db.addEmployee(new Employee("Ayush", "9876543210")); db.addEmployee(new Employee("Nitul", "9977997799")); // Reading all contacts Log.d("Reading: ", "Reading all Employee Records .."); List<employee> employees = db.getAllEmployee(); for (Employee employee : employees) { String log = "Employee Id: " + employee.getEmpId() + " ,Employee Name: " + employee.getEmpName() + " ,Employee Phone: " + employee.getEmpPhoneNo(); // Writing Contacts to log Log.d("Name: ", log); } } }

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

package com.androidtutorialshub.sqlitedatabaseexample; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.util.Log; import java.util.List; public class MainActivity extends AppCompatActivity {     @Override     protected void onCreate(Bundle savedInstanceState) {         super.onCreate(savedInstanceState);         setContentView(R.layout.activity_main);         DatabaseHelper db = new DatabaseHelper(this);         // Inserting Contacts         Log.d("Insert: ", "Inserting.");         db.addEmployee(new Employee("Akanksha", "9988998899"));         db.addEmployee(new Employee("Lalit", "888888888"));         db.addEmployee(new Employee("Ayush", "9876543210"));         db.addEmployee(new Employee("Nitul", "9977997799"));         // Reading all contacts         Log.d("Reading: ", "Reading all Employee Records ..");         List<Employee> employees = db.getAllEmployee();         for (Employee employee : employees) {             String log = "Employee Id: " + employee.getEmpId() +                     " ,Employee Name: " + employee.getEmpName() +                     " ,Employee Phone: " + employee.getEmpPhoneNo();             // Writing Contacts to log             Log.d("Name: ", log);         }     } }

Share on Google Plus

About Unknown