Data storage is an essential element in Android application development. Various storage methods can be used to securely store user data, but among them, databases are the most commonly used. In this article, we will explore in detail how to create an SQLite database using Java in Android and perform CRUD (Create, Read, Update, Delete) operations on the data.
1. What is a Database?
A database is a system for storing and managing information in an organized manner. In Android, SQLite, a relational database, is primarily used. SQLite is a lightweight database suitable for small applications, operating as a file-based system that can be easily used without a separate server.
2. Setting Up SQLite Database
After creating an Android project, you need to set up the SQLite database. It is common to write a helper class to create and manage the database.
package com.example.myapp.database;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "myApp.db";
public static final String TABLE_NAME = "users";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_NAME = "name";
public static final String COLUMN_EMAIL = "email";
private static final String TABLE_CREATE =
"CREATE TABLE " + TABLE_NAME + " (" +
COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
COLUMN_NAME + " TEXT, " +
COLUMN_EMAIL + " TEXT);";
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(TABLE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);
}
}
3. Inserting Data
To add data to the database, use the SQLiteDatabase object to call the insert() method. The example below shows how to add user information to the database.
package com.example.myapp.database;
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
public class UserRepository {
private DatabaseHelper dbHelper;
public UserRepository(Context context) {
dbHelper = new DatabaseHelper(context);
}
public void addUser(String name, String email) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(DatabaseHelper.COLUMN_NAME, name);
values.put(DatabaseHelper.COLUMN_EMAIL, email);
db.insert(DatabaseHelper.TABLE_NAME, null, values);
db.close();
}
}
4. Retrieving Data
To retrieve stored data, use the query() method. This method returns a Cursor object, through which you can access the data.
package com.example.myapp.database;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
import java.util.List;
public class UserRepository {
// ... (existing code)
public List getAllUsers() {
List users = new ArrayList<>();
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor = db.query(DatabaseHelper.TABLE_NAME, null, null, null, null, null, null);
if (cursor.moveToFirst()) {
do {
User user = new User();
user.setId(cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_ID)));
user.setName(cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_NAME)));
user.setEmail(cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_EMAIL)));
users.add(user);
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return users;
}
}
5. Updating Data
To update existing data, use the update() method. The example below shows how to change a specific user’s email.
package com.example.myapp.database;
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
public class UserRepository {
// ... (existing code)
public void updateUser(int id, String email) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(DatabaseHelper.COLUMN_EMAIL, email);
db.update(DatabaseHelper.TABLE_NAME, values, DatabaseHelper.COLUMN_ID + " = ?", new String[]{String.valueOf(id)});
db.close();
}
}
6. Deleting Data
To delete specific data, use the delete() method. The example below explains how to delete a specific user’s data.
package com.example.myapp.database;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
public class UserRepository {
// ... (existing code)
public void deleteUser(int id) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.delete(DatabaseHelper.TABLE_NAME, DatabaseHelper.COLUMN_ID + " = ?", new String[]{String.valueOf(id)});
db.close();
}
}
7. Complete Code Example
The complete example, which includes all the methods above, can be compiled as follows.
package com.example.myapp.database;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
import java.util.List;
public class UserRepository {
private DatabaseHelper dbHelper;
public UserRepository(Context context) {
dbHelper = new DatabaseHelper(context);
}
public void addUser(String name, String email) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(DatabaseHelper.COLUMN_NAME, name);
values.put(DatabaseHelper.COLUMN_EMAIL, email);
db.insert(DatabaseHelper.TABLE_NAME, null, values);
db.close();
}
public List getAllUsers() {
List users = new ArrayList<>();
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor = db.query(DatabaseHelper.TABLE_NAME, null, null, null, null, null, null);
if (cursor.moveToFirst()) {
do {
User user = new User();
user.setId(cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_ID)));
user.setName(cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_NAME)));
user.setEmail(cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_EMAIL)));
users.add(user);
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return users;
}
public void updateUser(int id, String email) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(DatabaseHelper.COLUMN_EMAIL, email);
db.update(DatabaseHelper.TABLE_NAME, values, DatabaseHelper.COLUMN_ID + " = ?", new String[]{String.valueOf(id)});
db.close();
}
public void deleteUser(int id) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.delete(DatabaseHelper.TABLE_NAME, DatabaseHelper.COLUMN_ID + " = ?", new String[]{String.valueOf(id)});
db.close();
}
}
8. Summary and Conclusion
In this tutorial, we learned how to perform basic CRUD operations using the SQLite database in Android. Databases play an essential role in managing data within applications, and SQLite is particularly widely used in the Android environment. If a more complex data storage solution is required, considering the Room Persistence Library is also an option. Room provides an abstraction layer over the SQLite database, making database operations easier.