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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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\u2019t require any internet connection to access it.<\/p>\n
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.<\/p>\n
SQLite Database provides you with many features in Android. Let\u2019s see a few of the features below:<\/p>\n
1. SQLite allows all the CRUD operations: SQLite supports all essential database operations, including Create, Read, Update, and Delete (CRUD). This means you can add new records, retrieve existing ones, modify them, and delete them as needed, making it a full-featured relational database management system for local data storage.<\/p>\n
2. SQLite allows partial indexing and advanced compatibility like exporting JSON. SQLite supports partial indexing, which allows you to create indexes on only a subset of data, improving query performance for specific queries. Additionally, it has advanced compatibility features such as exporting data in JSON format, which is useful for integration with web services and other applications.<\/p>\n
3. It is a lightweight, scraped-out, open-source version of the SQL database. SQLite is designed to be a lightweight and minimalistic SQL database engine, offering essential SQL features without the overhead of larger, more complex database systems. As an open-source project, it is freely available and widely used in various applications.<\/p>\n
4. This stores data locally in the form of encrypted text files. SQLite stores data in a local file on the device, which can be encrypted to protect sensitive information. The entire database, including its schema and data, is contained within a single file, simplifying management and backup.<\/p>\n
5. SQLite doesn\u2019t require an internet connection to access the database, and all the operations work offline. Since SQLite is embedded within applications and operates locally, it does not require an internet connection to perform database operations. This makes it ideal for offline applications where consistent access to data is needed without network dependency.<\/p>\n
6. SQLite maintains ACID(Atomicity, Consistency, Isolation, and Durability) properties throughout the entire database. SQLite ensures that all database transactions are handled with ACID properties, guaranteeing that operations are completed fully or not at all (Atomicity), data remains consistent (Consistency), transactions are isolated from each other (Isolation), and changes are preserved even in the case of a system crash (Durability).<\/p>\n
7. It comes up with very easy-to-use APIs. SQLite provides straightforward and easy-to-use Application Programming Interfaces (APIs) for interacting with the database. These APIs facilitate operations such as querying, updating, and managing the database, making it accessible for developers of all skill levels.<\/p>\n
8. SQLite is standalone and doesn\u2019t have any external dependencies. SQLite operates as a self-contained, serverless database engine. It does not require any external database server or additional software, making it simple to integrate and deploy within applications.<\/p>\n
9. SQLite provides a command-line interface to execute complex queries and fetch data. SQLite includes a command-line interface (CLI) that allows developers to execute SQL queries directly, perform database management tasks, and retrieve data. This tool is useful for debugging, testing, and performing advanced database operations.<\/p>\n
10. It provides a faster and efficient search experience. Due to its lightweight nature and efficient indexing mechanisms, SQLite offers fast search and retrieval operations. This speed is particularly advantageous for applications that require quick access to data without the overhead of larger database systems.<\/p>\n
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.<\/p>\n
Let\u2019s see some crucial points about SQLite in Android:<\/p>\n
1. SQLite allows you to store the following types of data in the database:<\/p>\n
2. To use SQLite in your android projects, you need to import the \u2018android.database.sqlite\u2019<\/strong> package. The SQLite package contains several APIs and classes that help us to implement SQLite Database.<\/p>\n 3. We have a class known as SQLiteOpenHelper,<\/strong> which is quite helpful to create and manage databases.<\/p>\n 4. SQLiteDatabase<\/strong> is another class that provides us with several methods which are helpful to create, read, update, and delete records from the database.<\/p>\n So until now, you saw what SQLite is and its use 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\u2019 data.<\/p>\n Step 1: Start your Android Studio and create a project named \u201cTechVidvanSQLite.\u201d<\/p>\n Step 2: Now open your AndroidManifest.xml file and add the permission to access the external storage.<\/p>\n Code:<\/strong><\/p>\n 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 \u201cstudent.kt\u201d. 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.<\/p>\n Code: databaseHandler.kt<\/strong><\/p>\n Step 5: Now come back to your MainActivity.kt file and paste the below code there.<\/p>\n Code: MainActivity.kt<\/strong><\/p>\n Now your application is all set and ready to execute. Simply run it on your device or emulator and notice the output.<\/p>\n You need to see the output in the logcat window(that is located at the bottom of your android studio).<\/p>\n To view the data stored in SQLite, you need to follow the below steps:<\/p>\nMethods in SQLite Database<\/h3>\n
\n\n
\n Method<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n insert(tableName: String!, nullColumnHack: String!, dataValues: ContentValues!)<\/span><\/td>\n The insert() method is useful to insert a record in the specified table of a database.<\/span><\/td>\n<\/tr>\n \n rawQuery(sql: String!, selectionArgs: Array<String!>!)<\/span><\/td>\n The rawQuery() method runs a SQL query on the database and returns a Cursor object.\u00a0<\/span><\/td>\n<\/tr>\n \n openDatabase(path: File, openParams: SQLiteDatabase.OpenParams)<\/span><\/td>\n The openDatabase() method is used to open a database specified by the path of the database and the parameters.\u00a0<\/span><\/td>\n<\/tr>\n \n isOpen()<\/span><\/td>\n The isOpen() method is used to know if a database is already open or not.<\/span><\/td>\n<\/tr>\n \n deleteDatabase(file: File)<\/span><\/td>\n The deleteDatabase() method is useful to delete a database and all associated files created by the database engine.\u00a0<\/span><\/td>\n<\/tr>\n \n delete(table: String!, whereClause: String!, whereArgs: Array<String!>!)<\/span><\/td>\n The delete() method is used to delete records from the specified table that follows the condition of whereClause.<\/span><\/td>\n<\/tr>\n \n execSQL(sql: String!)<\/span><\/td>\n The execSQL() method executes the given SQL statement but doesn\u2019t execute SELECT or any other statement that returns data.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Implementation of SQLite in Android<\/h3>\n
<uses-permission android:name=\"android.permission.READ_EXTERNAL_STORAGE\"\/>\r\n<\/pre>\n
\nCode: student.kt<\/strong><\/p>\npackage com.techvidvan.techvidvansqlite\r\n\r\nclass student {\r\n var rollNum = 0\r\n var name: String? = null\r\n var collegeName: String? = null\r\n\r\n \/\/Null constructor\r\n constructor() {}\r\n\r\n \/\/Constructor to initialize the data\r\n constructor(roll: Int, name: String?, _college_name: String?) {\r\n rollNum = roll\r\n this.name = name\r\n collegeName = _college_name\r\n }\r\n\r\n constructor(name: String?, _college_name: String?) {\r\n this.name = name\r\n collegeName = _college_name\r\n }\r\n\r\n}\r\n<\/pre>\npackage com.techvidvan.techvidvansqlite\r\n\r\nimport android.content.ContentValues\r\nimport android.content.Context\r\nimport android.database.sqlite.SQLiteDatabase\r\nimport android.database.sqlite.SQLiteOpenHelper\r\nimport java.util.ArrayList\r\n\r\n\r\nclass DatabaseHandler(context: Context?) :\r\n SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {\r\n\r\n override fun onCreate(db: SQLiteDatabase)\r\n {\r\n\r\n \/\/Creating Students Table\r\n val CREATE_STUDENTS_TABLE = (\"CREATE TABLE \" + TABLE_STUDENTS + \"(\"\r\n + ROLL_NO + \" INTEGER PRIMARY KEY,\" + NAME + \" TEXT,\"\r\n + CLG_NAME + \" TEXT\" + \")\")\r\n\r\n db.execSQL(CREATE_STUDENTS_TABLE)\r\n }\r\n\r\n override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {\r\n \/\/ upgrade the database when required\r\n \/\/ if a table named students already exist then drop it\r\n db.execSQL(\"DROP TABLE IF EXISTS $TABLE_STUDENTS\")\r\n\r\n \/\/ creating the student table again\r\n onCreate(db)\r\n }\r\n\r\n \/\/ using the below code we can add students\r\n fun addStudent(studObj: student) {\r\n val db = this.writableDatabase\r\n val values = ContentValues()\r\n values.put(NAME, studObj.name) \/\/ Name of the student\r\n values.put(CLG_NAME, studObj.collegeName) \/\/ Name of the college\r\n\r\n \/\/ Inserting student record\r\n db.insert(TABLE_STUDENTS, null, values)\r\n\r\n db.close()\r\n }\r\n\r\n \/\/ below function retrieves a student from the table\r\n fun getStudent(id: Int): student {\r\n val db = this.readableDatabase\r\n val cursor = db.query(\r\n TABLE_STUDENTS,\r\n arrayOf(\r\n ROLL_NO,\r\n NAME, CLG_NAME\r\n ),\r\n \"$ROLL_NO=?\",\r\n arrayOf(id.toString()),\r\n null,\r\n null,\r\n null,\r\n null\r\n )\r\n cursor?.moveToFirst()\r\n\r\n \/\/\/return the student object\r\n return student(\r\n cursor!!.getString(0).toInt(),\r\n cursor.getString(1), cursor.getString(2)\r\n )\r\n }\r\n\r\n\r\n \/\/ below function gives us details of all the students in the form of list\r\n val allStudents: List<student>\r\n get()\r\n {\r\n val studentList: MutableList<student> = ArrayList<student>()\r\n\r\n \/\/Query to select all students\r\n val selectQuery = \"SELECT * FROM $TABLE_STUDENTS\"\r\n val db = this.writableDatabase\r\n val cursor = db.rawQuery(selectQuery, null)\r\n\r\n \/\/ traversing through each student record\r\n if (cursor.moveToFirst()) {\r\n do {\r\n val studentObj = student(cursor.getString(0).toInt(), cursor.getString(1),\r\n cursor.getString(2))\r\n\r\n \/\/adding student object to student list\r\n studentList.add(studentObj)\r\n\r\n } while (cursor.moveToNext())\r\n }\r\n\r\n \/\/ returning the students list\r\n return studentList\r\n }\r\n\r\n\r\n\r\n companion object\r\n {\r\n private const val DATABASE_VERSION = 1\r\n private const val DATABASE_NAME = \"StudentDatabase\"\r\n private const val TABLE_STUDENTS = \"students\"\r\n private const val ROLL_NO = \"rollNum\"\r\n private const val NAME = \"name\"\r\n private const val CLG_NAME = \"collegeName\"\r\n }\r\n}\r\n<\/pre>\npackage com.techvidvan.techvidvansqlite\r\n\r\nimport androidx.appcompat.app.AppCompatActivity\r\nimport android.os.Bundle\r\nimport android.util.Log\r\n\r\n\r\nclass MainActivity : AppCompatActivity()\r\n{\r\n override fun onCreate(savedInstanceState: Bundle?) {\r\n super.onCreate(savedInstanceState)\r\n setContentView(R.layout.activity_main)\r\n val db = DatabaseHandler(this@MainActivity)\r\n\r\n \/\/ Inserting Students Data\r\n Log.d(\"TechVidvanSQLite\", \"Your Student data is inserted\")\r\n db.addStudent(student(\"Mohit Kumar\", \"SRM University AP\"))\r\n db.addStudent(student(\"Rahul\", \"Manipal University\"))\r\n db.addStudent(student(\"Nitesh Singh\", \"IIT Delhi\"))\r\n db.addStudent(student(\"John\", \"PES University\"))\r\n\r\n\r\n \/\/Reading all the students data\r\n Log.d(\"TechVidvanSQLite\", \"Reading all the students data\")\r\n\r\n val studentList: List<student> = db.allStudents\r\n for (stud in studentList) {\r\n val log = \"Roll Number: \" + stud.rollNum.toString() + \" , Name: \" + stud.name\r\n .toString() + \" ,College Name \" +\r\n stud.collegeName.toString()\r\n\r\n \/\/Displaying the students data in log\r\n Log.d(\"TechVidvanSQLite\", log)\r\n }\r\n }\r\n}\r\n<\/pre>\nHow to view the data stored in SQLite in android studio?<\/h3>\n