Kotlin Android SQLite 教程

2025年3月17日 | 阅读 7 分钟

SQLite 是一个开源关系数据库，用于在 Android 设备上执行数据库操作，例如存储、操作或从数据库检索持久性数据。

默认情况下，SQLite 数据库嵌入在 Android 中。因此，无需执行任何数据库设置或管理任务。

SQLiteOpenHelper 类提供了使用 SQLite 数据库的功能。

SQLiteOpenHelper 类

android.database.sqlite.SQLiteOpenHelper 类用于数据库创建和版本管理。为了执行任何数据库操作，您必须提供 SQLiteOpenHelper 类的 onCreate() 和 onUpgrade() 方法的实现。

SQLiteOpenHelper 类的构造函数

SQLiteOpenHelper 类有两个构造函数。

构造函数	描述
SQLiteOpenHelper(context: Context, name: String, factory: SQLiteDatabase.CursorFactory, version: Int)	创建一个 SQLiteOpenHelper 对象，用于创建、打开和管理数据库。
SQLiteOpenHelper(context: Context, name: String, factory: SQLiteDatabase.CursorFactory, version: Int, errorHandler: DatabaseErrorHandler)	创建一个 SQLiteOpenHelper 对象，用于创建、打开和管理数据库。它指定错误处理程序。

SQLiteOpenHelper 类的方法

SQLiteOpenHelper 类中提供了几种方法。其中一些如下所述

方法	描述
public abstract void onCreate(SQLiteDatabase db)	仅在首次创建数据库时调用一次。
public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)	当数据库需要升级时调用。
public synchronized void close ()	关闭数据库对象。
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)	当数据库需要降级时调用。

SQLiteDatabase 类

它包含要在 SQLite 数据库上执行的方法，例如创建、更新、删除、选择等。

SQLiteDatabase 类的方法

SQLiteDatabase 类中有很多方法。其中一些如下

方法	描述
execSQL(String sql): Unit	执行 SQL 查询，而不是选择查询。
insert(String table, String nullColumnHack, ContentValues values): Long	在数据库中插入一条记录。 table 指定表名，nullColumnHack 不允许完全为空的值。如果第二个参数为空，则如果值为空，android 将存储空值。第三个参数指定要存储的值。
update(String table, ContentValues values, String whereClause, String[] whereArgs): Int	更新一行。
query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy): Cursor	返回结果集的游标。

Kotlin Android SQLite 数据库 CRUD 示例

在本示例中，我们将对 Android SQLite 数据库执行创建、读取、更新和删除操作。

activity_main.xml

在 activity_main.xml 文件中添加以下代码。在此文件中，我们添加了三个 EditText、一个 ListView、四个 Button，分别用于保存、查看、更新和删除操作。

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:layout_marginBottom="8dp"
    android:layout_marginEnd="8dp"
    android:layout_marginStart="8dp"
    android:layout_marginTop="8dp"
    android:orientation="vertical"
    tools:context="example.javatpoint.com.kotlinsqlitecrud.MainActivity">

<TableLayout
    android:layout_width="match_parent"
    android:layout_height="wrap_content">

    <TableRow>
        <TextView
            android:text="User Id"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_column="1" />

        <EditText

            android:id="@+id/u_id"
            android:layout_width="200dp"
            android:layout_height="wrap_content"
            android:layout_marginLeft="20sp"
            android:layout_marginStart="20sp"
            android:width="150px" />
    </TableRow>
    <TableRow>
         <TextView
            android:text="User Name"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_column="1" />

        <EditText
            android:id="@+id/u_name"
            android:width="200dp"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_column="2"
            android:layout_marginStart="20sp"
            android:layout_marginLeft="20sp"/>
    </TableRow>

    <TableRow>

        <TextView
            android:text="User Email"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_column="1" />

       <EditText
           android:id="@+id/u_email"
            android:width="200dp"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_column="2"
            android:layout_marginStart="20sp"
            android:layout_marginLeft="20sp" />
    </TableRow>

</TableLayout>
    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="350sp"
        android:layout_marginTop="20sp">
        <ListView
            android:id="@+id/listView"
            android:layout_width="wrap_content"
            android:layout_height="350sp"/>
    </LinearLayout>
<LinearLayout
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_marginTop="40sp"
    android:orientation="horizontal"
    android:layout_gravity="center">
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Save"
        android:onClick="saveRecord"/>
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="View"
        android:onClick="viewRecord"/>
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Update"
        android:onClick="updateRecord"/>
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Delete"
        android:onClick="deleteRecord"/>
</LinearLayout>
</LinearLayout>

MainActivity.kt

在 MainActivity.kt 类中添加以下代码。在这个类中，saveRecord() 函数保存记录。 viewRecord() 函数读取记录并将它们显示到 ListView 中，updateRecord() 函数根据 id 更新记录，deleteRecord() 函数删除记录。 val databaseHandler: DatabaseHandler= DatabaseHandler(this) 创建 DatabaseHandler 类的实例，调用 SQLite 数据库逻辑。

package example.javatpoint.com.kotlinsqlitecrud

import android.support.v7.app.AppCompatActivity
import android.os.Bundle
import android.view.View
import android.widget.EditText
import android.widget.Toast
import kotlinx.android.synthetic.main.activity_main.*
import android.content.DialogInterface
import android.support.v7.app.AlertDialog


class MainActivity : AppCompatActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
    }
    //method for saving records in database
    fun saveRecord(view: View){
        val id = u_id.text.toString()
        val name = u_name.text.toString()
        val email = u_email.text.toString()
        val databaseHandler: DatabaseHandler= DatabaseHandler(this)
        if(id.trim()!="" && name.trim()!="" && email.trim()!=""){
            val status = databaseHandler.addEmployee(EmpModelClass(Integer.parseInt(id),name, email))
            if(status > -1){
                Toast.makeText(applicationContext,"record save",Toast.LENGTH_LONG).show()
                u_id.text.clear()
                u_name.text.clear()
                u_email.text.clear()
            }
        }else{
            Toast.makeText(applicationContext,"id or name or email cannot be blank",Toast.LENGTH_LONG).show()
        }

    }
    //method for read records from database in ListView
    fun viewRecord(view: View){
        //creating the instance of DatabaseHandler class
        val databaseHandler: DatabaseHandler= DatabaseHandler(this)
        //calling the viewEmployee method of DatabaseHandler class to read the records
        val emp: List<EmpModelClass> = databaseHandler.viewEmployee()
        val empArrayId = Array<String>(emp.size){"0"}
        val empArrayName = Array<String>(emp.size){"null"}
        val empArrayEmail = Array<String>(emp.size){"null"}
        var index = 0
        for(e in emp){
            empArrayId[index] = e.userId.toString()
            empArrayName[index] = e.userName
            empArrayEmail[index] = e.userEmail
            index++
        }
        //creating custom ArrayAdapter
        val myListAdapter = MyListAdapter(this,empArrayId,empArrayName,empArrayEmail)
        listView.adapter = myListAdapter
    }
    //method for updating records based on user id
    fun updateRecord(view: View){
        val dialogBuilder = AlertDialog.Builder(this)
        val inflater = this.layoutInflater
        val dialogView = inflater.inflate(R.layout.update_dialog, null)
        dialogBuilder.setView(dialogView)

        val edtId = dialogView.findViewById(R.id.updateId) as EditText
        val edtName = dialogView.findViewById(R.id.updateName) as EditText
        val edtEmail = dialogView.findViewById(R.id.updateEmail) as EditText

        dialogBuilder.setTitle("Update Record")
        dialogBuilder.setMessage("Enter data below")
        dialogBuilder.setPositiveButton("Update", DialogInterface.OnClickListener { _, _ ->

            val updateId = edtId.text.toString()
            val updateName = edtName.text.toString()
            val updateEmail = edtEmail.text.toString()
            //creating the instance of DatabaseHandler class
            val databaseHandler: DatabaseHandler= DatabaseHandler(this)
            if(updateId.trim()!="" && updateName.trim()!="" && updateEmail.trim()!=""){
                //calling the updateEmployee method of DatabaseHandler class to update record
                val status = databaseHandler.updateEmployee(EmpModelClass(Integer.parseInt(updateId),updateName, updateEmail))
                if(status > -1){
                    Toast.makeText(applicationContext,"record update",Toast.LENGTH_LONG).show()
                }
            }else{
                Toast.makeText(applicationContext,"id or name or email cannot be blank",Toast.LENGTH_LONG).show()
            }

        })
        dialogBuilder.setNegativeButton("Cancel", DialogInterface.OnClickListener { dialog, which ->
            //pass
        })
        val b = dialogBuilder.create()
        b.show()
    }
    //method for deleting records based on id
    fun deleteRecord(view: View){
        //creating AlertDialog for taking user id
        val dialogBuilder = AlertDialog.Builder(this)
        val inflater = this.layoutInflater
        val dialogView = inflater.inflate(R.layout.delete_dialog, null)
        dialogBuilder.setView(dialogView)

        val dltId = dialogView.findViewById(R.id.deleteId) as EditText
        dialogBuilder.setTitle("Delete Record")
        dialogBuilder.setMessage("Enter id below")
        dialogBuilder.setPositiveButton("Delete", DialogInterface.OnClickListener { _, _ ->

            val deleteId = dltId.text.toString()
            //creating the instance of DatabaseHandler class
            val databaseHandler: DatabaseHandler= DatabaseHandler(this)
            if(deleteId.trim()!=""){
                //calling the deleteEmployee method of DatabaseHandler class to delete record
                val status = databaseHandler.deleteEmployee(EmpModelClass(Integer.parseInt(deleteId),"",""))
                if(status > -1){
                    Toast.makeText(applicationContext,"record deleted",Toast.LENGTH_LONG).show()
                }
            }else{
                Toast.makeText(applicationContext,"id or name or email cannot be blank",Toast.LENGTH_LONG).show()
            }

        })
        dialogBuilder.setNegativeButton("Cancel", DialogInterface.OnClickListener { _, _ ->
            //pass
        })
        val b = dialogBuilder.create()
        b.show()
    }
}

EmpModelClass.kt

创建一个名为 EmpModelClass.kt 的数据模型类

package example.javatpoint.com.kotlinsqlitecrud
//creating a Data Model Class
class EmpModelClass (var userId: Int, val userName:String , val userEmail: String)

custom_list.xml

创建一个自定义行布局，用于在 ListView 中显示列表项。

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical" android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:id="@+id/linearLayout">

    <TextView
        android:id="@+id/textViewId"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Id"
        android:textAppearance="@style/Base.TextAppearance.AppCompat.Medium"/>

    <TextView
        android:id="@+id/textViewName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Name"
        android:textAppearance="@style/Base.TextAppearance.AppCompat.Medium"/>

    <TextView
        android:id="@+id/textViewEmail"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Email"
        android:textAppearance="@style/Base.TextAppearance.AppCompat.Medium"/>
</LinearLayout>

MyListAdapter.kt

现在，创建一个名为 MyListAdapter.kt 的自定义适配器类，并扩展 ArrayAdapter 类，该类将数据模型填充到 ListView 中。

package example.javatpoint.com.kotlinsqlitecrud

import android.app.Activity
import android.view.View
import android.view.ViewGroup
import android.widget.ArrayAdapter
import android.widget.TextView

class MyListAdapter(private val context: Activity, private val id: Array<String>, private val name: Array<String>, private val email: Array<String>)
    : ArrayAdapter<String>(context, R.layout.custom_list, name) {

    override fun getView(position: Int, view: View?, parent: ViewGroup): View {
        val inflater = context.layoutInflater
        val rowView = inflater.inflate(R.layout.custom_list, null, true)

        val idText = rowView.findViewById(R.id.textViewId) as TextView
        val nameText = rowView.findViewById(R.id.textViewName) as TextView
        val emailText = rowView.findViewById(R.id.textViewEmail) as TextView

        idText.text = "Id: ${id[position]}"
        nameText.text = "Name: ${name[position]}"
        emailText.text = "Email: ${email[position]}"
        return rowView
    }
}

update_dialog.xml

创建一个布局以显示用于更新记录的 AlertDialog。

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:padding="10dp"
    android:layout_width="match_parent"
    android:layout_height="match_parent">

    <EditText
        android:id="@+id/updateId"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="enter id" />

    <EditText
        android:id="@+id/updateName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="enter name"/>

    <EditText
        android:id="@+id/updateEmail"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="enter email"/>
</LinearLayout>

delete_dialog.xml

创建一个布局以显示用于删除记录的 AlertDialog。

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:padding="10dp"
    android:layout_width="match_parent"
    android:layout_height="match_parent">

    <EditText
        android:id="@+id/deleteId"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="enter id" />
</LinearLayout>

DatabaseHandler.kt

创建扩展 SQLiteOpenHelper 类的 DatabaseHandler.kt 类，并覆盖其 onCreate(), onUpgrage() 函数。通过将 ContentValues 对象传递给 insert() 方法，将数据插入到数据库中。

package example.javatpoint.com.kotlinsqlitecrud

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.content.ContentValues
import android.database.Cursor
import android.database.sqlite.SQLiteException

//creating the database logic, extending the SQLiteOpenHelper base class
class DatabaseHandler(context: Context): SQLiteOpenHelper(context,DATABASE_NAME,null,DATABASE_VERSION) {
    companion object {
        private val DATABASE_VERSION = 1
        private val DATABASE_NAME = "EmployeeDatabase"
        private val TABLE_CONTACTS = "EmployeeTable"
        private val KEY_ID = "id"
        private val KEY_NAME = "name"
        private val KEY_EMAIL = "email"
    }
    override fun onCreate(db: SQLiteDatabase?) {
       // TODO("not implemented") //To change body of created functions use File | Settings | File Templates.
       //creating table with fields
        val CREATE_CONTACTS_TABLE = ("CREATE TABLE " + TABLE_CONTACTS + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
                + KEY_EMAIL + " TEXT" + ")")
        db?.execSQL(CREATE_CONTACTS_TABLE)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
      //  TODO("not implemented") //To change body of created functions use File | Settings | File Templates.
        db!!.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS)
        onCreate(db)
    }


    //method to insert data
    fun addEmployee(emp: EmpModelClass):Long{
        val db = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put(KEY_ID, emp.userId)
        contentValues.put(KEY_NAME, emp.userName) // EmpModelClass Name
        contentValues.put(KEY_EMAIL,emp.userEmail ) // EmpModelClass Phone
        // Inserting Row
        val success = db.insert(TABLE_CONTACTS, null, contentValues)
        //2nd argument is String containing nullColumnHack
        db.close() // Closing database connection
        return success
    }
    //method to read data
    fun viewEmployee():List<EmpModelClass>{
        val empList:ArrayList<EmpModelClass> = ArrayList<EmpModelClass>()
        val selectQuery = "SELECT  * FROM $TABLE_CONTACTS"
        val db = this.readableDatabase
        var cursor: Cursor? = null
        try{
            cursor = db.rawQuery(selectQuery, null)
        }catch (e: SQLiteException) {
            db.execSQL(selectQuery)
            return ArrayList()
        }
        var userId: Int
        var userName: String
        var userEmail: String
        if (cursor.moveToFirst()) {
            do {
                userId = cursor.getInt(cursor.getColumnIndex("id"))
                userName = cursor.getString(cursor.getColumnIndex("name"))
                userEmail = cursor.getString(cursor.getColumnIndex("email"))
                val emp= EmpModelClass(userId = userId, userName = userName, userEmail = userEmail)
                empList.add(emp)
            } while (cursor.moveToNext())
        }
        return empList
    }
    //method to update data
    fun updateEmployee(emp: EmpModelClass):Int{
        val db = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put(KEY_ID, emp.userId)
        contentValues.put(KEY_NAME, emp.userName) // EmpModelClass Name
        contentValues.put(KEY_EMAIL,emp.userEmail ) // EmpModelClass Email

        // Updating Row
        val success = db.update(TABLE_CONTACTS, contentValues,"id="+emp.userId,null)
        //2nd argument is String containing nullColumnHack
        db.close() // Closing database connection
        return success
    }
    //method to delete data
    fun deleteEmployee(emp: EmpModelClass):Int{
        val db = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put(KEY_ID, emp.userId) // EmpModelClass UserId
        // Deleting Row
       val success = db.delete(TABLE_CONTACTS,"id="+emp.userId,null)
        //2nd argument is String containing nullColumnHack
        db.close() // Closing database connection
        return success
    }
}

输出

下一主题#

Kotlin Android SQLite 教程

SQLiteOpenHelper 类

SQLiteOpenHelper 类的构造函数

SQLiteOpenHelper 类的方法

SQLiteDatabase 类

SQLiteDatabase 类的方法

Kotlin Android SQLite 数据库 CRUD 示例

activity_main.xml

MainActivity.kt

EmpModelClass.kt

custom_list.xml

MyListAdapter.kt

update_dialog.xml

delete_dialog.xml

DatabaseHandler.kt

联系信息

关注我们

教程

面试题

在线编译器

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

Kotlin 教程

控制流

函数

Array

String

异常处理

Null 安全

集合

注解

反射

Kotlin OOPs

范围

Java 互操作性

Regex

Android 启动

Kotlin Android

Kotlin Android SQLite 教程

SQLiteOpenHelper 类

SQLiteOpenHelper 类的构造函数

SQLiteOpenHelper 类的方法

SQLiteDatabase 类

SQLiteDatabase 类的方法

Kotlin Android SQLite 数据库 CRUD 示例

activity_main.xml

MainActivity.kt

EmpModelClass.kt

custom_list.xml

MyListAdapter.kt

update_dialog.xml

delete_dialog.xml

DatabaseHandler.kt

相关帖子

Android Options Menu

Google Map 当前位置

Android 媒体播放器

内部存储

Android WebView

Android 隐式 Intent

TabLayout with ViewPager

SharedPreferences

Google AdMob 横幅广告

TabLayout with FrameLayout

订阅 Tpoint Tech

联系信息

关注我们

教程

面试题

在线编译器