Android SQLite CRUD Operations Examples

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

1. Android SQLite CRUD Example.

android sqlite crud example

1.1 Create SQLite Database Example.

There are six button in the screen. When you click the first button, it will use below java code to create database BookInfo.db.

The SQLiteDBHelper class is a sub class of android.database.sqlite.SQLiteOpenHelper

SQLiteDBHelper sqLiteDBHelper = new SQLiteDBHelper(getApplicationContext(), DB_NAME, null, DB_VERSION);

sqLiteDBHelper.getWritableDatabase();

When getWritableDatabase() method is called, it will also invoke SQLiteDBHelper’s onCreate(SQLiteDatabase sqLiteDatabase) method. In this method it will execute sql commands to create 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();
    }
}

The database file is saved in /data/data/com.dev2qa.example/databases folder as below.

browse sqlite database file in android device monitor

You need to use android device monitor to see above database file. Please refer article Android Device Monitor Cannot Open Data Folder Resolve Method

You also need to run 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/

change database folder permission using adb

But if the database is already exist, and if the exist database version is bigger than newly created, you will see below database downgrade error message.

android sqlite database downgrade error

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 2.

sqlite can not downgrade database exception

To resolve this problem, you need to use adb shell to remove the exist database as below.

C:\Users\Jerry>adb shell
generic_x86:/ $ su
generic_x86:/ # rm /data/data/com.dev2qa.example/databases/*

Then you can create the database BookInfo.db again, it also create table book. You can use below command to see the table create 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>

1.2 Upgrade SQLite Database Example.

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();

So the SQLiteOpenHelper sub class’s onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) method will be invoked.

In onUpgrade() method, it will drop all exist tables and invoke onCreate() method to create 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);
}

You can use sqlite3 .schema command to see the table creation sql. The android_metadata table is 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.

Click the third button will insert data into 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.

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.

The fifth button can delete data in 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.

Click the sixth button will query the data in book table, it will also print each row data in android monitor console.

READ :   How To Show Data From SQLite Database In Android ListView

SQLiteDatabase‘s query() method will return a Cursor object, we can loop the cursor to fetch each row data.

SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
Cursor cursor = sqLiteDatabase.query(SQLiteDBHelper.BOOK_TABLE_NAME, null, null, null, null, null, null);

sqlite query result in android monitor console output

2. SQLite CRUD Example Source Code.

2.1 Main Layout Xml File.

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.

Do not forget close the SQLite database connection in activity’s onDestroy() method.

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 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 current version is bigger than DB_VERSION,
    *  then drop the exist 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.

SQLiteDBHelper.java

This java class is sub class of SQLiteOpenHelper class, it provide 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;

/**
 * Created by Jerry on 1/12/2018.
 */

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 execute create table sql command for 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();
    }
}
(Visited 4,772 times, 22 visits today)

Leave a Reply

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.