Chapter 27. Android Databases

If we are going to make apps that offer our users significant features, then almost certainly we are going to need a way to manage, store, and filter significant amounts of data.

It is possible to efficiently store very large amounts of data with JSON, but when we need to use that data selectively rather than simply restricting ourselves to the options of "save everything" and "load everything", we need to think about which other options are available.

A good computer science course would probably teach the algorithms necessary for handling the sorting and filtering our data, but the effort involved would be quite extensive, and what are the chances of us coming up with a solution that is as good as the people who provide us with the Android API?

So often, it makes sense to use the solutions provided in the Android API. As we have seen, JSON and SharedPreferences classes have their place but at some point, we need to move on to using real databases for real-world...

Database 101

Let's answer a whole bunch of those database-related questions, and then we can get started making apps that use SQLite.

So, what is a database?

What is a database?

A database is both a place of storage and the means to retrieve, store, and manipulate data. It helps to be able to visualize a database before learning how to use it. The actual structure of the internals of a database varies greatly depending upon the database in question. SQLite actually stores all its data in a single file.

It will aid our comprehension greatly however if we visualize our data as if it were in a spreadsheet, or sometimes, multiple spreadsheets. Our database, like a spreadsheet, will be divided into multiple columns that represent different types of data, and rows that represent entries into the database.

Think about a database with names and exam scores. Take a look at this visual representation of this data for how we could imagine it in a database:

Notice, however, that there is an extra column...

The SQL syntax primer

Before we can learn how to use SQLite with Android, we need to first learn the basics of how to use SQLite in general, in a platform-neutral context.

Let's look at some example SQL code that could be used on an SQLite database directly, without any Kotlin or Android classes, and then we can more easily understand what our Kotlin code is doing later on.

SQLite example code

SQL has keywords, much like Kotlin, that cause things to happen. Here is a flavor of some of the SQL keywords we will soon be using:

• INSERT: Allows us to add data to the database

• DELETE: Allows us to remove data from the database

• SELECT: Allows us to read data from the database

• WHERE: Allows us to specify the parts of the database that match specific criteria we want to INSERT, DELETE, or SELECT from

• FROM: Used for specifying a table or column name in a database

Note

There are many more SQLite keywords than this and, for a comprehensive list, take a look at this link: https://sqlite.org/lang_keywords.html...

The Android SQLite API

There are a number of different ways in which the Android API makes it fairly easy to use our app's database. The first class we need to get familiar with is SQLiteOpenHelper.

SQLiteOpenHelper and SQLiteDatabase

The SQLiteDatabase class is the class that represents the actual database. The SQLiteOpenHelper class, however, is where most of the action takes place. This class will enable us to get access to a database and initialize an instance of SQLiteDatabase.

In addition, the SQLiteOpenHelper class, which we will inherit from in our Age database app, has two functions to override. First, it has an onCreate function, which is called the first time a database is used, and it therefore makes sense that we would incorporate our SQL in which to create our table structure.

The other function we must override is onUpgrade, which, you can probably guess, is called when we upgrade our database (ALTER its structure).

Building and executing queries

As our database structures become...

Coding the database class

Here, we will put into practice everything we have learned so far and finish coding the Age database app. Before our Fragment classes from the previous section can interact with a shared database, we need a class to handle interaction with, and creation of, the database.

We will create a class that manages our database by implementing SQLiteOpenHelper. It will also define some String variables in a companion object to represent the names of the table and its columns. Furthermore, it will supply a bunch of helper functions we can call to perform all the necessary queries. Where necessary, these helper functions will return a Cursor object that we can use to show the data we have retrieved. It would be trivial then to add new helper functions should our app need to evolve:

Create a new class called DataManager and add the companion object, the constructor, and the init block:

Note

We discussed the companion object in Chapter 25, Advanced UI with Paging and Swiping

class...

Coding the Fragment classes to use the DataManager class

Add this highlighted code to the InsertFragment class to update the onCreateView function, as follows:

val view = inflater.inflate(
         R.layout.content_insert,
         container,
         false)

// Database and UI code goes here in next chapter
val dm = DataManager(activity!!)

val btnInsert = view.findViewById(R.id.btnInsert) as Button
val editName = view.findViewById(R.id.editName) as EditText
val editAge = view.findViewById(R.id.editAge) as EditText

btnInsert.setOnClickListener(
            {
          dm.insert(editName.text.toString(),
                 editAge.text.toString())
            }
)

return view

In the code, we get an instance of our DataManager class and a reference to each of our UI widgets. Then, in the onClick function of the button, we use the insert function to add a new name and age to the database. The values to insert are taken from the two EditText widgets.

Add this highlighted code to the DeleteFragment...

Running the Age Database app

Let's run through some of the functions of our app to make sure it is working as expected. First, I added a new name to the database using the Insert menu option:

And then, I confirmed it was there by viewing the Results option:

Then, I used the Delete menu option and looked at the Results option again to check that my chosen name was, in fact, removed:

Next, I searched for a name that I knew existed to test the Search function:

Let's review what we have done in this chapter.

Summary

We have covered a lot in this chapter. We have learned about databases and, in particular, the database of Android apps, SQLite. We have practiced the basics of communicating with and querying a database using the SQL language.

We have seen how the Android API helps us use an SQLite database, and have implemented our first working app with a database.

That is just about it, but please look at the brief final chapter that follows.