Android Studio And Sqlite Database Tutorial Mobile Application Development (Crud) Create, Read, Update And Delete

Android Studio And Sqlite Database Tutorial Mobile Application Development (Crud) Create, Read, Update And Delete

This is an android program that shows how to Create, Read, Update And Delete data stored in sqlite database. Different android “activities” (Main, Login, Home) are created in this tutorial. The source code below has been tested and it’s fully working.

SOURCE CODE

MainActivity.java


package com.example.InsertUpdateDeleteDisplayAndroidApplicationAndSqliteDatabase;

import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.database.Cursor;
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 {
    //Creating DatabaseHelper Object
    DatabaseHelper myDb;
    EditText userName, password, gender, dateOfBirth, editTextId;
    Button btnAddData;
    Button btnviewAll;
    Button btnDelete;
    Button btnLogin;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        myDb = new DatabaseHelper(this);

        editTextId = (EditText) findViewById(R.id.editTextTextPersonID);
        userName = (EditText) findViewById(R.id.editTextTextPersonUserName);
        password = (EditText) findViewById(R.id.editTextTextPersonPassword);
        gender = (EditText) findViewById(R.id.editTextTextPersonGender);
        dateOfBirth = (EditText) findViewById(R.id.editTextTextPersonDateOfBirth);
        btnAddData = (Button) findViewById(R.id.btnAddData);
        btnLogin = (Button) findViewById(R.id.btnLogin);


        //Invoking AddData
        AddData();
        //Invoking BtnLogin
        BtnLogin();
    }

    private void BtnLogin() {
        btnLogin.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                //Opening new activity on button click.
                openActivity2();
            }
        });
    }

    public void openActivity2() {
        Intent intent = new Intent(this, Login.class);
        startActivity(intent);
    }


    public void AddData() {
        btnAddData.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                boolean isInserted = myDb.insertData(userName.getText().toString(),
                        password.getText().toString(),
                        gender.getText().toString(),
                        dateOfBirth.getText().toString());
                if (isInserted == true)
                    Toast.makeText(MainActivity.this, "Data Inserted", Toast.LENGTH_LONG).show();
                else
                    Toast.makeText(MainActivity.this, "Data not Inserted", Toast.LENGTH_LONG).show();
            }
        });
    }

}

Login.java


package com.example.InsertUpdateDeleteDisplayAndroidApplicationAndSqliteDatabase;

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;

public class Login extends AppCompatActivity {
    DatabaseHelper myDb;
    EditText userName, password, gender,dateOfBirth, editTextId;
    Button btnLogin;

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

        myDb = new DatabaseHelper(this);

        userName = (EditText)findViewById(R.id.editTextTextPersonUserName);
        password = (EditText)findViewById(R.id.editTextTextPersonPassword);
        btnLogin = (Button) findViewById(R.id.btnLogin);

        login();

    }


    public void goToHome() {
        Intent intent = new Intent(this, Home.class);
        startActivity(intent);
    }


    private void login() {
        btnLogin.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                boolean isInserted = myDb.checkUser(userName.getText().toString(),
                        password.getText().toString()
                );
                if(isInserted == true) {
                    myDb.setUsername(userName.getText().toString());
                    Toast.makeText(Login.this, "Logged In As " + userName.getText().toString(), Toast.LENGTH_LONG).show();
                    goToHome();
                }
                else
                    Toast.makeText(Login.this,"Not Logged In",Toast.LENGTH_LONG).show();
            }
        });
    }
}


Home.java


package com.example.InsertUpdateDeleteDisplayAndroidApplicationAndSqliteDatabase;

import androidx.appcompat.app.AppCompatActivity;

import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class Home extends AppCompatActivity {

    DatabaseHelper myDb;
    EditText userName, password, gender, dateOfBirth, editTextId;
    Button btnAddData;
    Button btnUpdate;
    Button btnDelete;

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

        myDb = new DatabaseHelper(this);

        editTextId = (EditText) findViewById(R.id.editTextTextPersonID);
        userName = (EditText) findViewById(R.id.editTextTextPersonUserName);
        password = (EditText) findViewById(R.id.editTextTextPersonPassword);
        gender = (EditText) findViewById(R.id.editTextTextPersonGender);
        dateOfBirth = (EditText) findViewById(R.id.editTextTextPersonDateOfBirth);
        btnAddData = (Button) findViewById(R.id.btnAddData);
        btnDelete = (Button) findViewById(R.id.btnDelete);
        btnUpdate = (Button) findViewById(R.id.btnUpdate);


        userName.setText(myDb.getUsername());

        Cursor c = myDb.getAllData(myDb.getUsername());
        if (c.moveToNext()) {
            editTextId.setText(c.getString(0));
            userName.setText(c.getString(1));
            password.setText(c.getString(2));
            gender.setText(c.getString(3));
            dateOfBirth.setText(c.getString(4));
        }

        DeleteData();
        UpdateData();

    }

    /**
     *
     */
    public void clear() {
        editTextId.setText("");
        userName.setText("");
        password.setText("");
        gender.setText("");
        dateOfBirth.setText("");
    }

    /**
     *
     */
    public void DeleteData() {
        btnDelete.setOnClickListener(
                new View.OnClickListener() {
                    @Override
                    public void onClick(View v) {
                        Integer deletedRows = myDb.deleteData(editTextId.getText().toString());
                        if (deletedRows > 0)
                            Toast.makeText(Home.this, "Data Deleted", Toast.LENGTH_LONG).show();
                        else
                            Toast.makeText(Home.this, "Data not Deleted", Toast.LENGTH_LONG).show();
                    }
                }
        );
    }

    /**
     *
     */
    public void UpdateData() {
        btnUpdate.setOnClickListener(
                new View.OnClickListener() {
                    @Override
                    public void onClick(View v) {
                        boolean isUpdate = myDb.updateData(editTextId.getText().toString(),
                                userName.getText().toString(),
                                password.getText().toString(), gender.getText().toString(), dateOfBirth.getText().toString());
                        if (isUpdate == true)
                            Toast.makeText(Home.this, "Data Updated", Toast.LENGTH_LONG).show();
                        else
                            Toast.makeText(Home.this, "Data not Updated", Toast.LENGTH_LONG).show();
                    }
                }
        );
    }
}

DatabaseHelper.java





package com.example.InsertUpdateDeleteDisplayAndroidApplicationAndSqliteDatabase;

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

import androidx.annotation.Nullable;

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "Users.db";
    public static final String TABLE_NAME = "MyUsers";
    public static final String COL_1 = "ID";
    public static final String COL_2 = "USERNAME";
    public static final String COL_3 = "PASSWORD";
    public static final String COL_4 = "GENDER";
    public static final String COL_5 = "DATEOFBIRTH";
    public static String user = "";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT,USERNAME TEXT,PASSWORD TEXT,GENDER TEXT, DATEOFBIRTH TEXT)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    /**
     * @param username
     * @param password
     * @param gender
     * @param datOfBirth
     * @return
     */
    public boolean insertData(String username, String password, String gender, String datOfBirth) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_2, username);
        contentValues.put(COL_3, password);
        contentValues.put(COL_4, gender);
        contentValues.put(COL_5, datOfBirth);
        long result = db.insert(TABLE_NAME, null, contentValues);
        if (result == -1)
            return false;
        else
            return true;
    }

    /**
     * @param categoryex
     * @return
     */
    public Cursor getAllData(String categoryex) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res = db.rawQuery("select * from " + TABLE_NAME + " where USERNAME = '" + categoryex + "'", null);
        return res;
    }

    /**
     * @param id
     * @param username
     * @param password
     * @param gender
     * @param datOfBirth
     * @return
     */
    public boolean updateData(String id, String username, String password, String gender, String datOfBirth) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_1, id);
        contentValues.put(COL_2, username);
        contentValues.put(COL_3, password);
        contentValues.put(COL_4, gender);
        contentValues.put(COL_5, datOfBirth);
        db.update(TABLE_NAME, contentValues, "ID = ?", new String[]{id});
        return true;
    }

    /**
     * @param id
     * @return
     */
    public Integer deleteData(String id) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_NAME, "ID = ?", new String[]{id});
    }

    /**
     * @param username
     * @param password
     * @return
     */
    public boolean checkUser(String username, String password) {
        // array of columns to fetch
        String[] columns = {
                COL_1
        };
        SQLiteDatabase db = this.getReadableDatabase();
        // selection criteria
        String selection = COL_2 + " = ?" + " AND " + COL_3 + " = ?";
        // selection arguments
        String[] selectionArgs = {username, password};
        // query user table with conditions
        /**
         * Here query function is used to fetch records from user table this function works like we use sql query.
         * SQL query equivalent to this query function is
         * SELECT username FROM user WHERE username = 'jac' AND password = 'qwerty';
         */
        Cursor cursor = db.query(TABLE_NAME, //Table to query
                columns,                    //columns to return
                selection,                  //columns for the WHERE clause
                selectionArgs,              //The values for the WHERE clause
                null,                       //group the rows
                null,                       //filter by row groups
                null);                      //The sort order
        int cursorCount = cursor.getCount();
        cursor.close();
        db.close();
        if (cursorCount > 0) {
            return true;
        }
        return false;
    }

    /**
     * @param userName
     */
    public void setUsername(String userName) {
        this.user = userName;
    }

    /**
     * @return
     */
    public String getUsername() {
        return this.user;
    }
}

activity_main.xml


<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.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"
    tools:context=".MainActivity">

    <Button
        android:id="@+id/btnLogin"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="140dp"
        android:layout_marginRight="140dp"
        android:layout_marginBottom="32dp"
        android:text="Login"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent" />

    <Button
        android:id="@+id/btnAddData"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="136dp"
        android:layout_marginRight="136dp"
        android:layout_marginBottom="100dp"
        android:text="Sign Up User"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent" />

    <EditText
        android:id="@+id/editTextTextPersonUserName"
        android:layout_width="270dp"
        android:layout_height="48dp"
        android:layout_marginTop="8dp"
        android:layout_marginEnd="8dp"
        android:layout_marginRight="8dp"
        android:layout_marginBottom="36dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintBottom_toTopOf="@+id/editTextTextPersonPassword"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <EditText
        android:id="@+id/editTextTextPersonPassword"
        android:layout_width="270dp"
        android:layout_height="51dp"
        android:layout_marginTop="8dp"
        android:layout_marginEnd="8dp"
        android:layout_marginRight="8dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editTextTextPersonUserName" />

    <EditText
        android:id="@+id/editTextTextPersonGender"
        android:layout_width="270dp"
        android:layout_height="46dp"
        android:layout_marginTop="8dp"
        android:layout_marginEnd="8dp"
        android:layout_marginRight="8dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editTextTextPersonPassword" />

    <EditText
        android:id="@+id/editTextTextPersonDateOfBirth"
        android:layout_width="270dp"
        android:layout_height="52dp"
        android:layout_marginTop="8dp"
        android:layout_marginEnd="8dp"
        android:layout_marginRight="8dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editTextTextPersonGender" />

    <EditText
        android:id="@+id/editTextTextPersonID"
        android:layout_width="270dp"
        android:layout_height="40dp"
        android:layout_marginTop="4dp"
        android:layout_marginEnd="8dp"
        android:layout_marginRight="8dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editTextTextPersonDateOfBirth" />

    <TextView
        android:id="@+id/textView3"
        android:layout_width="101dp"
        android:layout_height="43dp"
        android:layout_marginTop="21dp"
        android:text="User Name"
        app:layout_constraintTop_toTopOf="parent"
        tools:layout_editor_absoluteX="16dp" />

    <TextView
        android:id="@+id/textView4"
        android:layout_width="103dp"
        android:layout_height="35dp"
        android:layout_marginTop="16dp"
        android:text="Password"
        app:layout_constraintTop_toBottomOf="@+id/textView3"
        tools:layout_editor_absoluteX="16dp" />

    <TextView
        android:id="@+id/textView5"
        android:layout_width="99dp"
        android:layout_height="36dp"
        android:layout_marginTop="18dp"
        android:text="Gender"
        app:layout_constraintTop_toBottomOf="@+id/textView4"
        tools:layout_editor_absoluteX="16dp" />

    <TextView
        android:id="@+id/textView6"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="23dp"
        android:text="Date Of Birth"
        app:layout_constraintTop_toBottomOf="@+id/textView5"
        tools:layout_editor_absoluteX="23dp" />

    <TextView
        android:id="@+id/textView7"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="33dp"
        android:text="ID"
        app:layout_constraintTop_toBottomOf="@+id/textView6"
        tools:layout_editor_absoluteX="19dp" />

</androidx.constraintlayout.widget.ConstraintLayout>

activity_login.xml


<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.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"
    tools:context=".Login">

    <Button
        android:id="@+id/btnLogin"
        android:layout_width="212dp"
        android:layout_height="56dp"
        android:layout_marginEnd="100dp"
        android:layout_marginRight="100dp"
        android:layout_marginBottom="128dp"
        android:text="Login"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent" />

    <EditText
        android:id="@+id/editTextTextPersonUserName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="16dp"
        android:layout_marginRight="16dp"
        android:layout_marginBottom="47dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintBottom_toTopOf="@+id/editTextTextPersonPassword"
        app:layout_constraintEnd_toEndOf="parent" />

    <EditText
        android:id="@+id/editTextTextPersonPassword"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="16dp"
        android:layout_marginRight="16dp"
        android:layout_marginBottom="47dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintBottom_toTopOf="@+id/btnLogin"
        app:layout_constraintEnd_toEndOf="parent" />

    <TextView
        android:id="@+id/textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="29dp"
        android:layout_marginLeft="29dp"
        android:layout_marginBottom="73dp"
        android:text="User Name"
        app:layout_constraintBottom_toTopOf="@+id/textView2"
        app:layout_constraintStart_toStartOf="parent" />

    <TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="29dp"
        android:layout_marginLeft="29dp"
        android:layout_marginBottom="249dp"
        android:text="Password"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintStart_toStartOf="parent" />
</androidx.constraintlayout.widget.ConstraintLayout>

activity_home.xml


<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.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"
    tools:context=".Home">

    <EditText
        android:id="@+id/editTextTextPersonUserName"
        android:layout_width="270dp"
        android:layout_height="48dp"
        android:layout_marginTop="8dp"
        android:layout_marginEnd="16dp"
        android:layout_marginRight="16dp"
        android:layout_marginBottom="36dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintBottom_toTopOf="@id/editTextTextPersonID"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.073" />

    <EditText
        android:id="@+id/editTextTextPersonID"
        android:layout_width="270dp"
        android:layout_height="40dp"
        android:layout_marginTop="40dp"
        android:layout_marginEnd="16dp"
        android:layout_marginRight="16dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintTop_toBottomOf="@id/editTextTextPersonDateOfBirth" />

    <EditText
        android:id="@+id/editTextTextPersonDateOfBirth"
        android:layout_width="270dp"
        android:layout_height="52dp"
        android:layout_marginTop="32dp"
        android:layout_marginEnd="16dp"
        android:layout_marginRight="16dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintTop_toBottomOf="@id/editTextTextPersonGender" />

    <Button
        android:id="@+id/btnDelete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="140dp"
        android:layout_marginRight="140dp"
        android:layout_marginBottom="32dp"
        android:text="Delete"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent" />

    <Button
        android:id="@+id/btnUpdate"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="136dp"
        android:layout_marginRight="136dp"
        android:layout_marginBottom="100dp"
        android:text="Update"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent" />

    <EditText
        android:id="@+id/editTextTextPersonGender"
        android:layout_width="270dp"
        android:layout_height="46dp"
        android:layout_marginTop="32dp"
        android:layout_marginEnd="16dp"
        android:layout_marginRight="16dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintTop_toBottomOf="@id/editTextTextPersonPassword" />

    <EditText
        android:id="@+id/editTextTextPersonPassword"
        android:layout_width="270dp"
        android:layout_height="51dp"
        android:layout_marginTop="32dp"
        android:layout_marginEnd="16dp"
        android:layout_marginRight="16dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintTop_toBottomOf="@id/editTextTextPersonUserName" />

    <TextView
        android:id="@+id/textView8"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="41dp"
        android:text="User Name"
        app:layout_constraintTop_toTopOf="parent"
        tools:layout_editor_absoluteX="13dp" />

    <TextView
        android:id="@+id/textView9"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="57dp"
        android:text="Password"
        app:layout_constraintTop_toBottomOf="@+id/textView8"
        tools:layout_editor_absoluteX="13dp" />

    <TextView
        android:id="@+id/textView10"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="60dp"
        android:text="Gender"
        app:layout_constraintTop_toBottomOf="@+id/textView9"
        tools:layout_editor_absoluteX="10dp" />

    <TextView
        android:id="@+id/textView11"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="62dp"
        android:text="Date Of Birth"
        app:layout_constraintTop_toBottomOf="@+id/textView10"
        tools:layout_editor_absoluteX="9dp" />

    <TextView
        android:id="@+id/textView12"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="71dp"
        android:text="ID"
        app:layout_constraintTop_toBottomOf="@+id/textView11"
        tools:layout_editor_absoluteX="16dp" />
</androidx.constraintlayout.widget.ConstraintLayout>

SCREENSHOTS

VIDEO DEMO





SOFTWARES

  1. DB Browser for SQLite
  2. Android Studio

Leave a Reply

Your email address will not be published. Required fields are marked *