Android SQLite Database Introduction

Android uses SQLite as it’s default built-in database. SQLite is a lightweight relational database. The database operation speed is very fast and its occupied disk space is small. Usually, it only needs some hundred KB of disk space to run.

This article will show you how to create an 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.

1. Because SQLite is an embedded database for android, so each android app has it’s own SQLite database.
2. Like Shared Preferences, the SQLite database is located under /data/data/<app package name>/databases folder on android devices.
3. Each database is a file with a .db file extension. In the below example, the database file is BookInfo.db.
   

1.1 How To Explore SQLite Database File In Android Emulator.

1. You need to use an android device monitor to explore the DB file. Please refer Android Device Monitor Cannot Open Data Folder Resolve Method to learn more.
2. But you need to start the android emulator first and run the below command in the dos window to change the folder permission to explore. Otherwise, the folder is not clickable on the 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

2. How To Create SQLite Database In Android Application.

2.1 Create SQLiteOpenHelper Instance.

1. android.database.sqlite.SQLiteOpenHelper is an abstract class that contains methods to create an SQLite database.
2. Because it is abstract, so you should extend it to create a sub-class and use it’s methods as below.

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

3. The class SQLiteDBHelper is just the sub-class of SQLiteOpenHelper, there are four input parameters in the constructor.
4. Context context: This is the activity context object.
5. String dbName: This is the SQLite database name (BookInfo.db).
6. SQLiteDatabase.CursorFactory factory: This is the custom cursor when executing a query, usually we set it to null.
7. 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. Below is the exception error message.

   android.database.sqlite.SQLiteException: Can't downgrade database from version 3 to 2.

2.2 Invoke SQLiteOpenHelper Methods To Create Database.

1. SQLiteOpenHelper provides the below methods to create an SQLite database.
2. getWritableDatabase(): This method will open a database if exist, if not exist then it will create a new one. The return value is an SQLiteDatabase object that makes connections 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 the database version increased so the onUpgrade() method is invoked.
   SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();

3. getReadableDatabase(): This method is similar to 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.
4. onCreate(SQLiteDatabase sqLiteDatabase) : This method is invoked when you call getWritableDatabase() or getReadableDatabase() methods. Commonly you can execute the 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);
   }

5. 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 the onCreate() method again to create the 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);
   }

6. close(): This method will close the SQLite database connection. Generally call it in activity’s onDestroy() method.

3. How To Run SQL Commands.

1. SQLiteDatabase object provides the below methods to run SQL commands after you get it via the getWritableDatabase() method.
2. insert(String tableName, null, ContentValues contentValues) : Insert data into table.

   sqLiteDatabase.insert(SQLiteDBHelper.BOOK_TABLE_NAME, null, contentValues);

3. 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."});

4. 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"});

5. 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);

6. execSQL(String sql) : Execute custom sql command.

   SQLiteDatabase sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();
   sqLiteDatabase.execSQL(createBookTableSql);

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

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

1. You can use ADB shell and sqlite3 to run SQL commands to brow DB, table, etc. Below commands in a dos window can show SQLite DB schema, list tables, 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>

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