SQLite Database Storage in Android App Development

In the previous chapter, we discussed using SharedPreferences for simple app data storage. However, for more complex data requirements, the SQLite database is a more suitable choice. SQLite is a lightweight, embedded relational database—ideal for Android apps that need to store structured data. Let’s dive deeper into how to use SQLite in Android applications.

1. What Is SQLite?

SQLite is a relational database written in C. It stores an entire database in a single file. Due to its lightweight nature, speed, and zero-configuration design (no separate server or installation required), it is widely adopted in Android development.

2. Using SQLite

In Android, you must create a database and implement core CRUD operations: Create (insert), Read (query), Update, and Delete. The following steps guide you through implementing these functionalities in your Android app.

2.1 Creating the Database and Table

First, create a subclass of SQLiteOpenHelper to manage database creation and version control.

public class MyDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "my_database.db";
    private static final int DATABASE_VERSION = 1;

    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_AGE = "age";

    public MyDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_NAME + " (" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                COLUMN_NAME + " TEXT," +
                COLUMN_AGE + " INTEGER)";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }
}

In the code above, we define a users table with three columns: id, name, and age.

2.2 Inserting Data

Next, insert data into the table using an INSERT operation.

public void addUser(String name, int age) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(COLUMN_NAME, name);
    values.put(COLUMN_AGE, age);

    db.insert(TABLE_NAME, null, values);
    db.close();
}

2.3 Querying Data

You can retrieve data from the table using a SELECT query.

public Cursor getAllUsers() {
    SQLiteDatabase db = this.getReadableDatabase();
    return db.query(TABLE_NAME, null, null, null, null, null, null);
}

2.4 Updating Data

You can also update existing records—for example, modifying a user’s age.

public void updateUser(int id, String name, int age) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(COLUMN_NAME, name);
    values.put(COLUMN_AGE, age);

    db.update(TABLE_NAME, values, COLUMN_ID + " = ?", new String[]{String.valueOf(id)});
    db.close();
}

2.5 Deleting Data

Finally, remove records using a DELETE operation.

public void deleteUser(int id) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_NAME, COLUMN_ID + " = ?", new String[]{String.valueOf(id)});
    db.close();
}

3. Practical Example

Below is a complete example demonstrating how to use the above SQLite methods within an Activity.

public class MainActivity extends AppCompatActivity {
    MyDatabaseHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHelper = new MyDatabaseHelper(this);
        dbHelper.addUser("Alice", 30);
        dbHelper.addUser("Bob", 25);

        Cursor cursor = dbHelper.getAllUsers();
        if (cursor.moveToFirst()) {
            do {
                int id = cursor.getInt(cursor.getColumnIndex(MyDatabaseHelper.COLUMN_ID));
                String name = cursor.getString(cursor.getColumnIndex(MyDatabaseHelper.COLUMN_NAME));
                int age = cursor.getInt(cursor.getColumnIndex(MyDatabaseHelper.COLUMN_AGE));
                // Here, you could display this data in UI components
            } while (cursor.moveToNext());
        }
        cursor.close();
    }
}

In real-world applications, database operations are typically performed on background threads—or using AsyncTask (deprecated but still illustrative)—to avoid blocking the main thread and ensure smooth UI responsiveness.

4. Summary

In this chapter, we thoroughly covered how to use the SQLite database for structured data storage and management in Android applications. By extending SQLiteOpenHelper, and implementing CRUD operations, you now have the foundation to handle structured data flexibly and efficiently.

The next chapter will explore another data storage option: file-based storage.