Overview

This tutorial assumes that you know how to store and retrieve data from SQLite on the Android platform. If not, see this tutorial.

Sometimes, you need to ship your own data to the mobile device. For my Android app, iTransit, I have to upload the planned bus & metro time schedules into the mobile devices so that the users can still consult them even when they are offline.

Shipping your own data to the mobile device is not that hard, as soon as, you know when SQLite database file is created. When extending SQLiteOpenHelper class, you might have the impression that SQLite database file is created in the onCreate() function. Well, it is not. When you are in onCreate() function, an empty database file is already created.

To tell your extended SQLiteOpenHelper class to use your own database file, you have to replace the database file at the constructor level. But, before this, you have to prepare your database so that Android can use it.

Prepare your database

• Create an assets folder for your application.
  • From Android Studio, right-click on your app. Then, select New->Folder->Assets Folder.
• Add your existing database file in the assets folder.

Implement DBHelper class to use your database file

Basically, what the code below does is:

In the constructor, if the application database file doesn't exist, then copy your existing database from the assets folder to the designated application database file location. That's it.

public class DBHelper extends SQLiteOpenHelper {
 
    private static final String dbName="appDatabase.db";
    private static final int dbVersion=1;
    private Context context;
    public DBHelper(Context content){
        super(content, dbName, null, dbVersion);
        this.context = content;
 
        this.copyDbIfNotExists();
    }
    private void copyDbIfNotExists()
    {
        // Ensure /data/data/YOUR_PACKAGE_NAME/databases/ directory is created.
        File dbDir = new File(context.getDatabasePath(dbName).getParentFile().getPath());
        if (!dbDir.exists())
            dbDir.mkdir();
 
        // Copy database starts here.
        String appDbPath = this.context.getDatabasePath(dbName).getAbsolutePath();
        File dbFile = new File(appDbPath);
        if(!dbFile.exists()){
            try {
                InputStream mInput = context.getAssets().open("myExistingDb.db");
                OutputStream mOutput = new FileOutputStream(appDbPath);
                byte[] mBuffer = new byte[1024];
                int mLength;
                while ((mLength = mInput.read(mBuffer, 0, 1024)) > 0)
                    mOutput.write(mBuffer, 0, mLength);
                mOutput.flush();
 
                mOutput.close();
                mInput.close();
            }
            catch (IOException ex){
                throw new Error("Error copying database: "+ex.getMessage());
            }
        }
    }
 
    @Override
    public void onCreate(SQLiteDatabase db) {
        // Nothing to do. Use existing database.
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Upgrade.
    }
}

Troubleshooting

• If your existing database seems not to be used, then uninstall the application and run it again. Executing and moving around the code above may have created an empty SQLite database file. Therefore, your existing database is not copied over. Execution order and initial state is important here.

• If you are getting an error about locale, then android_metadata table is missing from your database. Usually, Android should automatically add the android_metadata table for you. If not, you can manually add it as follows:

CREATE TABLE android_metadata (locale TEXT);
INSERT INTO android_metadata (locale) VALUES('en_US');

• You can retrieve your application database file using the Device File Explorere of your Android Studio. It is located at /data/data/YOUR_PACKAGE_NAME/databases/YOUR_APP_DB_NAME. Right-click on the file and save it locally so you can investigate further more.

Github

• https://github.com/xuanngo2001/android-sqlite-existing-database