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
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: