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.