SQLite CRUD operation with example in android

This is the starting point of every android learner.

One who wants to initiate database connectivity with the SQLite database should initiate the CRUD (Create, Read, Update, Delete) operation in Android.

So friend, in this tutorial we’ll learn the “CRUD” operation in SQLite database using android studio.

We have already learned about SQLite databases. If you don’t know about SQLite databases, read SQLite Database Tutorial.

What a new in this tutorial?

Before starting CRUD operation with SQLite, we should know that what we’ll learn in this tutorial. So, friends, we’ll learn simple CRUD operations using the SQLite database. User data store in the SQLite database and display in ListView. We’ll store the user’s first and last name, you can store any number of data that you want to do. This only, for example, you can store any data as per requirement. For extra UI design, we’ll use CardView and ImageView in a single data Layout Resource file.

What we’ll cover in this tutorial?

  • Create database,
  • creating records,
  • reading records,
  • update data, and delete data.
  • Show data in ListView,
  • Display data in CardView.
  • When clicking on CardView change the background color and display the Edit button and delete button.

You can’t find this type of tutorial anywhere because this tutorial is forever unique.

Let’s start CRUD operation with SQLite database in android studio.

Step 1:

Create a new android project.

Step 2:

Fill in all required fields.

Step 3:

In this step, we’ll be starting user interface like buttons and EditTexts.

You may also like

So, add buttons and EditeTexts in activity_main.xml.

Open activity_main.xml.

App ⇾ layout ⇾ activity_main.xml

activity_main.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"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <EditText
        android:id="@+id/fname"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="First Name"
        android:inputType="text" />

    <EditText
        android:id="@+id/lname"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Last Name"
        android:inputType="text" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <Button
            android:id="@+id/submit_btn"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="SUBMIT" />

        <Button
            android:id="@+id/edit_btn"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="EDIT"
            android:visibility="gone" />

        <Button
            android:id="@+id/display_btn"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="DISPLAY" />
    </LinearLayout>
</LinearLayout>

Step 4:

Now, let’s staring connectivity with the SQLite database.

Create a new database file named DBmain.java.

App ⇾ java ⇾ right click on package name ⇾ New ⇾ java class.

Extends SQLiteOpenHelper and implements the methods. Then create a constructer. After, create database name, table name and filed.

See the full example given below.

DBmain.java

package com.example.crudope;

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

import androidx.annotation.Nullable;

public class DBmain extends SQLiteOpenHelper {
    private static final String DBNAME = "student";
    private static final String TABLE = "subject";
    private static final int VER = 1;

    public DBmain(@Nullable Context context) {
        super(context, DBNAME, null, VER);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String query = "create table " + TABLE + "(id integer primary key, fname text, lname text)";
        db.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String query = "drop table if exists " + TABLE + "";
        db.execSQL(query);
        onCreate(db);
    }
}

Step 5:

Now, go to MainActivity.java.

App ⇾ java ⇾ packagename ⇾Mainactivity.java.

In the MainActivity.java file, we’ll create some methods and implement them.

Let’s understand by coding.

MainActivity.java

package com.example.crudope;

import androidx.appcompat.app.AppCompatActivity;

import android.content.ContentValues;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    DBmain dBmain;
    SQLiteDatabase sqLiteDatabase;
    EditText lname, fname;
    Button submit, display, edit;
    int id = 0;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dBmain = new DBmain(this);
        //create object
        findid();
        insertData();
        cleardata();
        editdata();
    }

    private void editdata() {
        if (getIntent().getBundleExtra("studata") != null) {
            Bundle bundle = getIntent().getBundleExtra("studata");
            id = bundle.getInt("id");
            fname.setText(bundle.getString("fname"));
            lname.setText(bundle.getString("lname"));

            edit.setVisibility(View.VISIBLE);
            submit.setVisibility(View.GONE);
        }
    }

    private void cleardata() {
        fname.setText("");
        lname.setText("");
    }

    private void insertData() {
        submit.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                ContentValues contentValues = new ContentValues();
                contentValues.put("fname", fname.getText().toString().trim());
                contentValues.put("lname", lname.getText().toString().trim());

                sqLiteDatabase = dBmain.getWritableDatabase();
                Long recid = sqLiteDatabase.insert("subject", null, contentValues);
                if (recid != null) {
                    Toast.makeText(MainActivity.this, "successfully insert", Toast.LENGTH_SHORT).show();
                    cleardata();
                } else {
                    Toast.makeText(MainActivity.this, "something wrong try again", Toast.LENGTH_SHORT).show();
                }
            }
        });
        display.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent intent = new Intent(MainActivity.this, MainActivity2.class);
                startActivity(intent);
            }
        });
        edit.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                ContentValues contentValues = new ContentValues();
                contentValues.put("fname", fname.getText().toString().trim());
                contentValues.put("lname", lname.getText().toString().trim());

                sqLiteDatabase = dBmain.getWritableDatabase();
                long recid = sqLiteDatabase.update("subject", contentValues, "id=" + id, null);
                if (recid != -1) {
                    Toast.makeText(MainActivity.this, "Update successfully", Toast.LENGTH_SHORT).show();
                    submit.setVisibility(View.VISIBLE);
                    edit.setVisibility(View.GONE);
                    cleardata();
                } else {
                    Toast.makeText(MainActivity.this, "something wrong try again", Toast.LENGTH_SHORT).show();
                }
            }
        });
    }

    private void findid() {
        fname = (EditText) findViewById(R.id.fname);
        lname = (EditText) findViewById(R.id.lname);
        submit = (Button) findViewById(R.id.submit_btn);
        display = (Button) findViewById(R.id.display_btn);
        edit = (Button) findViewById(R.id.edit_btn);
    }
}

We have already learned in detail in SQLite database ListView with CheckBox and RadioButtons values in SQLite, so we do not discuss more in this topic.

Step 6:

Now, we want to display data, so we’ll create a new empty activity.

App ⇾ java ⇾Right-click on package name ⇾ New ⇾ Activity ⇾ Empty Activity.

Name your activity. Here named MainActivity2 activity that is by default. You can name anything as you like.

Creating a new activity, go to activity_main2.xml and create a ListView widget on it.

See the below code.

Activity_main2.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=".MainActivity2">
<ListView
    android:divider="#E6DCCE"
    android:dividerHeight="1dp"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:id="@+id/lv"/>
</LinearLayout>

Step 7:

Open MainActivity2.java file.

In MainActivity2.java file, we’ll create some methods and implement them. We’ll use a custom adapter, so create an inner class and extends BaseAdapter and implement the method.

 We’ll need a single data layout file. So we’ll create a single data layout file in xml.

Step 8:

Create a layout file named singledata.xml.

App ⇾ layout ⇾Right-click on it ⇾New ⇾ layout resource file.

In the singledata.xml file, we’ll use CardView, TextView and ImageButton.

Full example of singledata.xml.

Singledata.xml

<?xml version="1.0" encoding="utf-8"?>
<androidx.cardview.widget.CardView xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    app:cardCornerRadius="12dp"
    app:cardElevation="8dp"
    android:id="@+id/cardview"
    app:cardMaxElevation="8dp"
    app:cardPreventCornerOverlap="true"
    app:cardUseCompatPadding="true">

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:padding="15dp">

        <TextView
            android:visibility="visible"
            android:id="@+id/txt_fname"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:gravity="center"
            android:text="textfname" />

        <TextView
            android:visibility="visible"
            android:id="@+id/txt_lname"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignParentEnd="true"
            android:gravity="center"
            android:text="textlname" />

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_below="@+id/txt_lname"
            android:orientation="horizontal">

            <ImageButton
                android:background="@null"
                android:layout_weight="1"
                android:visibility="gone"
                android:id="@+id/txt_edti"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:src="@drawable/ic_baseline_edit_24"/>

            <ImageButton
                android:visibility="gone"
                android:background="@null"
                android:src="@drawable/ic_baseline_delete_24"
                android:id="@+id/txt_delete"
                android:layout_weight="1"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content" />

        </LinearLayout>
    </RelativeLayout>
</androidx.cardview.widget.CardView>

New Attribute and work.

app:cardCornerRadius="12dp"

cardCornerRadius attribute is used to set corner radius.

app:cardUseCompatPadding="true"

cardUseCompatPadding attribute is used to add the same value padding.

app:cardMaxElevation="8dp"

cardMaxElevation This attribue is used to maximum elevation.

app:cardPreventCornerOverlap="true"

cardPreventCornerOverlap This used to extra padding to content.

app:cardElevation="8dp"

cardElevation this attribute defined card shadow.

android:visibility="visible"

This attribute is used to display button.

android:background="@null"

This attribute is use to make transparent background mean remove ImageButton background.

android:visibility="gone"

This attribute is used to be invisible but it is available in the layout.

Now, come to the MainActivity2.java

In MainActivity2.java class, add the below code.

MainActivity2.java

package com.example.crudope;

import androidx.appcompat.app.AppCompatActivity;
import androidx.cardview.widget.CardView;

import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Color;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.ImageButton;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

import java.util.Random;

public class MainActivity2 extends AppCompatActivity {
    DBmain dBmain;
    SQLiteDatabase sqLiteDatabase;
    String[] fname, lname;
    int[] id;
    ListView lv;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main2);
        dBmain = new DBmain(this);
        findid();
        displaydata();

    }

    private void displaydata() {
        sqLiteDatabase = dBmain.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.rawQuery("select *from subject", null);
        if (cursor.getCount() > 0) {
            id = new int[cursor.getCount()];
            fname = new String[cursor.getCount()];
            lname = new String[cursor.getCount()];
            int i = 0;

            while (cursor.moveToNext()) {
                id[i] = cursor.getInt(0);
                fname[i] = cursor.getString(1);
                lname[i] = cursor.getString(2);
                i++;
            }
            CustAdapter custAdapter = new CustAdapter();
            lv.setAdapter(custAdapter);
        }
    }

    private void findid() {
        lv = (ListView) findViewById(R.id.lv);
    }

    private class CustAdapter extends BaseAdapter {
        @Override
        public int getCount() {
            return fname.length;
        }

        @Override
        public Object getItem(int position) {
            return null;
        }

        @Override
        public long getItemId(int position) {
            return 0;
        }

        @Override
        public View getView(final int position, View convertView, ViewGroup parent) {
            TextView txtfname, txtlname;
            ImageButton txt_edit, txt_delete;
            CardView cardview;
            convertView = LayoutInflater.from(MainActivity2.this).inflate(R.layout.singledata, parent, false);
            txtfname = convertView.findViewById(R.id.txt_fname);
            txtlname = convertView.findViewById(R.id.txt_lname);
            txt_edit = convertView.findViewById(R.id.txt_edti);
            txt_delete = convertView.findViewById(R.id.txt_delete);
            cardview = convertView.findViewById(R.id.cardview);
            cardview.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    //background random color
                    Random random = new Random();
                    cardview.setCardBackgroundColor(Color.argb(255, random.nextInt(256), random.nextInt(256), random.nextInt(256)));
                    txt_delete.setVisibility(View.VISIBLE);
                    txt_edit.setVisibility(View.VISIBLE);
                    txtfname.setVisibility(View.GONE);
                    txtlname.setVisibility(View.GONE);
                }
            });
            txtfname.setText(fname[position]);
            txtlname.setText(lname[position]);
            txt_edit.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    Bundle bundle = new Bundle();
                    bundle.putInt("id", id[position]);
                    bundle.putString("fname", fname[position]);
                    bundle.putString("lname", lname[position]);
                    Intent intent = new Intent(MainActivity2.this, MainActivity.class);
                    intent.putExtra("studata", bundle);
                    startActivity(intent);
                }
            });
            txt_delete.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    sqLiteDatabase = dBmain.getReadableDatabase();
                    long recremove = sqLiteDatabase.delete("subject", "id=" + id[position], null);
                    if (recremove != -1) {
                        Toast.makeText(MainActivity2.this, "successfully delete", Toast.LENGTH_SHORT).show();
                        startActivity(new Intent(MainActivity2.this, MainActivity.class));
                        displaydata();
                    }
                }
            });
            return convertView;
        }
    }
}

Finally, the CURD operation is complete.

Now, run your app. You can see output like as below screen.

SQLite database CRUD operation in android studio
CardView data display ListView with SQLite database in android

Watch Related Videos on YouTube

Note: Any queries related to this app, then feel free to contact or comment.

Leave a Reply