Storing Android application data using Database (MySQL or SQLite): connecting Android app with database; CRUD operations using app; create and insert data; read, update and delete data

📖 Theory

Data that survives closing the app

FestConnect Mobile takes registrations, but when the user closes the app, they vanish: everything was in memory. A real app REMEMBERS.

Android ships a database built INTO every device for exactly this: SQLite, a lightweight relational database that stores data locally on the phone, no server needed. You already know SQL from BCA205 and BCA303; SQLite is that SQL, running on the device. This lesson gives FestConnect Mobile persistent storage: creating a table, and full CRUD (insert, read, update, delete) so registrations survive between sessions. (The slug also mentions MySQL: that is a SERVER database an app reaches over the network via an API; SQLite is the on-device choice here.)

📖 Theory

SQLite and SQLiteOpenHelper

SQLite is an embedded relational database inside Android, stored as a file on the device. To use it, you create a helper class extending SQLiteOpenHelper, which manages the database's creation and versioning:

• onCreate(db): runs ONCE when the database is first created: put your CREATE TABLE SQL here
• onUpgrade(db, old, new): handles schema changes when you bump the database version

The helper gives you a SQLiteDatabase object to run operations on. Because it is local and serverless, SQLite is perfect for a phone: fast, offline-capable, and private to the app. (On-device data like registrations or a cache lives here; data shared across users lives on a SERVER, reached via an API.)

⌨️ Practical

Create a table and do CRUD

class DbHelper(context: Context) :
    SQLiteOpenHelper(context, "fest.db", null, 1) {

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL("CREATE TABLE regs(id INTEGER PRIMARY KEY, name TEXT, event TEXT)")
    }
    override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) {
        db.execSQL("DROP TABLE IF EXISTS regs")
        onCreate(db)
    }
}

// INSERT with ContentValues:
fun addReg(db: SQLiteDatabase, name: String, event: String) {
    val values = ContentValues().apply {
        put("name", name)
        put("event", event)
    }
    db.insert("regs", null, values)
}

// READ with a parameterized query (injection-safe):
fun findByEvent(db: SQLiteDatabase, event: String): List<String> {
    val names = mutableListOf<String>()
    val cursor = db.rawQuery("SELECT name FROM regs WHERE event = ?", arrayOf(event))
    while (cursor.moveToNext()) {
        names.add(cursor.getString(0))
    }
    cursor.close()          // always close the cursor
    return names
}

📖 Theory

CRUD, cursors, and safety

The four operations on the SQLiteDatabase:

• insert: db.insert(table, null, contentValues), where ContentValues is a key-value of column to value
• read: db.query(...) or db.rawQuery(sql, args) returns a Cursor you iterate (cursor.moveToNext(), cursor.getString(index)), then CLOSE
• update: db.update(table, values, whereClause, whereArgs)
• delete: db.delete(table, whereClause, whereArgs)

One safety rule carries over from BCA504: use parameterized queries (? placeholders with selectionArgs/arrayOf(...)), NEVER string-concatenated user input, to prevent SQL injection. SQLite is a real SQL database, so it has the same injection risk and the same defence. (Modern Android often uses Room, a library that wraps SQLite with less boilerplate; know it exists.)

🧩 Quiz

Where does SQLite store FestConnect Mobile's registration data, and what makes it suitable for a phone app?

1. On a remote server, requiring internet for every read
2. Locally ON THE DEVICE, as an embedded database needing no server: fast, offline-capable, and private to the app
3. In the cloud only
4. It cannot store data persistently

💭 Think first

SQLite or a server database (MySQL)?

FestConnect Mobile could store registrations in on-device SQLite or in a server MySQL database (via an API, like BCA504's back-end). When is each right? Then tap.

⚠️ Watch out

SQLite traps

String-concatenating queries: use parameterized queries (? + args); injection risk is real in SQLite too (BCA504's lesson).

Not closing the Cursor: always cursor.close() after reading, or you leak.

Heavy DB work on the UI thread: do database operations off the main thread, or the app freezes.

Forgetting onUpgrade: schema changes need a version bump and onUpgrade handling.

Confusing SQLite (local) with MySQL (server): SQLite is on-device; a server DB is reached via an API.

📖 Theory

It remembers; now device powers

FestConnect Mobile now persists data locally with SQLite. The last two lessons of the unit tap the phone's HARDWARE: accessing the user's current LOCATION (to show nearby fest venues), and capturing an image with the CAMERA (for a profile or event photo). These device capabilities, gated by permissions, are what make a mobile app more than a small website. Location next, then camera.

✅ Summary

Key takeaways

• SQLite is Android's built-in, on-device relational database (a file, no server): fast, offline, private to the app.
• Extend SQLiteOpenHelper: onCreate runs CREATE TABLE; onUpgrade handles schema/version changes.
• CRUD on the SQLiteDatabase: insert (ContentValues), query/rawQuery (returns a Cursor), update, delete.
• Iterate a Cursor with moveToNext() and getString(index); always close it.
• Use parameterized queries (? + args), never concatenated input: SQL injection risk carries over from BCA504.
• SQLite is for LOCAL data; a server database (MySQL via API) is for SHARED/central data; Room wraps SQLite with less boilerplate.
• Memory hook: SQLite is the on-device SQL database; helper creates tables, CRUD via the db, parameterize queries.