How to use a pre-populated database in Android?

Using local databases for persisting data is fairly easy in Android because of the in-built SQLite Database it provides. If you have used it before, you would know that you would have to extend a helper class named ‘SQLiteOpenHelper’ for the purpose. The SQLiteOpenHelper class is a part of the Native Android library, therefore you wouldn’t have to add any additional dependencies to work with them.

But what if you want to use a database that comes shipped with the app? That is, you want the database in the app to already contain the data that you are looking for. You wouldn’t be able to do that with SQLiteOpenHelper.

But there exists a library for that exact purpose. SQLiteAssetHelper.

SQLiteAssetHelper

The Android SQLiteAssetHelper library allows you to build your SQLite database in your desktop computer, and to import and use it in your Android application. Let’s create a simple application to demonstrate the application of this library.

Step 1: Create a database quotes.db using your favorite SQLite database application (DB Browser for SQLite is a portable cross-platform freeware, which can be used to create and edit SQLite databases). Create a table ‘quotes’ with a single column ‘quote’. Insert some random quotes into the table ‘quotes’.

Step 2: The database can be imported into project either directly as it is, or as a compressed file. The compressed file is recommended if your database is too large in size. You can create either a ZIP compression or a GZ compression.

The file name of the compressed db file must be quotes.db.zip, if you are using ZIP compression or quotes.db.gz, if you are using GZ compression.

Step 3: Create a new application External Database Demo with a package name com.javahelps.com.javahelps.externaldatabasedemo.

Step 4: Open the build.gradle (Module: app) file and add the following dependency.

dependencies {
compile 'com.readystatesoftware.sqliteasset:sqliteassethelper:+'
}
Once you have saved the build.gradle file click on the 'Sync Now' link to update the project. You can synchronize the build.gradle, by right clicking on the build.gradle file and selecting Synchronize
build.gradle option as well.

Step 5: Right click on the app folder and create new assets folder.

Step 6: Create a new folder ‘databases’ inside the assets folder.

Step 7: Copy and paste the quotes.db.zip file inside the assets/databases folder.

Step 8: Create a new class DatabaseOpenHelper

package com.javahelps.externaldatabasedemo;

import android.content.Context;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

public class DatabaseOpenHelper extends SQLiteAssetHelper {
    private static final String DATABASE_NAME = "quotes.db";
    private static final int DATABASE_VERSION = 1;

    public DatabaseOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
}

Notice that rather than extending SQLiteOpenHelper, the DatabaseOpenHelper extends SQLiteAssetHelper class.

Step 9: Create a new class DatabaseAccess and enter the code as shown below. More details about this class is available at Advanced Android Database tutorial.

package com.javahelps.externaldatabasedemo;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

public class DatabaseAccess {
    private SQLiteOpenHelper openHelper;
    private SQLiteDatabase database;
    private static DatabaseAccess instance;

    /**
     * Private constructor to aboid object creation from outside classes.
     *
     * @param context
     */
    private DatabaseAccess(Context context) {
        this.openHelper = new DatabaseOpenHelper(context);
    }

    /**
     * Return a singleton instance of DatabaseAccess.
     *
     * @param context the Context
     * @return the instance of DabaseAccess
     */
    public static DatabaseAccess getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseAccess(context);
        }
        return instance;
    }

    /**
     * Open the database connection.
     */
    public void open() {
        this.database = openHelper.getWritableDatabase();
    }

    /**
     * Close the database connection.
     */
    public void close() {
        if (database != null) {
            this.database.close();
        }
    }

    /**
     * Read all quotes from the database.
     *
     * @return a List of quotes
     */
    public List<String> getQuotes() {
        List<String> list = new ArrayList<>();
        Cursor cursor = database.rawQuery("SELECT * FROM quotes", null);
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            list.add(cursor.getString(0));
            cursor.moveToNext();
        }
        cursor.close();
        return list;
    }
}

In this class, only the `getQuotes` method is implemented to read the data from the database. You have the full freedom to insert,
update and delete any rows in the database as usual. For more details, follow this link Advanced Android Database.

All the database related setups are completed and now we need to create a ListView to display the quotes.

Step 10: Add a ListView in your activity_main.xml.

<FrameLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".MainActivity">

    <ListView
        android:id="@+id/listView"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_gravity="center" />
</FrameLayout>

Step 11: Find the object of ListView in the onCreate method of MainActivity and feed the quotes which are read from the database.

package com.javahelps.externaldatabasedemo;

import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.widget.ArrayAdapter;
import android.widget.ListView;

import java.util.List;


public class MainActivity extends ActionBarActivity {
    private ListView listView;

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

        this.listView = (ListView) findViewById(R.id.listView);
        DatabaseAccess databaseAccess = DatabaseAccess.getInstance(this);
        databaseAccess.open();
        List<String> quotes = databaseAccess.getQuotes();
        databaseAccess.close();

        ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,
quotes);
        this.listView.setAdapter(adapter);
    }
}