3. SQLite Database

Written by Fuad Kamal

Using Files and Shared Preferences are two excellent ways for an app to store small bits of data. However, sometimes an app needs to store larger amounts of data in a more structured manner, which usually requires a database. The default database management system (DBMS) that Android uses is called SQLite. SQLite is a library that provides a DBMS, based on SQL. Some distinctive features of SQLite include:

• It uses dynamic types for tables. This means you can store a value in any column, regardless of the data type.
• It allows a single database connection to access multiple database files simultaneously.
• It’s capable of creating in-memory databases, which are very fast to work with.

Android provides the APIs necessary to create and interact with SQLite databases in the android.database.sqlite package.

Although these APIs are powerful and familiar to many developers, they are low-level and do require some time and effort to use. Currently, it’s recommended to use the Room Persistence Library instead, which will provide an abstraction layer for accessing the data in your app’s SQLite databases. One disadvantage to using the SQLite APIs is that there is no compile-time verification of the raw SQL queries, and if the database structure changes, the affected queries have to be updated manually. Another is that you need to write a lot of boilerplate code to connect and transform SQL queries and data objects.

If you have written an app in the past that utilizes the SQLite APIs in Java, this chapter will show you how to use them with Kotlin, instead. However, if you have never seen an app that utilizes the SQLite APIs, this section will show you an example of how to use them in your apps. Overall, this section will give you a greater understanding and appreciation for the new and improved Room Persistence Libraries.

Understanding database schemas

The first step to creating an app that reads from a SQLite database is to decide on a database schema. The schema is the formal declaration of how the data in a database is structured. It’s good practice to define constants that describe the database schema in a self-documenting way in their own class or file. These can be organized into subclasses for databases with multiple tables and should be visible throughout the scope of the project.

The schema you’ll define is going to create a database called todoitems.db, with one table inside. Inside this table will be three columns:

1. The primary key column will be an auto incrementing Integer named todoitemid.
2. The todoname column will contain the actual text of the TODO item.
3. The todoiscompleted column will contain a Long value that will represent whether a TODO item is completed or not.

Understanding CRUD operations

CRUD stands for Create, Read, Update and Delete. These are the basic operations that can be done with a database, to store, maintain and utilize data. Each in turn:

• Create: This operation adds a new record to the database. In SQL or Structured Query Language, this is accomplished using an INSERT INTO statement.

• Read: The read operation queries the database or searches and returns zero to many records meeting a specific criteria. In SQL, this is done by using a SELECT FROM statement.

• Update: The update operation performs a change to an existing record or set of records that meet a specific criteria. In SQL, the UPDATE statement is used.

• Delete: The delete operation is used to delete records meeting specific criteria. In SQL, the DELETE FROM statement is used to delete a record.

• To specify criteria, the WHERE clause is used in SQL, along with the commands listed above.

Fortunately, the SQLite APIs in Android provide a useful class called SQLiteOpenHelper, which will simplify the integration with the database. This will reduce the amount of knowledge you must have of raw SQL. However, if you’d like more information about SQL and how to syntactically use it with SQLite, refer to the documentation found in the Where to go from here? section at the end of this chapter.

Getting started

Open the starter project for this chapter and ignore the errors you get initially. Notice there are three sections in the com.raywenderlich.android.sqlitetodo package: model, view and controller.

• model contains the class ToDo, which represents a singular item on the TODO list.

• view contains SplashActivity.kt and MainActivity.kt. The first file contains a class which interface you see once you start the app. The second one contains the class used to allow the user interacting with TODO tasks.

• controller contains two files that serve as a bridge between the data and the interface. ToDoAdapter retrieves the data from the database and places it into the RecyclerView list, whereas ToDoDatabaseHandler contains the code that interacts with the database.

Now, it’s time to create the database schema utlilizing a contract class.

Creating the database constants using a contract class

In the model package, create a new file and name it TodoDbSchema.kt. Place the following code into the file:

object ToDoDbSchema {
  // 1
  const val DATABASE_VERSION = 1
  // 2
  const val DATABASE_NAME = "todoitems.db"
  object ToDoTable {
    // 3
    const val TABLE_NAME = "todoitems"
    object Columns {
      // 4
      const val KEY_TODO_ID = "todoid" 
      // 5
      const val KEY_TODO_NAME = "todoname" 
      // 6
      const val KEY_TODO_IS_COMPLETED = "iscompleted" 
    }
  }
}

Here’s what each part is:

1. The version of the database. Manually increment this number each time the database version is new, and you need to use the version to add update or migration logic.
2. The name of the database as it will be stored in the app-specific database folder on the device.
3. The name of the table will use to store the TODO items.
4. The column that contains the unique primary key for each TODO item.
5. The column that contains the text of the TODO item.
6. The column that will store an indicator whether or not you completed a TODO item.

Now, it’s time to create your own database!

Using SQLiteOpenHelper

SQLiteOpenHelper is a helper class designed to help manage database creation and version management. To use it, you need to create a subclass of it and implement onCreate(), onUpgrade() and optionally the onOpen methods. This class will then open the database if it exists, create it, if it does not exist, and upgrade it when necessary. It does all these things automatically, using the above-mentioned methods.

Now that the constants are in place and you’ve learned a bit about SQLiteOpenHelper, it’s time to create the database and add the CRUD operations.

Open ToDoDatabaseHandler.kt and add replace the class declaration with:

class ToDoDatabaseHandler(context: Context): SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) 

The above code makes the database handler class a subclass of SQLiteOpenHelper. This will allow the class to utilize the Android SQLite APIs in a simpler way.

Next up, you have to give the helper the schema data required to build the database.

Creating the database

First, the database must be created if it doesn’t already exist. This is automatically done by onCreate(), which will run only when it needs to. If the database already exists, this method will not run.

Note: When testing an app that utilizes SQLite, you must uninstall the app to delete the old database and reinstall the app to recreate it or completely clear the data of an app.

Add the following method to ToDoDatabaseHandler:

//This method creates the database
override fun onCreate(db: SQLiteDatabase?) {
	// 1
	val createToDoTable = """
  	CREATE TABLE $TABLE_NAME  (
    	$KEY_TODO_ID INTEGER PRIMARY KEY,
    	$KEY_TODO_NAME  TEXT,
    	$KEY_TODO_IS_COMPLETED  LONG );
	"""
	// 2
	db?.execSQL(createToDoTable)
}

The parts above:

1. Assign an SQL CREATE statement which creates the table described in the schema above, to the createToDoTable value.
2. Run the command using the database object provided by SQLiteOpenHelper.

The above method will be run anytime the app is run and the database isn’t found. Otherwise, the database exists, and there’s no reason to recreate it.

One of the downsides of SQL, in general, is having to write the statements perfectly. Be very mindful when creating SQL statements using concatenated strings. It’s very easy to make an error in doing so. It can be helpful to print the query using a LOG statement to check to see if the string is correct. The text of the query string is also displayed at the end of an error message in Logcat if the app crashes with an error. Additionally, you could use a local, or a Web SQL database terminal or environment, to try and validate the statement.

Now that the database has been created, you must add a mechanism for upgrading it. Add the following method to ToDoDatabaseHandler :

//This method is used to update the database
override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
  // 1
  db?.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
  // 2
  onCreate(db)
}

The above:

1. Executes the SQL statement to drop the old the table if it exists.
2. Calls onCreate() again to recreate the database, this time with an upgrade.

onUpgrade() is the one that the framework calls automatically when a different version of the DB is detected. This happens when an instance of the SQLiteOpenHelper implementation is created with a different version number in its constructor.

The implementation of this method should provide the code that is necessary to upgrade to the new version of the schema. Since this is a very simple example, you’re just dropping the previous table, and recreating it, using the new schema. But in real environments, and big apps, you’d usually do something called a migration.

Migration is the process of updating the database, without dropping it. For example, if you decide to add a new field to the TODO model, called toDoPriority, you would need to change the database as well, because you need to store that field, and retrieve it, but the old database doesn’t have that field defined. You would then update the version number (e.g., from 1 to 2) and write a SQL statement, to update the table, by adding a new column — toDoPriority.

In the next steps, you’ll add the CRUD operations to the database handler implementation.

Using ContentValues

Before you add items to the database, it’s important to understand a construct known as ContentValues. This is a class that allows you to store values that a ContentResolver can process. The information that is stored in objects of this class is stored as key-value pairs.

The key consists of the column name for the piece of information being stored, and the value consists of the value to be stored in that column. A key-value pair for each column of the database in a particular row is put into the ContentValues object before it is then handed to another method, which will resolve the content and perform the database operation.

Simply put, ContentValues are similar to a Map structures and are used to define the columns and the values to be used in database operations. As such, you’ll use them to insert data into the database and update existing models if needed.

Adding a TODO

Add the following code in createToDo(), to insert a TODO item into the table:

// 1
val db: SQLiteDatabase = writableDatabase
// 2
val values = ContentValues()
// 3
values.put(KEY_TODO_NAME, toDo.toDoName)
values.put(KEY_TODO_IS_COMPLETED, toDo.isCompleted)
// 4
db.insert(TABLE_NAME, null, values)
// 5
db.close()

With the above, you:

1. Get a writeable instance of the database and store it in the db value.
2. Create an object of the ContentValues class called values.
3. Take the text of the TODO from the toDoName field and put it into values. Use KEY_TODO_NAME as the key, and toDoName as the key’s value.
4. Put the key-value pair for the isCompleted field into values, as well.
5. Close the database, to avoid potential leaks.

Running the program

Build and run. Use the Floating Action Button with the plus sign icon to add a TODO item.

When you enter the text for the item, tap ADD.

Now that you’ve added the code to create the database and added an item, next step is to take a look at the new database.

Note: The item you added won’t be displayed in RecyclerView yet, but stay tuned; you’ll add that capability after a few more steps.

Viewing the SQLite database

Each app has its own folder to store databases on the device just like files. To look at the database that was just created, open the Device File Explorer as you did in Chapter 1, “Using Files”. You can found it on the bottom right-hand corner of Android Studio as a collapsed, vertical pane.

Once in the Device File Explorer:

• Expand the data ▸ data ▸ com.raywenderlich.android.sqlitetodo ▸ databases folder.
• Right-click on the databases folder and select Synchronize.
• Then select Save As….
• Save the entire folder to the location of your choice.

To view the database, you’ll need a tool that allows you to open the .db file. A tool such as the SQLite Browser works nicely; you can find it here: https://sqlitebrowser.org/dl/. Download, install and open the tool.

Select Open Database and find and select your .db file. Then, switch to the Browse Data tab. Find the drop-down Table: and select the todoitems item. Now the records in the table will be listed in the main area of the window and you can see the record that you added, along with its unique id and completed status.

Note: Another tool you can use to view the database on the command line is sqlite3, which you can find, here: https://developer.android.com/studio/command-line/sqlite3.

Now that you’ve successfully added a record to your database and viewed it in the file system, it’s time to write the rest of the CRUD operations!

Reading from a database

Before the record added in the previous step can be displayed, the app must have the capability to read the records from the database. First, the database must be queried for the records to display. Then, you’ll use a tool Cursor to iterate through the records and add them to a list.

Understanding the cursor

In Android, Cursor is assigned to the result set of a query being run against the database. Then it’s used to iterate over the result set in a type-safe manne - row by row, field by field.

Internally, the rows of data that are returned by a query are stored in the Cursor reads through the data-keeping track of its current position. To start iterating, the current position must be moved to point to a valid row of data, such as the first row. Then, a loop structure is utilized to keep reading while a next record exists, to read in the result set.

In ToDoDatabaseHandler.kt, in readToDos() replace return with the following code:

// 1
val db: SQLiteDatabase = readableDatabase
// 2
val list = ArrayList<ToDo>()
// 3
val selectAll = "SELECT * FROM $TABLE_NAME"
// 4
val cursor: Cursor = db.rawQuery(selectAll, null)
// 5
if (cursor.moveToFirst()) {
  do {
    // 6
    val toDo = ToDo().apply {
      toDoId = cursor.getLong(cursor.getColumnIndex(KEY_TODO_ID))
      toDoName = cursor.getString(cursor.getColumnIndex(KEY_TODO_NAME))
      isCompleted = cursor.getInt(cursor.getColumnIndex(KEY_TODO_IS_COMPLETED)) == 1
    }
    // 7
    list.add(toDo)
  } while (cursor.moveToNext())
}
// 8
cursor.close()
// 9
return list

With the above, you:

1. Get a readable instance of the database.
2. Create an ArrayList<ToDo> to store the records.
3. Construct the SELECT query to get the records.
4. Create a Cursor object using SELECT on the database.
5. Starting at the beginning, and move Cursor through all the records one at a time.
6. Assign the fields of each record to the corresponding attribute of a new ToDo item.
7. Add the item to the list.
8. Close Cursor, to avoid memory leaks
9. Return list` as a result.

Build and run. The record added in the previous step is visible now.

So far, you’ve added a lot of code to the project. The app is now able to create a database, update the database, add records and read the records. Next, you’ll add the capability to update an existing record, and finally, to delete records.

Updating a TODO

To add the capability to update a record, replace return in updateToDo() with the code below:

// 1
val todoId = toDo.toDoId.toString()
// 2
val db: SQLiteDatabase = writableDatabase
// 3
val values = ContentValues()
values.put(KEY_TODO_NAME, toDo.toDoName)
values.put(KEY_TODO_IS_COMPLETED, toDo.isCompleted)
// 4
return db.update(TABLE_NAME, values, "$KEY_TODO_ID=?", arrayOf(todoId))

With this code, you:

1. Prepare the todoId argument, for the UPDATE clause.
2. Get a writable instance of the database.
3. Create a ContentValues object to contain the key-value pairs, and put the TODO item’s property values in it.
4. Run the UPDATE query on the database and return the result of the operation.

Now build and run. Tap the edit icon that looks like a pencil next to the record. Update the record to a new value and tap Update.

Now, the record should reflect any changes you made as it is displayed in RecyclerView.

Note: db.update() is used in multiple places in the app. First, it was used in the previous example when changes were made to the name of the TODO item. The app also updates TODO items when the completed checkbox is checked.

There is one more bit of functionality to add to this app. The app must also be able to delete TODO items.

Deleting a TODO

In order to delete an item from the database, add the following code to deleteToDo():

val db: SQLiteDatabase = writableDatabase
db.delete(TABLE_NAME, "$KEY_TODO_ID=?", arrayOf(id.toString()))
db.close()

The above section of code simply gets an instance of the writeable database, deletes the correct TODO item filtered by its id, and closes the database.

Build and run. Tap the Delete icon that looks like a wastebasket and the TODO item magically disappears. The list is not empty.

You have successfully written all the CRUD operations! Now, you’ll add a unit test to the program with Robolectric to see how to unit test the model portion of the program.

Unit Testing with Robolectric

You’re able to view the contents of the database using the command line or a third-party tool. Wouldn’t it also be nice to run a JUnit test on the model portion of the app? This can often be overlooked in the development and testing process.

To create some simple unit tests, you’ll use Robolectric. Simply put, Robolectric is a framework that allows you to write Android powered unit tests and run them on a desktop JVM while still using the Android API. Robolectric enables you to run your Android tests in your integration environment without any additional setup, which makes it a convenient choice.

Now, you’ll create a unit test to test INSERT of the database. It will determine if the names of the items are inserting into the database correctly.

To get started, open build.gradle(Module:app) and add the following code into the android section:

testOptions {
  unitTests {
    includeAndroidResources = true
  }
}

This will tell gradle to use the includeAndroidResources flag, and the name pretty much explains what that means! Next, add the following dependencies in the dependencies section:

testImplementation "androidx.test:core:1.4.0"
testImplementation "org.robolectric:robolectric:4.4"

Sync the gradle file after making these changes. Next, open ToDoDatabaseHandler.kt and add the following code inside the class, after deleteToDo():

// 1
fun clearDbAndRecreate() {
  clearDb()
  onCreate(writableDatabase)
}
  
fun clearDb() {
  writableDatabase.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
}

// 2
fun getAllText(): String {
  var result = ""
  val cols = arrayOf(KEY_TODO_NAME, KEY_TODO_IS_COMPLETED)
  val cursor = writableDatabase.query(TABLE_NAME, cols,
      null, null, null, null, KEY_TODO_ID)
  val indexColumnName = cursor.getColumnIndexOrThrow(KEY_TODO_NAME)
  while (cursor != null && cursor.moveToNext()) {
    result += cursor.getString(indexColumnName)
  }

  cursor.close()
  return result
}

Here’s what’s happening:

1. You erase and recreate the database once the testing is done. This allows the test to be run multiple times and ensure that residual test data isn’t left in the database.
2. getAllText() has one job, to create a String object that consists of the TODO item names in the database all concatenated together. In the test you’ll write soon, you’ll see if the database contains the values you expect to see after inserting a few items.

Now, you’re ready to create the test. Unit tests are contained in the test folder. Open ToDoDatabaseTest.kt in com.raywenderlich.android.sqlitetodo (test). Replace the class definition wtih the following code:

@RunWith(RobolectricTestRunner::class)
class ToDoDatabaseTest {
  // 1
  lateinit var dbHelper: ToDoDatabaseHandler

  // 2
  @Before
  fun setup() {
    dbHelper = ToDoDatabaseHandler(ApplicationProvider.getApplicationContext())
    dbHelper.clearDbAndRecreate()
  }

  @Test
  @Throws(Exception::class)
  fun testDbInsertion() {
    // 3
    // Given
    val item1 = ToDo(0, "Test my Program", false)
    val item2 = ToDo(0, "Test my Program Again", false)

    // 4
    // When
    dbHelper.createToDo(item1)
    dbHelper.createToDo(item2)

    // 5
    // Then
    val allText = dbHelper.getAllText()
    val expectedData = "${item1.toDoName}${item2.toDoName}"
    Assert.assertEquals(allText, expectedData)
  }
  
  // 6
  @After
  fun tearDown() {
    dbHelper.clearDb()
  }
}

With the above, you:

1. First, declare a database helper property.
2. Perform setup by initializing the database, then clear and recreate the database to start with a fresh copy every time. The function annotated with @Setup will be the first function to run before every test.
3. The test is written in cucumber style or Given/When/Then. You first create two TODO items with two different names. Given those items…
4. …when they are added to the database…
5. …then Assert that getAllText() matches the expected value.
6. Clear the database after the test, just to be sure.

To run the test, click the green arrow next to the class declaration and select Run ‘TestDatabase’:

Then you can see the result of the test at the bottom of the screen:

The positive result indicates that the values returned from the database handler matched the expected string based on the two items that were manually created and inserted.

This proves that the name fields were inserted into the database correctly.

Another neat feature of Robolectric is the .HTML report it can produce. To see this report, make sure you are in project view utilizing the dropdown at the top-left of the editor. Expand the Gradle pane with the tab on the right-hand of the screen, and click the elephant icon to execute a new task. Then, in the dialog enter gradle testDebugUnitTest and press Enter.

This will create the report. Then, in Project view browse** to app/build/reports/tests/debug/index.html, right-click the file and select Open in Browser.

Now you can see a fantastic report of your testing. Excellent work!

Key points

• When creating an SQLite database, you should define a database schema — a group of String constants, used to define your database structure.
• A database needs to be created and updated, according to the context of the app, and the version of the database.
• If an app doesn’t have a database with the same name as in your schema, it will create one, using the defined schema.
• If the app already has a database with the same name, it will run the update process, but only if the database version changed.
• You should avoid dropping the database if it changes, and try to migrate the structure between versions.
• Every database consists of the four standard operations - Create, Read, Update, and Delete, or CRUD for short.
• To help you avoid so much SQL code, and to simplify the operations, Android utilizes the SQLiteOpenHelper.
• To store data for operations, the SQLiteOpenHelper uses ContentValues.
• ContentValues are just a key-value pair structure, just like a Map, which is used to insert or update data in the database.
• You can inspect the database by copying it from the Device File Explorer, and opening it with a tool, like the SQLite Browser or DB Browser.
• It’s a good practice to write some Unit tests for your database, to be sure everything works as expected, without running the application.

Where to go from here?

If you would like to know more about SQLite and the syntax that makes up SQLite queries, a more in-depth guide for SQLite can be found in SQLite’s Language Guide, which you can access here: https://sqlite.org/lang.html.

This “Unit Testing with Robolectric” article is also an interesting guide about how using Robolectric with Android, which you can read here: https://github.com/codepath/android_guides/wiki/Unit-Testing-with-Robolectric.