CRUD Masterclass: Building Your First SQLite Database in Android Studio

The ultimate guide to creating, reading, updating, and deleting data using the SQLiteOpenHelper class in Java.

Introduction: Why Use SQLite?

When building Android applications, you often need a place to store persistent data—data that remains even after the user closes the app. For small to medium-sized data sets, SQLite is the built-in, lightweight, and efficient solution.

In this tutorial, we’ll walk through the DatabaseHelper.java file, which contains all the logic for a basic CRUD (Create, Read, Update, Delete) application to manage student records.

Step 1: The Core Foundation (DatabaseHelper.java)

To interact with the database, we must extend the SQLiteOpenHelper class. This class handles the heavy lifting of opening the database, creating tables if they don’t exist, and upgrading the database schema when necessary.

1.1 Imports and Constants

We start by defining necessary imports and constants for our database file, table, and columns.

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 {

    // Database information
    private static final String DATABASE_NAME = "StudentDatabase.db";
    private static final int DATABASE_VERSION = 1;

    // Table and Column names (MANDATORY for avoiding typos!)
    public static final String TABLE_NAME = "student_records";
    public static final String COL_1_ROLL_NUM = "ROLL_NUMBER"; // Primary Key
    public static final String COL_2_NAME = "STUDENT_NAME";
    public static final String COL_3_FATHER_NAME = "FATHER_NAME";
    public static final String COL_4_EMAIL = "EMAIL";
    public static final String COL_5_DEPT = "DEPARTMENT";

    // ... rest of the code
}

• DATABASE_NAME: This is the name of the physical file that will be created on the device’s storage.
• DATABASE_VERSION: This is crucial for managing schema changes. If you ever change the table structure, you increment this number, which automatically triggers the onUpgrade method.
• Column Constants: Using public static final strings for column names (COL_1_ROLL_NUM, etc.) prevents careless spelling errors throughout your application.

1.2 Database and Table Creation

This is where the database file and the initial table are physically created.

    // SQL query to create the table
    private static final String CREATE_TABLE =
            "CREATE TABLE " + TABLE_NAME + " (" +
                    COL_1_ROLL_NUM + " TEXT PRIMARY KEY," + // Roll Number as primary key
                    COL_2_NAME + " TEXT," +
                    COL_3_FATHER_NAME + " TEXT," +
                    COL_4_EMAIL + " TEXT," +
                    COL_5_DEPT + " TEXT" +
                    ")";
                    
    public DatabaseHelper(Context context) {
        // Constructor: Passes the necessary context to the parent class.
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // onCreate: Called only once when the database file is first created.
        db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // onUpgrade: Called if DATABASE_VERSION is incremented.
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

• CREATE_TABLE: This is a standard SQL query. Notice we set ROLL_NUMBER to be the TEXT PRIMARY KEY, meaning every student must have a unique roll number.
• onCreate(SQLiteDatabase db): This method executes our CREATE_TABLE query. The database exists only after this method successfully runs.
• onUpgrade(): Our simple implementation ensures that if the app version changes, the old table is destroyed, and the new structure is created. (In production, you’d usually write migration logic to preserve data!)

Step 2: CREATE Operation (Inserting Data)

The insertStudent function is responsible for adding new rows to the table. We use a special Android class called ContentValues to handle the data insertion safely.

    // --- CREATE (Insert) Operation ---
    public boolean insertStudent(String name, String fatherName, String rollNum, String email, String department) {
        SQLiteDatabase db = this.getWritableDatabase(); // 1. Get writable database instance
        ContentValues contentValues = new ContentValues(); // 2. Create a holder for key-value pairs

        // 3. Put all data into the ContentValues object (Column Name, Value)
        contentValues.put(COL_1_ROLL_NUM, rollNum);
        contentValues.put(COL_2_NAME, name);
        contentValues.put(COL_3_FATHER_NAME, fatherName);
        contentValues.put(COL_4_EMAIL, email);
        contentValues.put(COL_5_DEPT, department);

        // 4. Perform the insert operation
        long result = db.insert(TABLE_NAME, null, contentValues);
        db.close(); // 5. Always close the connection

        return result != -1; // -1 means insertion failed
    }

• getWritableDatabase(): We need write permission to insert data.
• ContentValues: Think of this as a map (key-value structure) where the key is the column name (e.g., "STUDENT_NAME") and the value is the data we want to insert. This is the preferred way to interact with the database.
• db.insert(): This executes the insertion. It returns -1 if the operation failed (usually because the primary key, rollNum, already exists).

Step 3: READ Operation (Viewing All Records)

The getAllStudents function retrieves all rows and returns them in a Cursor object. The Cursor is like a pointer to the result set returned by the SQL query.

    // --- READ (View All) Operation ---
    public Cursor getAllStudents() {
        SQLiteDatabase db = this.getWritableDatabase();
        // rawQuery allows us to execute any standard SQL SELECT statement
        Cursor res = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
        
        // IMPORTANT: The Cursor is returned open. It must be closed 
        // by the calling Activity after all data has been read.
        return res;
    }

• db.rawQuery(): This executes the simple SQL query SELECT * FROM student_records.
• Cursor: The result set is stored here. In your MainActivity, you’ll use a while(res.moveToNext()) loop to iterate through the rows of data stored inside this Cursor.

Step 4: UPDATE Operation (Modifying Records)

The updateStudent function changes existing data. We need the Roll Number to tell SQLite which record to change, and ContentValues for the new data.

    // --- UPDATE Operation ---
    public boolean updateStudent(String rollNum, String name, String fatherName, String email, String department) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();

        // 1. Prepare new data to update
        contentValues.put(COL_2_NAME, name);
        contentValues.put(COL_3_FATHER_NAME, fatherName);
        contentValues.put(COL_4_EMAIL, email);
        contentValues.put(COL_5_DEPT, department);

        // 2. Perform the update operation
        // WHERE clause: COL_1_ROLL_NUM = ? (placeholder)
        // WHERE args: new String[] { rollNum } (value for placeholder)
        int rowsAffected = db.update(
                TABLE_NAME,
                contentValues,
                COL_1_ROLL_NUM + " = ?", 
                new String[] { rollNum }
        );
        db.close();
        
        return rowsAffected > 0; // Returns true if 1 or more rows were updated
    }

• db.update(): This method takes the table name, the new ContentValues, a SQL WHERE clause, and an array of arguments for the WHERE clause.
• Placeholders (?): Using COL_1_ROLL_NUM + " = ?" is a security best practice to prevent SQL injection, ensuring the rollNum is treated strictly as a data value.

Step 5: DELETE Operation (Removing Records)

The deleteStudent function is the simplest, as it only needs the Roll Number to identify and remove the row.

    // --- DELETE Operation ---
    public Integer deleteStudent(String rollNum) {
        SQLiteDatabase db = this.getWritableDatabase();

        // 1. Perform the delete operation
        // WHERE clause: COL_1_ROLL_NUM = ?
        int rowsDeleted = db.delete(
                TABLE_NAME,
                COL_1_ROLL_NUM + " = ?",
                new String[] { rollNum }
        );
        db.close();

        return rowsDeleted; // Returns the count of rows that were deleted
    }

• db.delete(): Similar to update, it uses a WHERE clause to specify which rows should be deleted.
• Return Value: The function returns an Integer representing the total number of rows that were successfully removed.

This DatabaseHelper.java file now gives you a robust, reusable component for all your student record needs! The next step is connecting these functions to your MainActivity and setting up the Scrollable Table view using the Cursor data.

Step 6: Integrating CRUD with the Android UI (MainActivity.java)

Now that our database logic is complete in DatabaseHelper.java, we need to connect those powerful functions to the user interface in our main Android Activity.

6.1 Setup and Initialization

In your MainActivity.java, you first need to declare your database helper instance and initialize it in the onCreate method. You will also need variables for all the UI elements (assuming you have five EditText fields and four Button elements in your activity_main.xml layout).

import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.database.Cursor;
import android.text.method.ScrollingMovementMethod;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
import androidx.appcompat.app.AlertDialog;
// ... other imports

public class MainActivity extends AppCompatActivity {
    
    // Declare Database Helper instance
    DatabaseHelper myDb; 

    // Declare UI elements
    EditText etName, etFather, etRoll, etEmail, etDept;
    Button btnInsert, btnView, btnUpdate, btnDelete;

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

        // 1. Initialize the Database Helper
        myDb = new DatabaseHelper(this); 

        // 2. Map UI elements from the layout
        etRoll = findViewById(R.id.editText_RollNum);
        etName = findViewById(R.id.editText_Name);
        etFather = findViewById(R.id.editText_FatherName);
        etEmail = findViewById(R.id.editText_Email);
        etDept = findViewById(R.id.editText_Department);

        btnInsert = findViewById(R.id.button_Add);
        btnView = findViewById(R.id.button_ViewAll);
        btnUpdate = findViewById(R.id.button_Update);
        btnDelete = findViewById(R.id.button_Delete);

        // 3. Set up listeners for all CRUD operations
        AddData();
        ViewAll();
        UpdateDataListener(); // Renamed to avoid confusion with the helper function
        DeleteDataListener();
    }

    // ... CRUD listener functions will go here
}

6.2 The CREATE Listener (AddData())

This function attaches an OnClickListener to the “Add” button, gathers the input, and calls the insertStudent method we defined earlier.

public void AddData() {
    btnInsert.setOnClickListener(
        new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // 1. Gather input from EditText fields
                String rollNum = etRoll.getText().toString();
                String name = etName.getText().toString();
                String fatherName = etFather.getText().toString();
                String email = etEmail.getText().toString();
                String department = etDept.getText().toString();

                // Simple check for required fields (Roll Number and Name)
                if (rollNum.isEmpty() || name.isEmpty()) {
                     Toast.makeText(MainActivity.this, "Roll Number and Name are required!", Toast.LENGTH_LONG).show();
                     return;
                }

                // 2. Call the DatabaseHelper function
                boolean isInserted = myDb.insertStudent(name, fatherName, rollNum, email, department);
                
                // 3. Provide user feedback
                if (isInserted) {
                    Toast.makeText(MainActivity.this, "Student Record Added!", Toast.LENGTH_LONG).show();
                    // Optional: Clear fields after successful insertion
                    etRoll.setText(""); etName.setText(""); // ... clear others
                } else {
                    Toast.makeText(MainActivity.this, "Insertion Failed (Check if Roll Number is unique)", Toast.LENGTH_LONG).show();
                }
            }
        }
    );
}

6.3 The READ Listener and Scrollable Table View (ViewAll())

This is the most critical part, as it demonstrates how to handle the Cursor and transform raw data into a readable, scrollable format for the user.

public void ViewAll() {
    btnView.setOnClickListener(
        new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // 1. Get the Cursor containing all records
                Cursor res = myDb.getAllStudents(); 
                
                // 2. Check if the database is empty
                if (res.getCount() == 0) {
                    // Show message is a custom function to display an AlertDialog (defined below)
                    showMessage("Database Status", "No student records found in the database.");
                    return;
                }

                // 3. Iterate through the Cursor and build the display text
                StringBuffer buffer = new StringBuffer();
                while (res.moveToNext()) {
                    // The numbers (0, 1, 2...) correspond to the column index in the table
                    // Roll Number (0), Name (1), Father Name (2), Email (3), Department (4)
                    buffer.append("Roll Number: " + res.getString(0) + "\n");
                    buffer.append("Name: " + res.getString(1) + "\n");
                    buffer.append("Father Name: " + res.getString(2) + "\n");
                    buffer.append("Email: " + res.getString(3) + "\n");
                    buffer.append("Department: " + res.getString(4) + "\n");
                    buffer.append("---------------------------------\n");
                }

                // 4. Close the Cursor to release resources
                res.close(); 
                
                // 5. Display the scrollable results
                showMessage("All Student Records", buffer.toString());
            }
        }
    );
}

// Custom Helper Function to display data in a Scrollable AlertDialog
public void showMessage(String title, String message) {
    // We use AlertDialog.Builder for a modal popup
    AlertDialog.Builder builder = new AlertDialog.Builder(this);
    builder.setCancelable(true);
    builder.setTitle(title);
    
    // To make the message scrollable, we create a TextView, 
    // set its movement method, and add it to the dialog view.
    final TextView messageTextView = new TextView(this);
    messageTextView.setText(message);
    messageTextView.setMovementMethod(new ScrollingMovementMethod());
    
    // Add some padding (optional, but good practice for aesthetics)
    int paddingDp = 10;
    float density = getResources().getDisplayMetrics().density;
    int paddingPixel = (int)(paddingDp * density);
    messageTextView.setPadding(paddingPixel, paddingPixel, paddingPixel, paddingPixel);

    builder.setView(messageTextView);
    builder.show();
}

6.4 The UPDATE Listener (UpdateDataListener())

This function requires the user to enter the Roll Number (to identify the target) and the new data for the remaining fields.

public void UpdateDataListener() {
    btnUpdate.setOnClickListener(
        new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // 1. Gather all input
                String rollNum = etRoll.getText().toString();
                String name = etName.getText().toString();
                String fatherName = etFather.getText().toString();
                String email = etEmail.getText().toString();
                String department = etDept.getText().toString();
                
                // Roll Number is mandatory for identification
                if (rollNum.isEmpty()) {
                    Toast.makeText(MainActivity.this, "Roll Number is required for Update!", Toast.LENGTH_LONG).show();
                    return;
                }

                // 2. Call the DatabaseHelper function
                boolean isUpdated = myDb.updateStudent(
                    rollNum, 
                    name, 
                    fatherName, 
                    email, 
                    department
                );

                // 3. Provide user feedback
                if (isUpdated) {
                    Toast.makeText(MainActivity.this, "Record Updated Successfully!", Toast.LENGTH_LONG).show();
                } else {
                    Toast.makeText(MainActivity.this, "Update Failed (Record not found)", Toast.LENGTH_LONG).show();
                }
            }
        }
    );
}

6.5 The DELETE Listener (DeleteDataListener())

Deletion is simple: the function only requires the primary key (Roll Number) to locate and remove the record.

public void DeleteDataListener() {
    btnDelete.setOnClickListener(
        new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // 1. Gather the Roll Number (the unique identifier)
                String rollNum = etRoll.getText().toString();
                
                if (rollNum.isEmpty()) {
                    Toast.makeText(MainActivity.this, "Enter Roll Number to Delete.", Toast.LENGTH_LONG).show();
                    return;
                }

                // 2. Call the DatabaseHelper function
                Integer deletedRows = myDb.deleteStudent(rollNum);
                
                // 3. Provide user feedback
                if (deletedRows > 0) {
                    Toast.makeText(MainActivity.this, deletedRows + " Record(s) Deleted Successfully!", Toast.LENGTH_LONG).show();
                    etRoll.setText(""); // Clear the field after deletion
                } else {
                    Toast.makeText(MainActivity.this, "Deletion Failed (Roll Number not found)", Toast.LENGTH_LONG).show();
                }
            }
        }
    );
}

Conclusion

By isolating the database commands in the DatabaseHelper and linking them directly to your UI actions in MainActivity, you have built a clean, scalable, and fully functional CRUD application. This architecture is the standard and most robust way to manage local data in Android using SQLite. Good luck teaching your students!

About The Author

Dr. Zeeshan Bhatti

See author's posts