Android SQLite Database
I hope you are enjoying your Android journey with us and are learning quite a few new things. Previously you saw several ways to store data in Android. Some of them were internal storage, external storage, and preferences.
Preferences, as you know, can be used for small amounts of data, and it stores data in the form of key-value pairs. Now, suppose you want to hold a large amount of data and run some queries on them. Then this is not possible using preferences because using preference, and you can get all values at once or a specific value belonging to the key provided.
SQLite being a scrapped-out version of SQL, provides you with features to run queries and get data based on those queries. Through this article, we will try to understand SQLite in detail and focus on the implementation part.
What is SQLite?
SQLite is a scrapped open-source version of SQL that is available in most android devices. It provides features similar to a standard SQL database and stores data in the form of relation. It is available offline for any android device and doesn’t require any internet connection to access it.
SQLite provides features to create, read, update and delete records in the database. It stores the records in the form of text files locally on your device. It allows you to run several queries and enable you to access multiple records at a time. Searching for a record in an SQLite database is relatively fast and efficient.
Features of SQLite
SQLite Database provides you with many features in Android. Let’s see a few of the features below:
1. SQLite allows all the CRUD operations: Create, Read, Update and Delete.
2. SQLite allows partial indexing and advanced compatibility like exporting JSON.
3. It is a lightweight, scraped-out, open-source version of the SQL database.
4. This stores data locally in the form of encrypted text files.
5. SQLite doesn’t require an internet connection to access the database, and all the operations work offline.
6. SQLite maintains ACID(Atomicity, Consistency, Isolation, and Durability) properties throughout the entire database.
7. It comes up with very easy-to-use APIs.
8. SQLite is standalone and doesn’t have any external dependencies.
9. SQLite provides a command-line interface to execute complex queries and fetch data.
10. It provides a faster and efficient search experience.
SQLite in Android
SQLite, as we know, is highly used in Android because of its lightweight and efficient features. It allows us to maintain consistency, concurrency, durability, and atomicity among the databases we manage. SQLite in android is compatible to work with large amounts of data and provides scalability options.
Let’s see some crucial points about SQLite in Android:
1. SQLite allows you to store the following types of data in the database:
- Strings or Characters
- Integers
- Floating point numbers
2. To use SQLite in your android projects, you need to import the ‘android.database.sqlite’ package. The SQLite package contains several APIs and classes that help us to implement SQLite Database.
3. We have a class known as SQLiteOpenHelper, which is quite helpful to create and manage databases.
4. SQLiteDatabase is another class that provides us with several methods which are helpful to create, read, update, and delete records from the database.
Methods in SQLite Database
| Method | Description |
| insert(tableName: String!, nullColumnHack: String!, dataValues: ContentValues!) | The insert() method is used to insert a record in the specified table of a database. |
| rawQuery(sql: String!, selectionArgs: Array<String!>!) | The rawQuery() method runs a SQL query on the database and returns a Cursor object. |
| openDatabase(path: File, openParams: SQLiteDatabase.OpenParams) | The openDatabase() method is used to open a database specified by the path of the database and the parameters. |
| isOpen() | The isOpen() method is used to know if a database is already open or not. |
| deleteDatabase(file: File) | The deleteDatabase() method is used to delete a database and all associated files created by the database engine. |
| delete(table: String!, whereClause: String!, whereArgs: Array<String!>!) | The delete() method is used to delete records from the specified table that follows the condition of whereClause. |
| execSQL(sql: String!) | The execSQL() method executes the given SQL statement but doesn’t execute SELECT or any other statement that returns data. |
Implementation of SQLite in Android
So until now, you saw what SQLite is and how it is used in android. Now it’s time for us to explore SQLite a bit further by building an application that involves SQLite in it. So, in this implementation, we will create an application to store college students’ data.
Step 1: Start your Android Studio and create a project named “TechVidvanSQLite.”
Step 2: Now open your AndroidManifest.xml file and add the permission to access the external storage.
Code:
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE"/>
Step 3: Now, you need to create a modal class for storing the students’ records. Just make a new Kotlin class file and name it as “student.kt”.
Code: student.kt
package com.techvidvan.techvidvansqlite
class student {
var rollNum = 0
var name: String? = null
var collegeName: String? = null
//Null constructor
constructor() {}
//Constructor to initialize the data
constructor(roll: Int, name: String?, _college_name: String?) {
rollNum = roll
this.name = name
collegeName = _college_name
}
constructor(name: String?, _college_name: String?) {
this.name = name
collegeName = _college_name
}
}
Step 4: Now, we need to create a databaseHandler class extending the properties from SQLiteOpenHelper class. The databaseHandler.kt file helps us in managing our database and performing operations on it.
Code: databaseHandler.kt
package com.techvidvan.techvidvansqlite
import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import java.util.ArrayList
class DatabaseHandler(context: Context?) :
SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
override fun onCreate(db: SQLiteDatabase)
{
//Creating Students Table
val CREATE_STUDENTS_TABLE = ("CREATE TABLE " + TABLE_STUDENTS + "("
+ ROLL_NO + " INTEGER PRIMARY KEY," + NAME + " TEXT,"
+ CLG_NAME + " TEXT" + ")")
db.execSQL(CREATE_STUDENTS_TABLE)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
// upgrade the database when required
// if a table named students already exist then drop it
db.execSQL("DROP TABLE IF EXISTS $TABLE_STUDENTS")
// creating the student table again
onCreate(db)
}
// using the below code we can add students
fun addStudent(studObj: student) {
val db = this.writableDatabase
val values = ContentValues()
values.put(NAME, studObj.name) // Name of the student
values.put(CLG_NAME, studObj.collegeName) // Name of the college
// Inserting student record
db.insert(TABLE_STUDENTS, null, values)
db.close()
}
// below function retrieves a student from the table
fun getStudent(id: Int): student {
val db = this.readableDatabase
val cursor = db.query(
TABLE_STUDENTS,
arrayOf(
ROLL_NO,
NAME, CLG_NAME
),
"$ROLL_NO=?",
arrayOf(id.toString()),
null,
null,
null,
null
)
cursor?.moveToFirst()
///return the student object
return student(
cursor!!.getString(0).toInt(),
cursor.getString(1), cursor.getString(2)
)
}
// below function gives us details of all the students in the form of list
val allStudents: List<student>
get()
{
val studentList: MutableList<student> = ArrayList<student>()
//Query to select all students
val selectQuery = "SELECT * FROM $TABLE_STUDENTS"
val db = this.writableDatabase
val cursor = db.rawQuery(selectQuery, null)
// traversing through each student record
if (cursor.moveToFirst()) {
do {
val studentObj = student(cursor.getString(0).toInt(), cursor.getString(1),
cursor.getString(2))
//adding student object to student list
studentList.add(studentObj)
} while (cursor.moveToNext())
}
// returning the students list
return studentList
}
companion object
{
private const val DATABASE_VERSION = 1
private const val DATABASE_NAME = "StudentDatabase"
private const val TABLE_STUDENTS = "students"
private const val ROLL_NO = "rollNum"
private const val NAME = "name"
private const val CLG_NAME = "collegeName"
}
}
Step 5: Now come back to your MainActivity.kt file and paste the below code there.
Code: MainActivity.kt
package com.techvidvan.techvidvansqlite
import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import android.util.Log
class MainActivity : AppCompatActivity()
{
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
val db = DatabaseHandler(this@MainActivity)
// Inserting Students Data
Log.d("TechVidvanSQLite", "Your Student data is inserted")
db.addStudent(student("Mohit Kumar", "SRM University AP"))
db.addStudent(student("Rahul", "Manipal University"))
db.addStudent(student("Nitesh Singh", "IIT Delhi"))
db.addStudent(student("John", "PES University"))
//Reading all the students data
Log.d("TechVidvanSQLite", "Reading all the students data")
val studentList: List<student> = db.allStudents
for (stud in studentList) {
val log = "Roll Number: " + stud.rollNum.toString() + " , Name: " + stud.name
.toString() + " ,College Name " +
stud.collegeName.toString()
//Displaying the students data in log
Log.d("TechVidvanSQLite", log)
}
}
}
Now your application is all set and ready to execute. Simply run it on your device or emulator and notice the output.
You need to see the output in the logcat window(that is located at the bottom of your android studio).
How to view the data stored in SQLite in android studio?
To view the data stored in SQLite, you need to follow the below steps:
1: Open your Android SQLite Project in Android Studio and then navigate to Tools—>Tool Windows—–>Device File Explorer as shown below.
2: Now, you can see the Device File Explorer window in front of you.
3: Now, you need to browse through data. After opening the data folder, you will find another subfolder named data. Open it and look for the package name of your project. In my case, it is “com.techvidvan.techvidvansqlite”.
4: Now open the databases folder present inside your package, as shown below.
5: Now select the database file and click on Save As option as shown below. In my case, my database name is “StudentDatabase”.
6: After clicking on Save As an option, a window will appear to provide the path where you wish to save this file. Provide your path and click on Ok, as shown below.
7: Now, your database file is successfully downloaded in your desired path. To view that database file, you need a tool known as DB Browser for SQLite. DB Browser is available for almost all distributions, Windows, macOS, Linux, etc. Use the link to download DB Browser on your system.
If you are a Linux user, then use the below command to install SQLite DB Browser.
Command:
snap install sqlitebrowser
Step 8: After successfully installing DB Browser, you need to open it and browse your database file as shown below.
After opening your database file, you need to browse the required table to get the results, as shown below.
Summary
Through this article, you came across another storage technique in android. You came across what SQLite is, its features, and its roles in android. Later on, you saw the classes present in SQLite in android and the involved methods. Finally, you saw an implementation of SQLite database through an android application.