Android SQLite CRUD Operations Examples

Leave a Comment / Android Tutorial

This article contains examples about how to create an SQLite database, how to create a table, and how to insert, update, delete, query SQLite tables. You can read the article Android SQLite Database Introduction for general SQLite concepts.

1. Android SQLite CRUD Example.

If you can not watch the above video, you can see it on the youtube URL https://youtu.be/BNuhInM6IfM

1.1 Create SQLite Database Example.

  1. There are six buttons on the screen. When you click the first button, it will use the below java code to create the database file BookInfo.db.
  2. The SQLiteDBHelper class is a sub class of android.database.sqlite.SQLiteOpenHelper 
    SQLiteDBHelper sqLiteDBHelper = new SQLiteDBHelper(getApplicationContext(), DB_NAME, null, DB_VERSION);

sqLiteDBHelper.getWritableDatabase();
  3. When the getWritableDatabase() method is called, it will also invoke SQLiteDBHelper‘s onCreate(SQLiteDatabase sqLiteDatabase) method.
  4. In this method, it will execute SQL commands to create a book table. 
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
    this.buildCreateTableSql();
    sqLiteDatabase.execSQL(createBookTableSql);

    Toast.makeText(ctx, "Table " + BOOK_TABLE_NAME + " is created successfully. ", Toast.LENGTH_SHORT).show();

    // Create book category table only when sqlite upgrade.
    if(isUpgrade) {
        sqLiteDatabase.execSQL(createCategoryTableSql);
        Toast.makeText(ctx, "Table " + CATEGORY_TABLE_NAME + " is created successfully. ", Toast.LENGTH_SHORT).show();
    }
}
  5. The database file is saved in /data/data/com.dev2qa.example/databases folder as below.
  6. You need to use the android device monitor to see the above database file. Please refer article Android Device Monitor Cannot Open Data Folder Resolve Method
  7. You also need to run the below command to change database folder access permission to explore them. 
    C:\Users\Jerry>adb shell
generic_x86:/ $ su
generic_x86:/ # chmod 777 /data
generic_x86:/ # chmod 777 /data/data
generic_x86:/ # chmod 777 /data/data/com.dev2qa.example/
generic_x86:/ # chmod 777 /data/data/com.dev2qa.example/databases/
  8. But if the database already exists, and if the existing database version is bigger than the newly created, you will see below database downgrade error message. 
    Can't downgrade database from version 3 to 1, please remove sqlite database by uninstall this app first.
  9. You can also see the database downgrade exception in the android monitor console output. The error message is Can’t downgrade database from version 3 to 1.
  10. To resolve this problem, you need to use the adb shell to remove the existing database as below. 
    C:\Users\Jerry>adb shell
generic_x86:/ $ su
generic_x86:/ # rm /data/data/com.dev2qa.example/databases/*
  11. Then you can create the SQLite database file BookInfo.db again, it also creates the table book. You can use the below command to see the table creation SQL commands. 
    C:\Users\Jerry>adb shell
generic_x86:/ $ su
generic_x86:/ # sqlite3 /data/data/com.dev2qa.example/databases/BookInfo.db
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE book( id integer primary key autoincrement, category_name text, title text, author text, price real );
sqlite>

If you can not watch the above video, you can see it on the youtube URL https://youtu.be/T-kqZQ13vbI

1.2 Upgrade SQLite Database Example.

  1. When you click the second button, it will create the same database with a bigger database version. 
    // Database version plus one.
int DB_VERSION += 1;

// Create instance of SQLiteDBHelper again, because the database version increased,
// so below code will trigger SQLiteDBHelper's onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) method.
SqLiteDBHelper sqLiteDBHelper = new SQLiteDBHelper(getApplicationContext(), DB_NAME, null, DB_VERSION);

// Create the database tables again, this time because database version increased so the onUpgrade() method is invoked.
sqLiteDBHelper.getWritableDatabase();
  2. So the SQLiteOpenHelper sub-class’s onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) method will be invoked.
  3. In the onUpgrade() method, it will drop all existing tables and invoke the onCreate() method to create the book and category table again. 
    /* This method will be invoked when newVersion is bigger than oldVersion.*/
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {

    Toast.makeText(ctx, "SQLiteDBHelper onUpgrade() method is invoked.", Toast.LENGTH_SHORT).show();

    // Drop table first if exist.
    sqLiteDatabase.execSQL("drop table if exists " + BOOK_TABLE_NAME);
    sqLiteDatabase.execSQL("drop table if exists " + CATEGORY_TABLE_NAME);

    if(newVersion > oldVersion)
    {
        this.isUpgrade = true;
    }

    this.onCreate(sqLiteDatabase);
}
  4. You can use the sqlite3 .schema command to see the table creation SQL. The android_metadata table is the SQLite default table. 
    C:\Users\Jerry>adb shell
generic_x86:/ $ su
generic_x86:/ # sqlite3 /data/data/com.dev2qa.example/databases/BookInfo.db
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE book( id integer primary key autoincrement, category_name text, title text, author text, price real );
CREATE TABLE category( id integer primary key autoincrement, category_name text );
sqlite>

1.3 Insert Data Into SQLite Table.

  1. Click the third button will insert data into the book and category table. SQLiteDatabase‘s insert() method will do this task. 
    SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
ContentValues contentValues = new ContentValues();

String categoryName = "Android";
// Insert data into category table first.
contentValues.put("category_name", categoryName);
sqLiteDatabase.insert(SQLiteDBHelper.CATEGORY_TABLE_NAME, null, contentValues);

1.4 Update Data In SQLite Table.

  1. Click the fourth button, it will update the book table in SQLite database use SQLiteDatabase‘s update() method. 
    SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
ContentValues contentValues = new ContentValues();

contentValues.put("price", 66);
sqLiteDatabase.update(SQLiteDBHelper.BOOK_TABLE_NAME, contentValues, "title = ?", new String[]{"Learn Android In 21 Days."});

1.5 Delete Data In SQLite Table.

  1. The fifth button can delete data in the book table when being clicked. It will invoke SQLiteDatabase‘s delete() method. 
    SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
sqLiteDatabase.delete(SQLiteDBHelper.BOOK_TABLE_NAME, " price = ?", new String[]{"99"});

1.6 Query SQLite Table Data.

  1. Click the sixth button will query the data in the book table, it will also print each row of data in the android monitor console.
  2. SQLiteDatabase‘s query() method will return a Cursor object, we can loop the cursor to fetch each row of data. 
    SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
Cursor cursor = sqLiteDatabase.query(SQLiteDBHelper.BOOK_TABLE_NAME, null, null, null, null, null, null);

2. SQLite CRUD Example Source Code.

2.1 Main Layout Xml File.

  1. activity_sqlite_crud.xml 
    <LinearLayout
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="vertical">

    <Button
        android:id="@+id/sqlite_create_db_table_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Create DB Add Book Table"/>

    <Button
        android:id="@+id/sqlite_upgrade_db_table_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Upgrade DB Add Category Table"/>

    <Button
        android:id="@+id/sqlite_insert_data_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Insert Data"/>

    <Button
        android:id="@+id/sqlite_update_data_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Update Book Data"/>

    <Button
        android:id="@+id/sqlite_delete_data_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Delete Book Data"/>

    <Button
        android:id="@+id/sqlite_query_data_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Query Book Data"/>

</LinearLayout>

2.2 Activity Java File.

  1. Do not forget to close the SQLite database connection in activity’s onDestroy() method.
  2. SQLiteCRUDActivity.java 
    package com.dev2qa.example.storage.sqlite;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

import com.dev2qa.example.R;

public class SQLiteCRUDActivity extends AppCompatActivity {

    private SQLiteDBHelper sqLiteDBHelper = null;

    private String DB_NAME = "BookInfo.db";

    private int DB_VERSION = 1;

    private String TABLE_NAME = "book";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite_crud);

        setTitle("dev2qa.com - Android SQLite Database CRUD Example.");

        // Click this button to create a sqlite database and add book table.
        Button createDBButton = (Button)findViewById(R.id.sqlite_create_db_table_button);
        createDBButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                // Initialize an instance of SQLiteDBHelper.
                sqLiteDBHelper = new SQLiteDBHelper(getApplicationContext(), DB_NAME, null, DB_VERSION);

                if(!hasDBVersionError()) {

                    // This line of code will create the database if not exist. If exist then it will do nothing.
                    // When database is created, the sqLiteDBHelper's onCreate() method will also be invoked,
                    // so book table will be created.
                    sqLiteDBHelper.getWritableDatabase();

                    Toast.makeText(getApplicationContext(), "SQLite database " + DB_NAME + " create successfully.", Toast.LENGTH_LONG).show();
                }
            }
        });

        // Click this button to upgrade sqlite database add category table.
        Button upgradeTableButton = (Button)findViewById(R.id.sqlite_upgrade_db_table_button);
        upgradeTableButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                if(!hasDBVersionError()) {

                    // Database version plus one.
                    DB_VERSION += 1;

                    // Create instance of SQLiteDBHelper again, because the database version increased,
                    // so below code will trigger SQLiteDBHelper's onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) method.
                    sqLiteDBHelper = new SQLiteDBHelper(getApplicationContext(), DB_NAME, null, DB_VERSION);

                    // Create the database tables again, this time because the database version increased so the onUpgrade() method is invoked.
                    sqLiteDBHelper.getWritableDatabase();

                    Toast.makeText(getApplicationContext(), "SQLite database " + DB_NAME + " upgrade successfully.", Toast.LENGTH_LONG).show();
                }
            }
        });

        // Click this button to insert data in book, category table.
        Button insertTableButton = (Button)findViewById(R.id.sqlite_insert_data_button);
        insertTableButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if(sqLiteDBHelper!=null) {
                    // Create the database tables again, this time because database version increased so the onUpgrade() method is invoked.
                    SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
                    ContentValues contentValues = new ContentValues();

                    String categoryName = "Android";
                    // Insert data into category table first.
                    contentValues.put("category_name", categoryName);
                    sqLiteDatabase.insert(SQLiteDBHelper.CATEGORY_TABLE_NAME, null, contentValues);
                    Toast.makeText(getApplicationContext(), "Insert data into category table successfully.", Toast.LENGTH_LONG).show();

                    // Then insert first row into book table.
                    contentValues.clear();
                    contentValues.put("category_name", categoryName);
                    contentValues.put("title", "Learn Android In 21 Days.");
                    contentValues.put("author", "Jerry");
                    contentValues.put("price", 100);
                    sqLiteDatabase.insert(SQLiteDBHelper.BOOK_TABLE_NAME, null, contentValues);

                    // Then insert second row into book table.
                    contentValues.clear();
                    contentValues.put("category_name", categoryName);
                    contentValues.put("title", "1000 Android Examples.");
                    contentValues.put("author", "Richard");
                    contentValues.put("price", 99);
                    sqLiteDatabase.insert(SQLiteDBHelper.BOOK_TABLE_NAME, null, contentValues);

                    // Then insert third row into book table.
                    contentValues.clear();
                    contentValues.put("category_name", categoryName);
                    contentValues.put("title", "Android Util Tool Handbook.");
                    contentValues.put("author", "Michael");
                    contentValues.put("price", 89);
                    sqLiteDatabase.insert(SQLiteDBHelper.BOOK_TABLE_NAME, null, contentValues);

                    Toast.makeText(getApplicationContext(), "Insert data into book table successfully.", Toast.LENGTH_LONG).show();
                }else
                {
                    Toast.makeText(getApplicationContext(), "Please create database first.", Toast.LENGTH_LONG).show();
                }
            }
        });

        // Click this button to update book price data.
        Button updateTableButton = (Button)findViewById(R.id.sqlite_update_data_button);
        updateTableButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if(sqLiteDBHelper!=null) {
                    // Create the database tables again, this time because database version increased so the onUpgrade() method is invoked.
                    SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
                    ContentValues contentValues = new ContentValues();

                    contentValues.put("price", 66);
                    sqLiteDatabase.update(SQLiteDBHelper.BOOK_TABLE_NAME, contentValues, "title = ?", new String[]{"Learn Android In 21 Days."});
                    Toast.makeText(getApplicationContext(), "Update book table price column successfully.", Toast.LENGTH_LONG).show();
                }else
                {
                    Toast.makeText(getApplicationContext(), "Please create database first.", Toast.LENGTH_LONG).show();
                }
            }
        });

        // Click this button to delete one row in book table.
        Button deleteTableButton = (Button)findViewById(R.id.sqlite_delete_data_button);
        deleteTableButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if(sqLiteDBHelper!=null) {
                    // Create the database tables again, this time because database version increased so the onUpgrade() method is invoked.
                    SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
                    sqLiteDatabase.delete(SQLiteDBHelper.BOOK_TABLE_NAME, " price = ?", new String[]{"99"});
                    Toast.makeText(getApplicationContext(), "Update book table price data successfully.", Toast.LENGTH_LONG).show();
                }else
                {
                    Toast.makeText(getApplicationContext(), "Please create database first.", Toast.LENGTH_LONG).show();
                }
            }
        });

        // Click this button to query book table rows and print in android monitor console.
        Button queryTableButton = (Button)findViewById(R.id.sqlite_query_data_button);
        queryTableButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if(sqLiteDBHelper!=null) {
                    // Create the database tables again, this time because database version increased so the onUpgrade() method is invoked.
                    SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
                    Cursor cursor = sqLiteDatabase.query(SQLiteDBHelper.BOOK_TABLE_NAME, null, null, null, null, null, null);

                    boolean hasRecord = cursor.moveToFirst();
                    if(hasRecord)
                    {
                        do{
                            int id = cursor.getInt(cursor.getColumnIndex("id"));
                            String title = cursor.getString(cursor.getColumnIndex("title"));
                            String author = cursor.getString(cursor.getColumnIndex("author"));
                            int price = cursor.getInt(cursor.getColumnIndex("price"));

                            StringBuffer bookInfoBuf = new StringBuffer();
                            bookInfoBuf.append("book id : ");
                            bookInfoBuf.append(id);
                            bookInfoBuf.append(" , title : ");
                            bookInfoBuf.append(title);
                            bookInfoBuf.append(" , author ");
                            bookInfoBuf.append(author);
                            bookInfoBuf.append(" , price ");
                            bookInfoBuf.append(price);

                            Log.d(SQLiteDBHelper.LOG_TAG_SQLITE_DB, bookInfoBuf.toString());

                        }while(cursor.moveToNext());
                    }

                    Toast.makeText(getApplicationContext(), "Look at android monitor console to see the query result.", Toast.LENGTH_LONG).show();
                }else
                {
                    Toast.makeText(getApplicationContext(), "Please create database first.", Toast.LENGTH_LONG).show();
                }
            }
        });
    }

    /*
    *  Check SQLite database version, if the current version is bigger than DB_VERSION,
    *  then drop the existing database. Otherwise a "Can't downgrade database from version 3 to 2" exception
    *  will occur.
    * */
    private boolean hasDBVersionError()
    {
        boolean ret = false;
        try
        {
            SQLiteDatabase sqliteDatabase = sqLiteDBHelper.getReadableDatabase();
        }catch(SQLiteException ex)
        {
            ret = true;

            String errorMessage = ex.getMessage();

            Log.d(SQLiteDBHelper.LOG_TAG_SQLITE_DB, errorMessage, ex);

            if(errorMessage.startsWith("Can't downgrade database from version"))
            {
                Toast.makeText(getApplicationContext(), errorMessage + " , please remove sqlite database by uninstall this app first.", Toast.LENGTH_LONG).show();
            }else
            {
                Toast.makeText(getApplicationContext(), "Create db error, error message is " + errorMessage, Toast.LENGTH_LONG).show();
            }
        }finally {
            return ret;
        }
    }

    @Override
    protected void onDestroy() {
      super.onDestroy();
      if(sqLiteDBHelper!=null)
      {
         //Close the sqlite database connection.
         sqLiteDBHelper.close();
         sqLiteDBHelper = null;
      }
    }
}

2.3 SQLite Database Operation Java File.

  1. SQLiteDBHelper.java
  2. This java class is a subclass of SQLiteOpenHelper class, it provides methods to implement database CRUD operations. 
    package com.dev2qa.example.storage.sqlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class SQLiteDBHelper extends SQLiteOpenHelper {

    private Context ctx;

    //
    private String createBookTableSql = "";

    private String createCategoryTableSql = "";

    private boolean isUpgrade = false;

    public static final String BOOK_TABLE_NAME = "book";

    public static final String CATEGORY_TABLE_NAME = "category";

    public static final String LOG_TAG_SQLITE_DB = "LOG_TAG_SQLITE_DB";

    /*
    *  context : Android activity context object.
    *  name : SQLite database name.
    *  factory : CursorFactory object, generally is null.
    *  version : SQLite database version.
    * */
    public SQLiteDBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        ctx = context;
    }

    /*
    *  This method executes create table SQL command for the SQLite database.
    *  It is invoked when SQLiteDBHelper instance is created.
    * */
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        this.buildCreateTableSql();
        sqLiteDatabase.execSQL(createBookTableSql);

        Toast.makeText(ctx, "Table " + BOOK_TABLE_NAME + " is created successfully. ", Toast.LENGTH_SHORT).show();

        // Create book category table only when sqlite upgrade.
        if(isUpgrade) {
            sqLiteDatabase.execSQL(createCategoryTableSql);
            Toast.makeText(ctx, "Table " + CATEGORY_TABLE_NAME + " is created successfully. ", Toast.LENGTH_SHORT).show();
        }
    }

    /* This method will be invoked when newVersion is bigger than oldVersion.*/
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {

        Toast.makeText(ctx, "SQLiteDBHelper onUpgrade() method is invoked.", Toast.LENGTH_SHORT).show();

        // Drop table first if exist.
        sqLiteDatabase.execSQL("drop table if exists " + BOOK_TABLE_NAME);
        sqLiteDatabase.execSQL("drop table if exists " + CATEGORY_TABLE_NAME);

        if(newVersion > oldVersion)
        {
            this.isUpgrade = true;
        }

        this.onCreate(sqLiteDatabase);
    }

    // Build all the create table SQL because each app has it's own database,
    // so we manage all create table SQL command in one method.
    private void buildCreateTableSql()
    {
        // Build create book table SQL.
        StringBuffer bookSqlBuf = new StringBuffer();

        // Create table sql.
        bookSqlBuf.append("create table ");
        bookSqlBuf.append(BOOK_TABLE_NAME);
        bookSqlBuf.append("( id integer primary key autoincrement,");
        bookSqlBuf.append(" category_name text,");
        bookSqlBuf.append(" title text,");
        bookSqlBuf.append(" author text,");
        bookSqlBuf.append(" price real )");

        createBookTableSql = bookSqlBuf.toString();


        // Build create category table SQL.
        StringBuffer categorySqlBuf = new StringBuffer();

        // Create table sql.
        categorySqlBuf.append("create table ");
        categorySqlBuf.append(CATEGORY_TABLE_NAME);
        categorySqlBuf.append("( id integer primary key autoincrement,");
        categorySqlBuf.append(" category_name text )");

        createCategoryTableSql = categorySqlBuf.toString();
    }
}

Leave a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.