Introduction As with networking, working with a database is an equally frequent need when developing android applications. In part, the two areas are intertwined because the database usually stores data that is retrieved from the network and now needs to be cached to reduce the number of requests to the network. When working with a simple table with a few dozen rows, you won't have any problems. However, in my practice, there was a case where you had to load dozens of databases with hundreds of tables, some of which contained about a million rows. I've also had cases where tables were linked via nested with nesting levels as high as 5-10. To solve all these problems, I had to use all the libraries and tools listed below to get the desired result or to fix bugs. Because one tool sometimes didn't allow me to do what the other one allowed me to do. SELECT Observations and Assumptions First, I want to draw your attention to some comments and assumptions: This article will look at specific examples whose code can be inserted into , run and see what happens; Android Studio This article will focus on working with relational databases. We will not consider non-relational ( ) databases; NoSQL We will consider the database ; sqlite We will go from simple things, gradually increasing in complexity; The code will not be broken into layers according to and patterns. This is done to simplify the examples and the article as a whole; MVP MVVM The article is designed for developers with medium level and above, so we won't dwell on very simple things, for the same reason we won't go deep into each individual example or library. Only an overview of the tools to broaden your horizons is provided. Selecting an Example To review the libraries and tools below, let's choose an example. Let's say we have this table with two columns: id and color. Let's try to write this table to the database in different ways and get this result: Tools and Libraries for Working With the SQLite Database SQLiteOpenHelper Let's take a look at the first native tool for working with a database on . Our task is to write data to the database on the device. SQLiteOpenHelper Android You don't need to pull in any libraries. Create an , insert the following code and run it. Activity Don't forget to set it to the background thread as shown : here package com.leonidivankin.draftsandroid.articles.db

import android.content.ContentValues
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.os.Bundle
import android.util.Log
import androidx.appcompat.app.AppCompatActivity

class SQLiteOpenHelperActivity : AppCompatActivity() {

    private val db by lazy {
        object : SQLiteOpenHelper(this, "colors_db", null, 1) {
            override fun onCreate(db: SQLiteDatabase) {
                db.execSQL("CREATE TABLE $TABLE_NAME (_id INTEGER PRIMARY KEY AUTOINCREMENT, color TEXT)")
            }
            override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {}
        }.readableDatabase
    }

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        Thread{
            insert( "green")
            insert( "red")
            insert( "blue")
            get()
        }.start()
    }

    private fun insert(color: String) {
        val contentValues = ContentValues()
        contentValues.put(COLUMN_NAME, color)
        db.insert(TABLE_NAME, null, contentValues)
    }

    private fun get() {
        val cursor = db.query(TABLE_NAME, arrayOf("_id", COLUMN_NAME), null, null, null, null, null)
        var i = 0
        while (!cursor.isLast) {
            cursor.moveToPosition(i)
            val id = cursor.getInt(0)
            val color = cursor.getString(1)
            Log.d("DbExample", "$id $color")
            i++
        }
    }

    override fun onDestroy() {
        super.onDestroy()
        db.close()
    }

    companion object {
        const val TABLE_NAME = "colors"
        const val COLUMN_NAME = "color"
    }
} We created a database. colors_db The output log shows that we achieved our goal: D: 1 green
D: 2 red
D: 3 blue As you can see, creating the table, filling it with data, and outputting it took no more than 50 lines of code. Pros and cons +no additional libraries are needed -it is hard to work with complex queries and selections -it is necessary to map to , and back each time ContentValues Cursor, Useful Links: https://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper https://www.sqlite.org/index.html It doesn't handle errors, methods to change the database version, etc. in order to reduce the code. We will need an example with this table next. Room Now make the same table with the help of from Google, which is part of . In fact, is an , i.e. a shell with a more user-friendly interface. Room Android Jetpack Room ORM Adding a dependency on libraries: implementation 'androidx.room:room-runtime:2.1.0'
kapt 'androidx.room:room-compiler:2.1.0' Add a dependency to the module level file: kapt build.gradle apply plugin: 'kotlin-kapt' Create an , insert the code and run it as shown : Activity here package com.leonidivankin.draftsandroid.articles.db

import android.os.Bundle
import android.util.Log
import androidx.appcompat.app.AppCompatActivity
import androidx.room.*
import androidx.room.OnConflictStrategy.REPLACE

class RoomActivity : AppCompatActivity() {

    private val colorDao by lazy {
        Room
            .databaseBuilder(applicationContext, AppDatabase::class.java, "colors_room")
            .build()
            .colorDao()
    }

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        Thread {
            val list = listOf(Color(1, "green"), Color(2, "red"), Color(3, "blue"))
            colorDao.insert(list)
            Log.d("DbExample", "${colorDao.get()}")
        }.start()
    }
}

@Database(entities = [Color::class], version = 1, exportSchema = false)
abstract class AppDatabase : RoomDatabase() {
    abstract fun colorDao(): ColorDao
}

@Entity(tableName = "colors")
data class Color(@PrimaryKey val id: Int, val color: String)

@Dao
interface ColorDao {
    @Query("SELECT * FROM colors") fun get(): List<Color>
    @Insert(onConflict = REPLACE) fun insert(color: List<Color>)
} We created the base. colors_room Here we also reached our goal rather quickly, as confirmed by the log: D: [Color(id=1, color=green), Color(id=2, color=red), Color(id=3, color=blue)] Pros and cons +simplifies the creation of complex queries +can give objects to , , RxJava LiveData Flow +works with objects, not strings +can give a list of objects at once +it has automigrations -like any library it makes the project heavier Useful Links: https://developer.android.com/jetpack/androidx/releases/room We achieved the initially set task in 30-40 lines. As you know, is currently the most popular library for working with databases on Android due to its simplicity and speed. Room Other ORMs In fact, the library is an . Its main task is to simplify the work with the database. On , there were previously popular other ORMs for working with databases, such as , , , and others. Room ORM SQLite Android ORMLite SugarORM Freezer There are more than a dozen of them. They lost their popularity after the release of , especially considering the fact that Room is faster than other , as evidenced by benchmarks like . Room ORMs this one This concludes our discussion of database libraries. Next we'll take a look at the inspection tools. Database Inspection Device File Explorer + SQLite Studio The entire database is stored on Android in , in one or more files. There is no need to raise additional servers or connections to work with it. Therefore, the easiest way to see what is in the resulting file is to download it to your computer (it's free) and open it in editor. That's what we'll do. SQLite Internal Storage SQLite Studio Go to the following path: and find the previously created databases and : Device File Explorer > Data > Data > your_package_name > databases colors_db colors_room Select these files and save them to your computer: Next, download and install it. SQLite Studio https://sqlitestudio.pl/ Open and find our file, as shown in the figure below: SQLite Studio As a result, the databases should appear in the window on the left. Double-click to open the desired database and table, and it should appear in the window on the right: In addition to viewing the database, you can also change the data and upload it back to your mobile device. That's what we'll do. Let's change the color to and save it as an example: green purple Then go back to and upload the new file by clicking the button: Device File Explorer Upload Sometimes you need to restart the application in order for the data to be updated. Go to , comment out some of the code to write and restart the application: RoomActivity //val list = listOf(Color(1, "green"), Color(2, "red"), Color(3, "blue"))
//colorDao.insert(list) See : LogCat D: [Color(id=1, color=purple), Color(id=2, color=red), Color(id=3, color=blue)] We see that the color value of the first object has changed to . This gives you a great toolkit for managing the base during development, debugging and error correction. Color purple Pros and cons +you can record data -you have to download and upload the database every time Useful Links: https://www.sqlite.org/index.html We have analyzed one of the easiest and most reliable ways to inspect the database. Let's look at a few more. Android Studio Database Inspector The method shown in the example above has long been the main way to view the database using the GUI. However, introduced the . Android Studio 4.1 Database Inspector To use it, you need to: go to the tab; App Inspection select the process of your application (in my case it is ); com.leonidivankin.draftandroid find your database (in my case it is ); colors_room find the necessary table (in my case it is ). colors As you can see, the data is the same. In this tool it is also possible to edit the value. Click twice on the line and enter the desired value, for example, I will enter : rose Restart the application and look at : LogCat D: [Color(id=1, color=rose), Color(id=2, color=red), Color(id=3, color=blue)] As you can see, the value in the logs changed to . rose I want to note that in the tool worked rather crookedly: it was not always possible to connect to the necessary process, sometimes the connection to the database was unexpectedly lost, but the quality and functions are constantly improving. Android Studio 4.1 In works much better, although some of the problems remain. Android Studio Chipmunk 2021.2.1 Database Inspector Pros and cons +no need to copy the base and fill it back in -raw tool We saw how to view the database without leaving . Next, let's see how to do it via . SQLite Android Studio ADB Adb+sqlite Sometimes the GUI functions are not enough, for example, if you want to create a new empty database. Or if you want to write a script with a sequence of actions for testing. tools will help. Adb+sqlite To use it, run the application on an emulator (!) and enter the following lines: adb shell
su
cd data/data/com.leonidivankin.draftsandroid/databases
sqlite3 colors_room
SELECT * FROM colors; Where, - the name of my package, , - the name of the base and table from the examples above. com.leonidivankin.draftsandroid colors_room colors We see in the console that the contents of the table are displayed, as in the previous examples: A few observations: It is necessary to run the application on an emulator, otherwise, there will be an error or , because you need to root the phone; Permission denied su: not found It is obligatory to put a semicolon ";" after otherwise, it may cause an error or the query will not be executed. SELECT * FROM colors; You can also view a list of all databases. To do this, you must enter: ls -R /data/data/com.leonidivankin.draftsandroid/databases As a result, we have all the files contained in the folder: databases Pros and cons +A very wide range of commands are available compared to graphical commands +it is possible to write a script for automation or self-testing -You have to enter commands via the console -Need a rooted phone or emulator Useful Links: https://developer.android.com/studio/command-line/adb Despite the fact that tools do not have a graphical interface, it deserves attention, because it is more flexible and provides more and more opportunities for automation of development or testing, for example, to correctly handle migrations. Adb+sqlite Conclusion Working with SQLite databases that contain dozens of tables with thousands of rows is a non-trivial task. Very often, finding an error requires all the possible arsenal that is only available on . The tools presented above will help you solve these problems. Android has been using the database since its inception. And even though some other tools change every year, has remained one of the main repositories for the second decade. And, by all accounts, it will stay that way. Android SQLite SQLite That's why the advice we offer here will never lose its relevance. If you use any tools not mentioned in this article, please do share them in the comments. Also, read about networking tools for android development . here

Flow

Google

Super

2022 - Android Advocate of the Year

Nominated for 2022 - Android Advocate of the Year

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

Best Android Tools to Work With Databases

Too Long; Didn't Read

Companies Mentioned

Coin Mentioned

Leonid Ivankin

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

Best Android Tools to Work With Databases

Too Long; Didn't Read

Companies Mentioned

Coin Mentioned

Leonid Ivankin

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES