DatabaseHelper Class Using SQLite

By Osman AdamAndroid Development

Class Declaration and Constructor

class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION)

The line declares the DatabaseHelper class, which extends SQLiteOpenHelper. It takes one parameter in its constructor – a Context. This usually is the application context or activity context that it needs in order to use the file system and resources provided by Android.
SQLiteOpenHelper is an android helper class to manage database creation and onupgrade management. By extending it, our DatabaseHelper class will inherit methods used in creating, upgrading, and opening the database.

Companion Object and Constants

companion object {
    private const val DATABASE_NAME = "files.db" private const val DATABASE_VERSION = 2 const val TABLE_NAME = "files" const val COLUMN_ID = "id" const val COLUMN_FILE_NAME = "file_name" const val COLUMN_FILE_PATH = "file_path" const val COLUMN_DATE = "date_saved"
}

The companion object in Kotlin is similar to static members in Java. It contains constants used throughout the class:

  • DATABASE_NAME: The name of the SQLite database file.
  • DATABASE_VERSION: The version number of the database schema. It’s set to 2, indicating there’s been an update to the schema.
  • TABLE_NAME: The name of the table in the database.
  • COLUMN_*: These constants define the names of the columns in the table.

onCreate Method

override fun onCreate(db: SQLiteDatabase) {
    val createTable = "CREATE TABLE $TABLE_NAME (" +
        "$COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
        "$COLUMN_FILE_NAME TEXT, " +
        "$COLUMN_FILE_PATH TEXT, " +
        "$COLUMN_DATE TEXT)" db.execSQL(createTable)
}

1. Method Declaration

override fun onCreate(db: SQLiteDatabase)
  • override keyword indicates that this method is overriding a method from the superclass (SQLiteOpenHelper).
  • fun is the Kotlin keyword for declaring a function.
  • onCreate is the name of the method.
  • db: SQLiteDatabase is the parameter. It’s an instance of SQLiteDatabase that the method can use to execute SQL commands.

2. Creating the SQL Stateme

val createTable = "CREATE TABLE $TABLE_NAME (" +
    "$COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
    "$COLUMN_FILE_NAME TEXT, " +
    "$COLUMN_FILE_PATH TEXT, " +
    "$COLUMN_DATE TEXT)"

This creates a String containing an SQL command to create a new table. Let’s break it down:

  • CREATE TABLE is the SQL command to create a new table.
  • $TABLE_NAME is a variable containing the name of the table (defined in the companion object).
  • The parentheses () contain the definitions for each column in the table:
    • $COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT: This creates an ID column that automatically increments for each new row.
    • $COLUMN_FILE_NAME TEXT: This creates a column to store the file name as text.
    • $COLUMN_FILE_PATH TEXT: This creates a column to store the file path as text.
    • $COLUMN_DATE TEXT: This creates a column to store the date as text.

3. Executing the SQL Statement

db.execSQL(createTable)
  • db is the SQLiteDatabase instance passed to the method.
  • execSQL is a method of SQLiteDatabase that executes an SQL statement directly.
  • createTable is the SQL statement we created earlier.

Purpose and When It's Called

This is due to the onCreate method inside the SQLiteOpenHelper class which will be invoked by the system, after the latter created the database for the first time. Usually this happens:

  1. When the app is installed.
  2. When the application is running for the very first time after installation.
  3. If the database file has been deleted.

Its purpose is to configure the initial structure of the database, creating all the necessary tables and defining their columns.

onUpgrade Method

Let’s break down the onUpgrade method and explain it in depth:

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

1. Method Declaration

override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int)
  • override keyword indicates that this method is overriding a method from the superclass (SQLiteOpenHelper).
  • fun is the Kotlin keyword for declaring a function.
  • onUpgrade is the name of the method.
  • It takes three parameters:
    • db: SQLiteDatabase: An instance of SQLiteDatabase that the method can use to execute SQL commands.
    • oldVersion: Int: The version number of the database before the upgrade.
    • newVersion: Int: The new version number of the database after the upgrade.

2. Dropping the Existing Table

db.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
  • db.execSQL executes an SQL statement directly.
  • "DROP TABLE IF EXISTS $TABLE_NAME" is an SQL command that deletes the existing table if it exists.
    • DROP TABLE is the SQL command to delete a table.
    • IF EXISTS prevents an error if the table doesn’t exist.
    • $TABLE_NAME is the name of the table (defined in the companion object).

3. Recreating the Table

onCreate(db)
  • This line calls the onCreate method we discussed earlier.
  • It recreates the table with the current schema.

Purpose and When It's Called

The onUpgrade method is called when the database needs to be upgraded to a new version. This typically happens:

  1. When you update your app and increase the DATABASE_VERSION constant.
  2. When a user updates your app to a version with a higher DATABASE_VERSION.

getAllFiles Method

Let’s break down the getAllFiles method:

fun getAllFiles(): List<Triple<String, String, String>> {
    val db = readableDatabase val cursor = db.query(TABLE_NAME, null, null, null, null, null, null)
    val files = mutableListOf<Triple<String, String, String>>()

    while (cursor.moveToNext()) {
      val name = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_NAME))
      val path = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_PATH))
      val date = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_DATE))
      files.add(Triple(name, path, date))
    }
    cursor.close()
    return files
  }

1. Method Declaration

fun getAllFiles(): List<Triple<String, String, String>>
  • fun is the Kotlin keyword for declaring a function.
  • getAllFiles is the name of the method.
  • : List<Triple<String, String, String>> indicates that this method returns a List of Triple objects, where each Triple contains three String values (for file name, file path, and date).

2. Getting a Readable Database Instance 

val db = readableDatabase
  • readableDatabase is a property inherited from SQLiteOpenHelper.
  • It returns a readable SQLiteDatabase instance.
  • Using readableDatabase instead of writableDatabase is more efficient when you only need to read from the database.

3. Querying the Database

val cursor = db.query(TABLE_NAME, null, null, null, null, null, null)
  • db.query() is a method to perform a SELECT query on the database.
  • Parameters:
    1. TABLE_NAME: The name of the table to query.
    2. null: Columns to return. null means all columns.
    3. null: The WHERE clause. null means no WHERE clause (select all rows).
    4. null: The values for the WHERE clause. Not used here as there’s no WHERE clause.
    5. null: GROUP BY clause. Not used in this query.
    6. null: HAVING clause. Not used in this query.
    7. null: ORDER BY clause. Not used in this query.
  • This query effectively translates to the SQL statement: SELECT * FROM $TABLE_NAME

4. Preparing the Result List

val files = mutableListOf<Triple<String, String, String>>()
  • Creates an empty mutable list to store the results.
  • Each item in the list will be a Triple of Strings (file name, file path, date).

5. Iterating Through the Results

while (cursor.moveToNext()) {
    val name = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_NAME))
    val path = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_PATH))
    val date = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_DATE))
    files.add(Triple(name, path, date))
  }
  • cursor.moveToNext(): Moves the cursor to the next row in the result set. Returns false when there are no more rows.
  • For each row:
    • cursor.getString(): Retrieves the String value from the specified column.
    • cursor.getColumnIndexOrThrow(): Gets the index of the specified column.
    • Triple(name, path, date): Creates a new Triple object with the retrieved values.
    • files.add(): Adds the Triple to the list of results.

6. Cleaning Up and Returning Results

cursor.close()
return files
  • cursor.close(): Closes the cursor to release its resources. This is important to prevent memory leaks.
  • return files: Returns the list of Triples containing all the file information.

Purpose and Usage

The getAllFiles method retrieves all file records stored in the database. It’s useful when you need to display a list of all files, perhaps in a file browser or gallery view within your app.

getFilesByDate Method

Let’s break down the getFilesByDate method

fun getFilesByDate(date: String): List<Pair<String, String>> {
    val db = readableDatabase val cursor = db.query(
      TABLE_NAME,
      null,
      "$COLUMN_DATE = ?",
      arrayOf(date),
      null,
      null,
      null
    )
    val files = mutableListOf<Pair<String, String>>()

    while (cursor.moveToNext()) {
      val name = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_NAME))
      val path = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_PATH))
      files.add(Pair(name, path))
    }
    cursor.close()
    return files
  }

1. Method Declaration 

fun getFilesByDate(date: String): List<Pair<String, String>>
  • fun is the Kotlin keyword for declaring a function.
  • getFilesByDate is the name of the method.
  • date: String is the parameter, expecting a date string to filter the results.
  • : List<Pair<String, String>> indicates that this method returns a List of Pair objects, where each Pair contains two String values (for file name and file path).

2. Getting a Readable Database Instance

val db = readableDatabase
  • readableDatabase is a property inherited from SQLiteOpenHelper.
  • It returns a readable SQLiteDatabase instance.
  • Using readableDatabase is appropriate here as we’re only reading from the database.

3. Querying the Database

val cursor = db.query(
    TABLE_NAME,
    null,
    "$COLUMN_DATE = ?",
    arrayOf(date),
    null,
    null,
    null
  )
  • db.query() is a method to perform a SELECT query on the database.
  • Parameters:
    1. TABLE_NAME: The name of the table to query.
    2. null: Columns to return. null means all columns.
    3. "$COLUMN_DATE = ?": The WHERE clause. This filters the results to only include rows where the date column matches the provided date.
    4. arrayOf(date): The values for the WHERE clause. The date parameter is used to replace the ? in the WHERE clause.
    5. null: GROUP BY clause. Not used in this query.
    6. null: HAVING clause. Not used in this query.
    7. null: ORDER BY clause. Not used in this query.
  • This query effectively translates to the SQL statement: SELECT * FROM $TABLE_NAME WHERE $COLUMN_DATE = ?
  • The ? in the WHERE clause is a placeholder that gets replaced with the date value, preventing SQL injection.

4. Preparing the Result List

val files = mutableListOf<Pair<String, String>>()
  • Creates an empty mutable list to store the results.
  • Each item in the list will be a Pair of Strings (file name and file path).

5. Iterating Through the Results

while (cursor.moveToNext()) {
    val name = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_NAME))
    val path = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_PATH))
    files.add(Pair(name, path))
  }

For each row in the result set:

  • cursor.getString(): Retrieves the String value from the specified column.
  • cursor.getColumnIndexOrThrow(): Gets the index of the specified column. If the column doesn’t exist, it throws an IllegalArgumentException.
  • COLUMN_FILE_NAME and COLUMN_FILE_PATH are constants defined in the companion object, representing the column names in the database.
  • Pair(name, path): Creates a new Pair object with the retrieved file name and path.
  • files.add(): Adds the Pair to the list of results.

6. Cleaning Up and Returning Results

cursor.close()
return files
  • cursor.close(): Closes the cursor to release its resources. This is important to prevent memory leaks.
  • return files: Returns the list of Pairs containing the file information for the specified date.

Purpose and Usage

The getFilesByDate method retrieves all file records stored in the database for a specific date. This can be useful in various scenarios:

  1. Displaying files created or modified on a particular day.
  2. Implementing a calendar view of files in your app.
  3. Performing operations (like backups) on files from a specific date.

fileExists Method

Let’s break down the fileExists method:

fun fileExists(fileName: String): Boolean {
    val db = readableDatabase val cursor = db.query(
      "files",
      arrayOf("id"),
      "file_name = ?",
      arrayOf(fileName),
      null,
      null,
      null
    )
    val exists = cursor.count > 0
    cursor.close()
    return exists
  }

1. Method Declaration

fun fileExists(fileName: String): Boolean
  • fun is the Kotlin keyword for declaring a function.
  • fileExists is the name of the method.
  • fileName: String is the parameter, expecting the name of the file to check for existence.
  • : Boolean indicates that this method returns a Boolean value (true if the file exists, false otherwise).

2. Getting a Readable Database Instance

val db = readableDatabase
  • readableDatabase is a property inherited from SQLiteOpenHelper.
  • It returns a readable SQLiteDatabase instance.
  • Using readableDatabase is appropriate here as we’re only reading from the database.

3. Querying the Database

val cursor = db.query(
    "files",
    arrayOf("id"),
    "file_name = ?",
    arrayOf(fileName),
    null,
    null,
    null
  )
  • db.query() is a method to perform a SELECT query on the database.
  • Parameters:

    1. “files”: The name of table to query. Note that this uses a string literal instead of the TABLE_NAME constant used in other methods.
    2. arrayOf(“id”): Indicates we only need to retrieve the “id” column. It’s more efficient to do it this way rather than a SELECT all columns when we only need to check existence.
    3. “file_name = ?”: WHERE. This clause filters the results to include only the rows where the file_name column matches the given fileName.
    4. arrayOf(fileName): Values to be set for the WHERE. Replaces the? in the WHERE with the parameter named fileName.
    5. null: GROUP BY clause. In this query, it’s not used.
    6. null: HAVING clause. Nor is this used in this query.
    7. null: ORDER BY clause. And once more, it’s not used in this query.
  • This query effectively translates to the SQL statement: SELECT id FROM files WHERE file_name = ?
  • The ? in the WHERE clause is a placeholder that gets replaced with the fileName value, preventing SQL injection.

4. Checking for Existence

val exists = cursor.count > 0
  • cursor.count returns the number of rows in the result set.
  • If cursor.count > 0, it means at least one row was found, indicating that a file with the given name exists in the database.
  • The result of this comparison (true or false) is stored in the exists variable.

5. Cleaning Up and Returning Result

cursor.close()
return exists
  • cursor.close(): Closes the cursor to release its resources. This is important to prevent memory leaks.
  • return exists: Returns the boolean value indicating whether the file exists in the database.

Purpose and Usage

The fileExists method checks if a file with a given name is already recorded in the database. This can be useful in various scenarios:

  1. Avoid the duplication of entries while adding new files.
  2. Check if a file should be added to the database or if it just requires an update.
  3. Validating provided input from the user while searching for files.

Full DatabaseHelper Class

package com.example.digitaldiaryapp import android.content.Context import android.database.sqlite.SQLiteDatabase import android.database.sqlite.SQLiteOpenHelper import android.content.ContentValues import java.io.File import java.io.IOException class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

      companion object {
          private const val DATABASE_NAME = "files.db" private const val DATABASE_VERSION = 2 // Changed version to 2 for the upgrade const val TABLE_NAME = "files" const val COLUMN_ID = "id" const val COLUMN_FILE_NAME = "file_name" const val COLUMN_FILE_PATH = "file_path" const val COLUMN_DATE = "date_saved" // New column for the save date
      }

      override fun onCreate(db: SQLiteDatabase) {
          val createTable = "CREATE TABLE $TABLE_NAME (" +
                  "$COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                  "$COLUMN_FILE_NAME TEXT, " +
                  "$COLUMN_FILE_PATH TEXT, " +
                  "$COLUMN_DATE TEXT)" // Create the new column db.execSQL(createTable)
      }

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

      // Modified insert method to include date fun insertFile(fileName: String, filePath: String, dateSaved: String): Long {
          val db = writableDatabase val contentValues = ContentValues().apply {
              put(COLUMN_FILE_NAME, fileName)
              put(COLUMN_FILE_PATH, filePath)
              put(COLUMN_DATE, dateSaved) // Insert the date into the new column
          }
          return db.insert(TABLE_NAME, null, contentValues)
      }

      fun getAllFiles(): List<Triple<String, String, String>> { // I have updated this to return date as well val db = readableDatabase val cursor = db.query(TABLE_NAME, null, null, null, null, null, null)
          val files = mutableListOf<Triple<String, String, String>>() // Using Triple for file name, path, and date while (cursor.moveToNext()) {
              val name = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_NAME))
              val path = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_PATH))
              val date = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_DATE)) // Retrieve the saved date files.add(Triple(name, path, date))
          }
          cursor.close()
          return files
      }

      fun getFilesByDate(date: String): List<Pair<String, String>> {
          val db = readableDatabase val cursor = db.query(
              TABLE_NAME,
              null,
              $COLUMN_DATE = ?,
              arrayOf(date),
              null,
              null,
              null
          )
          val files = mutableListOf<Pair<String, String>>()

          while (cursor.moveToNext()) {
              val name = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_NAME))
              val path = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_PATH))
              files.add(Pair(name, path))
          }
          cursor.close()
          return files
      }

      fun fileExists(fileName: String): Boolean {
          val db = readableDatabase val cursor = db.query(
              "files",
              arrayOf("id"),
              "file_name = ?",
              arrayOf(fileName),
              null,
              null,
              null
          )
          val exists = cursor.count > 0 cursor.close()
          return exists
      }

      fun updateFile(fileName: String, filePath: String, dateSaved: String) {
          val db = writableDatabase val values = ContentValues().apply {
              put("file_path", filePath)
              put("date_saved", dateSaved)
          }
          db.update("files", values, "file_name = ?", arrayOf(fileName))
      }

      fun deleteFile(fileName: String): Int {
          val db = this.writableDatabase return db.delete(TABLE_NAME, $COLUMN_FILE_NAME = ?, arrayOf(fileName))
      }

      fun deleteAllFiles(context: Context) {
          val db = readableDatabase // Query to get all file names val cursor = db.query(TABLE_NAME, arrayOf(COLUMN_FILE_NAME), null, null, null, null, null)

          while (cursor.moveToNext()) {
              val fileName = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FILE_NAME))
              val fileToDelete = File(context.filesDir, fileName)

              if (fileToDelete.exists()) {
                  val deleted = fileToDelete.delete()
                  if (!deleted) {
                      throw IOException("Failed to delete file: $fileName")
                  }
              }
          }
          cursor.close()

          // Delete all entries from the database db.delete(TABLE_NAME, null, null)
      }
  }

Leave a Reply

Your email address will not be published. Required fields are marked *