follow Android 007 , get a full set of Android development learning materials for free

What is a SQLite database

SQLite database is a lightweight relational database suitable for mobile devices, with high speed and small space Unlike shared preferences, which is suitable for storing a small amount of simple data, SQLite database is suitable for storing a large amount of complex data
Android native provides API interface for operating database, but the operation is complicated, so a large number of third-party open source operation libraries have emerged, such as GreenDAO, Realm and so on However, Google recently launched Jetpack family bucket, with its own Room database operation library, which is convenient and fast to use. At the same time, it can be combined with other libraries in Jetpack to complete the development efficiently

Basic sample

1. Use Android native mode to operate the database

a. Renderings:

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-xwhrnhnt-1598402428745)( https://upload-images.jianshu.io/upload_images/6169789-0121b82ea983be9e.gif?imageMogr2/auto -orient/strip)]

b. Method of use

1). Add database auxiliary operation class

/**
 * Database auxiliary operation class
 */
class DbHelper
    (context: Context?, databaseName: String, version: Int) : SQLiteOpenHelper(
    context, databaseName, null, version
) {
    //sql statement for creating user table, table name: user_table, including primary key_ id, user name field: user_name. 
    private var createUserTableSql =
        ("CREATE TABLE user_table(_id INTEGER PRIMARY KEY, user_name text)")

    /**
     * Create database table
     */
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(createUserTableSql)
    }

    /**
     * Database upgrade
     */
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        val sql = "DROP TABLE IF EXISTS user_table"
        db.execSQL(sql)
        onCreate(db)
    }

    /**
     * insert data
     */
    fun insert(tableName: String, cv: ContentValues) = writableDatabase.insert(tableName, null, cv)

    /**
     * Delete specified data
     */
    fun delete(tableName: String, id: Int): Int {
        val where = "_id = ?"
        val whereValue = arrayOf(id.toString())
        return writableDatabase.delete(tableName, where, whereValue)
    }

    /**
     * Delete all data
     */
    fun deleteAll(tableName: String) = writableDatabase.delete(tableName, null, null)

    /**
     * Update data
     */
    fun update(
        tableName: String, values: ContentValues, whereClause: String?, whereArgs: Array<String>?
    ) = writableDatabase.update(tableName, values, whereClause, whereArgs)

    /**
     * Query data and return a cursor of the specified data set
     */
    fun query(tableName: String): Cursor {
        return readableDatabase.query(tableName, null, null, null, null, null, null)
    }
}

2). Call in activity

• Activity code

class MainActivity : AppCompatActivity() {
    private lateinit var dbHelper: DbHelper
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        dbHelper = DbHelper(this, "user_database", 1)

        addDataBtn.setOnClickListener { addUser() }
        updateDataBtn.setOnClickListener { updateUser() }
        delDataBtn.setOnClickListener { delUser() }
        queryDataBtn.setOnClickListener { queryUser() }
    }

    /**
     * New data
     */
    private fun addUser() {
        val values = ContentValues().apply {
            put("user_name", "Zhang San")
        }
        dbHelper.insert("user_table", values)
    }

    /**
     * Update data
     */
    private fun updateUser() {
        val values = ContentValues().apply {
            put("user_name", "Li Si")
        }
        dbHelper.update("user_table", values, null, null)
    }

    /**
     * Delete data
     */
    private fun delUser() {
        dbHelper.deleteAll("user_table")
    }

    /**
     * Query data
     */
    private fun queryUser() {
        val cursor = dbHelper.query("user_table")
        if (cursor.moveToFirst()) {
            do {
                val id = cursor.getInt(cursor.getColumnIndex("_id"))
                val name = cursor.getString(cursor.getColumnIndex("user_name"))
                Log.d("MainActivity", "User: ${id}_$name")
            } while (cursor.moveToNext())
        } else {
            Log.d("MainActivity", "The database is empty!")
        }
        cursor.close()
    }
}

• Layout file corresponding to Activity

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:gravity="center"
    android:orientation="vertical">
    <Button
        android:id="@+id/addDataBtn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="New data" />
    <Button
        android:id="@+id/updateDataBtn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Update data" />
    <Button
        android:id="@+id/delDataBtn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Delete data" />
    <Button
        android:id="@+id/queryDataBtn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Query data" />
</LinearLayout>

2. Use Room to operate the database

a. Renderings:

b. Method of use

1). In the app module build Add dependency in gradle file:

//Integrate room for SQLite database operation
implementation "androidx.room:room-runtime:2.2.5"
kapt "androidx.room:room-compiler:2.2.5"

//Integrating coroutines for asynchronous operations
implementation "org.jetbrains.kotlinx:kotlinx-coroutines-android:1.3.0"
implementation "org.jetbrains.kotlinx:kotlinx-coroutines-core:1.3.0"

2). Define data class:

/**
 * The user data class used for the test includes the primary key, id field and user name field of the automatically generated value.
 */
@Entity(tableName = "user_table")
class User(
    @PrimaryKey(autoGenerate = true) var id: Long = 0,
    @ColumnInfo(name = "user_name") var name: String = ""
)

3). Define database table operation class

/**
 * User database table operation class
 */
@Dao
public interface UserDao {
    //New data
    @Insert
    long[] add(User... users);

    @Update
    void update(User... users);

    //Delete all data
    @Query("DELETE FROM USER_TABLE")
    void deleteAll();

    //Query all data
    @Query("SELECT * FROM USER_TABLE")
    List<User> query();
}

4). Create database class

/**
 * Database class, which is used to create database and operate all database tables.
 * All database table classes are placed in entities, separated by commas;
 * version Indicates the current database version number. If the database structure is modified after the application is published, the version number needs to be changed (generally add 1)
 */
@Database(entities = [User::class], version = 1, exportSchema = false)
abstract class AppDatabase : RoomDatabase() {
    //All database table operation classes are placed here
    abstract val userDao: UserDao

    companion object {
        //Customize the database file name as long as there are no multiple database files with the same name in the application.
        private const val DATABASE_NAME = "user_database"

        @Volatile
        private var instance: AppDatabase? = null

        @Synchronized
        fun getInstance(context: Context) =
            instance
                ?: buildDatabase(context)
                    .also { instance = it }

        private fun buildDatabase(context: Context) =
            Room.databaseBuilder(context, AppDatabase::class.java,
                DATABASE_NAME
            ).build()
    }
}

5). Call in activity

• Activity code

class MainActivity : AppCompatActivity() {
    //Record the last newly added user id, which is used to identify the user to be updated
    private var lastAddedUserId: Long = 0
    private lateinit var userDao: UserDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        userDao = AppDatabase.getInstance(this).userDao
        addDataBtn.setOnClickListener { addUser() }
        updateDataBtn.setOnClickListener { updateUser() }
        delDataBtn.setOnClickListener { delUser() }
        queryDataBtn.setOnClickListener { queryUser() }
    }

    private fun addUser() {
        GlobalScope.async {
            var result = userDao.add(User(name = "Zhang San"))
            lastAddedUserId = result[0]
        }
    }

    private fun updateUser() {
        val user = User(id = lastAddedUserId, name = "Li Si")

        GlobalScope.async {
            userDao.update(user)
        }
    }

    private fun delUser() {
        GlobalScope.async {
            userDao.deleteAll()
        }
    }

    private fun queryUser() {
        GlobalScope.async {
            val dataList = userDao.query()
            if (dataList.isNotEmpty()) {
                dataList.forEach { Log.d("MainActivity", "User: ${it.id}_${it.name}") }
            } else {
                Log.d("MainActivity", "The database is empty!")
            }
        }
    }
}

• Layout file corresponding to Activity

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:gravity="center"
    android:orientation="vertical">
    <Button
        android:id="@+id/addDataBtn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="New data" />
    <Button
        android:id="@+id/updateDataBtn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Update data" />
    <Button
        android:id="@+id/delDataBtn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Delete data" />
    <Button
        android:id="@+id/queryDataBtn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Query data" />
</LinearLayout>

Complete source code

https://gitee.com/cxyzy1/DatabaseDemo

Android development tutorial series summary

Developing language learning

Kotlin language foundation

UI control learning series

UI control_ TextView
UI control_ EditText
UI control_ Button
UI control_ ImageView
UI control_ RadioButton
UI control_ CheckBox
UI control_ ProgressBar

Follow the headlines and get the latest articles at the first time: