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

[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;
}
}
[/java]

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.

[java]

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) {

}

}

[/java]

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.

[java]

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;

}

[/java]

UPDATE

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

[java]

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;
}
[/java]

DELETE

Function deletes the row with the passed ID.

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

The complete DBHelper class looks like this.

[java]

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)});
}

}

[/java]

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.

[java]

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();
}
}
[/java]

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 *