Chapters
Chapters

Chapters

Hide chapters

Saving Data on Android

First Edition · Android 10 · Kotlin 1.3 · AS 3.5

Before You Begin

Section 0: 3 chapters
Show chapters Hide chapters

Saving Data Using Android SDK

Section 1: 4 chapters
Show chapters Hide chapters

Using Room

Section 2: 6 chapters
Show chapters Hide chapters

Using Firebase

Section 3: 11 chapters
Show chapters Hide chapters

3. SQLite Database
Written by Jenn Bailey

Heads up... You're reading this book for free, with parts of this chapter shown beyond this point as scrambled text.

You can unlock the rest of this book, and our entire catalogue of books and videos, with a Kodeco Personal Plan.

Unlock now

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 is 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 is 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 is 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 will 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.

The database schema
The database schema

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:

Getting started

Open the starter app for this chapter and ignore the errors you get initially. Notice there are three sections in the com.raywenderlich.sqlitetodo package: Model, View and Controller.

Creating the database constants using a contract class

In the Model, 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" 
    }
  }
}

Using the SQLiteOpenHelper class

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 the 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.

: SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION)

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

Creating the database

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

// 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)

// 1
db?.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
// 2
onCreate(db)

Using ContentValues

Before you add items to the database, it is 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.

Adding a TODO

Add the following code in the createToDo function, to insert a TODO 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()

Running the program

Run the program on an emulator and use the Floating Action Button with the plus sign icon to add a TODO item:

Adding an item
Uhtiqf um ajav

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.” It can be found on the bottom right-hand corner of Android Studio as a collapsed, vertical pane.

The database folder for the app and context menu of Device File Explorer
Tto kesuveye wavdes neb gge all azh katyojg sodu oh Cegaro Jaka Azgzopos

The database folder for the app and context menu of Device File Explorer
Fqo biwewaqi leznoj wok ste ect ikv gucsulq vici iv Tutelo Caha Oqnwonex

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 will use a tool called the Cursor to iterate through the records and add them to a list.

Understanding the cursor

In Android, a Cursor is assigned to the result set of a query being run against the database. The Cursor is then used to iterate over the result set in a type-safe manner. It iterates through the result set row by row, field by field.

// 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
The added to-do item
Bda uqxid no-wo udad

Updating a TODO

To add the capability to update a record, replace the line of code return 0 in the updateToDo function 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))
Update a record
Oxcada a hodeng

Updated record
Azkeful yazotc

Deleting a TODO

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

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

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.

testOptions {
  unitTests {
    includeAndroidResources = true
  }
}

testImplementation "org.robolectric:robolectric:3.6.1"

// 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
}

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

  // 2
  @Before
  fun setup() {
    dbHelper = ToDoDatabaseHandler(RuntimeEnvironment.application)
    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()
  }
}
Run the test
Veg twa tikk

The test passed
Nya jepk peysuc

Generate test summary
Fekukite locl juvyivz

Test report
Cekg yoxagd

Key points

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.

2. Using SharedPreferences
4. ContentProvider
Have a technical question? Want to report a bug? You can ask questions and report bugs to the book authors in our official book forum here.
© 2024 Kodeco Inc.

You're reading for free, with parts of this chapter shown as scrambled text. Unlock this book, and our entire catalogue of books and videos, with a Kodeco Personal Plan.

Unlock now