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

<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>

<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>

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("");
    }
}

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});
    }
}