Putting SQL to work - Srinivas Narasegouda

Introducing SQLite

SQLite is a lightweight, embedded, and self-contained relational database management system (RDBMS) that stores data in a single file on disk. It is widely used in mobile applications because it does not require a separate database server. SQLite supports standard SQL queries, making it easy to use for storing and retrieving structured data.

Key Features of SQLite

Serverless: No need to run a separate database server.
Lightweight: The entire database is a single file stored locally.
Self-contained: Requires minimal setup and dependencies.
Cross-platform: Works on Android, iOS, Windows, macOS, and Linux.
ACID-compliant: Ensures reliable transactions and data integrity.
Supports SQL: You can use standard SQL queries like SELECT, INSERT, UPDATE, and DELETE.

Common Uses of SQLite

Storing user preferences and settings in mobile apps.
Caching data for offline access.
Local storage for small-scale applications.
Handling structured data without the need for an external server.

In and Out of SQLite

This refers to how SQLite interacts with applications and how data flows into and out of the database.

Data Flow in SQLite

User Input → Data is entered through UI elements (e.g., forms).
SQLite Database → The app processes this data and stores it in SQLite.
Data Retrieval → The app retrieves stored data and displays it to the user.
Data Modification → Users can update or delete records.
Persistence → Data remains stored even after the app is closed.

Ways to Work with SQLite in Android

Using SQLiteOpenHelper: Helps create and manage the database.
Direct SQL Queries: Using rawQuery() or execSQL().
Using Content Providers: An abstraction for accessing shared data.
Using Room Database (Recommended): A modern database library that simplifies SQLite operations.

Android program to demonstrate SQLite

The following android program demonstrate how SQLite is implemented in android studio. In this example, we demonstrate the CRUD (Create Read Update Delete) operations. First, design the form (activity_main.xml)

<?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:padding="16dp"
        android:orientation="vertical">

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

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

        <EditText
            android:id="@+id/etContact"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Enter Contact" />

        <EditText
            android:id="@+id/etAddress"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Enter Address" />

        <EditText
            android:id="@+id/etPassword"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Enter Password"
            android:inputType="textPassword" />

        <Button
            android:id="@+id/btnRegister"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Register" />

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

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

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

        <TextView
            android:id="@+id/tvResult"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="16sp"
            android:text="Results will be displayed here" />
    </LinearLayout>
</ScrollView>

Create a java class DatabaseHelper.java and write the following code.

package com.example.unit_4_sqlite_demo;

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 {

    private static final String DATABASE_NAME = "users.db";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_NAME = "users";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_NAME +
                " (name TEXT, email TEXT PRIMARY KEY, contact TEXT, address TEXT, password TEXT)";
        db.execSQL(createTable);
    }

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

    public boolean insertUser(String name, String email, String contact, String address, String password) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("email", email);
        values.put("contact", contact);
        values.put("address", address);
        values.put("password", password);

        long result = db.insert(TABLE_NAME, null, values);
        return result != -1;
    }

    public Cursor getAllUsers() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
    }

    public boolean updateUser(String email, String name, String contact, String address, String password) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("contact", contact);
        values.put("address", address);
        values.put("password", password);

        int rowsUpdated = db.update(TABLE_NAME, values, "email=?", new String[]{email});
        return rowsUpdated > 0;
    }

    public boolean deleteUser(String email) {
        SQLiteDatabase db = this.getWritableDatabase();
        int rowsDeleted = db.delete(TABLE_NAME, "email=?", new String[]{email});
        return rowsDeleted > 0;
    }

}

Implement the following code in MainActivity.java file.

package com.example.unit_4_sqlite_demo;

import androidx.appcompat.app.AppCompatActivity;

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

public class MainActivity extends AppCompatActivity {

    EditText etName, etEmail, etContact, etAddress, etPassword;
    Button btnRegister, btnView, btnUpdate, btnDelete;
    TextView tvResult;
    DatabaseHelper databaseHelper;

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

        etName = findViewById(R.id.etName);
        etEmail = findViewById(R.id.etEmail);
        etContact = findViewById(R.id.etContact);
        etAddress = findViewById(R.id.etAddress);
        etPassword = findViewById(R.id.etPassword);
        btnRegister = findViewById(R.id.btnRegister);
        btnView = findViewById(R.id.btnView);
        btnUpdate = findViewById(R.id.btnUpdate);
        btnDelete = findViewById(R.id.btnDelete);
        tvResult = findViewById(R.id.tvResult);
        databaseHelper = new DatabaseHelper(this);

        btnRegister.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                boolean inserted = databaseHelper.insertUser(
                        etName.getText().toString(),
                        etEmail.getText().toString(),
                        etContact.getText().toString(),
                        etAddress.getText().toString(),
                        etPassword.getText().toString());

                Toast.makeText(MainActivity.this, inserted ? "User Registered" : "Error", Toast.LENGTH_SHORT).show();
            }
        });

        btnView.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Cursor cursor = databaseHelper.getAllUsers();
                StringBuilder result = new StringBuilder();
                while (cursor.moveToNext()) {
                    result.append("Name: ").append(cursor.getString(0)).append("\n");
                    result.append("Email: ").append(cursor.getString(1)).append("\n");
                    result.append("Contact: ").append(cursor.getString(2)).append("\n");
                    result.append("Address: ").append(cursor.getString(3)).append("\n\n");
                }
                tvResult.setText(result.toString());
            }
        });

        btnUpdate.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                boolean updated = databaseHelper.updateUser(
                        etEmail.getText().toString(),
                        etName.getText().toString(),
                        etContact.getText().toString(),
                        etAddress.getText().toString(),
                        etPassword.getText().toString());

                Toast.makeText(MainActivity.this, updated ? "User Updated" : "Error", Toast.LENGTH_SHORT).show();
            }
        });

        btnDelete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                boolean deleted = databaseHelper.deleteUser(etEmail.getText().toString());
                Toast.makeText(MainActivity.this, deleted ? "User Deleted" : "Error", Toast.LENGTH_SHORT).show();
            }
        });
    }
}