CRUD Operation Using SQLiteDatabase Tutorial
Hey, Developers! In this tutorial we will learn how to perform CRUD operation in SQLiteDatabase here we will create database, create tables, create records, read records, update records, and delete records in the SQLite database for Android.
You may already know that in android we have SQLite database that we can use as a local SQL database for our android application.
What is CRUD?
As the heading tells you, in SQLite Database we will learn about the CRUD operation. But what does CRUD mean? CRUD is nothing more than an abbreviation for the basic operations in any database that we perform. And these are the operations.
- CREATE
- READ
- UPDATE
- DELETE
Creating New Project
At first, we will create android application. Then, we will see how to perform CRUD Operation using SQLiteDatabase in the application.
Follow steps below to create new project. Please ignore the steps if you have already created a new application.
| S. No. | Steps |
| 1 | Open Android Studio. |
| 2 | Click on Start a new Android Studio Project then choose your project and select Empty Activity. |
| 3 | Write application name as CRUDOperation after this write a package name you can write your own package name. |
| 4 | Select language Java. Select minimum SDK you need. However, we have selected 17 as minimum SDK. Then, click next button. |
| 5 | If you have followed above process correctly, you will get a newly created project successfully. However, you can also visit tutorial to Create a New Project to know steps in detail. |
Database Structure
The first thing you need is the structure of the database. Depending on the system, we create database structure. But we're not building a complex application here, and it's just a tutorial that demonstrates SQLite Database usage.
We're going to use the following table structure for this.
Now we only have a single table, but you will have multiple tables with some complex relationships in real-world scenarios. Also remember one thing when you create a table that creates a column named ID with int as PRIMARY KEY and AUTOINCREMENT.(If you're confused, don't worry, we'll see now how we can create database tables using SQL).
SQL Queries
Now let's see how in our SQLite database we can create the above table.
Creating the Table
CREATE TABLE employees (
id INTEGER NOT NULL CONSTRAINT employees_pk PRIMARY KEY AUTOINCREMENT,
name varchar(200) NOT NULL,
department varchar(200) NOT NULL,
joiningdate datetime NOT NULL,
salary double NOT NULL
);
Creating a new Record
CREATE TABLE employees (
INSERT INTO employees
(name, department, joiningdate, salary)
VALUES
('Anil Bisht', 'Content Writer', '2017-09-30 10:00:00', '40000');
Reading All Existing Records
SELECT * FROM employees;
Reading Specific Record
SELECT * FROM employees WHERE id = 1;
Note: * Specifies all columns, if you want a specific column or multiple columns, but not all column you can write names such as SELECT name, department.
Updating a Record
UPDATE employees
SET
name = 'Ankit Negi',
department = 'Tester',
salary = '100000'
WHERE id = 1;
Deleting a Record
DELETE FROM employees WHERE id = 1;
These are just a few simple basic operations, but in our database we can perform a lots of task.
Note: SQL Queries are not case sensitive.
User Interface Design
We need an interface from where we can accomplish these tasks to implement all the above-mentioned queries in our application. Now, let's think about the screens we need to create an app that will perform all the above queries with user interaction.
Activity_Main.xml
The first thing is to add a fresh record to our database, and the following code can be used for this.
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_centerVertical="true"
android:orientation="vertical"
android:padding="16dp">
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginBottom="12dp"
android:text="Add a new Employee"
android:textAlignment="center"
android:textAppearance="@style/Base.TextAppearance.AppCompat.Large"/>
<EditText
android:id="@+id/editTextName"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Enter Employee Name" />
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:paddingLeft="6dp"
android:text="Select Department" />
<Spinner
android:id="@+id/spinnerDepartment"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:entries="@array/departments"/>
<EditText
android:id="@+id/editTextSalary"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:digits="0123456789"
android:hint="Enter Employee Salary"
android:inputType="number"/>
<Button
android:id="@+id/buttonAddEmployee"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Add Employee" />
<TextView
android:id="@+id/textViewViewEmployees"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="16dp"
android:text="View Employees"
android:textAlignment="center"
android:textAppearance="@style/Base.TextAppearance.AppCompat.Medium"
android:textStyle="bold" />
</LinearLayout>
</RelativeLayout>
String.xml
We have to describe an array as the entries to the spinner for the spinner that we used at the above screen.
Go to value => strings.xml and change this as below.
<resources>
<string name="app_name">CRUD Operation</string>
<array name="departments">
<item>Technical</item> <item>Support</item>
<item>Research and Development</item>
<item>Marketing</item>
<item>Human Resource</item>
</array>
</resources>
Fetching All the Employees
Now we need to check all the stored employee from the database after saving them to the database. We may use a ListView to do this.
activity_employee.xml
So, we need to create an activity_employee.xml in res=>layout folder for see all employee list. And include the following code.
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".EmployeeActivity">
<ListView
android:id="@+id/listViewEmployees"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
</RelativeLayout>
But here we're not going to use a simple ListView as we're going to use a Custom ListView to display multiple items in the List.
list_layout_employee.xml
And we also need another layout for the ListView item, so generate another design file called list_layout_employee.xml in res=>layout folder. And include the following code.
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="horizontal"
android:padding="8dp">
<LinearLayout
android:layout_width="230dp"
android:layout_height="wrap_content"
android:orientation="vertical">
<TextView
android:id="@+id/textViewName"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginBottom="5dp"
android:layout_marginTop="10dp"
android:text="Mohit Negi" android:textAppearance="@style/Base.TextAppearance.AppCompat.Large" />
<TextView
android:id="@+id/textViewDepartment"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Technical"
android:textAppearance="@style/Base.TextAppearance.AppCompat.Medium"
/>
<TextView android:id="@+id/textViewSalary"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="INR 40000"
android:textAppearance="@style/Base.TextAppearance.AppCompat.Medium" />
<TextView
android:id="@+id/textViewJoiningDate"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="8dp"
android:text="04-06-2019 10:00:00" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="vertical">
<Button
android:id="@+id/buttonEditEmployee"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="5dp"
android:background="@color/colorPrimary"
android:text="Edit" />
<Button
android:id="@+id/buttonDeleteEmployee"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="5dp"
android:background="@color/colorAccent"
android:text="Delete" />
</LinearLayout>
</LinearLayout>
dialog_update_employee.xml
Now we're going to create another layout file (not just a layout file because we're going to edit the employee in an alert dialog) to update the employee data. So, we need to create an dialog_update_employee.xml. in res=>layout folder
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:padding="16dp">
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginBottom="12dp"
android:text="Edit Employee"
android:textAlignment="center"
android:textAppearance="@style/Base.TextAppearance.AppCompat.Large" />
<EditText
android:id="@+id/editTextName"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Enter Employee Name" />
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:paddingLeft="6dp"
android:text="Select Department" />
<Spinner
android:id="@+id/spinnerDepartment"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:entries="@array/departments" />
<EditText
android:id="@+id/editTextSalary"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:digits="0123456789"
android:hint="Enter Employee Salary"
android:inputType="number" />
<Button
android:id="@+id/buttonUpdateEmployee"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Update" />
</LinearLayout>
MainActivity.Java
We will perform Adding an employee operation inside MainActivity.java so inside this file write the following code.
package com.ukacademe.crudoperation;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;
import java.text.SimpleDateFormat;
import java.util.Calendar;
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
public static final String DATABASE_NAME = "myemployeedatabase";
TextView textViewViewEmployees;
EditText editTextName, editTextSalary;
Spinner spinnerDepartment;
SQLiteDatabase mDatabase;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
textViewViewEmployees = (TextView) findViewById(R.id.textViewViewEmployees);
editTextName = (EditText) findViewById(R.id.editTextName);
editTextSalary = (EditText) findViewById(R.id.editTextSalary);
spinnerDepartment = (Spinner) findViewById(R.id.spinnerDepartment);
findViewById(R.id.buttonAddEmployee).setOnClickListener(this);
textViewViewEmployees.setOnClickListener(this);
//creating a database
mDatabase = openOrCreateDatabase(DATABASE_NAME, MODE_PRIVATE, null);
createEmployeeTable();
}
//this method will create the table
//as we are going to call this method everytime we will launch the application
//I have added IF NOT EXISTS to the SQL
//so it will only create the table when the table is not already created
private void createEmployeeTable() {
mDatabase.execSQL(
"CREATE TABLE IF NOT EXISTS employees (\n" +
" id INTEGER NOT NULL CONSTRAINT employees_pk PRIMARY KEY AUTOINCREMENT,\n" +
" name varchar(200) NOT NULL,\n" +
" department varchar(200) NOT NULL,\n" +
" joiningdate datetime NOT NULL,\n" +
" salary double NOT NULL\n" +
");"
);
}
//this method will validate the name and salary
//dept does not need validation as it is a spinner and it cannot be empty
private boolean inputsAreCorrect(String name, String salary) {
if (name.isEmpty()) {
editTextName.setError("Please enter a name");
editTextName.requestFocus();
return false;
}
if (salary.isEmpty() || Integer.parseInt(salary) <= 0) {
editTextSalary.setError("Please enter salary");
editTextSalary.requestFocus();
return false;
}
return true;
}
//In this method we will do the create operation
private void addEmployee() {
String name = editTextName.getText().toString().trim();
String salary = editTextSalary.getText().toString().trim();
String dept = spinnerDepartment.getSelectedItem().toString();
//getting the current time for joining date
Calendar cal = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss");
String joiningDate = sdf.format(cal.getTime());
//validating the inptus
if (inputsAreCorrect(name, salary)) {
String insertSQL = "INSERT INTO employees \n" +
"(name, department, joiningdate, salary)\n" +
"VALUES \n" +
"(?, ?, ?, ?);";
//using the same method execsql for inserting values
//this time it has two parameters
//first is the sql string and second is the parameters that is to be binded with the query
mDatabase.execSQL(insertSQL, new String[]{name, dept, joiningDate, salary});
Toast.makeText(this, "Employee Added Successfully", 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;
}
}
}
Employee.Java
Click on src => main => java => com.ukacademe.crudoperation package and create Employee.java file into it. And include the following code.
package com.ukacademe.crudoperation;
/**
* Created by UK Academe on 09/06/2019.
*/
public class Employee {
int id;
String name, dept, joiningDate;
double salary;
public Employee(int id, String name, String dept, String joiningDate, double salary) {
this.id = id;
this.name = name;
this.dept = dept;
this.joiningDate = joiningDate;
this.salary = salary;
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public String getDept() {
return dept;
}
public String getJoiningDate() {
return joiningDate;
}
public double getSalary() {
return salary;
}
}
EmployeeAdapter.Java
Now we're going to build a custom adapter class for our ListView. So click on src => main => java => com.ukacademe.crudoperation package and create EmployeeAdapter.java file into it. And include the following code.
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.Button;
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 09/06/2019.
*/
public class EmployeeAdapter extends ArrayAdapter {
Context mCtx;
int listLayoutRes;
List employeeList;
SQLiteDatabase mDatabase;
public EmployeeAdapter(Context mCtx, int listLayoutRes, List employeeList, SQLiteDatabase mDatabase) {
super(mCtx, listLayoutRes, employeeList);
this.mCtx = mCtx;
this.listLayoutRes = listLayoutRes;
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(listLayoutRes, null);
final Employee employee = employeeList.get(position);
TextView textViewName = view.findViewById(R.id.textViewName);
TextView textViewDept = view.findViewById(R.id.textViewDepartment);
TextView textViewSalary = view.findViewById(R.id.textViewSalary);
TextView textViewJoiningDate = view.findViewById(R.id.textViewJoiningDate);
textViewName.setText(employee.getName());
textViewDept.setText(employee.getDept());
textViewSalary.setText(String.valueOf(employee.getSalary()));
textViewJoiningDate.setText(employee.getJoiningDate());
Button buttonDelete = view.findViewById(R.id.buttonDeleteEmployee);
Button buttonEdit = view.findViewById(R.id.buttonEditEmployee);
//adding a clicklistener to button
buttonEdit.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
updateEmployee(employee);
}
});
//the delete operation
buttonDelete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
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) {
String sql = "DELETE FROM employees WHERE id = ?";
mDatabase.execSQL(sql, new Integer[]{employee.getId()});
reloadEmployeesFromDatabase();
}
});
builder.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialogInterface, int i) {
}
});
AlertDialog dialog = builder.create();
dialog.show();
}
});
return view;
}
private void updateEmployee(final Employee employee) {
final 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 EditText editTextName = view.findViewById(R.id.editTextName);
final EditText editTextSalary = view.findViewById(R.id.editTextSalary);
final Spinner spinnerDepartment = view.findViewById(R.id.spinnerDepartment);
editTextName.setText(employee.getName());
editTextSalary.setText(String.valueOf(employee.getSalary()));
final AlertDialog dialog = builder.create();
dialog.show();
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 = spinnerDepartment.getSelectedItem().toString();
if (name.isEmpty()) {
editTextName.setError("Name can't be blank");
editTextName.requestFocus();
return;
}
if (salary.isEmpty()) {
editTextSalary.setError("Salary can't be blank");
editTextSalary.requestFocus();
return;
}
String sql = "UPDATE employees \n" +
"SET name = ?, \n" +
"department = ?, \n" +
"salary = ? \n" +
"WHERE id = ?;\n";
mDatabase.execSQL(sql, new String[]{name, dept, salary, String.valueOf(employee.getId())});
Toast.makeText(mCtx, "Employee Updated", Toast.LENGTH_SHORT).show();
reloadEmployeesFromDatabase();
dialog.dismiss();
}
});
}
private void reloadEmployeesFromDatabase() {
Cursor cursorEmployees = mDatabase.rawQuery("SELECT * FROM employees", null);
if (cursorEmployees.moveToFirst()) {
employeeList.clear();
do {
employeeList.add(new Employee(
cursorEmployees.getInt(0),
cursorEmployees.getString(1),
cursorEmployees.getString(2),
cursorEmployees.getString(3),
cursorEmployees.getDouble(4)
));
} while (cursorEmployees.moveToNext());
}
cursorEmployees.close();
notifyDataSetChanged();
}
}
EmployeeActivity.Java
Now we have to retrive and display the employee data in our ListView. So for this click on src => main => java => com.ukacademe.crudoperation package and create EmployeeActivity.java file into it. And include the following code.
package com.ukacademe.crudoperation;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.widget.ListView;
import java.util.ArrayList;
import java.util.List;
public class EmployeeActivity extends AppCompatActivity {
List employeeList;
SQLiteDatabase mDatabase;
ListView listViewEmployees;
EmployeeAdapter adapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_employee);
listViewEmployees = (ListView) findViewById(R.id.listViewEmployees);
employeeList = new ArrayList<>();
//opening the database
mDatabase = openOrCreateDatabase(MainActivity.DATABASE_NAME, MODE_PRIVATE, null);
//this method will display the employees in the list
showEmployeesFromDatabase();
}
private void showEmployeesFromDatabase() {
//we used rawQuery(sql, selectionargs) for fetching all the employees
Cursor cursorEmployees = mDatabase.rawQuery("SELECT * FROM employees", null);
//if the cursor has some data
if (cursorEmployees.moveToFirst()) {
//looping through all the records
do {
//pushing each record in the employee list
employeeList.add(new Employee(
cursorEmployees.getInt(0),
cursorEmployees.getString(1),
cursorEmployees.getString(2),
cursorEmployees.getString(3),
cursorEmployees.getDouble(4)
));
} while (cursorEmployees.moveToNext());
}
//closing the cursor
cursorEmployees.close();
//creating the adapter object
adapter = new EmployeeAdapter(this, R.layout.list_layout_employee, employeeList, mDatabase);
//adding the adapter to listview
listViewEmployees.setAdapter(adapter);
}
}
Congratulations! your CRUDOperation App is completed just run it on your device or in emulator.
Output