CRUD Operations in SQLite using SQLiteOpenHelper – Android

By | June 26, 2016

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 *