Q12

Question 12

Create an application to Create, Insert, Update and Delete operation on the database.

App Insights:

Taking Input as EditText and TextView, added onClick functions for each button written in a database helper class which execute the corresponding CREATE, INSERT, UPDATE and DELETE function in SQL database. It creates necessary table and database if does not exist before. Added View all entries in database which shows up entries in AlertDialog box. Update and Delete needs an id to pick the row and change in database correspondingly.

Browse Source Code

Code

activity_main.xml

<RelativeLayout 
xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"

    tools:context=".MainActivity">

    <TextView
        android:id="@+id/app_name"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true"
        android:layout_centerHorizontal="true"
        android:layout_marginStart="100dp"
        android:layout_marginTop="25dp"
        android:text="@string/AppHeading"
        android:
        textAppearance="?android:attr/textAppearanceLarge"
        android:textColor="@color/black"
        android:textSize="25sp"
        android:layout_alignParentLeft="true"
        android:layout_marginLeft="100dp" />


    <TextView
        android:id="@+id/textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true"
        android:layout_marginStart="15dp"
        android:layout_marginTop="100dp"
        android:text="@string/name"
        android:
        textAppearance="?android:attr/textAppearanceLarge"
        android:layout_alignParentLeft="true"
        android:layout_marginLeft="15dp" />

    <TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/editText_name"
        android:layout_alignParentStart="true"
        android:layout_marginStart="15dp"
        android:layout_marginTop="25dp"
        android:text="@string/surname"
        android:
        textAppearance="?android:attr/textAppearanceLarge"
        android:layout_alignParentLeft="true"
        android:layout_marginLeft="15dp" />

    <TextView
        android:id="@+id/textView3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/editText_surname"
        android:layout_alignParentStart="true"
        android:layout_marginStart="15dp"
        android:layout_marginTop="25dp"
        android:text="@string/marks"
        android:
        textAppearance="?android:attr/textAppearanceLarge"
        android:layout_alignParentLeft="true"
        android:layout_marginLeft="15dp" />


    <TextView
        android:id="@+id/textView_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/editText_Marks"
        android:layout_alignParentStart="true"
        android:layout_marginStart="15dp"
        android:layout_marginTop="25dp"
        android:text="@string/id"
        android:
        textAppearance="?android:attr/textAppearanceLarge"
        android:layout_alignParentLeft="true"
        android:layout_marginLeft="15dp" />


    <EditText
        android:id="@+id/editText_name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_alignTop="@+id/textView"
        android:layout_marginTop="-10dp"
        android:layout_toEndOf="@+id/textView"
        android:autofillHints=""
        android:inputType="text"
        tools:ignore="LabelFor"
        android:layout_toRightOf="@+id/textView" />

    <EditText
        android:id="@+id/editText_surname"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_alignTop="@+id/textView2"
        android:layout_marginStart="1dp"
        android:layout_marginTop="-10dp"
        android:layout_toEndOf="@+id/textView2"
        android:autofillHints=""
        android:inputType="text"
        tools:ignore="LabelFor"
        android:layout_marginLeft="1dp"
        android:layout_toRightOf="@+id/textView2" />

    <EditText
        android:id="@+id/editText_Marks"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/editText_surname"
        android:layout_marginStart="1dp"
        android:layout_marginTop="27dp"
        android:layout_toEndOf="@+id/textView3"
        android:height="15dp"
        android:autofillHints=""
        android:inputType="number"
        tools:ignore="LabelFor"
        android:layout_marginLeft="1dp"
        android:layout_toRightOf="@+id/textView3" />

    <EditText
        android:id="@+id/editText_id"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@id/editText_Marks"
        android:layout_marginStart="1dp"
        android:layout_marginTop="15dp"
        android:layout_toEndOf="@id/textView_id"
        android:autofillHints=""
        android:inputType="number"
        tools:ignore="LabelFor"
        android:layout_toRightOf="@id/textView_id"
        android:layout_marginLeft="1dp" />

    <Button
        android:id="@+id/button_add"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/editText_Marks"
        android:layout_alignParentStart="true"
        android:layout_marginStart="50dp"
        android:layout_marginTop="100dp"
        android:text="@string/add_data"
        android:layout_alignParentLeft="true"
        android:layout_marginLeft="50dp" />

    <Button
        android:id="@+id/button_viewAll"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="100dp"
        android:layout_marginTop="370dp"
        android:layout_toEndOf="@id/button_add"
        android:height="15dp"
        android:text="@string/view_all"
        android:layout_marginLeft="100dp"
        android:layout_toRightOf="@id/button_add" />

    <Button
        android:id="@+id/button_update"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/button_add"
        android:layout_marginStart="55dp"
        android:text="@string/update"
        android:layout_marginLeft="55dp" />

    <Button
        android:id="@+id/button_delete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@id/button_viewAll"
        android:layout_marginStart="118dp"
        android:layout_toEndOf="@id/button_update"
        android:text="@string/delete"
        android:layout_marginLeft="118dp"
        android:layout_toRightOf="@id/button_update" />

    <Button
        android:id="@+id/clear"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentEnd="false"
        android:layout_centerHorizontal="true"
        android:layout_marginTop="500dp"
        android:onClick="clear"
        android:text="@string/reset"
        tools:ignore="RelativeOverlap"
        android:layout_alignParentRight="false" />

    <TextView
        android:id="@+id/textView4"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentBottom="true"
        android:layout_marginStart="25dp"
        android:
        text="@string/for_update_delete_enter_id_first"
        android:textAllCaps="true"
        android:textSize="24sp"
        android:layout_marginLeft="25dp" />

</RelativeLayout>

strings.xml

<resources>
    <string name="app_name">Q12_Database_App</string>
    <string name="name">Name</string>
    <string name="surname">Surname</string>
    <string name="marks">Marks</string>
    <string name="reset">Reset</string>
    <string name="id">ID</string>
    <string name="AppHeading">Database Application</string>
    <string name="add_data">Add data</string>
    <string name="view_all">View All</string>
    <string name="update">Update</string>
    <string name="delete">Delete</string>
    <string 
    name="for_update_delete_enter_id_first">
    For Update/delete, Enter ID first</string>


</resources>

MainActivity.java

package com.example.q12_database_app;

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

import androidx.appcompat.app.AppCompatActivity;


public class MainActivity extends AppCompatActivity {
    dbhelper myDb;
    EditText editName,editSurname,editMarks ,editTextId;
    Button btnAddData;
    Button btnviewAll;
    Button btnDelete;

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

        editName = findViewById(R.id.editText_name);
        editSurname = findViewById(R.id.editText_surname);
        editMarks = findViewById(R.id.editText_Marks);
        editTextId = findViewById(R.id.editText_id);
        btnAddData = findViewById(R.id.button_add);
        btnviewAll = findViewById(R.id.button_viewAll);
        btnviewUpdate= findViewById(R.id.button_update);
        btnDelete= findViewById(R.id.button_delete);
        AddData();
        viewAll();
        UpdateData();
        DeleteData();
    }
    public void DeleteData() {
        btnDelete.setOnClickListener(
                v -> {
                    Integer deletedRows = 
                    myDb.deleteData(
                    editTextId.getText().toString()
                    );
                    if(deletedRows > 0)
                        Toast.makeText(
                        MainActivity.this,
                        "Data Deleted",
                        Toast.LENGTH_LONG
                        ).show();
                    else
                        Toast.makeText(
                        MainActivity.this,
                        "Data not Deleted",
                        Toast.LENGTH_LONG
                        ).show();
                }
        );
    }
    public void UpdateData() {
        btnviewUpdate.setOnClickListener(
                v -> {
                    boolean isUpdate = 
                    myDb.updateData(
                    editTextId.getText().toString(),
                            editName.getText().toString(),
                            editSurname.getText().toString(),
                            editMarks.getText().toString());
                    if(isUpdate)
                        Toast.makeText(
                        MainActivity.this,
                        "Data Update",
                        Toast.LENGTH_LONG
                        ).show();
                    else
                        Toast.makeText(
                        MainActivity.this,
                        "Data not Updated",
                        Toast.LENGTH_LONG
                        ).show();
                }
        );
    }
    public  void AddData() {
        btnAddData.setOnClickListener(
                v -> {
                    boolean isInserted = 
                    myDb.insertData(
                    editName.getText().toString(),
                            editSurname.getText().toString(),
                            editMarks.getText().toString());
                    if(isInserted)
                        Toast.makeText(
                        MainActivity.this,
                        "Data Inserted",
                        Toast.LENGTH_LONG
                        ).show();
                    else
                        Toast.makeText(
                        MainActivity.this,
                        "Data not Inserted",
                        Toast.LENGTH_LONG
                        ).show();
                }
        );
    }

    public void viewAll() {
        btnviewAll.setOnClickListener(
                v -> {
                    Cursor res = myDb.getAllData();
                    if(res.getCount() == 0) {
                        // show message
                        showMessage("Error","Nothing found");
                        return;
                    }

                    StringBuilder buffer = 
                    new StringBuilder();
                    while (res.moveToNext()) {
                        buffer.append("Id :").append(
                        res.getString(0)).append("\n");
                        buffer.append("Name :").append(
                        res.getString(1)).append("\n");
                        buffer.append("Surname :").append(
                        res.getString(2)).append("\n");
                        buffer.append("Marks :").append(
                        res.getString(3)).append("\n\n");
                    }

                    // Show all data
                    showMessage("Data",buffer.toString());
                }
        );
    }

    public void showMessage(String title,String Message){
        AlertDialog.Builder builder = 
        new AlertDialog.Builder(this);
        builder.setCancelable(true);
        builder.setTitle(title);
        builder.setMessage(Message);
        builder.show();
    }
    public void clear(View v) {
        editName.setText("");
        editSurname.setText("");
        editMarks.setText("");
        editTextId.setText("");
    }
}

dbhelper.java

package com.example.q12_database_app;

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

public class dbhelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "Student.db";
    public static final String TABLE_NAME = "student_table";
    public static final String COL_1 = "ID";
    public static final String COL_2 = "NAME";
    public static final String COL_3 = "SURNAME";
    public static final String COL_4 = "MARKS";

    public dbhelper(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 " 
        + ",NAME TEXT,SURNAME TEXT,MARKS INTEGER)");
    }

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

    public boolean insertData(String name,
    String surname,String marks) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_2,name);
        contentValues.put(COL_3,surname);
        contentValues.put(COL_4,marks);
        long result = db.insert(TABLE_NAME,null, 
        contentValues);
        return result != -1;
    }

    public Cursor getAllData() {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.rawQuery("select * from " 
        + TABLE_NAME, null);
    }

    public boolean updateData(String id,
    String name,String surname,String marks) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_1,id);
        contentValues.put(COL_2,name);
        contentValues.put(COL_3,surname);
        contentValues.put(COL_4,marks);
        db.update(TABLE_NAME, contentValues,
         "ID = ?",new String[] { id });
        return true;
    }

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

Download Q12_Database_App.apk to Run and Test Locally

Output

Studio Screenshot Main Activity (1/18)

App Screenshot Main Activity (2/18)

Studio Screenshot Adding First Row (3/18)

Adding data App Screenshot Adding First Row (4/18)

Studio Screenshot Adding Second Row (5/18)

App Screenshot Adding Second Row (6/18)

Studio Screenshot Adding Third Row (7/18)

App Screenshot Adding Third Row (8/18)

Studio Screenshot Viewing All Three Rows (9/18)

App Screenshot Viewing All Three Rows (10/18)

Studio Screenshot Updating Row One (11/18)

App Screenshot Updating Row One (12/18)

Studio Screenshot Viewing All After Update (13/18)

App Screenshot Viewing All After Update (14/18)

Studio Screenshot Deleting Row Two (15/18

App Screenshot Deleting Row Two (16/18)

Studio Screenshot Viewing All After Delete (17/18)

App Screenshot Viewing All After Delete (18/18)