SQLite Database in Android

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
SQLite Database in Android

Android app or any application is made of two things Data And Functionality. So In Any application we need a way to store and manage data, Whether you are building a to-do list app, a note-taking app, or a contact app. In Android there are many ways to store data as mentioned below : 

But when you want to store large set of data locally then SQLite Database is a preferable option

What is SQLite Database in Android ?

SQLite Database in Android is a lightweight Relational database which comes with Android OS. SQLite Database in Android is an Open Source database, SQLite supports standard relational database features like SQL. SQLite is an open-source relational database used to perform database operations on android devices such as storing, manipulating or retrieving persistent data from the database. It is embedded in android by default. So there is no need to perform any database setup or administration task. If you want to store data locally in Android SQLite is way to go. 

SQLite only has four primitive data types: INTEGER, REAL, TEXT, and BLOB. All other types must be converted into one of these fields before getting saved in the database

SQLite Setup in Android

To Setup SQLite Database we need to create a subclass of SQLiteOpenHelper. SQLiteOpenHelper class provides way to manage database creation and version management. It provides two methods onCreate(SQLiteDatabase db), onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion), which is useful for Database managment.

				
					public class DBHelper extends SQLiteOpenHelper {
 @Override
 public void onCreate(SQLiteDatabase db) {
 
 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

 }
 
}

Android SQLiteOpenHelper Class

The SQLiteOpenHelper class is used for database creation and version management. It helps you handle upgrades and creation of your database schema. For performing any database operations, You have to provide the implementation of onCreate() and onUpgrade() methods of SQLiteOpenHelper class and you also need to Implement it’s constructor. There are three constructors of SQLiteOpenHelper class:

  • SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)  
  • SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler)
  • SQLiteOpenHelper(context: Context?, name: String?, version: Int, openParams: SQLiteDatabase.OpenParams)

Important Methods Of SQLiteOpenHelper class

For creating a table in SQLite Database we will use the onCreate() method of your SQLiteOpenHelper subclass. onCreate() method is called when the database is created for the first time, so we will use this method to execute SQL statements that will create a TABLE in SQLite Database.  

Creating a Table

				
					@Override
public void onCreate(SQLiteDatabase db) {

  String createNotesData = "CREATE TABLE NotesData ("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "name TEXT, "
                + "Description TEXT)";

        db.execSQL(createNotesData);
}

Here execSQL method is a method of SQLiteDatabase, which is used to executes the sql query.

SQLiteDatabase Methods

Insert Into SQLite Database Table

To insert data into SQLite Database, we will be using the insert() method of SQLiteDatabase. insert method requires three parameters which are as follows: 

insert(String tableName, String nullColumnHack, ContentValues values)

  • String table : Here we will pass table name where we want to insert the data 
  • String nullColumnHack: optional may be null, If you want to insert an empty row, in that case ContentValues have no content value, and you should use nullColumnHack 
  • ContentValues contentValues: ContentValues are smilar to an Associative array or HashMap Where we can put key value pair (COLUMN_NAME and It’s Value)  

The following code snippet shows how to insert a new record in the SQLite database

Here getWritableDatabase() method is used to open database connection in write mode. Another method you can use is getReadableDatabase() which is used to open the database connection in read mode.

Difference Between getWritableDatabase() and getReadableDatabase()

  • getWritableDatabase(): getWritableDatabase method locks the Data file so other Processes may not access it.
  • getReadableDatabase(): getReadableDatabase do not lock files Because there are no changes made to the database, So at a same time many processes can read the database. 

Fetch Data From SQLite Database

To fetch data from the SQLite Database, we will be using query() method of SQLiteDatabase. query method requires following parameters : 

Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)

  • String table: Here we will pass table name from where we want to get the data.
  • String[] columns: For specific columns select from the table.
  • String selection: A filter declaring which rows to return, formatted as an SQL WHERE clause. Passing null will return all rows for the given table.
  • String [] selectionArgs: You may include ?s in selection, which will be replaced by the values from selectionArgs, in the order that they appear in the selection. The values will be bound as Strings. If selection is null or does not contain ?s then selectionArgs may be null.  
  • groupBy:  A Filter used for how to group rows. Same as SQL GROUP BY clause, Pass null if you don’t want row to be grouped.
  • having: A filter declare which row groups to include in the cursor. Same as SQL HAVING clause, Passing null will cause all row groups to be included, and is required when row grouping is not being used.
  • orderBy: How to order the rows, Same AS SQL ORDER BY clause. Passing null will use the default sort order, which may be unordered.    
For more info on query() method: check here 

Here, query() method returns cursor. 

Cursor: cursor is the result set from a query operation is called cursor, we loop through the cursor items to process each line of a search result. 

Updating Record in SQLite Database in Android

To update data into SQLite Database, we will be using the update() method of SQLiteDatabase. update method requires the following parameteres :

int update(String table, ContentValues values, String whereClause, String[] whereArgs)

  • String table: Here we will pass table name from where we want to update the data.
  • ContentValues: Similar as in insert() method.
  • String whereClause: It’s condition (where you want to update into SQLite Database)
  • String[] whereArgs: It allows you to substitute placeholders (?) in the WHERE clause of your SQL statement. These placeholders are then replaced with the actual values from the whereArgs array. This approach helps prevent SQL injection attacks and also makes your code cleaner and more flexible.      

Delete Record From SQLite Database in Android

To delete data from SQLite Database, we will be using the delete() method of SQLiteDatabase. delete method requires the following parameteres :

delete(String table, String whereClause, String[] whereArgs)

  • String table: Pass table name where you want to delete the data.
  • String whereClause: It’s condition. Passing null will delete all rows.
  • String[] whereArgs: Same as update() method.  

Step by Step Implementation

Here, we have created a small project of NotesApp as shown below : 

This app consists of 3 helper classes and 3 activity. First we start with DBHelper.java class, this class will manage all the database operations. We define DBHelper.java class inside db package.

File: DBHelper class 

In activity_main.xml file, we have created Two Buttons and Two Edittext views, the one edittext is to get the title of the notes and another is to get the description and two buttons, one to save the notes and another is to read all the notes.

File: activity_main.xml

File : MainActivity class file in Kotlin/Java 

In, MainActivity we have called the insert() method and on read button click we have opened the other activity (AllNotesActivity) to fetch all the notes saved inside the SQLite database.

now in, AllNotesActivity we have fetch all the notes using fetchAllNotes() method of the DBHelper class and using listview, we have displayed all the notes that is fetched from the SQLite Database.

activity_all_notes.xml file

we have created a custome listview for the notes so for that we have created custom view (notes_view) and custom adapter class(NotesAdapter).

notes_view.xml file

 

File: NotesAdapter class


File: AllNotesActivity class File Kotlin/Java

In activity_notes.xml file, we have created Two Buttons and Two Edittext views, the one edittext is to update the title of the notes and another is to update the description and two buttons, one to update the notes and another is to delete note.

File: activity_notes.xml

File : NotesActivity class file in Kotlin/Java 

In, NotesActivity we have called the updateRecord() method and on update button and we have called deleteRecord() method on delete button.

If you find this content Helpful feel free to Enroll in our Android Development Course with Kotlin contact us for more information