CRUD Operations in SQLite using SQLiteOpenHelper – Android

By | June 29, 2016
0 Comment

We do SQLite operations in Android by extending the SQLiteOpenHelper Class.

I will create an Employee table with three fields

id
name
company.

I have a model class same as the database table Employee.

Employee.java

public class Employee {
 
    public int id;
    public String name, company;
 
    public void setAll(int id, String name, String company) {
        this.id = id;
        this.name = name;
        this.company = company;
    }
}

Lets see How we can do the CRUD (Create, Read, Update and Delete) Operations.

CREATE

We create a class that extends the SQLiteOpenHelper.
Override the “onCreate” method to write the Table create Query.

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
 
import java.util.ArrayList;
import java.util.HashMap;
 
public class DBHelper extends SQLiteOpenHelper {
 
    public static final String TAG = "DBHelper";
 
    public static final String DATABASE_NAME = "EmpDB.db";
    public static final String EMP_TABLE_NAME = "EmpTable";
    public static final String EMP_COLUMN_ID = "id";
    public static final String EMP_COLUMN_NAME = "name";
    public static final String EMP_COLUMN_COMPANY = "company";
 
    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }
 
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(
                "CREATE TABLE IF NOT EXISTS " + EMP_TABLE_NAME + " (" + EMP_COLUMN_ID + " integer primary key, " + EMP_COLUMN_NAME + " text, " + EMP_COLUMN_COMPANY + " text)"
        );
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
    }
     
}

READ

The below function gets all records from the SQLite Database, but it has a parameter which is used to search the Database. if the search string is null then fetch all the records from the database.

public ArrayList<Employee> getAllEmployees(String searchName) {
  
        ArrayList<Employee> employeeList = new ArrayList<Employee>();
        String query;
 
        if (searchName == null) {
            query = "select * from "  + EMP_TABLE_NAME;
        } else
            query = "select * from " + EMP_TABLE_NAME  + " where name like '%" + searchName + "%'";
 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery(query, null);
        res.moveToFirst();
 
        while (res.isAfterLast() == false) {
 
            Employee emp = new Employee();
 
            int id = res.getInt(0);
            String name = res.getString(1);
            String company = res.getString(2);
 
            emp.setAll(id, name, company);
            employeeList.add(emp);
 
            res.moveToNext();
        }
        return employeeList;
         
}

UPDATE

Below function updates the table row belonging to a particular ID.

public boolean updateDB(Integer id, String name, String company) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("name", name);
    contentValues.put("company", company);
    db.update(EMP_TABLE_NAME, contentValues, "id = ? ", new String[]{Integer.toString(id)});
    return true;
}

DELETE

Function deletes the row with the passed ID.

public Integer deleteRow(Integer id) {
    SQLiteDatabase db = this.getWritableDatabase();
    return db.delete(EMP_TABLE_NAME, "id = ? ", new String[]{Integer.toString(id)});
}

The complete DBHelper class looks like this.

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
 
import java.util.ArrayList;
import java.util.HashMap;
 
public class DBHelper extends SQLiteOpenHelper {
 
    public static final String TAG = "DBHelper";
 
    public static final String DATABASE_NAME = "EmpDB.db";
    public static final String EMP_TABLE_NAME = "EmpTable";
    public static final String EMP_COLUMN_ID = "id";
    public static final String EMP_COLUMN_NAME = "name";
    public static final String EMP_COLUMN_COMPANY = "company";
 
    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }
 
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(
                "create table IF NOT EXISTS " + EMP_TABLE_NAME + " (id integer primary key, name text, company text)"
        );
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
    }
 
    /* Insert a row into the database */
    public boolean insert(String name, String company) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", name);
        contentValues.put("company", company);
        db.insert(EMP_TABLE_NAME, null, contentValues);
        return true;
    }
 
    /* Get the first row ID from the table */
    public int getFirstId() {
 
        int idToUpdate = 0;
        String query = "select id from " + EMP_TABLE_NAME + " LIMIT 1";
 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery(query, null);
 
        if (null != res && res.getCount() > 0) {
            res.moveToFirst();
            idToUpdate = res.getInt(0);
        }
        return idToUpdate;
    }
 
    /* Get all employees records from the table based on search criteria */
    public ArrayList<Employee> getAllEmployees(String searchName) {
  
        ArrayList<Employee> employeeList = new ArrayList<Employee>();
        String query;
 
        if (searchName == null) {
            query = "select * from "  + EMP_TABLE_NAME;
        } else
            query = "select * from " + EMP_TABLE_NAME  + " where name like '%" + searchName + "%'";
 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery(query, null);
        res.moveToFirst();
 
        while (res.isAfterLast() == false) {
 
            Employee emp = new Employee();
 
            int id = res.getInt(0);
            String name = res.getString(1);
            String company = res.getString(2);
 
            emp.setAll(id, name, company);
            employeeList.add(emp);
 
            res.moveToNext();
        }
        return employeeList;
         
   }
 
    /* Update the table row with ID - id */
    public boolean updateDB(Integer id, String name, String company) {
        Log.i(TAG, "Updating ID : " + id);
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", name);
        contentValues.put("company", company);
        db.update(EMP_TABLE_NAME, contentValues, "id = ? ", new String[]{Integer.toString(id)});
        return true;
    }
 
    /* Delete the row with ID - id from the employees table */
    public Integer deleteRow(Integer id) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(EMP_TABLE_NAME, "id = ? ", new String[]{Integer.toString(id)});
    }
 
}

Here is the MainActivity that implements the database functions.
Please create a layout xml with the below UI elements in the MainActivity and link them.

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.inputmethod.InputMethodManager;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;
 
import java.util.ArrayList;
 
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
 
    public static final String TAG = "MainActivity";
 
    TextView mTv;
    EditText empName, empCompany, edtSearch;
    Button btnInsert, btnUpdate, btnSearch, btnDelete, btnShowAll;
 
    private DBHelper myDb;
    ArrayList<Employee> allEmployees;
 
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
 
        // Edittext
        empName = (EditText) findViewById(R.id.empName);
        empCompany = (EditText) findViewById(R.id.empCompany);
        edtSearch = (EditText) findViewById(R.id.edtSearch);
 
        // Buttons
        btnInsert = (Button) findViewById(R.id.insert_btn);
        btnUpdate = (Button) findViewById(R.id.update_btn);
        btnSearch = (Button) findViewById(R.id.search_btn);
        btnDelete = (Button) findViewById(R.id.delete_btn);
        btnShowAll = (Button) findViewById(R.id.showall_btn);
 
        // TextView
        mTv = (TextView) findViewById(R.id.listTv);
 
        // Add Click Listeners
        btnInsert.setOnClickListener(this);
        btnUpdate.setOnClickListener(this);
        btnSearch.setOnClickListener(this);
        btnDelete.setOnClickListener(this);
        btnShowAll.setOnClickListener(this);
 
        // Create an instance of our DB Helper Class
        myDb = new DBHelper(this);
 
        allEmployees = myDb.getAllEmployees(null);
        printTable(allEmployees);
 
    }
 
    void printTable(ArrayList<Employee> allEmployees) {
 
        // Empty text in the TextView to show new values
        mTv.setText("");
 
        StringBuffer stringBuffer = new StringBuffer();
        // Append table values in a String buffer
        for (Employee emp : allEmployees) {
            stringBuffer.append("[ " + emp.id + " ] [ " + emp.name + " ] [ " + emp.company + " ]\n");
        }
 
        // Show the table values
        mTv.setText(stringBuffer);
    }
 
    @Override
    public void onClick(View v) {
 
        if (v == btnInsert) {
            String name = empName.getText().toString().trim();
            String company = empCompany.getText().toString().trim();
 
            if (name.length() > 0 && company.length() > 0) {
                myDb.insert(name, company);
                allEmployees = myDb.getAllEmployees(null);
            } else {
                showToastMessage("Name or Company is Empty");
            }
        }
 
        if (v == btnSearch) {
            String searchVal = edtSearch.getText().toString().trim();
            allEmployees = myDb.getAllEmployees(searchVal);
            Log.i(TAG, "Searched Size : " + allEmployees.size());
            if (allEmployees.size() == 0) {
                showToastMessage("No Results");
            }
        }
 
        if (v == btnDelete) {
            int idToDelete = myDb.getFirstId();
            if (idToDelete > 0) {
                myDb.deleteRow(idToDelete);
                allEmployees = myDb.getAllEmployees(null);
            }
        }
 
        if (v == btnUpdate) {
            int idToUpdate = myDb.getFirstId();
            if (idToUpdate > 0) {
                myDb.updateDB(idToUpdate, "Updated Name", "Updated Company");
                allEmployees = myDb.getAllEmployees(null);
            }
        }
 
        if (v == btnShowAll) {
            edtSearch.setText("");
            allEmployees = myDb.getAllEmployees(null);
        }
 
        hideKeyBoard();
        printTable(allEmployees);
 
    }
 
    void hideKeyBoard() {
        // Check if no view has focus:
        View view = this.getCurrentFocus();
        if (view != null) {
            InputMethodManager imm = (InputMethodManager) getSystemService(INPUT_METHOD_SERVICE);
            imm.hideSoftInputFromWindow(view.getWindowToken(), 0);
        }
    }
 
    void showToastMessage(String message) {
        Toast.makeText(this, message, Toast.LENGTH_LONG).show();
    }
}

All Done.

Please make your layout with the buttons and textviews mentioned in the MainActivity.

You can send your queries and comments to coderzheaven@gmail.com.

Leave a Reply

Your email address will not be published. Required fields are marked *