Database Integration: SQLite & MySQL in Android Apps

TAKEAWAYS

• SQLite stores data locally in a structured format (tables, rows, columns) using SQL queries, while MySQL runs on remote servers and supports concurrent user access.
• Android apps interact with SQLite via SQLiteOpenHelper and SQLiteDatabase, and with MySQL using libraries like MySQL Connector or REST APIs.
• JSON decoding in Android converts server responses (e.g., from APIs) into usable data objects using libraries like Gson or Jackson.
• APIs (e.g., Google Maps API, NEPSE stock data) fetch dynamic data, while SQLite caches local data for offline use (e.g., Daraz order history).
• Transactions ensure data integrity (e.g., bank transfers) by grouping operations into atomic units.
• Real-world apps combine both: eSewa uses SQLite for local transaction logs and MySQL for server-side user records.

1. Introduction to Databases in Android

Databases organize data efficiently for retrieval, insertion, and updates. In mobile apps, two types dominate:

• SQLite: Lightweight, file-based, ideal for local storage (e.g., app settings, user profiles).
• MySQL: Server-based, supports multiple users, used for cloud databases (e.g., bank records, e-commerce orders).

Key Concepts

• Table: A structured dataset (e.g., Customer in a bank app).
• Row: A single record (e.g., one customer’s data).
• Column: A field (e.g., account_no, name).
• Primary Key: Uniquely identifies a row (e.g., account_no).
• Foreign Key: Links tables (e.g., order_id in a Customer table).

2. SQLite: Local Database Integration

SQLite is embedded in Android apps. It uses SQL (Structured Query Language) for operations.

SQLite Data Types

Creating a SQLite Database

1. Extend SQLiteOpenHelper to define the database schema.
2. Override onCreate() to run SQL CREATE TABLE statements.

sequenceDiagram
    participant App
    participant SQLiteOpenHelper
    participant SQLiteDatabase

    App->>SQLiteOpenHelper: onCreate()
    SQLiteOpenHelper->>SQLiteDatabase: execSQL("CREATE TABLE Student(Roll INTEGER PRIMARY KEY, Name TEXT)")
    SQLiteDatabase-->>SQLiteOpenHelper: Table created

Worked Example: Inserting a Student Record

Table Schema:

CREATE TABLE Student (
    Roll INTEGER PRIMARY KEY,
    Name TEXT,
    Address TEXT
);

Android Code:

SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("Name", "Ramesh");
values.put("Address", "Kathmandu");
db.insert("Student", null, values);

State After Insertion:

Figure: SQLite Table "Student" after insertion
┌─────────┬─────────────┬─────────────────┐
│ Roll    │ Name        │ Address         │
├─────────┼─────────────┼─────────────────┤
│ 1       │ Ramesh      │ Kathmandu      │
└─────────┴─────────────┴─────────────────┘

CRUD Operations

3. MySQL: Remote Database Integration

MySQL runs on servers (e.g., AWS, Google Cloud). Android apps connect via:

• Direct JDBC: Not recommended for Android (uses MySQL Connector).
• REST APIs: Preferred (e.g., JSON-based endpoints).

Connecting to MySQL via API

1. Backend Server: Exposes endpoints (e.g., GET /customers, POST /customers).
2. Android App: Sends HTTP requests (using Retrofit or Volley) and decodes JSON.

Example API Response (JSON):

[
    {
        "account_no": "1001",
        "name": "John Doe",
        "account_type": "Savings",
        "amount": 5000.00
    }
]

Android Code to Fetch Data:

// Using Retrofit
public interface ApiService {
    @GET("customers")
    Call<List<Customer>> getCustomers();
}

// Decode JSON into Customer objects
class Customer {
    private String account_no;
    private String name;
    // Getters and setters
}

4. JSON Decoding in Android

JSON is a text format for structured data (e.g., API responses). Android uses libraries like Gson to parse it.

Example: Decoding a Customer Object

Gson gson = new Gson();
String jsonResponse = "[{\"account_no\":\"1001\",\"name\":\"John Doe\"}]";
Type customerListType = new TypeToken<List<Customer>>(){}.getType();
List<Customer> customers = gson.fromJson(jsonResponse, customerListType);

State After Decoding:

Figure: Parsed Customer Objects
┌─────────────┬─────────────┐
│ account_no  │ name        │
├─────────────┼─────────────┤
│ 1001        │ John Doe    │
└─────────────┴─────────────┘

5. Transactions in Databases

Transactions ensure data consistency (e.g., transferring money between accounts).

SQLite Transaction Example

db.beginTransaction();
try {
    // Deduct from sender
    db.execSQL("UPDATE Account SET amount = amount - 100 WHERE account_no = '1001'");
    // Add to receiver
    db.execSQL("UPDATE Account SET amount = amount + 100 WHERE account_no = '1002'");
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

States:

1. Before Transaction:

   Account 1001: 1000
   Account 1002: 500
   

2. After Transaction:

   Account 1001: 900
   Account 1002: 600
   

6. Comparing SQLite and MySQL

In the Real World

1. eSewa:

   • SQLite: Stores local transaction history (e.g., "Paid ₹100 to Pathao on 2023-10-01").
   • MySQL: Central server tracks all user balances and transactions across devices.

2. Daraz:

   • SQLite: Caches product details for offline browsing (e.g., "Nike Shoes" available in inventory).
   • MySQL: Server updates stock levels in real-time when orders are placed.

3. NEPSE (Stock Market):

   • MySQL: Stores real-time stock prices, user portfolios, and trade histories.
   • APIs: Android apps (e.g., NEPSE’s official app) fetch live data via REST endpoints.

7. Practical Example: Bank App with SQLite

Scenario: Insert a customer record and fetch it.

Step 1: Define Database Schema

CREATE TABLE Customer (
    account_no TEXT PRIMARY KEY,
    name TEXT,
    account_type TEXT,
    amount REAL
);

Step 2: Insert Data

SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("account_no", "ACC123");
values.put("name", "Alice");
values.put("account_type", "Savings");
values.put("amount", 2500.00);
db.insert("Customer", null, values);

Step 3: Fetch Data

Cursor cursor = db.query("Customer", null, "account_no = ?", new String[]{"ACC123"}, null, null, null);
while (cursor.moveToNext()) {
    String name = cursor.getString(cursor.getColumnIndex("name"));
    System.out.println("Customer: " + name);
}

Output:

Customer: Alice

Exam Tip

• SQL Queries: Always practice INSERT, SELECT, UPDATE, and DELETE with real-world examples (e.g., bank transactions).
• JSON Decoding: Know how to parse JSON into objects (use Gson or Jackson).
• API Integration: Understand REST endpoints and HTTP methods (GET, POST).
• Transactions: Explain why they are needed (e.g., "If a transfer fails halfway, both accounts should revert").
• SQLite vs. MySQL: Compare their use cases (local vs. remote) and concurrency support.
• Past Exam Focus: Expect questions on:
  • Connecting to MySQL via API (e.g., "Design an app to fetch customer data from a remote MySQL server").
  • SQLite schema design (e.g., "Create a table for a hospital’s Doctor records").
  • JSON decoding (e.g., "Decode this JSON into a Book object").

Visual Summary:

mindmap
  root((Database Integration in Android))
    SQLite
      Local Storage
      No Server
      Single User
      Example: Daraz Order Cache
    MySQL
      Remote Server
      Multi-User
      APIs for Fetching Data
      Example: eSewa Server
    JSON
      Parsing API Responses
      Example: NEPSE Stock Prices
    Transactions
      Atomic Operations
      Example: Bank Transfer