How to perform CRUD operation in SQlite Database Android

Please Subscribe Youtube| Like Facebook | Follow Twitter

For Kotlin follow this article

Introduction

In this article we will learn how to perform CRUD operation in Sqlite Database Android (Example Code).          

E500 1.83 inch HD Screen ECG Monitor Heart Rate Blood Pressure

                                                   

Our Example

In our example we have Student Database table containing StudentID and StudentName as columns. In our main screen we will perform CRUD operation i.e create, read, update and delete student record.

Steps

Follow below steps

1) Code Database Operations in your app

2) Run and test your app

1) Code Database Operations in your app

Below is xml code of our Layout file

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

    <EditText
        android:id="@+id/student_id"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:ems="10"
        android:inputType="number"
        android:hint="Student ID"
        />

    <EditText
        android:id="@+id/student_name"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:ems="10"
        android:inputType="textPersonName"
        android:hint="Student Name"
        />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:text="Load All Students"
        android:onClick="loadStudents"
        />

    <TextView
        android:id="@+id/result"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:hint="Result"
        android:textSize="30dp"
        />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:onClick="addStudent"
        android:text="ADD" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:onClick="updateStudent"
        android:text="UPDATE"
        />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:onClick="deleteStudent"
        android:text="DELETE By Id"
        />


</LinearLayout>

MyDBHandler class is our database handler class which extends SQLiteOpenHelper class to manage database operations. First time on constructor (MyDBHandler) call we will create our database. In onCreate() method we will create student table.

MyDBHandler(Context context)
{
	super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

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

loadHandler() method will read all records from student table. addHandler() method will insert a new record in student table. updateHandler() method will update an existing record in student table. deleteHandler() method will delete selected record from student table.

String loadHandler() {
	String result = "";
	String query = "Select*FROM " + TABLE_STUDENTS;
	SQLiteDatabase db = this.getWritableDatabase();
	Cursor cursor = db.rawQuery(query, null);
	while (cursor.moveToNext()) {
		int result_0 = cursor.getInt(0);
		String result_1 = cursor.getString(1);
		result += String.valueOf(result_0) + " " + result_1 +
				System.getProperty("line.separator");
	}
	cursor.close();
	db.close();
	if(result.equals(""))
		result="No Record Found";
	return result;
}

long addHandler(Student student) {
	long id;
	ContentValues values = new ContentValues();
	values.put(COLUMN_ID, student.getID());
	values.put(COLUMN_NAME, student.getStudentName());
	SQLiteDatabase db = this.getWritableDatabase();
	id = db.insert(TABLE_STUDENTS, null, values);
	db.close();
	return id;
}

boolean updateHandler(int ID, String name) {
	SQLiteDatabase db = this.getWritableDatabase();
	ContentValues args = new ContentValues();
	args.put(COLUMN_ID, ID);
	args.put(COLUMN_NAME, name);
	return db.update(TABLE_STUDENTS, args, COLUMN_ID + "=" + ID, null) > 0;
}

boolean deleteHandler(int ID) {
	boolean result = false;
	String query = "Select*FROM " + TABLE_STUDENTS + " WHERE " + COLUMN_ID + " = '" + String.valueOf(ID) + "'";
	SQLiteDatabase db = this.getWritableDatabase();
	Cursor cursor = db.rawQuery(query, null);
	Student student = new Student();
	if (cursor.moveToFirst()) {
		student.setID(Integer.parseInt(cursor.getString(0)));
		db.delete(TABLE_STUDENTS, COLUMN_ID + "=?",
				new String[] {
						String.valueOf(student.getID())
				});
		cursor.close();
		result = true;
	}
	db.close();
	return result;
}

In Mainactivity on respective buttons clicks loadStudents(), addStudent(), updateStudent() and deleteStudent() methods will be called which will then internally invokes corresponding MyDBHandler method for crud operation.

public void loadStudents(View view) {
	resultText.setText(dbHandler.loadHandler());
	studentId.setText("");
	studentName.setText("");
}

public void addStudent (View view) {
	if(!studentId.getText().toString().isEmpty() && !studentName.getText().toString().isEmpty()) {
		int id = Integer.parseInt(studentId.getText().toString());
		String name = studentName.getText().toString();
		Student student = new Student(id, name);
		long insertId=dbHandler.addHandler(student);
		if(insertId==-1){
			resultText.setText("Record already exists");
		}
		else{
			studentId.setText("");
			studentName.setText("");
			resultText.setText("Record added");
		}
	}
	else{
		resultText.setText("Please fill correct id and name");
	}
}

public void updateStudent(View view) {
	if( !studentId.getText().toString().isEmpty() && !studentName.getText().toString().isEmpty()) {
		boolean result = dbHandler.updateHandler(Integer.parseInt(
				studentId.getText().toString()), studentName.getText().toString());
		if (result) {
			studentId.setText("");
			studentName.setText("");
			resultText.setText("Record Updated");
		} else {
			resultText.setText("No Record Found");
		}
	}
	else{
		resultText.setText("Please fill correct id and name");
	}
}

public void deleteStudent(View view) {
	if(!studentId.getText().toString().isEmpty()) {
		boolean result = dbHandler.deleteHandler(Integer.parseInt(
			studentId.getText().toString()));
		if (result) {
			studentId.setText("");
			studentName.setText("");
			resultText.setText("Record Deleted");
		} else {
			resultText.setText("No Record Found");
		}
	} else{
		resultText.setText("Please fill correct id");
	}
}

Whole Code

activity_main xml file

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

    <EditText
        android:id="@+id/student_id"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:ems="10"
        android:inputType="number"
        android:hint="Student ID"
        />

    <EditText
        android:id="@+id/student_name"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:ems="10"
        android:inputType="textPersonName"
        android:hint="Student Name"
        />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:text="Load All Students"
        android:onClick="loadStudents"
        />

    <TextView
        android:id="@+id/result"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:hint="Result"
        android:textSize="30dp"
        />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:onClick="addStudent"
        android:text="ADD" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:onClick="updateStudent"
        android:text="UPDATE"
        />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:onClick="deleteStudent"
        android:text="DELETE By Id"
        />


</LinearLayout>

MainActivity.java

package com.programtown.example;

import android.os.Bundle;
import android.text.method.ScrollingMovementMethod;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import androidx.appcompat.app.AppCompatActivity;

public class MainActivity extends AppCompatActivity {

    TextView resultText;
    EditText studentId;
    EditText studentName;
    MyDBHandler dbHandler;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        resultText = (TextView) findViewById(R.id.result);
        studentId = (EditText) findViewById(R.id.student_id);
        studentName = (EditText) findViewById(R.id.student_name);
        resultText.setMovementMethod(new ScrollingMovementMethod());
        dbHandler= new MyDBHandler(this);

    }

    public void loadStudents(View view) {
        resultText.setText(dbHandler.loadHandler());
        studentId.setText("");
        studentName.setText("");
    }

    public void addStudent (View view) {
        if(!studentId.getText().toString().isEmpty() && !studentName.getText().toString().isEmpty()) {
            int id = Integer.parseInt(studentId.getText().toString());
            String name = studentName.getText().toString();
            Student student = new Student(id, name);
            long insertId=dbHandler.addHandler(student);
            if(insertId==-1){
                resultText.setText("Record already exists");
            }
            else{
                studentId.setText("");
                studentName.setText("");
                resultText.setText("Record added");
            }
        }
        else{
            resultText.setText("Please fill correct id and name");
        }
    }

    public void updateStudent(View view) {
        if( !studentId.getText().toString().isEmpty() && !studentName.getText().toString().isEmpty()) {
            boolean result = dbHandler.updateHandler(Integer.parseInt(
                    studentId.getText().toString()), studentName.getText().toString());
            if (result) {
                studentId.setText("");
                studentName.setText("");
                resultText.setText("Record Updated");
            } else {
                resultText.setText("No Record Found");
            }
        }
        else{
            resultText.setText("Please fill correct id and name");
        }
    }

    public void deleteStudent(View view) {
        if(!studentId.getText().toString().isEmpty()) {
            boolean result = dbHandler.deleteHandler(Integer.parseInt(
                studentId.getText().toString()));
            if (result) {
                studentId.setText("");
                studentName.setText("");
                resultText.setText("Record Deleted");
            } else {
                resultText.setText("No Record Found");
            }
        } else{
            resultText.setText("Please fill correct id");
        }
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        dbHandler.close();
    }

}

MyDBHandler.java

package com.programtown.example;

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

public class MyDBHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "studentDB.db";
    private static final String TABLE_STUDENTS = "students";
    private static final String COLUMN_ID = "StudentID";
    private static final String COLUMN_NAME = "StudentName";

    MyDBHandler(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

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

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

    String loadHandler() {
        String result = "";
        String query = "Select*FROM " + TABLE_STUDENTS;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(query, null);
        while (cursor.moveToNext()) {
            int result_0 = cursor.getInt(0);
            String result_1 = cursor.getString(1);
            result += String.valueOf(result_0) + " " + result_1 +
                    System.getProperty("line.separator");
        }
        cursor.close();
        db.close();
        if(result.equals(""))
            result="No Record Found";
        return result;
    }

    long addHandler(Student student) {
        long id;
        ContentValues values = new ContentValues();
        values.put(COLUMN_ID, student.getID());
        values.put(COLUMN_NAME, student.getStudentName());
        SQLiteDatabase db = this.getWritableDatabase();
        id = db.insert(TABLE_STUDENTS, null, values);
        db.close();
        return id;
    }

    boolean updateHandler(int ID, String name) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues args = new ContentValues();
        args.put(COLUMN_ID, ID);
        args.put(COLUMN_NAME, name);
        return db.update(TABLE_STUDENTS, args, COLUMN_ID + "=" + ID, null) > 0;
    }

    boolean deleteHandler(int ID) {
        boolean result = false;
        String query = "Select*FROM " + TABLE_STUDENTS + " WHERE " + COLUMN_ID + " = '" + String.valueOf(ID) + "'";
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(query, null);
        Student student = new Student();
        if (cursor.moveToFirst()) {
            student.setID(Integer.parseInt(cursor.getString(0)));
            db.delete(TABLE_STUDENTS, COLUMN_ID + "=?",
                    new String[] {
                            String.valueOf(student.getID())
                    });
            cursor.close();
            result = true;
        }
        db.close();
        return result;
    }

}

Student.java

package com.programtown.example;


public class Student {
    private int id;
    private String studentName;

    Student() {
    }
    Student(int id, String studentName) {
        this.id = id;
        this.studentName = studentName;
    }
    void setID(int id) {
        this.id = id;
    }
    int getID() {
        return this.id;
    }
    void setStudentName(String studentname) {
        this.studentName = studentname;
    }
    String getStudentName() {
        return this.studentName;
    }
}

2) Run and test your app

Conclusion

So in this post we have learned how to perform CRUD operation in SQlite Database Android with example code.

Please Subscribe Youtube| Like Facebook | Follow Twitter