Android SQLite Database Example Tutorial

In this tutorial, we are going to learn about Android SQLite database with examples on how it can be used.

Once you start building android app, you will come to a point where you will need to persist data from your app users or data the app will use to work as it is intended to work.

There are different options available in android to store persistence data like using Shared Preference and flat file. Each of these options has its own advantages and disadvantages.

The important feature of using a structural database is the rate at which you can search and index data stored in it.

Android SDK comes with default database called SQLite. This is the lite version for hand held and mobile devices. It is purposely built for android mobile devices because of its small memory footprint.

The android SQLite API can be found in android.database.sqlite. There, you will see the different classes and methods that will help you store and manipulate android SQLite database in your application.

Note that android database file is stored in internal storage that is associated with the app that created it. So no other application in the device will have access to the database.

But if you want to expose your app SQLite database so that other applications can access, use and manipulate it, then your have to write your own custom content provider using the database as repository.

If you want to learn how to use android default Content Provider, I will suggest you read my android tutorial on Android Calendar Content Provider Example.

Note also as I stated above. You can also write your own custom content provider to expose your app data and let other apps have access to it. To understand how it works, you can read my tutorial on Android Custom Content Provider Example Tutorial.

Before we dive in further to explore more about android SQLite database, it is also important to emphasize that, there are new android database that are current available to use in your application.

Some of these databases are used as a third party android libraries and they have different philosophies.

Among them are Android Relational Mapping databases like OrmLite, Sugar ORM and among others. They are wrapper classes for android SQLite database which maps individual table in the database to Plain Object Java Object.

In addition to this, there are also the new real-time or reactive android databases like Real-Time Firebase Database and Android Realm Database. One good thing I found with using reactive database in android is the ability to trigger events that will update android UI views when there is a change in the structure of the database.

Now, let go back to the main tutorial topic on android SQLite database.

In other to create a new SQLite database in your android application, we will first look at the SQLiteOpenHelper class. This is a helper class that manages the creation and updating of android SQLite database. It uses the onCreate() and onUpgrade() callback methods to create and update a single SQLite database.

You create a class that extends from SQLiteOpenHelper class. When this class is instantiated, you can call the openOrCreateDatabase or getWritableDatabase() or getReadableDatabase() depending on what you plan to achieve.

Then you can use the instance of the database to execute other different methods like insert, delete, update and create row in the database.

Note that whenever you change the structure of the database, you will increment the database version number by 1.

For this tutorial, we will create a android project that can store, delete and create new data using Sqlite.

Since android SQLite is embedded inside the internal storage, there is no way to instantly see what is going on inside your database excerpt if you are using other external tools or Android Studio file explorer.

There is also a way where by we can create our android SQLite database file outside our application, insert all the data we need and we will put it in the assets folder inside android project.

Note that this is achieve by using a party android library called Android SQLite Asset Helper. You can find some of the examples in my previous tutorial

Let’s start with building a new application using android studio or any IDE of your choice.

In other to get a visual understanding of what we are going to create in this android tutorial, I have add below some screen shots from the application.

SOME SCREENSHOT FROM THE APPLICATION

android sqlite database

CREATE NEW ANDROID PROJECT

Lets start to soil our hands in code. Start up your IDE. For this tutorial, I am using the following tools and environment, feel free to use what works for you.

Windows 10

Android Studio

Sony Xperia ZL

Min SDK 14

Target SDK 25

To create a new android application project, follow the steps as stipulated below.

Go to File menu

Click on New menu

Click on Android Application

Enter Project name: AndroidSQLiteExample

Package: com.inducesmile.androidsqliteexample

Select Empty Activity

Name your activity: MainActivity

Keep other default selections

Continue to click on next button until Finish button is active, then click on Finish Button.

ADD LIBRARY DEPENDENCIES IN BUILD.GRADLE

As stated before, to use the Realm database we need to add Realm as gradle plugin in our project build.gradle file.

Open your build.gradle (project) file and add the code below.

apply plugin: 'com.android.application'
apply plugin: 'com.android.application'
android {
    compileSdkVersion 25
    buildToolsVersion "24.0.1"
    defaultConfig {
        applicationId "com.inducesmile.androidsqliteexample"
        minSdkVersion 14
        targetSdkVersion 25
        versionCode 1
        versionName "1.0"
        testInstrumentationRunner "android.support.test.runner.AndroidJUnitRunner"
    }
    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.pro'
        }
    }
}
dependencies {
    compile fileTree(dir: 'libs', include: ['*.jar'])
    androidTestCompile('com.android.support.test.espresso:espresso-core:2.2.2', {
        exclude group: 'com.android.support', module: 'support-annotations'
    })
    compile 'com.android.support:appcompat-v7:25.0.1'
    compile 'com.intuit.sdp:sdp-android:1.0.3'
    compile 'com.android.support:design:25.0.1'
    compile 'com.android.support:cardview-v7:25.0.1'
    compile 'com.android.support:recyclerview-v7:25.0.1'
    compile 'com.android.support:support-v4:25.0.1'
    compile 'com.google.code.gson:gson:2.6.1'
    testCompile 'junit:junit:4.12'
}

 STRINGS.XML

We are going to update our project strings.xml file located in the values folder inside the res folder. Open the file and add the code below to it.

<resources>
    <string name="app_name">Android SQLite Example</string>
    <string name="product_name">Product name</string>
    <string name="enter_name">Enter name</string>
    <string name="enter_quantity">Enter quantity</string>
</resources>

 COLORS.XML

Open the colors.xml file in the same location as the strings.xml file and add the code below to the file.

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <color name="colorPrimary">#3F51B5</color>
    <color name="colorPrimaryDark">#303F9F</color>
    <color name="colorAccent">#FF4081</color>
    <color name="colorWhite">#ffffff</color>
    <color name="colorBlack">#000000</color>
    <color name="colorBorder">#d0ced0</color>
</resources>

Create Sub-Class of SQLiteOpenHelper

SqliteDatabase.java

Create a new package folder inside your main application package folder and name it database.

Inside this folder, create a file and name it SqliteDatabase. This class will extend from the Android SQLIteOpenHelper class.

Override the OnCreate() and onUpgrade() callback method. Inside the onCreate() method, we will execute the sql command that will create a table in our database.

For simplicity, we will add all the methods we will use to query our database like insert, update, delete and search inside this class.

Open the file and add the code below to it.

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.inducesmile.androidsqliteexample.Product;
import java.util.ArrayList;
import java.util.List;
public class SqliteDatabase extends SQLiteOpenHelper {
    private    static final int DATABASE_VERSION =    5;
    private    static final String    DATABASE_NAME = "product";
    private    static final String TABLE_PRODUCTS = "products";
    private static final String COLUMN_ID = "_id";
    private static final   String COLUMN_PRODUCTNAME = "productname";
    private static final String COLUMN_QUANTITY = "quantity";
    public SqliteDatabase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_PRODUCTS_TABLE = "CREATE    TABLE " + TABLE_PRODUCTS + "(" + COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_PRODUCTNAME + " TEXT," + COLUMN_QUANTITY + " INTEGER" + ")";
        db.execSQL(CREATE_PRODUCTS_TABLE);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);
        onCreate(db);
    }
    public List<Product> listProducts(){
        String sql = "select * from " + TABLE_PRODUCTS;
        SQLiteDatabase db = this.getReadableDatabase();
        List<Product> storeProducts = new ArrayList<>();
        Cursor cursor = db.rawQuery(sql, null);
        if(cursor.moveToFirst()){
            do{
                int id = Integer.parseInt(cursor.getString(0));
                String name = cursor.getString(1);
                int quantity = Integer.parseInt(cursor.getString(2));
                storeProducts.add(new Product(id, name, quantity));
            }while (cursor.moveToNext());
        }
        cursor.close();
        return storeProducts;
    }
    public void addProduct(Product product){
        ContentValues values = new ContentValues();
        values.put(COLUMN_PRODUCTNAME, product.getName());
        values.put(COLUMN_QUANTITY, product.getQuantity());
        SQLiteDatabase db = this.getWritableDatabase();
        db.insert(TABLE_PRODUCTS, null, values);
    }
    public void updateProduct(Product product){
        ContentValues values = new ContentValues();
        values.put(COLUMN_PRODUCTNAME, product.getName());
        values.put(COLUMN_QUANTITY, product.getQuantity());
        SQLiteDatabase db = this.getWritableDatabase();
        db.update(TABLE_PRODUCTS, values, COLUMN_ID    + "    = ?", new String[] { String.valueOf(product.getId())});
    }
    public Product findProduct(String name){
        String query = "Select * FROM "    + TABLE_PRODUCTS + " WHERE " + COLUMN_PRODUCTNAME + " = " + "name";
        SQLiteDatabase db = this.getWritableDatabase();
        Product mProduct = null;
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()){
            int id = Integer.parseInt(cursor.getString(0));
            String productName = cursor.getString(1);
            int productQuantity = Integer.parseInt(cursor.getString(2));
            mProduct = new Product(id, productName, productQuantity);
        }
        cursor.close();
        return mProduct;
    }
    public void deleteProduct(int id){
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_PRODUCTS, COLUMN_ID    + "    = ?", new String[] { String.valueOf(id)});
    }
}

 Product.java

As you can seen from the code above, the SqliteDatabase class uses the Product class to wrap each row in the database product table.

The product table contains three columns – id, productname and quantity.

Create a new Java class file and name it Product.java. Open this file and add the code below to it.

public class Product {
    private    int    id;
    private    String name;
    private    int    quantity;
    public Product(String name, int quantity) {
        this.name = name;
        this.quantity = quantity;
    }
    public Product(int id, String name, int quantity) {
        this.id = id;
        this.name = name;
        this.quantity = quantity;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getQuantity() {
        return quantity;
    }
    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }
}

 MainActivity class

If you are using Android Studio for this tutorial, you will notice that Android Studio has already created a default MainActivity class for us with its corresponding layout file.

In the activity_main.xml file, we will add a RecyclerView widget which will list all the product we have add. Each product listed will have two image icons which can be use to delete or edit the product.

The RecyclerView widget will use an Adapter to bind the data source from Sqlite database to each list item.

We will implement a dialog pop-up for editing and adding a product to the database.

Open the MainActivity page and add the code below to it.

import android.content.DialogInterface;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.v7.app.AlertDialog;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.text.TextUtils;
import android.view.LayoutInflater;
import android.view.View;
import android.widget.EditText;
import android.widget.FrameLayout;
import android.widget.Toast;
import com.inducesmile.androidsqliteexample.adapter.ProductAdapter;
import com.inducesmile.androidsqliteexample.database.SqliteDatabase;
import java.util.List;
public class MainActivity extends AppCompatActivity {
    private static final String TAG = MainActivity.class.getSimpleName();
    private SqliteDatabase mDatabase;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        FrameLayout fLayout = (FrameLayout) findViewById(R.id.activity_to_do);
        RecyclerView productView = (RecyclerView)findViewById(R.id.product_list);
        LinearLayoutManager linearLayoutManager = new LinearLayoutManager(this);
        productView.setLayoutManager(linearLayoutManager);
        productView.setHasFixedSize(true);
        mDatabase = new SqliteDatabase(this);
        List<Product> allProducts = mDatabase.listProducts();
        if(allProducts.size() > 0){
            productView.setVisibility(View.VISIBLE);
            ProductAdapter mAdapter = new ProductAdapter(this, allProducts);
            productView.setAdapter(mAdapter);
        }else {
            productView.setVisibility(View.GONE);
            Toast.makeText(this, "There is no product in the database. Start adding now", Toast.LENGTH_LONG).show();
        }
        FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
        fab.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                // add new quick task
                addTaskDialog();
            }
        });
    }
    private void addTaskDialog(){
        LayoutInflater inflater = LayoutInflater.from(this);
        View subView = inflater.inflate(R.layout.add_product_layout, null);
        final EditText nameField = (EditText)subView.findViewById(R.id.enter_name);
        final EditText quantityField = (EditText)subView.findViewById(R.id.enter_quantity);
        AlertDialog.Builder builder = new AlertDialog.Builder(this);
        builder.setTitle("Add new product");
        builder.setView(subView);
        builder.create();
        builder.setPositiveButton("ADD PRODUCT", new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {
                final String name = nameField.getText().toString();
                final int quantity = Integer.parseInt(quantityField.getText().toString());
                if(TextUtils.isEmpty(name) || quantity <= 0){
                    Toast.makeText(MainActivity.this, "Something went wrong. Check your input values", Toast.LENGTH_LONG).show();
                }
                else{
                    Product newProduct = new Product(name, quantity);
                    mDatabase.addProduct(newProduct);
                    //refresh the activity
                    finish();
                    startActivity(getIntent());
                }
            }
        });
        builder.setNegativeButton("CANCEL", new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {
                Toast.makeText(MainActivity.this, "Task cancelled", Toast.LENGTH_LONG).show();
            }
        });
        builder.show();
    }
    @Override
    protected void onDestroy() {
        super.onDestroy();
        if(mDatabase != null){
            mDatabase.close();
        }
    }
}

 activity_main.xml

Now, open the corresponding layout file and add the code below. You can see from the layout file that xml code is very easy to understand.

<?xml version="1.0" encoding="utf-8"?>
<FrameLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    android:id="@+id/activity_to_do"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="@color/colorWhite"
    android:padding="@dimen/_4sdp"
    tools:context="com.inducesmile.androidsqliteexample.MainActivity">
    <android.support.v7.widget.RecyclerView
        android:id="@+id/product_list"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:orientation="vertical"
        android:paddingBottom="@dimen/_8sdp"
        android:scrollbars="none" />
    <android.support.design.widget.FloatingActionButton
        android:id="@+id/fab"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="bottom|right"
        android:layout_marginRight="@dimen/_16sdp"
        android:layout_marginEnd="@dimen/_16sdp"
        android:layout_marginBottom="@dimen/_16sdp"
        android:src="@android:drawable/ic_input_add" />

</FrameLayout>

Create an Adapter Class for RecyclerView

Right now we are going to create the RecyclerView Adapter class we will use to bind the data source to RecyclerView item list.

This class will inherit fron RecyclerView Adapter and override onCreateViewHolder() and onBindViewHolder() callback methods.

Create a new package folder and name it adapter. Inside the adapter package folder, create a new Java class file and name it ProductAdapter.java.

Open the ProductAdapter.java and add the code below to it.

ProductAdapter.java

import android.app.Activity;
import android.content.Context;
import android.content.DialogInterface;
import android.support.v7.app.AlertDialog;
import android.support.v7.widget.RecyclerView;
import android.text.TextUtils;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.EditText;
import android.widget.Toast;
import com.inducesmile.androidsqliteexample.Product;
import com.inducesmile.androidsqliteexample.R;
import com.inducesmile.androidsqliteexample.database.SqliteDatabase;
import java.util.List;
public class ProductAdapter extends RecyclerView.Adapter<ProductViewHolder>{
    private Context context;
    private List<Product> listProducts;
    private SqliteDatabase mDatabase;
    public ProductAdapter(Context context, List<Product> listProducts) {
        this.context = context;
        this.listProducts = listProducts;
        mDatabase = new SqliteDatabase(context);
    }
    @Override
    public ProductViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
        View view = LayoutInflater.from(parent.getContext()).inflate(R.layout.product_list_layout, parent, false);
        return new ProductViewHolder(view);
    }
    @Override
    public void onBindViewHolder(ProductViewHolder holder, int position) {
        final Product singleProduct = listProducts.get(position);
        holder.name.setText(singleProduct.getName());
        holder.editProduct.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                editTaskDialog(singleProduct);
            }
        });
        holder.deleteProduct.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                //delete row from database
                mDatabase.deleteProduct(singleProduct.getId());
                //refresh the activity page.
                ((Activity)context).finish();
                context.startActivity(((Activity) context).getIntent());
            }
        });
    }
    @Override
    public int getItemCount() {
        return listProducts.size();
    }
    private void editTaskDialog(final Product product){
        LayoutInflater inflater = LayoutInflater.from(context);
        View subView = inflater.inflate(R.layout.add_product_layout, null);
        final EditText nameField = (EditText)subView.findViewById(R.id.enter_name);
        final EditText quantityField = (EditText)subView.findViewById(R.id.enter_quantity);
        if(product != null){
            nameField.setText(product.getName());
            quantityField.setText(String.valueOf(product.getQuantity()));
        }
        AlertDialog.Builder builder = new AlertDialog.Builder(context);
        builder.setTitle("Edit product");
        builder.setView(subView);
        builder.create();
        builder.setPositiveButton("EDIT PRODUCT", new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {
                final String name = nameField.getText().toString();
                final int quantity = Integer.parseInt(quantityField.getText().toString());
                if(TextUtils.isEmpty(name) || quantity <= 0){
                    Toast.makeText(context, "Something went wrong. Check your input values", Toast.LENGTH_LONG).show();
                }
                else{
                    mDatabase.updateProduct(new Product(product.getId(), name, quantity));
                    //refresh the activity
                    ((Activity)context).finish();
                    context.startActivity(((Activity)context).getIntent());
                }
            }
        });
        builder.setNegativeButton("CANCEL", new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {
                Toast.makeText(context, "Task cancelled", Toast.LENGTH_LONG).show();
            }
        });
        builder.show();
    }
}

ProductViewHolder.java

To use the ViewHolder pattern in our RecyclerView Adapter, we are going to create a new Java class file that will extend from the default ViewHolder class.

Create the file and name it ProductViewHolder.java. Open the file and add the code below to it.

import android.support.v7.widget.RecyclerView;
import android.view.View;
import android.widget.ImageView;
import android.widget.TextView;
import com.inducesmile.androidsqliteexample.R;
public class ProductViewHolder extends RecyclerView.ViewHolder {
    public TextView name;
    public ImageView deleteProduct;
    public  ImageView editProduct;
    public ProductViewHolder(View itemView) {
        super(itemView);
        name = (TextView)itemView.findViewById(R.id.product_name);
        deleteProduct = (ImageView)itemView.findViewById(R.id.delete_product);
        editProduct = (ImageView)itemView.findViewById(R.id.edit_product);
    }
}

 Create Layout Files

If you look at our adapter class, you will recognise that it makes use of a layout file that represents each item of the RecyclerView list.

Inside the android project layout folder, we will create two extra layout files as shown below.

add_product_layout.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:padding="@dimen/_12sdp"
    android:orientation="vertical">
    <EditText
        android:id="@+id/enter_name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="@string/enter_name"
        android:textColorHint="@color/colorBorder"
        android:textSize="@dimen/_12sdp"
        android:inputType="text"
        android:background="@drawable/brown_border"
        android:maxLines="1"
        android:layout_marginRight="@dimen/_8sdp"
        android:layout_marginEnd="@dimen/_8sdp"
        android:textColor="@color/colorBlack"
        android:padding="@dimen/_12sdp"/>
    <EditText
        android:id="@+id/enter_quantity"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="@string/enter_quantity"
        android:textColorHint="@color/colorBorder"
        android:layout_marginTop="@dimen/_24sdp"
        android:textSize="@dimen/_12sdp"
        android:inputType="number"
        android:background="@drawable/brown_border"
        android:maxLines="1"
        android:layout_marginRight="@dimen/_8sdp"
        android:layout_marginEnd="@dimen/_8sdp"
        android:textColor="@color/colorBlack"
        android:padding="@dimen/_12sdp"/>
</LinearLayout>

 product_list_layout.xml

<?xml version="1.0" encoding="utf-8"?>
<android.support.v7.widget.CardView xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:card_view="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:layout_marginBottom="1dp"
    card_view:cardElevation="@dimen/_2sdp"
    card_view:cardUseCompatPadding="true">
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:padding="@dimen/_12sdp">
        <TextView
            android:id="@+id/product_name"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="4"
            android:text="@string/product_name"
            android:textColor="@color/colorBlack"
            android:textSize="@dimen/_12sdp"
            android:textStyle="bold" />
        <ImageView
            android:id="@+id/edit_product"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_gravity="center_vertical"
            android:layout_weight="1"
            android:contentDescription="@string/app_name"
            android:src="@drawable/pedit" />
        <ImageView
            android:id="@+id/delete_product"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_gravity="center"
            android:layout_weight="1"
            android:contentDescription="@string/app_name"
            android:src="@drawable/pdelete" />
    </LinearLayout>
</android.support.v7.widget.CardView>

This brings us to the end of this tutorial. I hope that you have learn something. Run your app and see for yourself.

You can download the code for this tutorial below. If you are having hard time downloading the tutorial, kindly contact me.

Remember to subscribe with your email address to be among the first to receive my new android blog post once it is published.

OTHER INTERESTING POSTS:

One Response

Add a Comment