Working with SQLite database in Android (without auxiliary libraries)

Contract class

The Contract class has only the role of being a container for constants that define names for URIs, tables, and columns. This class allows us to use the same constants in all other classes, which makes our job much easier when working with the database because it allows us to change the column name or similar in one place.

1) Creating classes and constructors

The first thing is to create a class and its empty constructor. Since this is just a container for constants that are static, there is no need to instantiate this class, and to prevent someone from accidentally instantiating the class, we define the constructor as private:

2) Creating a subclass that represents the table

It is recommended that for each table within the database we create a new static subclass that implements the BaseColumns interface. Within this class we define table-related constants such as table name and column names:

NOTE:
It is not necessary to assign constants for columns named _ID and _COUNT because we get them by implementing the “BaseColumns” interface. See more about this interface here.

SQLiteOpenHelper class

For maximum control over local data and working with the SQLite database (for executing SQL requests…) it is good to create a class that extends the SQLiteOpenHelper class. The “SQLiteOpenHelper” class is, as its name suggests, a “helper” class that facilitates database creation and versioning.

Constructive method

As we mentioned our class extends the SQLiteOpenHelper.java class, so its constructor method generated by Android Studio looks like this:

As you can see, the constructor method generated in this way has quite a number of attributes, however, if we define constants for the name and version of the database, then the constructor method could look like in the following example:

Immediately after extending the class, we notice that our class must implement two methods: onCreate() and onUpgrade(). We will talk about their role later.

Instantiating a class using the “singleton pattern”

Since the database is used throughout the application (services, fragments…) for this reason, the recommended practice is to apply the so-called “Singleton Pattern” to instances of the SQLiteOpenHelper class to avoid memory leaks. The best solution is to make the database instance a single instance for the entire life cycle of the application.

The getInstance() static method ensures that there will be only one AppDBHelper instance and only one buyLista.db database at any given time. If the instance of the helper class is not initialized (“sInstance” object), one object will be created, and if it has already been created, then that existing object will simply be used.

Database reference

Getting the database that is created as soon as this helper class is instantiated is done by calling the getWriteableDatabase() method. Since the database connection is cached, it is not “expensive” to call this onemethod wherever we need it in the class.

Now that we have an instance of our database that inherits the properties of the SQLiteDatabase.java class, we can use all of its methods. You can view some of the frequently used methods here.

Defining the table within onCreate()

Within the onCreate() method, our database is defined. First, we write a “query” that creates a table, and then we execute it with the execSQL() method. The SQL query that creates the table looks like this:

And when we apply it to our example like this:

Database versioning

As we already mentioned, the class that extends “SQLiteOpenHelper” is also used for database versioning. Database versioning is important because if at some point in the new version of our application we realize that we need another column, we won’t be able to do it without this.
We need to define within the onUpgrade() method what we want to be done if the table changes. This method will only be called if a database with the same DATABASE_NAME already exists, but the DATABASE_VERSION is different from the version of the database that exists on disk.

Example

In this example, the simplest implementation is shown, when in this case the old table is deleted and recreated:

Query to the database – db.query()

One of the most important functions for which this helper method is intended is getting data from the database. We get data from the database with a standard SQL query using the method query() which returns a Cursor object. You can see more about the Cursor object and what it represents here.

Example

When we have data within the Cursor object, we need to go through it and fill the list, this is usually done in the form of one method, as in the following example:

Inserting data – db.insert()

The insert() method is used to insert data into the database. This method takes parameters: “tablename”, “nullColumnHack” and “ContentValues”. ContentValues is a class responsible for storing a key-value set of values ​​within an object. To store one set of dataits method (String key, TypeData value):

is used

Only when we have fields defined in one row and saved in the ContentValues object can we insert them into the database with the aforementioned insert() method:

Deleting a row from the table – db.delete()

To delete a table row, it is necessary to pass to the delete() method the name of the table and the WHERE clause on the basis of which it will find the desired row:

You can see the entire code of this class here, while the entire project can be found on the GitHub page “SQLdatabaseWithoutLibrary”.

When we have created this helper class and defined its methods for working with the database, in the MVVM architecture we access them from the Repository class while it further broadcasts changes to higher levels. See how the whole process looks in the article “Repository – ViewModel – View cycle”.