CRUD Operation using SQLiteOpenHelper Tutorial
Hey, Developers! In last tutorial we learned about the basic operation of CRUD in the SQLite database. However, the method that we used in the previous tutorial is not recommended. Rather, we should use the SQLiteOpenHelper class. And that's why we've got this SQLiteOpenHelper Tutorial. We will do the same thing we did in the last tutorial, but with SQLiteOpenHelper class.
If you came to this tutorial straight, then it is extremely recommended that you first go to the earlier tutorial, as I'm going to work on the last project here, so you need to get the last project before you begin.
From the link given below you can visit the previous tutorial.
CRUD Operation Using SQLiteDatabase
What is SQLiteOpenHelper?
It is a class found within the package android.database.sqlite. It is a helping class that helps to create the database, manage the operations and also manage the version.
We will create a class to use SqliteOpenHelper, and then extend SQLiteOpenHelper within the class we created.
Creating and Managing Database with SQLiteOpenHelper
- So open the project that we developed in the last tutorial.
- Now generate a class, you can name it anything, and we've provided the name as DatabaseManager.
- Within this class we need to override the process onCreate() and onUpgrade(). We will generate the necessary tables inside onCreate() and we can upgrade the database inside onUpgrade() (like we can add more tables).
- We will also describe the techniques for all the operations that we want to perform in our database.
- Below is my DatabaseManager class with all the CRUD performance techniques. The database is the same as in the past tutorial.
package com.ukacademe.crudoperation;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by UKAcademe on 27/06/2019.
*/
//The class is extending SQLiteOpenHelper
public class DatabaseManager extends SQLiteOpenHelper {
/*
* This time we will not be using the hardcoded string values
* Instead here we are defining all the Strings that is required for our database
* for example databasename, table name and column names.
* */
private static final String DATABASE_NAME = "EmployeesDatabase";
private static final int DATABASE_VERSION = 1;
private static final String TABLE_NAME = "employees";
private static final String COLUMN_ID = "id";
private static final String COLUMN_NAME = "name";
private static final String COLUMN_DEPT = "department";
private static final String COLUMN_JOIN_DATE = "joiningdate";
private static final String COLUMN_SALARY = "salary";
/*
* We need to call the super i.e. parent class constructur
* And we need to pass 4 parameters
* 1. Context context -> It is the context object we will get it from the activity while creating the instance
* 2. String databasename -> It is the name of the database and here we are passing the constant that we already defined
* 3. CursorFactory cursorFactory -> If we want a cursor to be initialized on the creation we can use cursor factory, it is optionall and that is why we passed null here
* 4. int version -> It is an int defining our database version
* */
DatabaseManager(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
/*
* The query to create our table
* It is same as we had in the previous post
* The only difference here is we have changed the
* hardcoded string values with String Variables
* */
String sql = "CREATE TABLE " + TABLE_NAME + " (\n" +
" " + COLUMN_ID + " INTEGER NOT NULL CONSTRAINT employees_pk PRIMARY KEY AUTOINCREMENT,\n" +
" " + COLUMN_NAME + " varchar(200) NOT NULL,\n" +
" " + COLUMN_DEPT + " varchar(200) NOT NULL,\n" +
" " + COLUMN_JOIN_DATE + " datetime NOT NULL,\n" +
" " + COLUMN_SALARY + " double NOT NULL\n" +
");";
/*
* Executing the string to create the table
* */
sqLiteDatabase.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
/*
* We are doing nothing here
* Just dropping and creating the table
* */
String sql = "DROP TABLE IF EXISTS " + TABLE_NAME + ";";
sqLiteDatabase.execSQL(sql);
onCreate(sqLiteDatabase);
}
/*
* CREATE OPERATION
* ====================
* This is the first operation of the CRUD.
* This method will create a new employee in the table
* Method is taking all the parameters required
*
* Operation is very simple, we just need a content value objects
* Inside this object we will put everything that we want to insert.
* So each value will take the column name and the value that is to inserted
* for the column name we are using the String variables that we defined already
* And that is why we converted the hardcoded string to variables
*
* Once we have the contentValues object with all the values required
* We will call the method getWritableDatabase() and it will return us the SQLiteDatabase object and we can write on the database using it.
*
* With this object we will call the insert method it takes 3 parameters
* 1. String -> The table name where the value is to be inserted
* 2. String -> The default values of null columns, it is null here as we don't have any default values
* 3. ContentValues -> The values that is to be inserted
*
* insert() will return the inserted row id, if there is some error inserting the row
* it will return -1
*
* So here we are returning != -1, it will be true of record is inserted and false if not inserted
* */
boolean addEmployee(String name, String dept, String joiningdate, double salary) {
ContentValues contentValues = new ContentValues();
contentValues.put(COLUMN_NAME, name);
contentValues.put(COLUMN_DEPT, dept);
contentValues.put(COLUMN_JOIN_DATE, joiningdate);
contentValues.put(COLUMN_SALARY, salary);
SQLiteDatabase db = getWritableDatabase();
return db.insert(TABLE_NAME, null, contentValues) != -1;
}
/*
* READ OPERATION
* =================
* Here we are reading values from the database
* First we called the getReadableDatabase() method it will return us the SQLiteDatabase instance
* but using it we can only perform the read operations.
*
* We are running rawQuery() method by passing the select query.
* rawQuery takes two parameters
* 1. The query
* 2. String[] -> Arguments that is to be binded -> We use it when we have a where clause in our query to bind the where value
*
* rawQuery returns a Cursor object having all the data fetched from database
* */
Cursor getAllEmployees() {
SQLiteDatabase db = getReadableDatabase();
return db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
}
/*
* UPDATE OPERATION
* ==================
* Here we are performing the update operation. The proecess is same as the Create operation.
* We are first getting a database instance using getWritableDatabase() method as the operation we need to perform is a write operation
* Then we have the contentvalues object with the new values
*
* to update the row we use update() method. It takes 4 parameters
* 1. String -> It is the table name
* 2. ContentValues -> The new values
* 3. String -> Here we pass the column name = ?, the column we want to use for putting the where clause
* 4. String[] -> The values that is to be binded with the where clause
* */
boolean updateEmployee(int id, String name, String dept, double salary) {
SQLiteDatabase db = getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put(COLUMN_NAME, name);
contentValues.put(COLUMN_DEPT, dept);
contentValues.put(COLUMN_SALARY, salary);
return db.update(TABLE_NAME, contentValues, COLUMN_ID + "=?", new String[]{String.valueOf(id)}) == 1;
}
/*
* DELETE OPERATION
* ======================
*
* This is the last delete operation. To delete again we need a writable database using getWritableDatabase()
* Then we will call the delete method. It takes 3 parameters
* 1. String -> Table name
* 2. String -> The where clause passed as columnname = ?
* 3. String[] -> The values to be binded on the where clause
* */
boolean deleteEmployee(int id) {
SQLiteDatabase db = getWritableDatabase();
return db.delete(TABLE_NAME, COLUMN_ID + "=?", new String[]{String.valueOf(id)}) == 1;
}
}
Modifying the Last Project
- In the last tutorial we've had the following classes.
- Employee.java => The model class to store employee. We do not need any modifications here.
- EmployeeActivity .java => The activity where all the employees are displayed.
- EmployeeAdapter.java =>The custom list adapter for our list of employees.
- MainActivity.java => We're inserting employee into the database here.
- We will change EmployeeActivity.java, EmployeeAdapter.java and MainActivity.java.
MainActivity.Java
Creating the Employee
Let's begin with MainActivity.java. We have added comments on the lines that have been modified or added.
package com.ukacademe.crudoperation;
import android.content.Intent;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.Toast;
import java.text.SimpleDateFormat;
import java.util.Calendar;
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
EditText editTextName, editTextSalary;
Spinner spinnerDept;
//We creating our DatabaseManager object
DatabaseManager mDatabase;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//initializing the database manager object
mDatabase = new DatabaseManager(this);
editTextName = (EditText) findViewById(R.id.editTextName);
editTextSalary = (EditText) findViewById(R.id.editTextSalary);
spinnerDept = (Spinner) findViewById(R.id.spinnerDepartment);
findViewById(R.id.buttonAddEmployee).setOnClickListener(this);
findViewById(R.id.textViewViewEmployees).setOnClickListener(this);
}
private void addEmployee() {
String name = editTextName.getText().toString().trim();
String salary = editTextSalary.getText().toString().trim();
String dept = spinnerDept.getSelectedItem().toString();
if (name.isEmpty()) {
editTextName.setError("Name can't be empty");
editTextName.requestFocus();
return;
}
if (salary.isEmpty()) {
editTextSalary.setError("Salary can't be empty");
editTextSalary.requestFocus();
return;
}
Calendar cal = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss");
String joiningDate = sdf.format(cal.getTime());
//adding the employee with the DatabaseManager instance
if (mDatabase.addEmployee(name, dept, joiningDate, Double.parseDouble(salary)))
Toast.makeText(this, "Employee Added", Toast.LENGTH_SHORT).show();
else
Toast.makeText(this, "Could not add employee", Toast.LENGTH_SHORT).show();
}
@Override
public void onClick(View view) {
switch (view.getId()) {
case R.id.buttonAddEmployee:
addEmployee();
break;
case R.id.textViewViewEmployees:
startActivity(new Intent(this, EmployeeActivity.class));
break;
}
}
}
EmployeeActivity.Java
Reading All Employees
Come to EmployeeActivity.java now.
package com.ukacademe.crudoperation;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.widget.ListView;
import java.util.ArrayList;
import java.util.List;
public class EmployeeActivity extends AppCompatActivity {
List employeeList;
ListView listView;
//The databasemanager object
DatabaseManager mDatabase;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_employee);
//Instantiating the database manager object
mDatabase = new DatabaseManager(this);
employeeList = new ArrayList<>();
listView = (ListView) findViewById(R.id.listViewEmployees);
loadEmployeesFromDatabase();
}
private void loadEmployeesFromDatabase() {
//we are here using the DatabaseManager instance to get all employees
Cursor cursor = mDatabase.getAllEmployees();
if (cursor.moveToFirst()) {
do {
employeeList.add(new Employee(
cursor.getInt(0),
cursor.getString(1),
cursor.getString(2),
cursor.getString(3),
cursor.getDouble(4)
));
} while (cursor.moveToNext());
//passing the databasemanager instance this time to the adapter
EmployeeAdapter adapter = new EmployeeAdapter(this, R.layout.list_layout_employee, employeeList, mDatabase);
listView.setAdapter(adapter);
}
}
}
EmployeeAdapter.Java
Updating and Deleting the Employees
Finally, come inside EmployeeAdapter.java and modify it as below.
package com.ukacademe.crudoperation;
import android.content.Context;
import android.content.DialogInterface;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.annotation.NonNull;
import android.support.annotation.Nullable;
import android.support.v7.app.AlertDialog;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;
import java.util.List;
/**
* Created by UK Academe on 27/06/2019.
*/
public class EmployeeAdapter extends ArrayAdapter {
Context mCtx;
int layoutRes;
List employeeList;
//the databasemanager object
DatabaseManager mDatabase;
//modified the constructor and we are taking the DatabaseManager instance here
public EmployeeAdapter(Context mCtx, int layoutRes, List employeeList, DatabaseManager mDatabase) {
super(mCtx, layoutRes, employeeList);
this.mCtx = mCtx;
this.layoutRes = layoutRes;
this.employeeList = employeeList;
this.mDatabase = mDatabase;
}
@NonNull
@Override
public View getView(int position, @Nullable View convertView, @NonNull ViewGroup parent) {
LayoutInflater inflater = LayoutInflater.from(mCtx);
View view = inflater.inflate(layoutRes, null);
TextView textViewName = view.findViewById(R.id.textViewName);
TextView textViewDept = view.findViewById(R.id.textViewDepartment);
TextView textViewSalary = view.findViewById(R.id.textViewSalary);
TextView textViewJoinDate = view.findViewById(R.id.textViewJoiningDate);
final Employee employee = employeeList.get(position);
textViewName.setText(employee.getName());
textViewDept.setText(employee.getDept());
textViewSalary.setText(String.valueOf(employee.getSalary()));
textViewJoinDate.setText(employee.getJoiningDate());
view.findViewById(R.id.buttonDeleteEmployee).setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
deleteEmployee(employee);
}
});
view.findViewById(R.id.buttonEditEmployee).setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
updateEmployee(employee);
}
});
return view;
}
private void updateEmployee(final Employee employee) {
AlertDialog.Builder builder = new AlertDialog.Builder(mCtx);
LayoutInflater inflater = LayoutInflater.from(mCtx);
View view = inflater.inflate(R.layout.dialog_update_employee, null);
builder.setView(view);
final AlertDialog alertDialog = builder.create();
alertDialog.show();
final EditText editTextName = view.findViewById(R.id.editTextName);
final EditText editTextSalary = view.findViewById(R.id.editTextSalary);
final Spinner spinner = view.findViewById(R.id.spinnerDepartment);
editTextName.setText(employee.getName());
editTextSalary.setText(String.valueOf(employee.getSalary()));
view.findViewById(R.id.buttonUpdateEmployee).setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String name = editTextName.getText().toString().trim();
String salary = editTextSalary.getText().toString().trim();
String dept = spinner.getSelectedItem().toString().trim();
if (name.isEmpty()) {
editTextName.setError("Name can't be empty");
editTextName.requestFocus();
return;
}
if (salary.isEmpty()) {
editTextSalary.setError("Salary can't be empty");
editTextSalary.requestFocus();
return;
}
//calling the update method from database manager instance
if (mDatabase.updateEmployee(employee.getId(), name, dept, Double.valueOf(salary))) {
Toast.makeText(mCtx, "Employee Updated", Toast.LENGTH_SHORT).show();
loadEmployeesFromDatabaseAgain();
}
alertDialog.dismiss();
}
});
}
private void deleteEmployee(final Employee employee) {
AlertDialog.Builder builder = new AlertDialog.Builder(mCtx);
builder.setTitle("Are you sure?");
builder.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialogInterface, int i) {
//calling the delete method from the database manager instance
if (mDatabase.deleteEmployee(employee.getId()))
loadEmployeesFromDatabaseAgain();
}
});
builder.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialogInterface, int i) {
}
});
AlertDialog alertDialog = builder.create();
alertDialog.show();
}
private void loadEmployeesFromDatabaseAgain() {
//calling the read method from database instance
Cursor cursor = mDatabase.getAllEmployees();
employeeList.clear();
if (cursor.moveToFirst()) {
do {
employeeList.add(new Employee(
cursor.getInt(0),
cursor.getString(1),
cursor.getString(2),
cursor.getString(3),
cursor.getDouble(4)
));
} while (cursor.moveToNext());
}
notifyDataSetChanged();
}
}
That's it now that you can run your application and it will behave the same way it was in the previous tutorial.
Output