CRUD operation using SQLite in Android

Crud operation using SQLite in Android. In this article, we will learn how to perform CRUD operations using SQLite in Android.

The Android SDK that we are going to use here is the set of development tools to develop applications for the android platform. The database we are using is SQLite that comes with android. So, let us start with the development.

Crud Stands for Create, Read, Update and Delete operation in Sqlite. In this project, we will create an SQLite database and perform read update and delete operations in the database.

Following will be the project structure:

Step1: Getting Started With Android Studio

  • Create a project with Empty Activity

MainActivity.java

  • In this class, we have defined four different types of Objects Spinner, Button, String, and ArrayList.
  • Spinner is a DropDown menu in Android we will use it to display UserNames in this application.
  • Button is used to connect with the button view in the XML.
  • The String is basically an object that represents the sequence of char values.
  • ArrayList used a dynamic array to store data it’sdata = new ArrayList<>(db.read()); db.read() will return arraylist object to data object which contain all users info an array with no size defined.
  • getUser() method will insert user names from data object to users object.
  • findViewById is used to connect an object to the view which has an id.
  • ArrayAdapter is used to populate the spinner view with a list of arrays.
  • We are populating the Spinner view with user names.
  • setAdapter is used to assign ArrayAdapter to Spinner View.
  • setOnItemSelectedListener is used to Listen to any changes made in the spinner view.
  • AdapterView will return the item selected from the dropdown list.
  • getItemPosition(i).toString(); will return the item selected to String
  • setOnClickListener will start when we click on Create Button
  • The intent is used to start or jump to another activity
  • putExtra method is used to send data and key pair with activity to another activity
  • We are using the S1 object to send the user names to other activities.
  • On the delete button, we are sending the username which is passed to DBHelper class delete Method.
package com.example.cred_operation_sql;

import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.Spinner;
import android.widget.Toast;

import com.example.cred_operation_sql.SQL.DBHelper;
import com.example.cred_operation_sql.SQL.Data;
import java.util.ArrayList;

public class MainActivity extends AppCompatActivity{
    private Spinner spinnerMain;
    private Button createMain,readMain,updateMain,deleteMain;
    private String s;
    private String s1;
    DBHelper db = new DBHelper(MainActivity.this);
    private static ArrayList<Data> data ;
    private static ArrayList<String> users;
    @Override
    protected void onCreate(Bundle savedInstanceState)  {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        data =new ArrayList<>(db.readData());
        users = new ArrayList<>();
        getUser();
        spinnerMain = findViewById(R.id.spinnerMain);
        createMain = findViewById(R.id.createEditText);
        readMain = findViewById(R.id.readEditText);
        updateMain = findViewById(R.id.updateEditText);
        deleteMain = findViewById(R.id.deleteEditText);
        ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,android.R.layout.simple_list_item_activated_1,users);
        spinnerMain.setAdapter(adapter);
        spinnerMain.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
            @Override
            public void onItemSelected(AdapterView<?> adapterView, View view, int i, long l) {
                s1=adapterView.getItemAtPosition(i).toString();

            }

            @Override
            public void onNothingSelected(AdapterView<?> adapterView) {

            }
        });

        createMain.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                s = "CreateMain";
                Intent i = new Intent(MainActivity.this, RIU_Activity.class);

                i.putExtra("Button",s);

                startActivity(i);
            }
        });
        readMain.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                s = "ReadMain";
                Intent i = new Intent(MainActivity.this, RIU_Activity.class);
                i.putExtra("Button",s);
                i.putExtra("GetUser",s1);
                startActivity(i);
            }
        });
        updateMain.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                s = "UpdateMain";
                Intent i = new Intent(MainActivity.this, RIU_Activity.class);
                i.putExtra("Button",s);
                i.putExtra("GetUser",s1);
                startActivity(i);
            }
        });
        deleteMain.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if (db.deleteData(s1)){
                    Toast.makeText(MainActivity.this,"Data Dropped",Toast.LENGTH_SHORT).show();

                }
            }
        });

    }
    public static void getUser(){
        for (int i =0 ; i<data.size();i++){
            users.add(data.get(i).getUserName());
        }

    }

}

activity_main.xml

  • LinearLayout is used as the parent node of our XML file.
  • orientation is used to set how the child gets a view in an XML file.
  • gravity is used to place child in the parent node
  • Width and height are used to set view height and width. Match_parent is to set the layout to the size of the screen and wrap_content is used to set layout size to the size of the content.
  • background tag is used to set view background color and other things.
  • Spinner is a drop-down menu in Android.
  • Button is used to place the simple button on a screen
  • id tag is used to give a unique id to every view so we can modify the view from our java class
  • Text tag is used to set Text on any view.
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout 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:orientation="vertical"
    android:gravity="center"
    android:showDividers="middle"
    android:divider="@drawable/spaceing"
    android:background="@drawable/page"
    >
    <Spinner
        android:id="@+id/spinnerMain"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"/>

    <Button
        android:id="@+id/createEditText"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Create DataBase"
        android:background="@drawable/btn"
        />
    <Button
        android:id="@+id/readEditText"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Read Data"
        android:background="@drawable/btn"/>
    <Button
        android:id="@+id/updateEditText"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:background="@drawable/btn"
        android:text="Update"/>
    <Button
        android:id="@+id/deleteEditText"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:background="@drawable/btn"
        android:text="Delete"/>
</LinearLayout>

Step 2: Create, read and update Activity

  • Create a new empty activity with the name RUI_Activity.

Create

  • In CreateMain if Statement we are creating new users and Details.
  • We assign listener to the submit button when we click on the button “setOnClickListner”.
  • we will read the data from editText View and pass it to insertData method “db.insertData” in DBHelper class db is the object of DBHelper class.
  • If the insertData method failed it will return the boolean value false and we Exit if the statement
  • If it returns true we will send a message to the home screen “Data Inserted” through the Toast method.
  • finish() method is used to kill the current running activity.

Update

  • Through intent, we receive different string values to String s.
  • In the first, if statement we execute the Read function we receive a username from Spinner from MainActivity class.
  • we run through the database and display username email and number.
  • Using readData method of DBHelper class we receive all the users and their details.
  • Then we find the user which is selected in the Spinner and then displays it to EditText view in activity_main.xml through .setText() method.
  • setEnabled is used enable and disable editText view in XML
  • getUserName, getEmail, getNumber method is used to get details of the user from the database
  • In the update Statement, we first set userName to uneditable so we cant change userName
  • Then we get the userName selected from the spinner through the intent getStringExtra method with the key “GetUser”
  • Then we get all the list of users from DBHelper class to ArrayList object list db is Object of DBHelper class
  • Then through for loop we loop through all the users and match it with user selected from spinner
  • In if statement we check form all the users from data that match the user selected in the spinner
  • If the user match then retrieve the details and set it to editText View through getUserName from DBHelper class and set it to EditText view by setText()
  • Then we add a listener to the button. When the button is pressed retrieve the details from editText and send data to method db.update();
  • db.update method is used to update details of the user if it returns true we make a Toast with the message Data Inserted and we start MainActivity with the help of Intent and finish the current running activity from the background.

RIU_Activity.java

package com.example.cred_operation_sql;

import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import com.example.cred_operation_sql.SQL.DBHelper;
import com.example.cred_operation_sql.SQL.Data;

import java.util.ArrayList;
package com.example.cred_operation_sql;

import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
import com.example.cred_operation_sql.SQL.DBHelper;
import com.example.cred_operation_sql.SQL.Data;

import java.util.ArrayList;

public class RIU_Activity extends AppCompatActivity {
    private EditText editTextName,editTextEmail,editTextNumber;
    private Button btnRIU;
    private Intent i;
    private String s;
    private ArrayList<Data> list;
    private DBHelper db = new DBHelper(RIU_Activity.this);
    @Override
    protected void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_rui);
        editTextName = findViewById(R.id.editUserNameRIU);
        editTextEmail = findViewById(R.id.editEmailRIU);
        editTextNumber = findViewById(R.id.editNumberRIU);
        btnRIU = findViewById(R.id.btnRIU);
        i = getIntent();
        s=i.getStringExtra("Button");
        if (s.equals("ReadMain")){
            String name = null, email = null, number = null;
            name =i.getStringExtra("GetUser");
            list = new ArrayList<>(db.readData());
          for (Data d : list) {
            String temp = d.getUserName();
            if (temp.equals(name)) {
                name = d.getUserName();
                email = d.getEmail();
                number = d.getNumber();
                editTextName.setText(name);
                editTextEmail.setText(email);
                editTextNumber.setText(number);
                btnRIU.setText("OK");
                btnRIU.setOnClickListener(new View.OnClickListener() {
                    @Override
                    public void onClick(View view) {
                            Intent i = new Intent(RIU_Activity.this,MainActivity.class);
                            startActivity(i);
                            finish();
                    }
                });
            }
        }

        editTextName.setEnabled(false);
        editTextEmail.setEnabled(false);
        editTextNumber.setEnabled(false);

    }else if (s.equals("CreateMain")){
            editTextName.setText("");
            editTextEmail.setText("");
            editTextNumber.setText("");
            btnRIU.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {
                    String name ,email,number;
                    name = editTextName.getText().toString();

                    email = editTextEmail.getText().toString();
                    number = editTextNumber.getText().toString();
                    if (db.insertData(name,email,number)){
                        Toast.makeText(RIU_Activity.this,"Data Inserted",Toast.LENGTH_SHORT).show();
                        Intent i = new Intent(RIU_Activity.this,MainActivity.class);
                        startActivity(i);
                        finish();
                    }
                }
            });
        }
        else if (s.equals("UpdateMain")){
            editTextName.setEnabled(false);
            String name = null, email = null, number = null;
            name =i.getStringExtra("GetUser");
            list = new ArrayList<>(db.readData());
            for (Data d : list) {
                String temp = d.getUserName();
                if (temp.equals(name)) {
                    name = d.getUserName();
                    email = d.getEmail();
                    number = d.getNumber();
                    editTextName.setText(name);
                    editTextEmail.setText(email);
                    editTextNumber.setText(number);
                    btnRIU.setOnClickListener(new View.OnClickListener() {
                        @Override
                        public void onClick(View view) {
                           String name1 = editTextName.getText().toString();
                          String email1 = editTextEmail.getText().toString();
                            String number1 =editTextNumber.getText().toString();
                            if (db.updateData(name1,email1,number1)){
                                Toast.makeText(RIU_Activity.this,"Data Inserted",Toast.LENGTH_SHORT).show();
                                Intent i = new Intent(RIU_Activity.this,MainActivity.class);
                                startActivity(i);
                                finish();
                            }else {
                                Toast.makeText(RIU_Activity.this,"Data Insertion Failed",Toast.LENGTH_SHORT).show();
                                finish();
                            }
                        }
                    });
                }
            }
        }else if (s.equals("DeleteMain")){
            String name = i.getStringExtra("GetUser");
            if (db.deleteData(name)){
                    Toast.makeText(RIU_Activity.this,"Data Droped",Toast.LENGTH_SHORT).show();
                }else {
                Toast.makeText(RIU_Activity.this,"Failed to remove data",Toast.LENGTH_SHORT).show();
            }
            Intent i = new Intent(RIU_Activity.this,MainActivity.class);
            startActivity(i);
            finish();
        }
    }
}

activity_rui.xml

  • Here we again use parent node as Linear Layout
  • We have three EditText views and one Button view
  • EditText view is used to display text field in which we can type
  • The hint tag is used to show hints in the text field when the user clicks on it.
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout 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=".RIU_Activity"
    android:orientation="vertical"
    android:divider="@drawable/spaceing"
    android:showDividers="middle"
    android:gravity="center"
    android:background="@drawable/page"
    tools:ignore="MissingDefaultResource">
    <EditText
        android:id="@+id/editUserNameRIU"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="User Name"
        />
    <EditText
        android:id="@+id/editEmailRIU"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Email"/>
    <EditText
        android:id="@+id/editNumberRIU"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Number"/>
    <Button
        android:id="@+id/btnRIU"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Submit"
        android:background="@drawable/btn"/>

</LinearLayout>

Create

Update

Step 2.1: Creating Read Activity

Right Click on com.example.project-name and then goto new -> activity -> empty activity. Make new empty activity with name ReadActivity

  • Here we are using ListView to display the list of users.
  • To populate ListView we use ArrayAdapter
  • readData(); will return all the users and details db is an object of DBHelper class
  • We are using ArrayList of type data class to get all the values through the readData method
  • getUsers(); method will return all the users from ArrayList data to ArrayList list type String
  • Now we will pass the list to arrayAdapter type String
  • ArrayAdapter contains three values first is context Second Style of the list and third List
  • setAdapter method is used to set the adapter to listView

ReadActivity.java

package com.example.cred_operation_sql;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.ListView;

import com.example.cred_operation_sql.SQL.DBHelper;
import com.example.cred_operation_sql.SQL.Data;

import java.util.ArrayList;

public class ReadActivity extends AppCompatActivity {
    private ListView listView;
    private static ArrayList<String> list = new ArrayList<>();
    DBHelper db = new DBHelper(ReadActivity.this);
    private static ArrayList<Data> data;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_read);
        listView = findViewById(R.id.list);
        data = new ArrayList<>(db.readData());
        getUser();
        ArrayAdapter<String> stringArrayAdapter = new ArrayAdapter<>(ReadActivity.this, android.R.layout.simple_list_item_1, list);
        listView.setAdapter(stringArrayAdapter);
    }

    public static void getUser() {
        for (int i = 0; i < data.size(); i++) {
            list.add(data.get(i).getUserName());
        }
    }

    @Override
    public void onBackPressed() {
 list.clear();
        finish();
        super.onBackPressed();
    }
}

activity_read.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout 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=".ReadActivity">
    <ListView
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/list"></ListView>
</LinearLayout>

Step 3: Setting Up SQLite DataBase

Right-click on com.example.project-name and go to new and then package, name it Sql then create a new java class with name DBHelper and Create another class with the name Data.

DBHelper.Java

  • Extend Class with SQLiteOpenHelper.
  • SQLite class is used to create and manage a database
  • With the help of the constructor, we create data with name “DataBase” and version 1.
  • Then we override onCreate and onUpgrade method these are the abstracts method of class SQLiteOpenHelper.
  • onCreate method is used to create Table in database with the help of SQLiteDatabase object we call method execSQL.
  • execSQL method is used to create table name and create fields.
  • we will execute the query “create table UserDetails(userName VARCHAR primary key, email VARCHAR, number TEXT)”
  • This query will create a table with the name UserDetails and Field userName, email, and number
  • Now in the insertData method, we create an object of SQLiteDatabase class and refer it to getWritableDatabase
  • Then we create an object of ContentValue and use the put method to insert values in the object
  • With the help of SQLite object, we call method to insert and pass in Table name (UserDetails) and contentValues
  • If the insert method fails it will return a -1 value
  • In updateData method, rawQuery will search for userName which we passed to the method in the database, and return cursor object to that details
  • Then we use the update method to update details of users with contentValues
  • Same on deleteData method DBW.delete method will search for user name and remove user and its details
  • On readData method will return ArrayList of Data class with the help of cursor we loop through ever user details and add users and its details to ArrayList.
package com.example.cred_operation_sql.SQL;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;

public class DBHelper extends SQLiteOpenHelper {
    private ArrayList<Data> list = new ArrayList<>();
    private ContentValues contentValues;
    public DBHelper(Context context) {
        super(context,"DataBase",null,1);
    }

    @Override
    public void onCreate(SQLiteDatabase DB) {
    DB.execSQL("Create table UserDetails(userName VARCHAR primary key, email VARCHAR, number TEXT)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase DB, int i, int i1) {
    DB.execSQL("drop Table if exists UserDetails");
    }
    public Boolean insertData(String userName,String email,String number){
        SQLiteDatabase DBW = this.getWritableDatabase();
        contentValues = new ContentValues();
        contentValues.put("UserName",userName);
        contentValues.put("Email",email);
        contentValues.put("Number",number);
        long l = DBW.insert("userDetails",null,contentValues);
        if (l == -1){
            return false;
        }else {
            return true;
        }
    }
    public Boolean updateData(String userName,String email,String number){
        SQLiteDatabase DBW = this.getWritableDatabase();
        contentValues = new ContentValues();
        contentValues.put("UserName",userName);
        contentValues.put("Email",email);
        contentValues.put("Number",number);
        Cursor cursor = DBW.rawQuery("Select * from userDetails where userName = ?",new String[]{userName});
        cursor.moveToFirst();
    
        if (cursor.getCount()>0){
            long l = DBW.update("UserDetails",contentValues,"userName=?",new String[]{userName});
            if (l == -1) {
                return false;
            }else {
                return true;
            }
            }else {
            return false;
        }
        }
    public Boolean deleteData(String userName){
        SQLiteDatabase DBW = this.getWritableDatabase();
        Cursor cursor = DBW.rawQuery("Select * from UserDetails where userName = ?",new String[]{userName});
        if (cursor.getCount()>0){
            long l =DBW.delete("UserDetails","userName = ?",new String[]{userName});
            if (l == -1){
                return false;
            }else {
                return true;
            }
        }else {
            return false;
        }
        }
        public ArrayList<Data> readData(){
            String name,email,number;
            SQLiteDatabase DBR = this.getReadableDatabase();
            Cursor cursor = DBR.rawQuery("Select * from UserDetails",null);
            cursor.moveToFirst();
            if (cursor.moveToFirst()){
                do {
                    name =cursor.getString(0);
                    email = cursor.getString(1);
                    number = cursor.getString(2);
                    list.add(new Data(name,email,number));
                }while (cursor.moveToNext());
            }
            DBR.close();
            return list;
        }
    }

Data.Java

  • In the Data class, we create three objects of String class userName, email, and number
  • Then we create a constructor of this class with values username, email, and number
  • So when we make an array of this class we can pass three values in the array as a list in a single position
  • get method is used to return the value of these object
package com.example.cred_operation_sql.SQL;

public class Data {
    String userName , email , number;

    public Data(String userName, String email, String number) {
        this.userName = userName;
        this.email = email;
        this.number = number;
    }

    public String getUserName() {
        return userName;
    }

    public String getEmail() {
        return email;
    }

    public String getNumber() {
        return number;
    }
}

Step 3: Creating Styles for button and views

Right-click on drawable then go to new and create a new drawable resource file with name btn, page, and spacing.

  • shape tag is used to give shape to the Button.
  • corners tag is used for cutting the corners of view.
  • padding tag is used to give space between text and view.

btn.xml

<?xml version="1.0" encoding="utf-8"?>
<selector xmlns:android="http://schemas.android.com/apk/res/android">
    <item android:state_enabled="true" >
        <shape android:shape="rectangle" >
            <corners android:radius="10dp"/>
            <gradient android:startColor="#A443E9" android:endColor="#38f9d7" android:type="linear"/>
            <padding android:left="10dp" android:right="10dp"/>
        </shape>
    </item>
</selector>

page.xml

<?xml version="1.0" encoding="utf-8"?>
<selector xmlns:android="http://schemas.android.com/apk/res/android">
<item>
    <shape>
        <gradient android:angle="90" android:startColor="#A443E9" android:endColor="#38f9d7" android:type="linear"/>
    </shape>
</item>
</selector>

spaceing.xml

<?xml version="1.0" encoding="utf-8"?>
<shape xmlns:android="http://schemas.android.com/apk/res/android">
    <size android:height="40dp"
         android:width="0dp"/>
</shape>

Thus, this is how we perform CRUD operations using SQLite in Android.

Android practice tasks

https://codebun.com/login-and-registration-in-android-using-firebase/

https://codebun.com/login-and-registration-in-android-using-firebase/

https://codebun.com/dynamic-and-static-dropdown-menuspinner-in-android/

https://codebun.com/crud-operation-in-android-using-firebase-database/

https://codebun.com/how-to-get-data-from-api-in-android/

https://codebun.com/how-to-create-custom-alert-dialog-in-android/

https://codebun.com/search-and-sort-records-in-android-with-recycler-view/

https://codebun.com/create-a-custom-notification-with-custom-message-in-android/