Android SQLite Database Introduction

Android use SQLite as it’s default built-in database. SQLite is a light weight relational database. The database operation speed is very fast and it occupied disk space is small. Usually it only need some hundred KB disk space to run.

This article will show you how to create SQLite database for your android app, and how to insert, update, delete and query data from SQLite.

1. Where Android SQLite Database Is Saved In.

Because SQLite is embedded database for android, so each android app has it’s own SQLite database.

Like Shared Preferences, the SQLite database is located under /data/data/<app package name>/databases folder in android device.

Each database is a file with .db file extension. In below example, the database file is BookInfo.db.

browse sqlite database file in android device monitor

1.1 How To Explore SQLite Database File In Android Emulator.

You need use android device monitor to explore the db file. Please refer Android Device Monitor Cannot Open Data Folder Resolve Method to learn more.

But you need to start android emulator first and run below command in dos window to change the folder permission to explore. Otherwise the folder is not clickable in android device monitor.

C:\Users\Jerry>adb shell
generic_x86:/ $ su
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

2. How To Create SQLite Database In Android Application.

2.1 Create SQLiteOpenHelper Instance.

android.database.sqlite.SQLiteOpenHelper is an abstract class that contains methods to create database.

Because it is abstract, so you should extends it to create a sub class and use it’s methods as below.

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

The class SQLiteDBHelper is just the sub class of SQLiteOpenHelper, there are four input parameters in the constructor.

  1. Context context : This is the activity context object.
  2. String dbName : This is the SQLite database name (BookInfo.db).
  3. SQLiteDatabase.CursorFactory factory : This is the custom cursor when execute query, usually we set it to null.
  4. int version : This is the current database version. If this value is less than exist db version, then an exception will be thrown, because SQLite db is not allowed to downgrade.
    sqlite can not downgrade database exception
READ :   Android Sharedpreferences Save/Load Java Object Example

2.2 Invoke SQLiteOpenHelper Methods To Create Database.

SQLiteOpenHelper provide below methods to create SQLite database.

  1. getWritableDatabase() : This method will open an database if exist, if not exist then it will create a new one. The return value is a SQLiteDatabase object that make connection with the SQLite DB.But when the database is not writable for example disk space is full, then it will throw an exception.
    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.
    SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
  2. getReadableDatabase() : This method is similar as getWritableDatabase(), the only difference is that if the disk space is full, it will not throw an exception, instead it will return a read only SQLiteDatabase object.
  3. onCreate(SQLiteDatabase sqLiteDatabase) : This method is invoked when you call getWritableDatabase() or getReadableDatabase() methods. Commonly you can execute create table sql command in this method.
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String createBookTableSql = "create table book(title text, price real)";
        sqLiteDatabase.execSQL(createBookTableSql);
    }
  4. onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) : This method is invoked when you call getWritableDatabase() or getReadableDatabase() methods and set the db version higher than exist db version when create the SQLiteOpenHelper instance. Commonly we execute drop table sql commands in this method, then call onCreate() method again to create table.
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        // Drop table first if exist.
        sqLiteDatabase.execSQL("drop table if exists " + BOOK_TABLE_NAME);
        sqLiteDatabase.execSQL("drop table if exists " + CATEGORY_TABLE_NAME);
    
        this.onCreate(sqLiteDatabase);
    }
  5. close() : This method will close the SQLite database connection. Generally call it in activity’s onDestroy() method.
READ :   Android Multi AutoComplete TextView Example

3. How To Run Sql Commands.

SQLiteDatabase provide below methods to run sql commands after you get it via getWritableDatabase() method.

  1. insert(String tableName, null, ContentValues contentValues) : Insert data into table.
    sqLiteDatabase.insert(SQLiteDBHelper.BOOK_TABLE_NAME, null, contentValues);
  2. update(String tableName, ContentValues content_values, String where_clause, String[] where_caluse_place_holder_value_array) : Update table rows filtered by where clause.
    sqLiteDatabase.update(SQLiteDBHelper.BOOK_TABLE_NAME, contentValues, "title = ?", new String[]{"Learn Android In 21 Days."});
  3. delete(String tableName, ContentValues content_values, String where_clause, String[] where_caluse_place_holder_value_array) : Delete table rows filtered by where clause.
    sqLiteDatabase.delete(SQLiteDBHelper.BOOK_TABLE_NAME, " price = ?", new String[]{"99"});
  4. query(String tableName, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) : Query the table and return a cursor to fetch the row data.
    SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
    Cursor cursor = sqLiteDatabase.query(SQLiteDBHelper.BOOK_TABLE_NAME, null, null, null, null, null, null);
  5. execSQL(String sql) : Execute custom sql command.
    SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
    sqLiteDatabase.execSQL(createBookTableSql);

To learn more methods, please refer
https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

4. Use ADB Shell And SQLite3 To Query SQLite Table.

You can use adb shell and sqlite3 to run sql commands to brow db, table etc. Below commands in a dos window can show db schema, list table and query table data.

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> .table
android_metadata book
sqlite> select * from book;
sqlite> select * from book;
1|Android|Learn Android In 21 Days.|Jerry|100.0
2|Android|1000 Android Examples.|Richard|99.0
3|Android|Android Util Tool Handbook.|Michael|89.0
sqlite>

To learn more sqlite3 commands, type .help in sqlite > prompt command line.

adb shell sqlite3 operate sqlite database table command

(Visited 1,249 times, 5 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.