Utilizing SQLite Database in Android
SQLite is a popular and lightweight database management system native to Android. In this article, we will explore an alternative method to work with SQLite databases in Android using Kotlin without relying on Room. Instead, we will use the SQLiteDatabase class directly to create and manage the database. We will create a sample application to demonstrate CRUD (Create, Read, Update, Delete) operations using this approach.
Implementation steps:
Step 1: Creating the Database Helper Class
We need to create a helper class that will be responsible for managing database creation, upgrades, and versioning.
import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
data class YourDataModel(
val id: Long,
val name: String,
val age: Int
)
class DatabaseHelper(context: Context) :
SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
companion object {
private const val DATABASE_NAME = "my_database.db"
private const val DATABASE_VERSION = 1
// Define table and column names
const val TABLE_NAME = "my_table"
const val COLUMN_ID = "id"
const val COLUMN_NAME = "name"
const val COLUMN_AGE = "age"
}
override fun onCreate(db: SQLiteDatabase) {
val createTableQuery = """
CREATE TABLE $TABLE_NAME (
$COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT,
$COLUMN_NAME TEXT,
$COLUMN_AGE INTEGER
)
""".trimIndent()
db.execSQL(createTableQuery)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
// Add any upgrade logic here if needed
}
fun insertData(name: String, age: Int): Long {
val values = ContentValues().apply {
put(COLUMN_NAME, name)
put(COLUMN_AGE, age)
}
return writableDatabase.insert(TABLE_NAME, null, values)
}
fun readData(): List<YourDataModel> {
val dataList = mutableListOf<YourDataModel>()
val cursor: Cursor = readableDatabase.query(TABLE_NAME, null, null, null, null, null, null)
with(cursor) {
while (moveToNext()) {
val id = getLong(getColumnIndexOrThrow(COLUMN_ID))
val name = getString(getColumnIndexOrThrow(COLUMN_NAME))
val age = getInt(getColumnIndexOrThrow(COLUMN_AGE))
dataList.add(YourDataModel(id, name, age))
}
}
cursor.close()
return dataList
}
fun updateData(id: Long, name: String, age: Int): Int {
val values = ContentValues().apply {
put(COLUMN_NAME, name)
put(COLUMN_AGE, age)
}
val selection = "$COLUMN_ID = ?"
val selectionArgs = arrayOf(id.toString())
return writableDatabase.update(TABLE_NAME, values, selection, selectionArgs)
}
fun deleteData(id: Long): Int {
val selection = "$COLUMN_ID = ?"
val selectionArgs = arrayOf(id.toString())
return writableDatabase.delete(TABLE_NAME, selection, selectionArgs)
}
}
Step 2: Using the Database in Your Activities/Fragments
Finally, we can use the DatabaseHelper class in our activities or fragments to perform database operations.
class MainActivity : AppCompatActivity() {
private lateinit var databaseHelper: DatabaseHelper
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
databaseHelper = DatabaseHelper(this)
// Example: Inserting data
val insertedId = databaseHelper.insertData("John Doe", 30)
// Example: Retrieving data
val dataList = databaseHelper.readData()
for (item in dataList) {
Log.d("Database", "ID: ${item.id}, Name: ${item.name}, Age: ${item.age}")
}
// Example: Updating data
databaseHelper.updateData(insertedId, "Jane Doe", 31)
// Example: Deleting data
databaseHelper.deleteData(insertedId)
}
override fun onDestroy() {
super.onDestroy()
databaseHelper.close()
}
}