Hey Technoz! Many times, We must have seen many apps which saves data in device memory. Similarly, it uses the SQLite database for CRUD operations and to store the data. So, we will study how to use SQLite database to store the data in the form of tables – rows and columns in this CRUD Operations on SQLite database in android tutorial.
What is SQLite?
The SQLite database is an in-built, open source and lightweight database in android. Certainly, it stores data in a form of text file on a device. When there is requirement of storing large amount of data, SQLite is more preferable way than any other data structure like Shared Preferences.
What is CRUD?
The CRUD is nothing but an acronym of:
- C: Create
- R: Read
- U: Update
- D: Delete
Obviously, There is no need of any special software or connectivity for performing CRUD operations in SQLite database. However, This tutorial assumes you have some basic knowledge of SQL with create, insert, update and delete queries. So lets move towards CRUD Operations on SQLite database in android.
CRUD Operations on SQLite database in android
First of all, Lets start by creating a table ‘Employee’ with 4 fields as id (AUTO_INCREMENT), name, email and cname (company name). As a result, We will add, view, update and delete records from the table.
Defining Constants
Besides, create a class Constants as follows to define all constants like table name and column names.
/*Constants.java*/
package net.softglobe.sqlitedemo;
public class Constants {
//Table name
public static final String TABLE = "Employee";
//Table columns
public static final String COL_ID = "id";
public static final String COL_NAME = "name";
public static final String COL_EMAIL = "email";
public static final String COL_CNAME = "cname";
}
Adding Helping Classes
Above all, the SQLiteOpenHelper is a class which helps to handle database operations. Now, we will create a new class DBHelper.java and extend it from SQLiteOpenHelper class. Of course, it needs to implement two methods as onCreate and onUpgrade and one constructor. The constructor has database name and version name defined statically. Certainly, Each time the database is modified, the version name is updated. The onUpgrade method will drop the table if it already exists. Lets code the file DBHelper.java as follows.
/*
* DBHelper.java
* This class extends SQLiteOpenHelper to manage SQL tasks
* It implements methods and constructor. It creates database
* in constructor and table in onCreate method.*/
package net.softglobe.sqlitedemo;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.Nullable;
public class DBHelper extends SQLiteOpenHelper {
//version number to upgrade database version
//each time if you Add, Edit table, you need to change the
//version number.
private static final int DB_VERSION = 1;
private static final String DB_NAME = "Glad";
public DBHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
//All necessary tables you like to create will create here
String CREATE_TABLE = "CREATE TABLE " + Constants.TABLE + "("
+ Constants.COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ Constants.COL_NAME + " VARCHAR(255),"
+ Constants.COL_EMAIL + " VARCHAR(255),"
+ Constants.COL_CNAME + " VARCHAR(255));";
//Execute query to create table
sqLiteDatabase.execSQL(CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
// Drop older table if existed, all data will be gone!!!
sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + Constants.TABLE);
// Create tables again
onCreate(sqLiteDatabase);
}
}
Defining Required Functions
Now, as we have to perform CRUD operations in SQLite database, we will make a collection of respective methods in the following class file Functions.java
/*Functions.java*/
package net.softglobe.sqlitedemo;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class Functions {
String name,email,cname;
private DBHelper dbHelper;
//constructor to create object instantly
public Functions(Context context){
dbHelper = new DBHelper(context);
}
public long insertdata(String name, String email, String cname){
this.name = name;
this.email = email;
this.cname = cname;
//Open connection to write data
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(Constants.COL_NAME, name);
values.put(Constants.COL_EMAIL, email);
values.put(Constants.COL_CNAME, cname);
//insert into table using built in method 'insert' in SQLitedatabase.class
long retid = db.insert(Constants.TABLE, null, values);
return retid;
}
public String viewdata(){
SQLiteDatabase db = dbHelper.getWritableDatabase();
String [] columns = {Constants.COL_ID,Constants.COL_NAME, Constants.COL_EMAIL, Constants.COL_CNAME};
Cursor cursor = db.query(Constants.TABLE,columns,null,null,null,null,null);
StringBuffer buffer = new StringBuffer();
while (cursor.moveToNext()){
int cid = cursor.getInt(cursor.getColumnIndex(Constants.COL_ID));
String name = cursor.getString(cursor.getColumnIndex(Constants.COL_NAME));
String email = cursor.getString(cursor.getColumnIndex(Constants.COL_EMAIL));
String cname = cursor.getString(cursor.getColumnIndex(Constants.COL_CNAME));
buffer.append(cid+" "+name+" "+email+" "+cname+"\n");
}
return buffer.toString();
}
public long edit(String id, String name){
//Open connection to write data
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(Constants.COL_NAME, name);
String [] whereArgs = {id};
long retid = db.update(Constants.TABLE,values,Constants.COL_ID+" = ?",whereArgs);
return retid;
}
public int delete(String id){
//Open connection to write data
SQLiteDatabase db = dbHelper.getWritableDatabase();
String[] whereArgs ={id};
int retid = db.delete(Constants.TABLE,Constants.COL_ID + " = ?",whereArgs);
return retid;
}
}
Furthermore, the above class contains a constructor in which the object of class DBHelper is created. Probably, we have created an insertdata method for insert operation. Because it is a paramaterized method, it takes arguments from user and insert() is a predefined method in SQLiteDatabase class. Before performing any operation, we need to open database connection. The getWritableDatabase() method does this task. A ContentValues class put the values in Global constants. As a result, status of operation is held in retid variable and returned to calling function.
Similarly, viewdata() method is used to retrieve data from database. The Cursor class is used to fetch values as shown above. The StringBuffer() method is used to send the fetched data to the calling function.
Even more, an update task is completed by edit() function. update() method is predefined in class SQLiteDatabase.
Finally, the delete task is achieved by delete method. Consequently, we again open database connection and use predefined delete() method to accomplish a task.
Creating Activity
So far we have made java classes, now we create an activity to perform all those operations. Similarly, create new activity MainActivity.java as follows. Furthermore, for CRUD operations in SQLite database, it takes the input from user, passing parameters to functions and showing data.
/*MainActivity.java*/
package net.softglobe.sqlitedemo;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;
public class MainActivity extends AppCompatActivity {
EditText nametxt,emailtxt,cnametxt;
Functions fun;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
fun = new Functions(this);
}
public void addRecord(View view){
nametxt = (EditText) findViewById(R.id.name);
emailtxt = (EditText) findViewById(R.id.email);
cnametxt = (EditText) findViewById(R.id.cname);
String name = nametxt.getText().toString();
String email = emailtxt.getText().toString();
String cname = cnametxt.getText().toString();
if (name.isEmpty() || email.isEmpty() || cname.isEmpty()){
Toast.makeText(this, "Please fill all the fields!", Toast.LENGTH_SHORT).show();
}
else {
long retid = fun.insertdata(name, email, cname);
if (retid > 0) {
Toast.makeText(this, "Insertion Successful", Toast.LENGTH_SHORT).show();
} else {
Toast.makeText(this, "Insertion Failed!", Toast.LENGTH_SHORT).show();
}
}
}
public void viewdata_new(View view){
String data = fun.viewdata();
Toast.makeText(this, data, Toast.LENGTH_SHORT).show();
}
public void updaterec(View view){
EditText editId,edit_name;
editId = (EditText) findViewById(R.id.editId);
edit_name = (EditText) findViewById(R.id.edit_name);
String id = editId.getText().toString();
String name = edit_name.getText().toString();
if (id.isEmpty() || name.isEmpty()){
Toast.makeText(this, "Plese enter id and name", Toast.LENGTH_SHORT).show();
}
else{
long retid = fun.edit(id,name);
if (retid>=1){
Toast.makeText(this, "Update Successful!", Toast.LENGTH_SHORT).show();
}
else{
Toast.makeText(this, "Update Failed!", Toast.LENGTH_SHORT).show();
}
}
}
public void delrec(View view){
EditText del_id;
del_id = findViewById(R.id.del_id);
String id = del_id.getText().toString();
if (id.isEmpty()){
Toast.makeText(this, "Please enter id", Toast.LENGTH_SHORT).show();
}
else {
int retid = fun.delete(id);
if (retid >=1){
Toast.makeText(this, "Record Deleted Sccessfully!", Toast.LENGTH_SHORT).show();
}
else{
Toast.makeText(this, "No such record Exists!", Toast.LENGTH_SHORT).show();
}
}
}
}
Designing the Layouts
So, paste the following code in file content_main.xml
<?xml version="1.0" encoding="utf-8"?>
<android.support.constraint.ConstraintLayout 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"
app:layout_behavior="@string/appbar_scrolling_view_behavior"
tools:context=".MainActivity"
tools:showIn="@layout/activity_main">
<ScrollView
android:layout_width="match_parent"
android:layout_height="match_parent">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="vertical"
android:layout_margin="10sp">
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="@color/colorPrimaryDark"
android:text="Add Record"
android:textColor="@color/white"
android:textSize="20sp"
android:gravity="center_horizontal"
android:paddingTop="5sp"
android:paddingBottom="5sp"/>
<EditText
android:id="@+id/name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Name"/>
<EditText
android:id="@+id/email"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Email"/>
<EditText
android:id="@+id/cname"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Company Name"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Add Record"
android:onClick="addRecord"/>
<TextView
android:layout_width="match_parent"
android:layout_height="5sp"
android:background="@color/colorAccent"
android:layout_marginBottom="5sp"
android:layout_marginTop="5sp"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="View Records"
android:onClick="viewdata_new"/>
<TextView
android:layout_width="match_parent"
android:layout_height="5sp"
android:background="@color/colorAccent"
android:layout_marginBottom="5sp"
android:layout_marginTop="5sp"/>
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="@color/colorPrimaryDark"
android:text="Update Name"
android:textColor="@color/white"
android:textSize="20sp"
android:gravity="center_horizontal"
android:paddingTop="5sp"
android:paddingBottom="5sp"/>
<EditText
android:id="@+id/editId"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Enter Id"/>
<EditText
android:id="@+id/edit_name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Enter New Name"
android:textSize="18sp" />
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Update Name"
android:onClick="updaterec"/>
<TextView
android:layout_width="match_parent"
android:layout_height="5sp"
android:background="@color/colorAccent"
android:layout_marginBottom="5sp"
android:layout_marginTop="5sp"/>
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="@color/colorPrimaryDark"
android:text="Delete Record"
android:textColor="@color/white"
android:textSize="20sp"
android:gravity="center_horizontal"
android:paddingTop="5sp"
android:paddingBottom="5sp"/>
<EditText
android:id="@+id/del_id"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Enter id"
android:textSize="18sp" />
<Button
android:id="@+id/del_btn"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Delete Record"
android:onClick="delrec"
android:layout_marginBottom="20sp"/>
</LinearLayout>
</ScrollView>
</android.support.constraint.ConstraintLayout>
Code following file as activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<android.support.design.widget.CoordinatorLayout 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">
<android.support.design.widget.AppBarLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:theme="@style/AppTheme.AppBarOverlay">
<android.support.v7.widget.Toolbar
android:id="@+id/toolbar"
android:layout_width="match_parent"
android:layout_height="?attr/actionBarSize"
android:background="?attr/colorPrimary"
app:popupTheme="@style/AppTheme.PopupOverlay" />
</android.support.design.widget.AppBarLayout>
<include layout="@layout/content_main" />
</android.support.design.widget.CoordinatorLayout>
See the Output Screens
As a result, we completed the coding part for performing CRUD operations in SQLite database. Hence, you can test the code on emulator or on a real device. Even more, see the view below.
Finding the SQLite Database File
Thus, we have successfully completed the CRUD Operations on SQLite database in android. If you want to see the records in table form, follow the following steps: (Assuming app is running on virtual device)
- Open Device File Explorer on right side in android studio.
- Go to path: data => data => [app-package-name] => databases => [database-name]
- Take the file as save with .db extension
- You will need ‘DB Browser for SQLite’ (Download_link) to open this file.
- Drag and drop the file to the window to see the database in table form.
As a result, see the screenshot below for clarification.
Download Source Code
Rather, you can always download source code files of CRUD operations from the below link.
Even more, if you have any question implementing the above tutorial, feel free to comment below. Please subscribe to our newsletter to catch such knowledgeable tutorials. 🙂