Android: Storing Data Using SQLite Database

There are different ways to store data in Android. One of them is storing in the database. The most commonly used database in Android is SQLite. In this tutorial, I will demonstrate how to use SQLite in Android application for storing, retrieving, modifying and deleting data.

We will be using movie database with the following structure for this tutorial:

Movies Table Structure

Movie Table Structure

Creating the Project

First of all lets create a project in Android Studio and name it SQLiteTutorial and give package name as com.androiddeft.sqlitetutorial. You can check detailed steps on how to create a project in Android Studio here: Installing Android Studio and Running your First Android Application

Defining Movies Class

Let’s define a java class which will be useful in holding movie properties during different database operations. Define getters and setters as well. Create package com.androiddeft.sqlitetutorial.vo under the main package. Now create a class named MovieDetailsVO (Right Click on package ==> New ==> Java Class) inside newly created package. Define the 4 properties of movies database. To generate getters and setters, Right Click ► Generate ►Getters and Setters ► Choose properties for which you wish to generate getters and setters.

 

Defining SQLite Databse Handler

We have to create a database helper which will help us to perform CRUD (Create-Retrieve-Update-Delete) operations. First of all, create a package named com.androiddeft.sqlitetutorial.helper inside the main package. Now create a class named SQLiteDBHelper which extends the class SQLiteOpenHelper provided by Android. After creating the class you need to extend the methods

onCreate() : Called when the database is created for the first time. This is where the creation of tables and the initial population of the tables should happen.

onUpgrade() : Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.

All CRUD Operations (Insert, Update,Select,Delete)

We will declare 5 methods to accomplish all CURD operations:

Inserting a new Record

We populate the object of the class MovieDetailsVO with movie details and call addMovie(MovieDetailsVO) method of SQLiteDBHelper class. The addMovie() method implements the insert operation. In order to establish the database connection and write to the database, we need to call getWritableDatabase() method of the parent class. It is always recommended to close the database connection after performing the desired operation.

Selecting a single Record

In order to select a single row from the table, we need to pass the movieId as the parameter to getAllMovies() method.Here movieId is the primary key of the movies table. Here we need to require to call getReadableDatabase() method of the parent class in order to initialize the database connection. The method returns MovieDetailsVO value object, which has the details of the movie.

Retrieving all Records

To read all the rows from the database we need to fire a raw select query and then iterate over the cursor and populate a list of MovieDetailsVO object.

Updating a Record

When you wish to update the details of the movie, you can pass the MovieDetailsVO object with the updated details to updateMovie(MovieDetailsVO) method.

Deleting a Record

For deleting a movie record you need to pass movieId to the deleteMovie(int) method.

Complete SQLiteDBHelper.java class

The User Interface

In order to make use of the application, we need a user interface. We will implement the simplest UI for this application and the xml code for the same goes below.

<?xml version="1.0" encoding="utf-8"?>
<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="wrap_content">

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical">

        <LinearLayout
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingLeft="10sp">

            <TextView
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="0.25"
                android:text="@string/movieName" />

            <EditText
                android:layout_width="0dp"
                android:id="@+id/txtMovieNameAdd"
                android:layout_height="wrap_content"
                android:layout_weight="0.75"
                android:ems="10" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingLeft="10sp">

            <TextView
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="0.25"
                android:text="@string/genre" />

            <EditText
                android:layout_width="0dp"
                android:id="@+id/txtGenreAdd"
                android:layout_height="wrap_content"
                android:layout_weight="0.75"
                android:ems="10" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingLeft="10sp">

            <TextView
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="0.25"
                android:text="@string/year" />

            <EditText
                android:id="@+id/txtYearAdd"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="0.75"
                android:ems="10" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingLeft="10sp">

            <TextView
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_gravity="center_vertical"
                android:layout_weight="0.25"

                android:text="@string/rating" />


            <EditText
                android:id="@+id/txtRatingAdd"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="0.75"
                android:ems="10"

                />
        </LinearLayout>

        <LinearLayout
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal">

            <View
                android:layout_width="0dp"
                android:layout_height="match_parent"
                android:layout_weight="0.50" />

            <Button
                android:id="@+id/btnAdd"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="0.20"
                android:text="@string/submit" />
        </LinearLayout>

        <LinearLayout
            android:paddingLeft="10sp"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:id="@+id/movieSpinnerLayout"
            android:orientation="horizontal"
            android:padding="5sp">

            <TextView
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_gravity="center_vertical"
                android:layout_weight="0.25"

                android:text="@string/select_movie" />


            <Spinner
                android:id="@+id/movieSpinner"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_gravity="center_vertical"
                android:layout_weight="0.75" />


        </LinearLayout>
        <LinearLayout
            android:id="@+id/displayArea"
            android:layout_width="match_parent"
            android:visibility="gone"
            android:layout_height="fill_parent"
            android:orientation="vertical">

            <LinearLayout
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:orientation="horizontal"
                android:padding="10sp">

                <TextView
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.25"
                    android:text="@string/movieName" />

                <TextView
                    android:id="@+id/txtMovieNameDisplay"
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.75"
                    android:ems="10" />
            </LinearLayout>

            <LinearLayout
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:orientation="horizontal"
                android:padding="10sp">

                <TextView
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.25"
                    android:text="@string/genre" />

                <TextView
                    android:id="@+id/txtGenreDisplay"
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.75"
                    android:ems="10" />
            </LinearLayout>

            <LinearLayout
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:orientation="horizontal"
                android:padding="10sp">

                <TextView
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.25"
                    android:text="@string/year" />

                <TextView
                    android:id="@+id/txtYearDisplay"
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.75"
                    android:ems="10" />
            </LinearLayout>

            <LinearLayout
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:orientation="horizontal"
                android:padding="10sp">

                <TextView
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_gravity="center_vertical"
                    android:layout_weight="0.25"

                    android:text="@string/rating" />


                <TextView
                    android:id="@+id/txtRatingDisplay"
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.75"
                    android:ems="10"

                    />
            </LinearLayout>
        </LinearLayout>

        <LinearLayout
            android:id="@+id/editArea"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:visibility="gone"
            android:orientation="vertical">

            <LinearLayout
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:orientation="horizontal"
                android:paddingLeft="10sp">

                <TextView
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.25"
                    android:text="@string/movieName" />

                <EditText
                    android:id="@+id/txtMovieNameEdit"
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.75"
                    android:ems="10" />
            </LinearLayout>

            <LinearLayout
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:orientation="horizontal"
                android:paddingLeft="10sp">

                <TextView
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.25"
                    android:text="@string/genre" />

                <EditText
                    android:id="@+id/txtGenreEdit"
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.75"
                    android:ems="10" />
            </LinearLayout>

            <LinearLayout
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:orientation="horizontal"
                android:paddingLeft="10sp">

                <TextView
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.25"
                    android:text="@string/year" />

                <EditText
                    android:id="@+id/txtYearEdit"
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.75"
                    android:ems="10" />
            </LinearLayout>

            <LinearLayout
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:orientation="horizontal"
                android:paddingLeft="10sp">

                <TextView
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_gravity="center_vertical"
                    android:layout_weight="0.25"

                    android:text="@string/rating" />


                <EditText
                    android:id="@+id/txtRatingEdit"
                    android:layout_width="0dp"
                    android:layout_height="wrap_content"
                    android:layout_weight="0.75"
                    android:ems="10"

                    />
            </LinearLayout>
        </LinearLayout>
        <LinearLayout
            android:id="@+id/editButton"
            android:layout_width="fill_parent"
            android:visibility="gone"
            android:layout_height="wrap_content"
            android:orientation="horizontal">

            <Button
                android:id="@+id/btnDelete"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="0.20"
                android:text="@string/delete" />
            <View
                android:layout_width="0dp"
                android:layout_height="match_parent"
                android:layout_weight="0.30" />

            <Button
                android:id="@+id/btnEdit"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="0.20"
                android:text="@string/edit" />
        </LinearLayout>

    </LinearLayout>
</ScrollView>

Usage

The below java code instantiates the SQLiteDBHelper object and uses its method to perform all CRUD operations

You can see the below video on how to use the app. If you have any queries you can write them down in the comments section. I will try my best to provide a solution.

Download Source CodeDownload APK

Stay Connected

No spam guarantee.

Abhishek

Abhishek

Abhishek loves coding. His favorite is Android Development. Apart from coding he loves traveling and reading.
Abhishek

Latest posts by Abhishek (see all)

2 thoughts on “Android: Storing Data Using SQLite Database”

Leave a Reply

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