Making Android Databases Easy: Spotlight on SQLite

Dive into the world of Android data management with our blog post on using SQLite! Learn how to efficiently store and manage data in Android applications. From creating tables to performing CRUD operations, discover the power of SQLite in app development.

Android SQLite

Android SQLite is a lightweight database that comes with the Android operating system. It combines a clean SQL interface with very low memory requirements and respectable speed. For Android, SQLite is “baked into” the Android runtime, allowing each Android application to create its own SQLite databases. The native Android SQLite API isn’t JDBC, as JDBC might be too heavy for a memory-constrained smartphone. Once a database is successfully created, it resides in the “data/data//databases/” directory, accessible via the Android Device Monitor. SQLite is a typical relational database, containing tables (composed of rows and columns), indexes, and more. We can create our own tables to hold the data accordingly. This structure is referred to as a schema.

Android SQLite SQLiteOpenHelper

Android provides features for handling evolving database schemas, largely relying on the use of the SQLiteOpenHelper class. SQLiteOpenHelper is designed to address two very common issues.

1. When the application is run for the first time – At this point, we don’t have a database yet. Hence, we need to create the tables, indexes, starter data, etc.
2. When the application is upgraded to a newer schema version – Our database still remains in the old schema from the older version of the app. We have the opportunity to change the database schema to meet the requirements of the rest of the app.

SQLiteOpenHelper encapsulates this logic for creating and updating a database according to our specifications. For this, we need to create a custom subclass of SQLiteOpenHelper that implements at least the following three methods.

        public class DatabaseHelper extends SQLiteOpenHelper {
            public static final String TABLE_NAME = "COUNTRIES";
            public static final String _ID = "_id";
            public static final String SUBJECT = "subject";
            public static final String DESC = "description";
            static final String DB_NAME = "JOURNALDEV_COUNTRIES.DB";
            static final int DB_VERSION = 1;

            private static final String CREATE_TABLE = "create table " + TABLE_NAME + "(" + _ID
                    + " INTEGER PRIMARY KEY AUTOINCREMENT, " + SUBJECT + " TEXT NOT NULL, " + DESC + " TEXT);";

            public DatabaseHelper(Context context) {
                super(context, DB_NAME, null, DB_VERSION);
            }

            @Override
            public void onCreate(SQLiteDatabase db) {
                db.execSQL(CREATE_TABLE);
            }

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

Opening and Closing the Android SQLite Database Connection

Before performing database operations like inserting, updating, or deleting records in a table, first open the database connection by calling the getWritableDatabase() method.

        public DBManager open() throws SQLException {
            dbHelper = new DatabaseHelper(context);
            database = dbHelper.getWritableDatabase();
            return this;
        }

To close a database connection, call the following method.

        public void close() {
            dbHelper.close();
        }

Inserting a New Record into the Android SQLite Database Table

The following code snippet demonstrates how to insert a new record into the Android SQLite database.

        public void insert(String name, String desc) {
            ContentValues contentValue = new ContentValues();
            contentValue.put(DatabaseHelper.SUBJECT, name);
            contentValue.put(DatabaseHelper.DESC, desc);
            database.insert(DatabaseHelper.TABLE_NAME, null, contentValue);
        }

Updating a Record in the Android SQLite Database Table

The following snippet shows how to update a single record.

        public int update(long _id, String name, String desc) {
            ContentValues contentValues = new ContentValues();
            contentValues.put(DatabaseHelper.SUBJECT, name);
            contentValues.put(DatabaseHelper.DESC, desc);
            int i = database.update(DatabaseHelper.TABLE_NAME, contentValues, DatabaseHelper._ID + " = " + _id, null);
            return i;
        }

Deleting a Record in Android SQLite

Simply pass the ID of the record to be deleted, as shown below.

        public void delete(long _id) {
            database.delete(DatabaseHelper.TABLE_NAME, DatabaseHelper._ID + "=" + _id, null);
        }

Conclusion

SQLite is a powerful way to store data in Android applications. By using SQLiteOpenHelper and other features, developers can create, manage, and update databases to meet their application requirements. If you want to expand your Android app with a local database, SQLite is an excellent choice.