Kotlin Android SQLite教程

本文概述

  • SQLiteOpenHelper类
  • SQLiteOpenHelper类的构造方法
  • SQLiteOpenHelper类的方法
  • SQLiteDatabase类
  • SQLiteDatabase类的方法
  • Kotlin Android SQLite数据库CRUD示例
SQLite是一个开放源代码关系数据库, 用于在Android设备上执行数据库操作, 例如从数据库中存储, 操作或检索持久性数据。
默认情况下, SQLite数据库嵌入在android中。因此, 无需执行任何数据库设置或管理任务。
SQLiteOpenHelper类提供使用SQLite数据库的功能。
SQLiteOpenHelper类 android.database.sqlite.SQLiteOpenHelper类用于数据库创建和版本管理。为了执行任何数据库操作, 你必须提供SQLiteOpenHelper类的onCreate()和onUpgrade()方法的实现。
SQLiteOpenHelper类的构造方法 SQLiteOpenHelper类有两个构造函数。
建设者 描述
SQLiteOpenHelper(上下文:上下文, 名称:字符串, 工厂:SQLiteDatabase.CursorFactory, 版本:Int) 创建一个SQLiteOpenHelper对象, 用于创建, 打开和管理数据库。
SQLiteOpenHelper(上下文:上下文, 名称:字符串, 工厂:SQLiteDatabase.CursorFactory, 版本: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):单位 执行SQL查询, 而不是选择查询。
insert(String table, String nullColumnHack, ContentValues values):长 在数据库上插入一条记录。该表指定表名, nullColumnHack不允许完全为空值。如果第二个参数为null, 则如果值为空, 则android将存储null值。第三个参数指定要存储的值。
update(String table, ContentValues values, String whereClause, String [] whereArgs):Int 更新一行。
查询(字符串表, 字符串[]列, 字符串选择, 字符串[] selectionArgs, 字符串groupBy, 具有字符串, 字符串orderBy):游标 返回结果集上的光标。
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.srcmini.com.kotlinsqlitecrud.MainActivity"> < TableLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"> < TableRow> < TextViewandroid:text="User Id"android:layout_width="wrap_content"android:layout_height="wrap_content"android:layout_column="1" /> < EditTextandroid: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> < TextViewandroid:text="User Name"android:layout_width="wrap_content"android:layout_height="wrap_content"android:layout_column="1" /> < EditTextandroid: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> < TextViewandroid:text="User Email"android:layout_width="wrap_content"android:layout_height="wrap_content"android:layout_column="1" /> < EditTextandroid: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> < LinearLayoutandroid:layout_width="wrap_content"android:layout_height="350sp"android:layout_marginTop="20sp"> < ListViewandroid:id="@+id/listView"android:layout_width="wrap_content"android:layout_height="350sp"/> < /LinearLayout> < LinearLayoutandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:layout_marginTop="40sp"android:orientation="horizontal"android:layout_gravity="center"> < Buttonandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:text="Save"android:onClick="saveRecord"/> < Buttonandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:text="View"android:onClick="viewRecord"/> < Buttonandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:text="Update"android:onClick="updateRecord"/> < Buttonandroid: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)创建调用SQLite数据库逻辑的DatabaseHandler类的实例。
package example.srcmini.com.kotlinsqlitecrudimport android.support.v7.app.AppCompatActivityimport android.os.Bundleimport android.view.Viewimport android.widget.EditTextimport android.widget.Toastimport kotlinx.android.synthetic.main.activity_main.*import android.content.DialogInterfaceimport android.support.v7.app.AlertDialogclass MainActivity : AppCompatActivity() {override fun onCreate(savedInstanceState: Bundle?) {super.onCreate(savedInstanceState)setContentView(R.layout.activity_main)}//method for saving records in databasefun 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 ListViewfun viewRecord(view: View){//creating the instance of DatabaseHandler classval databaseHandler: DatabaseHandler= DatabaseHandler(this)//calling the viewEmployee method of DatabaseHandler class to read the recordsval 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 = 0for(e in emp){empArrayId[index] = e.userId.toString()empArrayName[index] = e.userNameempArrayEmail[index] = e.userEmailindex++}//creating custom ArrayAdapterval myListAdapter = MyListAdapter(this, empArrayId, empArrayName, empArrayEmail)listView.adapter = myListAdapter}//method for updating records based on user idfun updateRecord(view: View){val dialogBuilder = AlertDialog.Builder(this)val inflater = this.layoutInflaterval dialogView = inflater.inflate(R.layout.update_dialog, null)dialogBuilder.setView(dialogView)val edtId = dialogView.findViewById(R.id.updateId) as EditTextval edtName = dialogView.findViewById(R.id.updateName) as EditTextval edtEmail = dialogView.findViewById(R.id.updateEmail) as EditTextdialogBuilder.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 classval databaseHandler: DatabaseHandler= DatabaseHandler(this)if(updateId.trim()!="" & & updateName.trim()!="" & & updateEmail.trim()!=""){//calling the updateEmployee method of DatabaseHandler class to update recordval 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 idfun deleteRecord(view: View){//creating AlertDialog for taking user idval dialogBuilder = AlertDialog.Builder(this)val inflater = this.layoutInflaterval dialogView = inflater.inflate(R.layout.delete_dialog, null)dialogBuilder.setView(dialogView)val dltId = dialogView.findViewById(R.id.deleteId) as EditTextdialogBuilder.setTitle("Delete Record")dialogBuilder.setMessage("Enter id below")dialogBuilder.setPositiveButton("Delete", DialogInterface.OnClickListener { _, _ -> val deleteId = dltId.text.toString()//creating the instance of DatabaseHandler classval databaseHandler: DatabaseHandler= DatabaseHandler(this)if(deleteId.trim()!=""){//calling the deleteEmployee method of DatabaseHandler class to delete recordval 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
【Kotlin Android SQLite教程】创建一个名为EmpModelClass.kt的数据模型类
package example.srcmini.com.kotlinsqlitecrud//creating a Data Model Classclass 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"> < TextViewandroid:id="@+id/textViewId"android:layout_width="match_parent"android:layout_height="wrap_content"android:text="Id"android:textAppearance="@style/Base.TextAppearance.AppCompat.Medium"/> < TextViewandroid:id="@+id/textViewName"android:layout_width="match_parent"android:layout_height="wrap_content"android:text="Name"android:textAppearance="@style/Base.TextAppearance.AppCompat.Medium"/> < TextViewandroid: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.srcmini.com.kotlinsqlitecrudimport android.app.Activityimport android.view.Viewimport android.view.ViewGroupimport android.widget.ArrayAdapterimport android.widget.TextViewclass 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.layoutInflaterval rowView = inflater.inflate(R.layout.custom_list, null, true)val idText = rowView.findViewById(R.id.textViewId) as TextViewval nameText = rowView.findViewById(R.id.textViewName) as TextViewval emailText = rowView.findViewById(R.id.textViewEmail) as TextViewidText.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"> < EditTextandroid:id="@+id/updateId"android:layout_width="match_parent"android:layout_height="wrap_content"android:ems="10"android:hint="enter id" /> < EditTextandroid:id="@+id/updateName"android:layout_width="match_parent"android:layout_height="wrap_content"android:ems="10"android:hint="enter name"/> < EditTextandroid: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"> < EditTextandroid: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.srcmini.com.kotlinsqlitecrudimport android.content.Contextimport android.database.sqlite.SQLiteDatabaseimport android.database.sqlite.SQLiteOpenHelperimport android.content.ContentValuesimport android.database.Cursorimport android.database.sqlite.SQLiteException//creating the database logic, extending the SQLiteOpenHelper base classclass DatabaseHandler(context: Context): SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {companion object {private val DATABASE_VERSION = 1private 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 fieldsval 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 datafun addEmployee(emp: EmpModelClass):Long{val db = this.writableDatabaseval contentValues = ContentValues()contentValues.put(KEY_ID, emp.userId)contentValues.put(KEY_NAME, emp.userName) // EmpModelClass NamecontentValues.put(KEY_EMAIL, emp.userEmail ) // EmpModelClass Phone// Inserting Rowval success = db.insert(TABLE_CONTACTS, null, contentValues)//2nd argument is String containing nullColumnHackdb.close() // Closing database connectionreturn success}//method to read datafun viewEmployee():List< EmpModelClass> {val empList:ArrayList< EmpModelClass> = ArrayList< EmpModelClass> ()val selectQuery = "SELECT* FROM $TABLE_CONTACTS"val db = this.readableDatabasevar cursor: Cursor? = nulltry{cursor = db.rawQuery(selectQuery, null)}catch (e: SQLiteException) {db.execSQL(selectQuery)return ArrayList()}var userId: Intvar userName: Stringvar userEmail: Stringif (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 datafun updateEmployee(emp: EmpModelClass):Int{val db = this.writableDatabaseval contentValues = ContentValues()contentValues.put(KEY_ID, emp.userId)contentValues.put(KEY_NAME, emp.userName) // EmpModelClass NamecontentValues.put(KEY_EMAIL, emp.userEmail ) // EmpModelClass Email// Updating Rowval success = db.update(TABLE_CONTACTS, contentValues, "id="+emp.userId, null)//2nd argument is String containing nullColumnHackdb.close() // Closing database connectionreturn success}//method to delete datafun deleteEmployee(emp: EmpModelClass):Int{val db = this.writableDatabaseval contentValues = ContentValues()contentValues.put(KEY_ID, emp.userId) // EmpModelClass UserId// Deleting Rowval success = db.delete(TABLE_CONTACTS, "id="+emp.userId, null)//2nd argument is String containing nullColumnHackdb.close() // Closing database connectionreturn success}}

输出:
Kotlin Android SQLite教程

文章图片
Kotlin Android SQLite教程

文章图片
Kotlin Android SQLite教程

文章图片
Kotlin Android SQLite教程

文章图片
Kotlin Android SQLite教程

文章图片
Kotlin Android SQLite教程

文章图片
Kotlin Android SQLite教程

文章图片
Kotlin Android SQLite教程

文章图片
Kotlin Android SQLite教程

文章图片

    推荐阅读