1. Introduction

Every application needs a way to store and manage data. For example, a student management app needs to save student names, email addresses, and other details.

Android provides SQLite, a lightweight relational database that allows applications to store structured data locally on a device.

SQLite works without an internet connection or an external database server. It is useful for applications that need local data storage.

In this tutorial, you will learn how to:

• Understand SQLite Database in Android.

• Create a database using SQLiteOpenHelper.

• Create a database table.

• Insert records into SQLite.

• Read and display database records.

• Update existing records.

• Delete records.

• Build a practical Student Database App using Java.

2. What Is SQLite Database?

SQLite is a lightweight relational database management system.

It stores information in tables containing rows and columns.

For example, a Student table can contain the following columns:

• id

• name

• email

Example student records:

ID	Name	Email
1	Rahul	rahul@gmail.com
2	Amit	amit@gmail.com
3	Priya	priya@gmail.com

Each row represents one student, and each column stores a particular type of information.

Advantages of SQLite

1. It works offline.

2. It does not require a separate database server.

3. It stores data locally on the device.

4. It supports SQL queries.

5. It supports CRUD operations.

6. It is suitable for small and medium-sized local data requirements.

3. Important SQLite Classes in Android

Android provides several classes for working with SQLite databases.

SQLiteOpenHelper

SQLiteOpenHelper is used to create, open, and upgrade a database.

Important methods:

• onCreate() – Called when the database is created for the first time.

• onUpgrade() – Called when the database version changes.

• getWritableDatabase() – Opens the database for reading and writing.

• getReadableDatabase() – Opens the database for reading.

SQLiteDatabase

SQLiteDatabase provides methods to perform database operations.

Important methods:

• insert() – Inserts a new record.

• query() – Retrieves records.

• update() – Updates existing records.

• delete() – Deletes records.

• execSQL() – Executes SQL statements.

ContentValues

ContentValues stores column names and their corresponding values before inserting or updating records.

Cursor

Cursor is used to read the results returned by a database query.

4. Create a New Android Studio Project

First, open Android Studio.

Follow these steps:

1. Click on New Project.

2. Select Empty Views Activity.

3. Enter the project name: StudentDatabaseApp.

4. Select Java as the programming language.

5. Choose your minimum SDK.

6. Click Finish.

Wait until Android Studio completes the project setup.

5. Understand the Database Structure

We will create a database named:

StudentDB

Inside this database, we will create a table named:

students

The table will contain three columns:

• id – Unique student ID.

• name – Student name.

• email – Student email address.

We will use an auto-incrementing primary key so that SQLite automatically generates a unique ID for each new student.

SQL table creation statement:

CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT
);

6. Create Database Helper Class

Now create a new Java class.

File name:

DBHelper.java

This class will manage the SQLite database and perform all CRUD operations.

Complete code:

package com.example.studentdatabaseapp;

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 {

    private static final String DATABASE_NAME = "StudentDB";
    private static final int DATABASE_VERSION = 1;

    private static final String TABLE_NAME = "students";

    private static final String COL_ID = "id";
    private static final String COL_NAME = "name";
    private static final String COL_EMAIL = "email";

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

    @Override
    public void onCreate(SQLiteDatabase db) {

        String query = "CREATE TABLE " + TABLE_NAME + " (" +
                COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COL_NAME + " TEXT, " +
                COL_EMAIL + " TEXT)";

        db.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db,
                          int oldVersion,
                          int newVersion) {

        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    // INSERT STUDENT
    public boolean insertStudent(String name, String email) {

        SQLiteDatabase db = getWritableDatabase();

        ContentValues values = new ContentValues();

        values.put(COL_NAME, name);
        values.put(COL_EMAIL, email);

        long result = db.insert(TABLE_NAME, null, values);

        return result != -1;
    }

    // READ ALL STUDENTS
    public String getAllStudents() {

        SQLiteDatabase db = getReadableDatabase();

        StringBuilder result = new StringBuilder();

        try (Cursor cursor = db.query(
                TABLE_NAME,
                null,
                null,
                null,
                null,
                null,
                COL_ID + " ASC")) {

            if (cursor.moveToFirst()) {

                do {

                    int id = cursor.getInt(
                            cursor.getColumnIndexOrThrow(COL_ID));

                    String name = cursor.getString(
                            cursor.getColumnIndexOrThrow(COL_NAME));

                    String email = cursor.getString(
                            cursor.getColumnIndexOrThrow(COL_EMAIL));

                    result.append("ID: ").append(id).append("\n");
                    result.append("Name: ").append(name).append("\n");
                    result.append("Email: ").append(email).append("\n");
                    result.append("----------------------\n");

                } while (cursor.moveToNext());

            } else {

                result.append("No student records found.");

            }
        }

        return result.toString();
    }

    // UPDATE STUDENT
    public boolean updateStudent(int id, String name, String email) {

        SQLiteDatabase db = getWritableDatabase();

        ContentValues values = new ContentValues();

        values.put(COL_NAME, name);
        values.put(COL_EMAIL, email);

        int result = db.update(
                TABLE_NAME,
                values,
                COL_ID + " = ?",
                new String[]{String.valueOf(id)}
        );

        return result > 0;
    }

    // DELETE STUDENT
    public boolean deleteStudent(int id) {

        SQLiteDatabase db = getWritableDatabase();

        int result = db.delete(
                TABLE_NAME,
                COL_ID + " = ?",
                new String[]{String.valueOf(id)}
        );

        return result > 0;
    }
}

Understanding the Important Code

Database name and version

private static final String DATABASE_NAME = "StudentDB";
private static final int DATABASE_VERSION = 1;

These constants define the database file name and its current version.

Database constructor

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

This constructor connects the helper class with Android's SQLiteOpenHelper.

onCreate()

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(query);
}

This method executes the SQL statement when the database is created for the first time.

onUpgrade()

This method handles database version changes. The example drops and recreates the table to keep the demonstration simple.

Important: Dropping a table deletes its existing records. Real applications should use proper database migrations to preserve user data.

7. Insert Data into SQLite Database

To insert a record, we use the insert() method.

ContentValues values = new ContentValues();

values.put("name", "Rahul");
values.put("email", "rahul@gmail.com");

long result = db.insert("students", null, values);

Explanation:

• ContentValues stores the data.

• put() adds values for database columns.

• insert() saves a new row.

• The method returns the new row ID, or -1 if insertion fails.

In our DBHelper.java class, we have already created the insertStudent() method to perform this operation.

8. Read Data from SQLite Database

To retrieve records, we use the query() method.

Cursor cursor = db.query(
        "students",
        null,
        null,
        null,
        null,
        null,
        "id ASC"
);

The query retrieves student records from the students table.

A Cursor helps us move through the returned records.

if (cursor.moveToFirst()) {

    do {

        String name = cursor.getString(
                cursor.getColumnIndexOrThrow("name"));

    } while (cursor.moveToNext());
}

Always close the Cursor after reading it. Our getAllStudents() method uses try-with-resources to close it automatically.

9. Update Existing Records

Sometimes we need to change a student's information.

For example, we want to update the name and email of student ID 2.

ContentValues values = new ContentValues();

values.put("name", "Amit Kumar");
values.put("email", "amit@gmail.com");

int result = db.update(
        "students",
        values,
        "id = ?",
        new String[]{"2"}
);

Explanation:

• ContentValues contains the new information.

• id = ? identifies the record to update.

• new String[]{"2"} supplies the student ID.

• The update method returns the number of affected rows.

If the result is greater than zero, the record was updated successfully.

10. Delete Records from SQLite Database

To delete a particular student record, we use the delete() method.

int result = db.delete(
        "students",
        "id = ?",
        new String[]{"2"}
);

This statement deletes the record whose ID is 2.

The method returns the number of deleted rows.

Using ? placeholders and selection arguments is safer than joining user input directly into an SQL statement.

11. Create the Student App Layout

Now open:

activity_main.xml

Replace the existing layout with this code:

<?xml version="1.0" encoding="utf-8"?>

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

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical"
        android:padding="20dp">

        <TextView
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Student Database App"
            android:textSize="24sp"
            android:textStyle="bold"
            android:gravity="center"
            android:padding="15dp" />

        <EditText
            android:id="@+id/etId"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Enter Student ID"
            android:inputType="number" />

        <EditText
            android:id="@+id/etName"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Enter Student Name"
            android:inputType="textPersonName" />

        <EditText
            android:id="@+id/etEmail"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Enter Student Email"
            android:inputType="textEmailAddress" />

        <Button
            android:id="@+id/btnInsert"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Insert Student" />

        <Button
            android:id="@+id/btnView"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="View Students" />

        <Button
            android:id="@+id/btnUpdate"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Update Student" />

        <Button
            android:id="@+id/btnDelete"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Delete Student" />

        <TextView
            android:id="@+id/tvResult"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Student records will appear here."
            android:textSize="16sp"
            android:padding="15dp" />

    </LinearLayout>

</ScrollView>

Understanding the Layout

Our screen contains:

• Student ID input field.

• Student Name input field.

• Student Email input field.

• Insert Student button.

• View Students button.

• Update Student button.

• Delete Student button.

• TextView to display database records.

The ScrollView allows the user to scroll through the screen when the content does not fit.

12. Write MainActivity.java Code

Now open:

MainActivity.java

Add the following code:

package com.example.studentdatabaseapp;

import android.os.Bundle;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import androidx.appcompat.app.AppCompatActivity;

public class MainActivity extends AppCompatActivity {

    EditText etId, etName, etEmail;
    Button btnInsert, btnView, btnUpdate, btnDelete;
    TextView tvResult;

    DBHelper dbHelper;

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

        etId = findViewById(R.id.etId);
        etName = findViewById(R.id.etName);
        etEmail = findViewById(R.id.etEmail);

        btnInsert = findViewById(R.id.btnInsert);
        btnView = findViewById(R.id.btnView);
        btnUpdate = findViewById(R.id.btnUpdate);
        btnDelete = findViewById(R.id.btnDelete);

        tvResult = findViewById(R.id.tvResult);

        dbHelper = new DBHelper(this);

        // INSERT STUDENT
        btnInsert.setOnClickListener(v -> {

            String name = etName.getText().toString().trim();
            String email = etEmail.getText().toString().trim();

            if (name.isEmpty() || email.isEmpty()) {

                Toast.makeText(this,
                        "Please enter name and email",
                        Toast.LENGTH_SHORT).show();

                return;
            }

            boolean inserted = dbHelper.insertStudent(name, email);

            if (inserted) {

                Toast.makeText(this,
                        "Student inserted successfully",
                        Toast.LENGTH_SHORT).show();

                clearFields();

            } else {

                Toast.makeText(this,
                        "Insert failed",
                        Toast.LENGTH_SHORT).show();
            }
        });

        // VIEW STUDENTS
        btnView.setOnClickListener(v -> {

            String data = dbHelper.getAllStudents();

            tvResult.setText(data);
        });

        // UPDATE STUDENT
        btnUpdate.setOnClickListener(v -> {

            String idText = etId.getText().toString().trim();
            String name = etName.getText().toString().trim();
            String email = etEmail.getText().toString().trim();

            if (idText.isEmpty() || name.isEmpty() || email.isEmpty()) {

                Toast.makeText(this,
                        "Enter ID, name and email",
                        Toast.LENGTH_SHORT).show();

                return;
            }

            int id = Integer.parseInt(idText);

            boolean updated = dbHelper.updateStudent(id, name, email);

            if (updated) {

                Toast.makeText(this,
                        "Student updated successfully",
                        Toast.LENGTH_SHORT).show();

                clearFields();

                tvResult.setText(dbHelper.getAllStudents());

            } else {

                Toast.makeText(this,
                        "Student ID not found",
                        Toast.LENGTH_SHORT).show();
            }
        });

        // DELETE STUDENT
        btnDelete.setOnClickListener(v -> {

            String idText = etId.getText().toString().trim();

            if (idText.isEmpty()) {

                Toast.makeText(this,
                        "Please enter Student ID",
                        Toast.LENGTH_SHORT).show();

                return;
            }

            int id = Integer.parseInt(idText);

            boolean deleted = dbHelper.deleteStudent(id);

            if (deleted) {

                Toast.makeText(this,
                        "Student deleted successfully",
                        Toast.LENGTH_SHORT).show();

                clearFields();

                tvResult.setText(dbHelper.getAllStudents());

            } else {

                Toast.makeText(this,
                        "Student ID not found",
                        Toast.LENGTH_SHORT).show();
            }
        });
    }

    private void clearFields() {

        etId.setText("");
        etName.setText("");
        etEmail.setText("");
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();

        if (dbHelper != null) {
            dbHelper.close();
        }
    }
}

Important: If your application uses a different package name, replace com.example.studentdatabaseapp in both Java files with your actual package name.

For a production application, perform database operations on a background thread instead of the main UI thread. Also, validate IDs and input data before using them.

13. Understand the Complete CRUD Operation

CRUD represents four basic database operations.

Create: Insert a new student record.

dbHelper.insertStudent("Rahul", "rahul@gmail.com");

Read: Retrieve all student records.

String data = dbHelper.getAllStudents();

Update: Change an existing student's details.

dbHelper.updateStudent(2, "Amit Kumar", "amit@gmail.com");

Delete: Remove a student using the ID.

dbHelper.deleteStudent(2);

These four operations are the foundation of database-driven applications.

14. Run and Test the Application

Now follow these practical steps:

1. Connect an Android phone or start an emulator.

2. Run the application.

3. Enter a student name, such as Rahul.

4. Enter an email, such as rahul@gmail.com.

5. Click Insert Student.

6. Click View Students to display saved records.

7. Enter a student ID, name, and email.

8. Click Update Student to modify that record.

9. Enter a student ID and click Delete Student to remove it.

Example Testing

First, insert these two records:

Name: Rahul
Email: rahul@gmail.com

Name: Amit
Email: amit@gmail.com

Click View Students.

You should see records similar to:

ID: 1
Name: Rahul
Email: rahul@gmail.com
----------------------

ID: 2
Name: Amit
Email: amit@gmail.com
----------------------

Now enter:

ID: 2
Name: Amit Kumar
Email: amitkumar@gmail.com

Click Update Student.

The second record should now contain the updated information.

Finally, enter ID 1 and click Delete Student. Rahul's record should be removed.

15. View the SQLite Database in Android Studio

Android Studio provides a Database Inspector tool for inspecting databases while an application is running on a supported device or emulator.

Follow these steps:

1. Run your application.

2. Open the View menu.

3. Select Tool Windows.

4. Open App Inspection.

5. Select Database Inspector.

6. Find the StudentDB database.

7. Expand the students table.

You can inspect the stored student records and run SQL queries from the inspector.

For example:

SELECT * FROM students;

This query displays all records from the students table.

16. Important SQLite Best Practices

Follow these practices when working with SQLite:

1. Use primary keys to identify individual records.

2. Use ContentValues for insert and update operations.

3. Use selection arguments instead of directly joining user input into SQL queries.

4. Close Cursor objects after reading data.

5. Validate user input before inserting records.

6. Use proper database migrations when changing table structures.

7. Avoid performing long-running database operations on the main UI thread.

8. Do not store sensitive information in plain text.

SQLite vs Room Database

SQLite is Android's built-in relational database engine.

Room is a persistence library that provides an abstraction layer over SQLite. It reduces boilerplate code and provides compile-time query verification.

For new Android applications, Android recommends considering Room instead of using the lower-level SQLite APIs directly. However, learning SQLite is valuable because it helps developers understand database operations and how Room works internally.

17. Common SQLite Errors and Solutions

Error 1: Table does not exist

Possible reason: The table creation query was not executed.

Solution: Check the onCreate() method and verify the table name.

Error 2: Database changes are not visible

Possible reason: The View Students button was not clicked after inserting a record.

Solution: Run the read operation again after changing the database.

Error 3: Update operation is not working

Possible reason: The entered student ID does not exist.

Solution: First click View Students and check the correct ID.

Error 4: Database structure is not updating

Possible reason: The database version was not increased.

Solution: Increase DATABASE_VERSION when changing the schema and implement a proper migration. During development, uninstalling and reinstalling the app can reset its local database, but this deletes stored data.

18. Conclusion

In this tutorial, you learned how to use SQLite Database in Android Studio with Java.

You created a database using SQLiteOpenHelper and performed all four CRUD operations.

You also learned how to use SQLiteDatabase, ContentValues, Cursor, and Database Inspector.

SQLite is an important concept for Android developers because it helps applications store and manage structured data locally.

By completing this practical Student Database App, you now have a foundation for building database-driven Android applications.

Practice Task: Extend this application by adding a phone number column to the students table. Update the insert, read, and update operations to support the new field.