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 SELECT 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.
First, I want to draw your attention to some comments and assumptions:
Only an overview of the tools to broaden your horizons is provided.
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:
Let's take a look at the first native tool SQLiteOpenHelper for working with a database on Android. Our task is to write data to the database on the device.
You don't need to pull in any libraries. Create an Activity, insert the following code and run it.
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 colors_db database.
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
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.
Now make the same table with the help of Room from Google, which is part of Android Jetpack. In fact, Room is an ORM, i.e. a shell with a more user-friendly interface.
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 kapt build.gradle module level file:
apply plugin: 'kotlin-kapt'
Create an Activity, insert the code and run it as shown 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 colors_room base.
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, Room is currently the most popular library for working with databases on Android due to its simplicity and speed.
In fact, the Room library is an ORM. Its main task is to simplify the work with the SQLite database. On Android, there were previously popular other ORMs for working with databases, such as ORMLite, SugarORM, Freezer, and others.
There are more than a dozen of them. They lost their popularity after the release of Room, especially considering the fact that Room is faster than other ORMs, as evidenced by benchmarks like this one.
This concludes our discussion of database libraries. Next we'll take a look at the inspection tools.
The entire SQLite database is stored on Android in Internal Storage, 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 SQLite Studio editor. That's what we'll do.
Go to the following path: Device File Explorer > Data > Data > your_package_name > databases and find the previously created databases colors_db and colors_room:
Select these files and save them to your computer:
Next, download SQLite Studio https://sqlitestudio.pl/ and install it.
Open SQLite Studio and find our file, as shown in the figure below:
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 green to purple and save it as an example:
Then go back to Device File Explorer and upload the new file by clicking the Upload button:
Sometimes you need to restart the application in order for the data to be updated. Go to RoomActivity, comment out some of the code to write and restart the application:
//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 Color object has changed to purple. This gives you a great toolkit for managing the base during development, debugging and error correction.
Pros and cons
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.
The method shown in the example above has long been the main way to view the database using the GUI. However, Android Studio 4.1 introduced the Database Inspector.
To use it, you need to:
com.leonidivankin.draftandroid
);
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 Android Studio 4.1 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.
In Android Studio Chipmunk 2021.2.1 Database Inspector works much better, although some of the problems remain.
Pros and cons
We saw how to view the SQLite database without leaving Android Studio. Next, let's see how to do it via ADB.
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. Adb+sqlite tools will help.
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, com.leonidivankin.draftsandroid
- the name of my package, colors_room, colors - the name of the base and table from the examples above.
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 Permission denied or su: not found
, because you need to root the phone;
It is obligatory to put a semicolon ";" after SELECT * FROM colors;
otherwise, it may cause an error or the query will not be executed.
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 databases folder:
Pros and cons
Useful Links: https://developer.android.com/studio/command-line/adb
Despite the fact that Adb+sqlite 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.
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 Android. The tools presented above will help you solve these problems.
Android has been using the SQLite database since its inception. And even though some other tools change every year, SQLite has remained one of the main repositories for the second decade. And, by all accounts, it will stay that way.
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.