Kotlin SQLite Tutorial

SQLite is a popular database engine used in many mobile applications. In this tutorial, we'll discuss how to use SQLite with Kotlin to create a simple database and perform basic CRUD operations.

Syntax

To use SQLite in an Android application with Kotlin, follow these steps:

1. Add the SQLite dependency in the Gradle file.
2. Create a DBHelper class that extends SQLiteOpenHelper.
3. Define the database schema and table structure.
4. Implement functions for CRUD operations in the DBHelper class.
5. Use the DBHelper class to interact with the database in your activity or fragment.

Example

Let's say you want to create a simple to-do list application with SQLite and Kotlin. Follow the steps below to create the database and perform CRUD operations:

1. Add the following dependency in the Gradle file:

implementation 'androidx.sqlite:sqlite-ktx:2.1.0'

2. Create a class called DBHelper that extends SQLiteOpenHelper:

class DBHelper(context: Context): SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    override fun onCreate(db: SQLiteDatabase?) {
        val createTable = "CREATE TABLE $TABLE_NAME ($COL_ID INTEGER PRIMARY KEY AUTOINCREMENT, $COL_DESC TEXT)"
        db?.execSQL(createTable)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        db?.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
        onCreate(db)
    }

    fun addTask(task: Task): Long {
        val db = this.writableDatabase
        val cv = ContentValues()
        cv.put(COL_DESC, task.description)
        val result = db.insert(TABLE_NAME, null, cv)
        db.close()
        return result
    }

    fun getAllTasks(): ArrayList<Task> {
        val list = ArrayList<Task>()
        val db = this.readableDatabase
        val query = "SELECT * FROM $TABLE_NAME"
        val result = db.rawQuery(query, null)
        if (result.moveToFirst()) {
            do {
                val task = Task()
                task.id = result.getLong(result.getColumnIndex(COL_ID))
                task.description = result.getString(result.getColumnIndex(COL_DESC))
                list.add(task)
            } while (result.moveToNext())
        }
        result.close()
        db.close()
        return list
    }

    fun updateTask(task: Task): Int {
        val db = this.writableDatabase
        val cv = ContentValues()
        cv.put(COL_DESC, task.description)
        val result = db.update(TABLE_NAME, cv, "$COL_ID=?", arrayOf(task.id.toString()))
        db.close()
        return result
    }

    fun deleteTask(task: Task): Int {
        val db = this.writableDatabase
        val result = db.delete(TABLE_NAME, "$COL_ID=?", arrayOf(task.id.toString()))
        db.close()
        return result
    }
}

3. Define the database schema and table structure:

const val DATABASE_NAME = "TaskDB"
const val DATABASE_VERSION = 1
const val TABLE_NAME = "Tasks"
const val COL_ID = "id"
const val COL_DESC = "description"

4. Implement functions for CRUD operations in the DBHelper class.

5. Use the DBHelper class to insert, read, update, and delete tasks in your activity or fragment:

class MainActivity : AppCompatActivity() {

    private lateinit var db: DBHelper
    private lateinit var tasks: ArrayList<Task>
    private lateinit var adapter: TaskListAdapter

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = DBHelper(this)

        tasks = db.getAllTasks()
        adapter = TaskListAdapter(this, tasks)
        listView.adapter = adapter

        fab.setOnClickListener { view ->
            val dialog = AddTaskDialog()
            dialog.show(supportFragmentManager, "AddTaskDialog")
        }
    }

    fun addTask(description: String) {
        val task = Task(0, description)
        db.addTask(task)
        tasks.clear()
        tasks.addAll(db.getAllTasks())
        adapter.notifyDataSetChanged()
    }

    fun deleteTask(task: Task) {
        db.deleteTask(task)
        tasks.clear()
        tasks.addAll(db.getAllTasks())
        adapter.notifyDataSetChanged()
    }
}

Output

The output of the example application is a simple list of tasks that can be added, edited, and deleted.

Explanation

The example code above demonstrates how to use SQLite with Kotlin to create a database and perform basic CRUD operations. In this example, we defined a database helper class called DBHelper, which extends SQLiteOpenHelper and provides methods for inserting, reading, updating, and deleting tasks.

To use the database helper class, we implemented it in our activity, and used it to populate a ListView with tasks. When the user clicks on the Add button, we use the AlertDialog to prompt the user for a task description and then add the task using the addTask() method. When the user clicks on a task, we use the AlertDialog to permit the user to either update or delete the task.

Use

Using SQLite with Kotlin in Android applications can be helpful in managing data, especially when the application requires offline support. It allows for storing data locally without having to rely on web services all the time.

Important Points

• Remember to close the database after you're done with any operation.
• Ensure that you follow good database design principles when defining your schema and table structure.
• Protect against SQL injection attacks by using prepared statements with placeholders.
• Avoid using raw SQL strings when possible, instead using parameterized queries.

Summary

In this tutorial, we discussed how to use SQLite with Kotlin to create a simple database and perform basic CRUD operations. We covered the syntax, example, output, explanation, use, important points, and summary of using SQLite with Kotlin. With this knowledge, you can now work with SQLite in your Kotlin-based Android application.