本文概述
- SQLiteOpenHelper类
- SQLiteOpenHelper类的构造方法
- SQLiteOpenHelper类的方法
- SQLiteDatabase类
- SQLiteDatabase类的方法
- Kotlin Android SQLite数据库CRUD示例
默认情况下, 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对象, 用于创建, 打开和管理数据库。它指定错误处理程序。 |
方法 | 描述 |
---|---|
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类的方法 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):游标 | 返回结果集上的光标。 |
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 TabLayout与FrameLayout
- 带有ViewPager的Kotlin Android TabLayout
- Kotlin Android SeekBar
- Kotlin android sharedpreferences用法
- Kotlin Android读写内部存储
- Kotlin Android读写外部存储
- Kotlin Android ListView
- Kotlin Android弹出菜单
- Kotlin Android选项菜单