Industrial Training

Enroll now!

Theoretical Paper

Practical Paper

Industrial Training

Previous
Next



SQLite Tutorial


Kotlin Android SQLite Tutorial


SQLite is an open-source relational database that is used to perform database operations on Android devices such as storing, manipulating or retrieving persistent data from the database.
By default SQLite database is embedded in android. So, there is no need to perform any database setup or administration task.
The SQLiteOpenHelper class provides the functionality to use the SQLite database.


SQLiteOpenHelper class


The android.database.sqlite.SQLiteOpenHelper class is used for database creation and version management. For performing any database operation, you have to provide the implementation of onCreate() and onUpgrade() methods of SQLiteOpenHelper class.


Constructors of SQLiteOpenHelper class


There are two constructors of SQLiteOpenHelper class.


Constructor Description
SQLiteOpenHelper(context: Context, name: String, factory: SQLiteDatabase.CursorFactory, version: Int) Creates an object of SQLiteOpenHelper for creating, opening and managing the database.
SQLiteOpenHelper(context: Context, name: String, factory: SQLiteDatabase.CursorFactory, version: Int, errorHandler: DatabaseErrorHandler) Creates an object of SQLiteOpenHelper for creating, opening and managing the database. It specifies the error handler.

Methods of SQLiteOpenHelper class


There are several methods available in the SQLiteOpenHelper class. Some of them are mentioned below:


Method Description
public abstract void onCreate(SQLiteDatabase db) Called only once when the database is created for the first time.
public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) Called when the database needs to upgrade.
public synchronized void close () Closes the database object.
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) called when the database needs to downgrade.

SQLiteDatabase class


It contains methods to be performed on the SQLite database such as create, update, delete, select etc.


Methods of SQLiteDatabase class


There are many methods in the SQLiteDatabase class. Some of them are as follows:


Methods of SQLiteOpenHelper class


There are several methods available in the SQLiteOpenHelper class. Some of them are mentioned below:


Method Description
execSQL(String sql): Unit Executes the SQL query, not a select query.
einsert(String table, String nullColumnHack, ContentValues values): Long Inserts a record on the database. The table specifies the table name, nullColumnHack doesn't allow completely null values. If the second argument is null, android will store null values if values are empty. The third argument specifies the values to be stored.
update(String table, ContentValues values, String whereClause, String[] whereArgs): Int Updates a row.
query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy): Cursor Returns a cursor over the resultset.

Kotlin Android SQLite Database CRUD Example


In this example, we will perform create, read, update and delete operation on Android SQLite database.


activity_main.xmle


In the activity_main.xml file add the following code. In this file, we added three EditText, one ListView, four Button for saving, view, update and delete operation.


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


Add the following code in the MainActivity.kt class. In this class, the saveRecord() function saves the records. The viewRecord() function reads the record and displays them into ListView, the updateRecord() function updates the record on the basis on id, and deleteRecord() function deletes the record. The val databaseHandler: DatabaseHandler= DatabaseHandler(this) creates the instance of DatabaseHandler class calls the SQLite database logic.


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()  
    }  
}  
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


Creating a data model class named as EmpModelClass.kt


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

custom_list.xml


Create a custom row layout for displaying the list items in the 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>  
< ?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


Now, create a custom adapter class named as MyListAdapter.kt and extends ArrayAdapter class which populates the data model into the 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  
    }  
}  
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


Create a layout for display AlertDialog for update record.


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


Create a layout for display AlertDialog for delete record.


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


Create the DatabaseHandler.kt class that extends SQLiteOpenHelper class and override its onCreate(), onUpgrage() functions. Insert data into the database by passing a ContentValues object to the insert() method.


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

Output:







Previous
Next
Hi I am Pluto.