Unit - 5 CRUD Operations using SQLiteOpenHelper Class

Lesson Plan: Unit - 05
Subject: P15A2AAD - Android Application Development
Topic of Study: Working with Database
Grade/Level: Master of Computer Applications
Objective: To CRUD operation using SQLiteOpenHelper Class
Time Allotment: 55 Minutes

Using SQLiteOpenHelper Class

Step 01 Exa. Android Project Structure
Step 02 Exa of app/src/main/AndroidManifest.xml

xml version="1.0" encoding="utf-8"?><manifest xmlns:android="http://schemas.android.com/apk/res/android"    package="com.example.exasqliteopen">

    <application        android:allowBackup="true"        android:icon="@mipmap/ic_launcher"        android:label="@string/app_name"        android:roundIcon="@mipmap/ic_launcher_round"        android:supportsRtl="true"        android:theme="@style/AppTheme">
        <activity android:name=".ViewPerson"></activity>
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

Step 03 Exa. of com.example.exasqliteopen.DatabaseHelper.java

package com.example.exasqliteopen;

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

public class DatabaseHelper extends SQLiteOpenHelper {
  public static final String DB_NAME="PersonsDB.db";
  public static final String TABLE_NAME="Perons";
  public static final String COLUMN_ID="id";
  public static final String COLUMN_NAME="name";
  public static final String COLUMN_ADD="address";

  public static final int DB_VER=1;

    public DatabaseHelper(Context context){
        super(context, DB_NAME, null, DB_VER);
    }

    @Override    public void onCreate(SQLiteDatabase db) {
    String sql = "CREATE TABLE "+TABLE_NAME+" (" +
            COLUMN_ID +" INTEGER PRIMARY KEY AUTOINCREMENT," +
            COLUMN_NAME +" TEXT," +
            COLUMN_ADD +" TEXT)";
    db.execSQL(sql);
    }

    @Override    public void onUpgrade(SQLiteDatabase db, int old_ver, int new_ver) {
    String sql = "DROP TABLE IF EXISTS Persons";
    db.execSQL(sql);
    onCreate(db);
    }
    public boolean addPerson(String nam, String add)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(COLUMN_NAME,nam);
        cv.put(COLUMN_ADD,add);
        db.insert(TABLE_NAME,null,cv);
        db.close();
        return true;
    }
    public Cursor getPerson(int id)
    {
        SQLiteDatabase db = this.getReadableDatabase();
        String sql = "SELECT * FROM "+TABLE_NAME+" where id="+id;
        Cursor c = db.rawQuery(sql,null);
        return c;
    }

    public boolean removePersonbyID(int id)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_NAME,"id=?",new String[]{String.valueOf(id)});
        return true;
    }

    public boolean removeallPersons()
    {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_NAME,null,null);
        return true;
    }

    public boolean updatePersonbyID(int id,String nam,String addr)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(COLUMN_NAME,nam);
        cv.put(COLUMN_ADD,addr);
        db.update(TABLE_NAME,cv,"id=?",new String[]{String.valueOf(id)});
        return true;
    }

    public boolean updateAllPerson(int id, String nam,String addr)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(COLUMN_NAME,nam);
        cv.put(COLUMN_ADD,addr);
        db.update(TABLE_NAME,cv,null,null);
        return true;
    }
}

Step 03 exa. of layout/activity_main.xml

XML Code of Layout:

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/etNAme"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:ems="10"        android:inputType="textPersonName" />

    <EditText        android:id="@+id/etvAddress"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:ems="10"        android:inputType="textPersonName" />

    <Button        android:id="@+id/btnSave"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:onClick="saveRecord"        android:text="Save" />

    <Button        android:id="@+id/btnView"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:onClick="viewRecord"        android:text="VIEW" />

    <Button        android:id="@+id/btnReset"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:onClick="clearRecord"        android:text="Reset" />
</LinearLayout>

Design of Component Tree
Design of BluePrint

Step 04 exa. of com.example.exasqliteopen.MainActivity.java

package com.example.exasqliteopen;

import android.content.Intent;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    EditText etName,etAddress;
    DatabaseHelper db;
    @Override    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = new DatabaseHelper(this);

        etName = findViewById(R.id.etNAme);
        etAddress = findViewById(R.id.etvAddress);
    }

    public void saveRecord(View view) {
        String n = etName.getText().toString();
        String a = etAddress.getText().toString();
        db.addPerson(n,a);
        Toast.makeText(MainActivity.this,
                    "Insert Sucess", Toast.LENGTH_SHORT).show();
    }

    public void viewRecord(View view) {
        Intent intent = new Intent(MainActivity.this,ViewPerson.class);
        startActivity(intent);
    }

    public void clearRecord(View view) {
        etName.setText("");
        etAddress.setText("");
    }
}

Step 05 exa. of layout/activity_view_person.xml

XML Code of Layout

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=".ViewPerson">

    <TextView        android:id="@+id/tvID"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="ID" />

    <EditText        android:id="@+id/etvID"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:ems="10"        android:inputType="number" />

    <Button        android:id="@+id/btnShow"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:onClick="showRecord"        android:text="Show" />

    <TextView        android:id="@+id/tvName"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="Name" />

    <EditText        android:id="@+id/etvName"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:ems="10"        android:inputType="textPersonName" />

    <TextView        android:id="@+id/tvAddress"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="Address" />

    <EditText        android:id="@+id/etvAddress"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:ems="10"        android:inputType="textPersonName" />

    <Button        android:id="@+id/btnBack"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:onClick="goBack"        android:text="Back" />

    <Button        android:id="@+id/btnDelete"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:onClick="removebyID"        android:text="Delete by ID" />

    <Button        android:id="@+id/btnUpdate"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:onClick="UpdatebyID"        android:text="Update by ID" />
</LinearLayout>

Design of Component Tree
Design of BluePrint


Step 06 exa. of com.example.exasqliteopen.ViewPerson.java

package com.example.exasqliteopen;

import android.content.Intent;
import android.database.Cursor;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;

public class ViewPerson extends AppCompatActivity {
    EditText etID,etName,etAddress;
    DatabaseHelper db;
    @Override    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        db = new DatabaseHelper(this);
        setContentView(R.layout.activity_view_person);
        etID = findViewById(R.id.etvID);
        etName = findViewById(R.id.etvName);
        etAddress = findViewById(R.id.etvAddress);
    }

    public void goBack(View view) {
        Intent intent = new Intent(ViewPerson.this,MainActivity.class);
        startActivity(intent);
    }

    public void showRecord(View view) {
        int id = Integer.parseInt(etID.getText().toString());
        Cursor c = db.getPerson(id);
        c.moveToFirst();
        if(c.getCount()==1)
        {
            etName.setText(c.getString(c.getColumnIndex(DatabaseHelper.COLUMN_NAME)));
            etAddress.setText(c.getString(c.getColumnIndex(DatabaseHelper.COLUMN_ADD)));
        }
        else        {
            Toast.makeText(this,
                    "no Record Found", Toast.LENGTH_SHORT).show();
            etName.setText("");
            etAddress.setText("");
        }
    }

    public void removebyID(View view) {
        int id = Integer.parseInt(etID.getText().toString());
        db.removePersonbyID(id);
        Toast.makeText(this, "Record Deleted", Toast.LENGTH_SHORT).show();
        etID.setText("");
    }

    public void UpdatebyID(View view) {
        int id = Integer.parseInt(etID.getText().toString());
        String n = etName.getText().toString();
        String a = etAddress.getText().toString();
        db.updatePersonbyID(id,n,a);
        Toast.makeText(this, "Record Updated", Toast.LENGTH_SHORT).show();
        etID.setText("");
    }
}

Unit - 5 CRUD Operations using SQLiteOpenHelper Class

Contact Form