SQLite Database

Establishing Connection to SQLite Database

• When you're using SQLite in an Android app, the first thing you need is a connection to the database. This is where the SQLiteOpenHelper class comes into play. You don’t need to worry about manually opening or closing the database in most cases — SQLiteOpenHelper takes care of it for you.
• We create a separate file for the database helper class. In your project structure, navigate to app > java > com.example.yourapplicationname. Right-click and create a new Java class. Name it DBHelper, and extend SQLiteOpenHelper to manage the database.
• The DBHelper class contains three main functions: a constructor, onCreate(), and onUpgrade().

Creating the Database Helper Class:

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

public class DBHelper extends SQLiteOpenHelper {

    // Database name and version
    private static final String DB_NAME = "stuDB.db"; // Name of the database file
    private static final int DB_VERSION = 1; // Version number (start with 1)

    // Constructor
    public DBHelper(Context context) {
        // Calls the parent constructor with database name, cursor factory, and version
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // This method is called when the database is created for the first time
        // We will create the tables here

        String createTableQuery = "CREATE TABLE stuData (" +
                "stuID INTEGER PRIMARY KEY, " +  // Column for student ID
                "stuName TEXT)";  // Column for student name

        db.execSQL(createTableQuery);  // Execute the SQL query to create the table
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // This method is called when the database needs to be upgraded
        // For now, we can leave it empty if we don't need upgrades
        db.execSQL("DROP TABLE IF EXISTS stuData");  // Drop the existing table
        onCreate(db);  // Recreate the table
    }
}
                        
                    

Code Breakdown:

1. Class Declaration:

   public class DBHelper extends SQLiteOpenHelper

   The DBHelper class extends SQLiteOpenHelper to gain access to SQLite database functionality. SQLiteOpenHelper is a built-in class in Android that simplifies working with SQLite databases.

2. Constructor:

   public DBHelper(Context context)

   The constructor of DBHelper calls the super() method, which is from the SQLiteOpenHelper class. The constructor of SQLiteOpenHelper takes four parameters:

   • Context context: This is an Android component that provides access to system services like resources, files, or databases. It's passed to the superclass so that it can interact with the system’s resources, especially for database creation. In this case, context is typically the activity or application that calls the constructor (e.g., MainActivity.this).
   • String name (DB_NAME): This is the name of the database file. In this example, the database file will be named stuDB.db.
   • CursorFactory factory (null): This is typically set to null unless you need a custom cursor factory. A cursor factory is used to create cursors when querying the database. In most cases, passing null is sufficient.
   • int version (DB_VERSION): This is the version number of the database. Each time you change the database schema, you must increment the version number. In this case, it's set to 1, indicating the first version of the database.
3. onCreate(SQLiteDatabase db):

   This method is called once when the database is created for the first time. Here, we create the stuData table with two columns:

   • stuID (integer, serves as the primary key)
   • stuName (text)

   db.execSQL(createTableQuery); executes the SQL query to create the table.

4. onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion):

   This method is called when the database version is upgraded. For now, it drops the old table and recreates it by calling onCreate(db) again.

   • db.execSQL("DROP TABLE IF EXISTS stuData"); drops the existing stuData table if it exists.
   • onCreate(db); recreates the table.

Using the DBHelper in our MainActivity

• Once the DBHelper class is set up, we need to create an instance of DBHelper in the activity (like MainActivity). This will automatically connect to the database when the app runs.

                        
import android.os.Bundle;
import android.widget.Toast;
import androidx.appcompat.app.AppCompatActivity;

public class MainActivity extends AppCompatActivity {

    // Declare an instance of the DBHelper class
    DBHelper myDB;

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

        // Create a new instance of DBHelper
        myDB = new DBHelper(this);  // 'this' refers to the current context (MainActivity)

        // To check if the database is created successfully, show a message
        Toast.makeText(this, "Database Connected", Toast.LENGTH_SHORT).show();
    }
}
                        
                    

Working with SQL Queries in SQLite

SQL (Structured Query Language) is used to interact with databases. In Android's SQLite, you use SQL queries to perform various operations such as inserting, updating, selecting, and deleting data. Below are the key concepts and steps involved in working with SQL queries in SQLite:

1. Executing SQL Queries

SQLiteDatabase provides methods like execSQL(), rawQuery(), and insert() to execute SQL statements. These methods allow you to perform different actions on the database:

• execSQL(): Used to execute SQL statements that don't return data, such as creating a table, inserting data, or updating/deleting records. Example: db.execSQL("CREATE TABLE...").
• rawQuery(): Used for SELECT queries that return data. It returns a Cursor object, which you can loop through to get the result. Example: Cursor cursor = db.rawQuery("SELECT * FROM table", null).
• insert(): Used for inserting data into a table. This method returns the row ID of the newly inserted record. Example: db.insert("table", null, contentValues).

2. Insert Data

To insert data into a table, you use the insert() method. This method inserts values into the specified columns in the table. The syntax is as follows:

                        ContentValues contentValues = new ContentValues();
                        contentValues.put("column_name", value);
                        long result = db.insert("table_name", null, contentValues);
                    

The insert() method takes three parameters:

• table_name: The name of the table where the data will be inserted.
• nullColumnHack: Usually set to null, it’s a safety mechanism for inserting data into empty tables.
• contentValues: A ContentValues object that stores the column names and values you want to insert.

3. Query Data

To retrieve data from a table, you use the rawQuery() method. It executes a SELECT SQL statement and returns a Cursor object containing the result set. Here’s how you use it:

                        Cursor cursor = db.rawQuery("SELECT * FROM table_name", null);
                    

The rawQuery() method takes two parameters:

• SQL Query: The SQL SELECT statement to fetch data from the table.
• SelectionArgs: Optional parameters used for query filtering (e.g., where clause arguments).

The Cursor object allows you to iterate over the result set. You can move through the rows using methods like moveToNext() and retrieve column data using methods like getString(), getInt(), etc. Example:

                        while (cursor.moveToNext()) {
                            String data = cursor.getString(cursor.getColumnIndex("column_name"));
                        }
                    

4. Update Data

To modify existing data, you use the update() method. It takes the table name, the ContentValues object with the new values, and a WHERE clause to specify which records to update. Example:

                        ContentValues contentValues = new ContentValues();
                        contentValues.put("column_name", new_value);
                        db.update("table_name", contentValues, "column_name = ?", new String[]{old_value});
                    

5. Delete Data

To delete data from a table, you use the delete() method. It requires the table name, a WHERE clause to specify which records to delete, and optional arguments. Example:

                        db.delete("table_name", "column_name = ?", new String[]{value_to_delete});
                    

6. Best Practices

• Always close the Cursor object after using it to free up resources.
• Use parameterized queries (i.e., with rawQuery()) to prevent SQL injection vulnerabilities.
• Use ContentValues to insert or update data rather than manually concatenating values into the SQL statement.

Inserting and Displaying data

                        
ContentValues cv = new ContentValues();
cv.put("columnName", value);
                        
                    

                        
ContentValues cv = new ContentValues();
cv.put("stuID", 1001);
cv.put("stuName", "John Doe");
                        
                    

                        
long result = db.insert("tableName", null, contentValues);
                        
                    

                        
ContentValues cv = new ContentValues();
cv.put("stuID", 1002);
cv.put("stuName", "Jane Smith");

long result = db.insert("stuData", null, cv);
if (result == -1) {
    // Insertion failed
} else {
    // Insertion successful
}
                        
                    

                        
Cursor cursor = db.rawQuery("SELECT * FROM stuData", null);

while (cursor.moveToNext()) {
    int id = cursor.getInt(0); // Column 0: stuID
    String name = cursor.getString(1); // Column 1: stuName
}
                        
                    

                                package com.example.myapplication;
import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;


import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.Toast;


public class MainActivity  extends AppCompatActivity {
    DBHelper myDB;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        myDB = new DBHelper(MainActivity.this);

    }

    public void insertData(View view){
        int ID = 212345;
        String name = "Preeti";
        boolean myb = myDB.insertStuDetail(ID, name);

        if (myb){
            Toast.makeText(this, "Data Inserted", Toast.LENGTH_SHORT).show();
        }else{
            Toast.makeText(this, "Data not Inserted", Toast.LENGTH_SHORT).show();
        }
    }

    public void display(View view){
        Cursor cursor = myDB.displayData();
        if (cursor.getCount()==0){
            Toast.makeText(this, "Data not Found", Toast.LENGTH_SHORT).show();
        }else{
            StringBuffer buffer = new StringBuffer();

            while(cursor.moveToNext()){
                buffer.append("STUDENT ID:" + cursor.getString(0) + "\n");
                buffer.append("STUDENT NAME: "+ cursor.getString(1) + "\n\n");
            }

            AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
            builder.setTitle("FROM DATABASE");
            builder.setMessage(buffer);
            builder.show();
        }
    }
}
                                
                            

                                package com.example.myapplication;

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

import androidx.annotation.Nullable;

public class DBHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "stuDB.db";
    private static final int DB_VERSION = 1;
    public DBHelper(@Nullable Context context) {
        super(context, DB_NAME, null, DB_VERSION); //Database Creation
        Toast.makeText(context, "DATABASE CREATED", Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table stuData(stuID Integer primary key, stuName Text)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP table if exists stuData");
        onCreate(db);
    }

    public boolean insertStuDetail(int id, String name){
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues cv = new ContentValues();
        cv.put("stuID", id);
        cv.put("stuName", name);

        long result = db.insert("stuData", null, cv);
        if(result==-1){
            return false;
        }else{
            return true;
        }
    }

    public Cursor displayData(){
        SQLiteDatabase db= this.getWritableDatabase();
        Cursor cursor = db.rawQuery("select * from stuData", null);
        return cursor;
    }

}
                                
                            

+ Updation + Deletion

                            <?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    android:layout_width="match_parent"
    android:layout_height="match_parent">

    <TextView
        android:id="@+id/title"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:text="SQLite Database Operations"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintEnd_toEndOf="parent" />

    <Button
        android:id="@+id/insertButton"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:text="Insert Data"
        android:onClick="insertData"
        app:layout_constraintTop_toBottomOf="@id/title"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintEnd_toEndOf="parent" />

    <Button
        android:id="@+id/displayButton"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:text="Display Data"
        android:onClick="displayData"
        app:layout_constraintTop_toBottomOf="@id/insertButton"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintEnd_toEndOf="parent" />

    <Button
        android:id="@+id/updateButton"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:text="Update Data"
        android:onClick="updateData"
        app:layout_constraintTop_toBottomOf="@id/displayButton"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintEnd_toEndOf="parent" />

    <Button
        android:id="@+id/deleteButton"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:text="Delete Data"
        android:onClick="deleteData"
        app:layout_constraintTop_toBottomOf="@id/updateButton"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintEnd_toEndOf="parent" />

</androidx.constraintlayout.widget.ConstraintLayout>
                            
                        

                            package com.example.myapplication;

import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;

import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    DBHelper databaseHelper;

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

        databaseHelper = new DBHelper(MainActivity.this);
    }

    // Insert student data into the database
    public void insertData(View view) {
        int studentId = 2345;
        String studentName = "Preeti";
        boolean isInserted = databaseHelper.insertStudentDetails(studentId, studentName);

        if (isInserted) {
            Toast.makeText(this, "Data Inserted Successfully", Toast.LENGTH_SHORT).show();
        } else {
            Toast.makeText(this, "Data Insertion Failed", Toast.LENGTH_SHORT).show();
        }
    }

    // Display all student data from the database
    public void displayData(View view) {
        Cursor cursor = databaseHelper.getAllStudentData();

        if (cursor.getCount() == 0) {
            Toast.makeText(this, "No Data Found", Toast.LENGTH_SHORT).show();
        } else {
            StringBuffer buffer = new StringBuffer();

            while (cursor.moveToNext()) {
                buffer.append("STUDENT ID: " + cursor.getString(0) + "\n");
                buffer.append("STUDENT NAME: " + cursor.getString(1) + "\n\n");
            }

            AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
            builder.setTitle("Student Data");
            builder.setMessage(buffer.toString());
            builder.show();
        }
    }

    // Update student data based on ID
    public void updateData(View view) {
        int studentId = 2345;
        String updatedName = "Seema";
        boolean isUpdated = databaseHelper.updateStudentData(studentId, updatedName);

        if (isUpdated) {
            Toast.makeText(this, "Data Updated Successfully", Toast.LENGTH_SHORT).show();
        } else {
            Toast.makeText(this, "Data Update Failed", Toast.LENGTH_SHORT).show();
        }
    }

    // Delete student data based on ID
    public void deleteData(View view) {
        int studentId = 2345;
        boolean isDeleted = databaseHelper.deleteStudentData(studentId);

        if (isDeleted) {
            Toast.makeText(this, "Data Deleted Successfully", Toast.LENGTH_SHORT).show();
        } else {
            Toast.makeText(this, "Data Deletion Failed", Toast.LENGTH_SHORT).show();
        }
    }
}
                            
                        

                            package com.example.myapplication;

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

import androidx.annotation.Nullable;

public class DBHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "stuDB.db";
    private static final int DATABASE_VERSION = 1;

    // Table and column constants
    private static final String TABLE_NAME = "stuData";
    private static final String COLUMN_ID = "stuID";
    private static final String COLUMN_NAME = "stuName";

    public DBHelper(@Nullable Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        Toast.makeText(context, "Database Created Successfully", Toast.LENGTH_SHORT).show();
    }

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

    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        String dropTableQuery = "DROP TABLE IF EXISTS " + TABLE_NAME;
        database.execSQL(dropTableQuery);
        onCreate(database);
    }

    // Insert student details into the database
    public boolean insertStudentDetails(int studentId, String studentName) {
        SQLiteDatabase database = this.getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        contentValues.put(COLUMN_ID, studentId);
        contentValues.put(COLUMN_NAME, studentName);

        // If the insert is successful, it will return true
        long insertionResult = database.insert(TABLE_NAME, null, contentValues);
        return insertionResult > 0; // Return true if the row was inserted
    }

    // Retrieve all student data from the database
    public Cursor getAllStudentData() {
        SQLiteDatabase database = this.getWritableDatabase();
        return database.rawQuery("SELECT * FROM " + TABLE_NAME, null);
    }

    // Update student details in the database
    public boolean updateStudentData(int studentId, String studentName) {
        SQLiteDatabase database = this.getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        contentValues.put(COLUMN_NAME, studentName);

        // Check if the student exists
        Cursor cursor = database.rawQuery("SELECT * FROM " + TABLE_NAME +
                " WHERE " + COLUMN_ID + " = " + studentId, null);

        if (cursor.getCount() > 0) {
            // If the student exists, update the data
            int updateResult = database.update(TABLE_NAME, contentValues,
                    COLUMN_ID + " = ?", new String[]{String.valueOf(studentId)});
            return updateResult > 0; // Return true if update is successful
        } else {
            return false; // Return false if student doesn't exist
        }
    }

    // Delete student data from the database
    public boolean deleteStudentData(int studentId) {
        SQLiteDatabase database = this.getWritableDatabase();

        // Check if the student exists
        Cursor cursor = database.rawQuery("SELECT * FROM " + TABLE_NAME +
                " WHERE " + COLUMN_ID + " = " + studentId, null);

        if (cursor.getCount() > 0) {
            // If the student exists, delete the data
            int deleteResult = database.delete(TABLE_NAME,
                    COLUMN_ID + " = ?", new String[]{String.valueOf(studentId)});
            return deleteResult > 0; // Return true if deletion is successful
        } else {
            return false; // Return false if student doesn't exist
        }
    }
}