CHAPTER 4:
Android: Data Storage
with SQLite
MODULE: MOBILE DEVELOPMENT
LEVEL: 3RD AND 4TH YEAR ENGINEERING
SPECIALIZATION: SECURITY & NETWORKS
ACADEMIC YEAR 2025/2026
What is a database?
System that stores data, managed by software
Relational database → data in tables
Table = rows + columns
Row = one record
Column = all values of a field
Data is structured, columns have types
Software = RDBMS (e.g., SQLite)
Summary:
RDB = set of tables
Table = set of records
Wchat is SQLite?
Small, fast SQL database engine.
Built into Android – no extra setup.
Full SQL support with minimal overhead.
Database file stored at:
/data/data/<APP_PACKAGE_NAME>/databases/<[Link]>
SQLite runs inside your app's same process (no separate server)
By default, only the app that creates the database can access it
To share data between apps → use ContentProvider
DATA TYPES IN SQLITE
SQLite uses only 5 data types:
NULL – for empty or unknown values
INTEGER – for whole numbers (no decimal point)
REAL – for decimal numbers (floating-point values)
TEXT – for strings (characters, words, sentences)
BLOB – for raw binary data (e.g., an image stored directly in the
database)
An Android database is a
SQLiteDatabase
In code, an Android database is an object of class
[Link]
This class provides methods for CRUD operations:
insert() → add a record (SQL INSERT)
update() → modify records (SQL UPDATE)
delete() → remove records (SQL DELETE)
query() → run a SELECT query (returns data)
execSQL() → run any SQL that doesn't return data (e.g., CREATE, ALTER, DROP)
CRUD = Create, Read, Update, Delete
(→ INSERT, SELECT, UPDATE, DELETE in SQL)
A helper class
(SQLiteOpenHelper)
To create or upgrade a database, write a class that extends the abstract class
[Link]
You must define a constructor – SQLiteOpenHelper has no no-argument constructor
The constructor typically used:
Parameters:
context – the application context
name – database filename (the database name)
factory – used to create Cursor objects; usually set to null
version – database version number (starts at 1)
From helper to
SQLiteDatabase
The constructor (from the previous slide) is a proxy – it runs quickly, without creating the
database immediately.
The database is actually created when you call:
getWritableDatabase() → for read + write access
getReadableDatabase() → for read-only access
These methods are called on your helper object.
Database creation & update
In a class that extends SQLiteOpenHelper (the helper class), certain methods
are automatically called by the system:
public void onCreate(SQLiteDatabase db)
Called automatically when the database does not yet exist and needs to be created.
→ Put the code for creating tables (and inserting initial data) here.
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
Called when the database version number has been incremented.
Both methods are abstract in the base class → you must implement them in your helper
class.
Creating tables in SQLite
Creating a table uses a very natural syntax:
CREATE TABLE nom_de_la_table (
nom_du_champ_1 type {contraintes},
nom_du_champ_2 type {contraintes},
…);
For each attribute (column), you must declare at least two pieces of
information:
Its name – to identify it
Its data type
Creating tables in SQLite
You can also declare constraints for each attribute in
the {constraints} section. Common constraints include:
PRIMARY KEY – defines the primary key on an attribute
NOT NULL – the attribute cannot be NULL
CHECK – validates that the attribute's value meets a condition
DEFAULT – specifies a default value
For example:
CREATE TABLE nom_de_la_table (
champ1 INTEGER PRIMARY KEY, champ2
TEXT NOT NULL,
champ3 REAL NOT NULL CHECK (champ3 > 0),
champ4 INTEGER DEFAULT 10);
Query execution
To execute an SQL query for which you don't expect a response or
don't care about the response,
Simply use the method: execSQL()
void execSQL(String sql)
Generally, use execSQL(String) whenever the query is NOT a
SELECT, UPDATE, INSERT, or DELETE.
Helper class code:
MyBaseOpenHelper
Executing a SELECT query with
SQLite
rawQuery() – for SELECT queries
Cursor rawQuery(String sql, String[] args)
sql : SELECT statement (can use ? as placeholder)
args : values for each ?
Example 1:
Executing a SELECT query with
SQLite
Example 2 :
Executing a SELECT query with
SQLite
1. Get a readable SQLiteDatabase:
SQLiteDatabase manages the database connection.
Use it to:
Read → getReadableDatabase()
Write → getWritableDatabase()
SQLiteDatabase db = [Link]();
Executing a SELECT query with
SQLite
2. Execute the SELECT query with query() or rawQuery() :
Cursor cursor = [Link]("users", null, null, null, null,
null, null);
Or
Cursor cursor = [Link]("SELECT * FROM users", null);
3. Iterate through the results using the Cursor.
Retrieving data from a database
The query() method belongs to the SQLiteDatabase class. It is used
to perform a SELECT without writing SQL directly.
JAVA SQL
The role of the Cursor
A Cursor lets you iterate through query results row by row.
Key methods:
[Link](); // Go to the first row
[Link](); // Move to the next row
[Link](int); // Get a value, int : columnIndex
[Link](int); // Get an integer value
[Link]("colonne"); // Returns the index of a column
The role of the Cursor
Example:
while ([Link]()) {
String name = [Link]([Link]("name"));
int age = [Link]([Link]("age"));
Log.d("User", name + ", " + age);
}
Always close the Cursor when done : [Link]();
Also close the database when it is no longer needed : [Link]();
The role of the Cursor
Cursor cursor = [Link]("SELECT * FROM users", null);
try {
while ([Link]()) {
String name = [Link]([Link]("name"));
int age = [Link]([Link]("age"));
Log.d("User", name + ", " + age);
}
} finally {
[Link](); // Always close the Cursor to free resources.
}
Insert data into a SQLiteDatabase
After obtaining a SQLiteDatabase, use its methods to perform database operations.
public long insert (String table, String nullColumnHack, ContentValues values)
Inserts values into the specified table.
nullColumnHack: name of a column that will be set to NULL if values is empty.
values: a key/value object (ContentValues) containing column names and their values.
Return value: the row ID of the inserted row, or -1 if an error occurs.
This operation is the Create (C) of CRUD.
Retrieving data from a database
To retrieve data, use:
public Cursor query (String table, String[] columns, String whereClause,
String[] selectionArgs, String groupBy, String having, String orderBy)
columns – list of columns to return. Set to null to get all columns.
whereClause – the WHERE clause (without the word "WHERE"). Set to null to get all rows.
selectionArgs – used if whereClause contains ? placeholders. The values for these
placeholders are provided here.
groupBy – the GROUP BY clause (without the words "GROUP BY"). Useful for COUNT(*) etc.
having – specifies which groups to return (like SQL's HAVING – a WHERE on the result of an
aggregate, not on raw data).
orderBy – the ORDER BY clause (without the words "ORDER BY"). Set to null for no sorting.
Exemple
Objectif : compter le nombre d'étudiants par niveau (L1, L2,
etc.)
JAVA SQL
Cursor cursor = [Link]( SELECT niveau, COUNT(*) AS total
"etudiants", FROM etudiants
new String[] {"niveau", "COUNT(*) AS total"}, GROUP BY niveau;
null, // pas de WHERE
null,
"niveau", // groupBy → on groupe par niveau
null,
null
);