SQLite Database is one of the most important local storage systems in Android development. It allows applications to store, manage, and retrieve structured data directly on the device. Unlike Shared Preferences, which is used for small key-value data, SQLite is designed for handling large and complex datasets such as records, tables, and relationships.
Almost every Android application that needs offline data storage uses SQLite or modern alternatives built on top of it like Room Database.
What is SQLite Database?
SQLite is a lightweight, embedded relational database system built into Android.
It stores data in tables consisting of rows and columns, similar to a spreadsheet.
Each table represents a specific type of data, such as users, products, or orders.
Why Use SQLite in Android?
SQLite is important because it:
- Stores structured data locally
- Works offline without internet
- Supports complex queries
- Handles large datasets efficiently
- Organizes data in tables
- Provides fast read and write operations
It is ideal for applications that require persistent and structured storage.
Where is SQLite Used?
SQLite is commonly used in:
- Banking apps
- E-commerce apps
- Student management systems
- Inventory systems
- Offline note apps
- Messaging apps
- Healthcare apps
- Booking systems
Any application that manages records uses SQLite.
Basic Structure of SQLite
SQLite stores data in:
- Databases
- Tables
- Rows
- Columns
Example table:
Users Table
------------------------
ID | Name | Email | Age
1 | Ali | a@x.com | 22
2 | Sara | s@x.com | 25
Each row represents a record.
Creating SQLite Database in Android
In Android, we use SQLiteOpenHelper class to create and manage databases.
Step 1: Create Database Helper Class
public class DatabaseHelper
extends SQLiteOpenHelper {
}
Constructor
public DatabaseHelper(Context context) {
super(context,
"MyDatabase.db",
null,
1);
}
Here:
- Database name: MyDatabase.db
- Version: 1
Creating Table
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE Users (" +
"ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
"Name TEXT, " +
"Email TEXT)");
}
This creates a Users table.
Upgrading Database
@Override
public void onUpgrade(
SQLiteDatabase db,
int oldVersion,
int newVersion) {
db.execSQL("DROP TABLE IF EXISTS Users");
onCreate(db);
}
This handles database version updates.
Inserting Data
To insert data into SQLite:
public boolean insertUser(
String name,
String email) {
SQLiteDatabase db =
this.getWritableDatabase();
ContentValues values =
new ContentValues();
values.put("Name", name);
values.put("Email", email);
long result =
db.insert(
"Users",
null,
values);
return result != -1;
}
If result is not -1, insertion is successful.
Reading Data from SQLite
public Cursor getAllUsers() {
SQLiteDatabase db =
this.getReadableDatabase();
return db.rawQuery(
"SELECT * FROM Users",
null);
}
This returns all stored records.
Using Cursor to Display Data
Cursor cursor =
databaseHelper.getAllUsers();
if(cursor.moveToFirst()) {
do {
String name =
cursor.getString(1);
String email =
cursor.getString(2);
System.out.println(
name + " " + email);
} while(cursor.moveToNext());
}
Cursor helps navigate database results.
Updating Data
public boolean updateUser(
String id,
String name,
String email) {
SQLiteDatabase db =
this.getWritableDatabase();
ContentValues values =
new ContentValues();
values.put("Name", name);
values.put("Email", email);
int result =
db.update(
"Users",
values,
"ID=?",
new String[]{id});
return result > 0;
}
This updates existing records.
Deleting Data
public boolean deleteUser(String id) {
SQLiteDatabase db =
this.getWritableDatabase();
int result =
db.delete(
"Users",
"ID=?",
new String[]{id});
return result > 0;
}
This removes a record from the table.
Complete Database Helper Example
public class DatabaseHelper
extends SQLiteOpenHelper {
public DatabaseHelper(Context context) {
super(context,
"MyDatabase.db",
null,
1);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE Users (" +
"ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
"Name TEXT, " +
"Email TEXT)");
}
@Override
public void onUpgrade(
SQLiteDatabase db,
int oldVersion,
int newVersion) {
db.execSQL(
"DROP TABLE IF EXISTS Users");
onCreate(db);
}
}
This is the foundation of SQLite in Android.
Inserting Data from Activity
DatabaseHelper db =
new DatabaseHelper(this);
boolean result =
db.insertUser(
"Ali",
"ali@email.com");
Retrieving Data in Activity
Cursor cursor =
db.getAllUsers();
while(cursor.moveToNext()) {
System.out.println(
cursor.getString(1));
}
Advantages of SQLite
SQLite offers many benefits:
- Works offline
- Lightweight and fast
- No server required
- Supports structured data
- Built into Android
- Reliable storage system
It is ideal for local databases.
Limitations of SQLite
SQLite has some limitations:
- Not suitable for large-scale cloud apps
- Requires manual query handling
- No built-in synchronization
- Limited concurrency support
- More complex than Shared Preferences
For modern apps, Room Database is often preferred.
Real-World Applications
SQLite is used in:
- Offline messaging apps
- Shopping carts
- Banking transaction history
- Inventory tracking systems
- Student record systems
- Note-taking apps
- Appointment booking apps
It is widely used in data-driven applications.
Common Beginner Mistakes
Not Closing Database
Always close database connections when not needed.
Incorrect Column Index
Wrong:
cursor.getString(3);
Correct:
cursor.getString(1);
SQL Injection Risk
Avoid raw user input in queries.
Forgetting Table Creation
Ensure onCreate() executes properly.
Best Practices
When using SQLite:
- Use parameterized queries
- Close cursors after use
- Handle database versioning properly
- Avoid heavy queries on main thread
- Use helper classes for structure
- Validate user input
These practices improve performance and security.
Importance of SQLite
SQLite is important because it:
- Enables offline data storage
- Supports structured information
- Improves app functionality
- Works without internet
- Stores large datasets efficiently
- Powers many real-world Android apps
It is a core database solution in Android development.
Conclusion
SQLite Database is a powerful local storage system in Android used to manage structured data in tables. It allows developers to create, read, update, and delete records efficiently using SQL commands. With its lightweight nature and built-in support in Android, SQLite is widely used in applications that require offline data storage and structured data management. Mastering SQLite is essential for building professional, data-driven Android applications.