SQLite Database in Android Development

SQLite is a lightweight relational database that is embedded in Android. It allows Android applications to store data in a structured way. It is a powerful tool for managing large amounts of data locally on the device. In this article, we will explore how to use SQLite databases in Android development using Kotlin.

1. Setting Up SQLite Database

To work with SQLite in Android, you need to create a helper class that extends SQLiteOpenHelper. This helper class provides methods to create, open, and upgrade the database.

Creating a Database Helper Class

To create a SQLite database in Android, you first need to define a SQLiteOpenHelper class that manages database creation and version management.

Example 1: Database Helper Class

    class MyDatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

        override fun onCreate(db: SQLiteDatabase) {
            val createTableQuery = "CREATE TABLE $TABLE_NAME ($COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT, $COLUMN_NAME TEXT, $COLUMN_AGE INTEGER)"
            db.execSQL(createTableQuery)
        }

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

        companion object {
            private const val DATABASE_NAME = "mydatabase.db"
            private const val DATABASE_VERSION = 1
            private const val TABLE_NAME = "user"
            private const val COLUMN_ID = "id"
            private const val COLUMN_NAME = "name"
            private const val COLUMN_AGE = "age"
        }
    }

In this example:

  • onCreate creates the database table with columns id, name, and age.
  • onUpgrade handles upgrading the database schema by dropping the existing table and creating a new one.

2. Inserting Data into SQLite Database

To insert data into the database, you can use the insert method of the SQLiteDatabase class.

Example 2: Inserting Data

    class MainActivity : AppCompatActivity() {

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

            val dbHelper = MyDatabaseHelper(this)
            val db = dbHelper.writableDatabase

            val contentValues = ContentValues()
            contentValues.put(MyDatabaseHelper.COLUMN_NAME, "John Doe")
            contentValues.put(MyDatabaseHelper.COLUMN_AGE, 30)

            // Insert data into database
            val newRowId = db.insert(MyDatabaseHelper.TABLE_NAME, null, contentValues)
            Toast.makeText(this, "New row inserted with ID: $newRowId", Toast.LENGTH_SHORT).show()
        }
    }

In this example:

  • ContentValues is used to store the values that will be inserted into the database.
  • db.insert inserts the data into the database, and returns the ID of the newly inserted row.

3. Retrieving Data from SQLite Database

To retrieve data from the SQLite database, you can use the query method of SQLiteDatabase or use a Cursor to access the rows returned by the query.

Example 3: Retrieving Data

    class MainActivity : AppCompatActivity() {

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

            val dbHelper = MyDatabaseHelper(this)
            val db = dbHelper.readableDatabase

            val projection = arrayOf(MyDatabaseHelper.COLUMN_ID, MyDatabaseHelper.COLUMN_NAME, MyDatabaseHelper.COLUMN_AGE)

            val cursor = db.query(
                MyDatabaseHelper.TABLE_NAME, projection, null, null, null, null, null
            )

            with(cursor) {
                while (moveToNext()) {
                    val id = getInt(getColumnIndexOrThrow(MyDatabaseHelper.COLUMN_ID))
                    val name = getString(getColumnIndexOrThrow(MyDatabaseHelper.COLUMN_NAME))
                    val age = getInt(getColumnIndexOrThrow(MyDatabaseHelper.COLUMN_AGE))

                    Toast.makeText(this@MainActivity, "ID: $id, Name: $name, Age: $age", Toast.LENGTH_LONG).show()
                }
            }
        }
    }

In this example:

  • db.query retrieves all the rows from the database table.
  • Cursor is used to iterate through the result set and extract the data from the columns.

4. Updating Data in SQLite Database

To update data in the database, you use the update method of SQLiteDatabase.

Example 4: Updating Data

    class MainActivity : AppCompatActivity() {

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

            val dbHelper = MyDatabaseHelper(this)
            val db = dbHelper.writableDatabase

            val contentValues = ContentValues()
            contentValues.put(MyDatabaseHelper.COLUMN_NAME, "Jane Doe")
            contentValues.put(MyDatabaseHelper.COLUMN_AGE, 28)

            // Update data in database
            val selection = "${MyDatabaseHelper.COLUMN_ID} = ?"
            val selectionArgs = arrayOf("1")

            val count = db.update(
                MyDatabaseHelper.TABLE_NAME, contentValues, selection, selectionArgs
            )
            Toast.makeText(this, "Rows updated: $count", Toast.LENGTH_SHORT).show()
        }
    }

In this example:

  • db.update is used to update existing records in the database. You provide a selection clause to specify which rows to update and the new values in ContentValues.

5. Deleting Data from SQLite Database

To delete data from the database, you use the delete method of SQLiteDatabase.

Example 5: Deleting Data

    class MainActivity : AppCompatActivity() {

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

            val dbHelper = MyDatabaseHelper(this)
            val db = dbHelper.writableDatabase

            val selection = "${MyDatabaseHelper.COLUMN_ID} = ?"
            val selectionArgs = arrayOf("1")

            // Delete data from database
            val deletedRows = db.delete(MyDatabaseHelper.TABLE_NAME, selection, selectionArgs)
            Toast.makeText(this, "Rows deleted: $deletedRows", Toast.LENGTH_SHORT).show()
        }
    }

In this example:

  • db.delete deletes the rows from the database where the selection condition matches.

6. Conclusion

SQLite is a powerful tool for storing structured data locally in Android applications. It is simple to set up and use for managing small to medium-sized datasets. By using the SQLiteOpenHelper class, you can easily create, read, update, and delete data in your app's database.




Advertisement