How can I embed an SQLite database into an application?

Question

I think I have some basic understanding problem so maybe someone's able to help :-)

I'm developing an Android application using Eclipse and this application will make use of a database (only reading from the database will be implemented). The database contains around 4,000 entries i.e. creating and populating the database via source code is not an option. Thus I have created the database in advance with all its records.

But how can I "embed" this database file into my application and then access it? The databse will be around 500 kB in file size. Downloading from a remote server is not an option either as this is not allowed.

Thanks, Robert

Answer 1

I solved that problem by:

1. adding file.db into project/assets folder;

2. writing next class:

   public class LinnaeusDatabase extends SQLiteOpenHelper{
   
       private static String DATABASE_NAME = "Dragonfly.db";
       public final static String DATABASE_PATH = "/data/data/com.kan.linnaeus/databases/";
       private static final int DATABASE_VERSION = 1;
   
       private SQLiteDatabase dataBase;
       private final Context dbContext;
   
       public LinnaeusDatabase(Context context) {
           super(context, DBActivity.DatabaseName, null, DATABASE_VERSION);
           this.dbContext = context;
           DATABASE_NAME = DBActivity.DatabaseName;
           // checking database and open it if exists
           if (checkDataBase()) {
               openDataBase();
           } else
           {
               try {
                   this.getReadableDatabase();
                   copyDataBase();
                   this.close();
                   openDataBase();
   
               } catch (IOException e) {
                   throw new Error("Error copying database");
               }
               Toast.makeText(context, "Initial database is created", Toast.LENGTH_LONG).show();
           }
       }
   
       private void copyDataBase() throws IOException{
           InputStream myInput = dbContext.getAssets().open(DATABASE_NAME);
           String outFileName = DATABASE_PATH + DATABASE_NAME;
           OutputStream myOutput = new FileOutputStream(outFileName);
   
           byte[] buffer = new byte[1024];
           int length;
           while ((length = myInput.read(buffer))>0){
               myOutput.write(buffer, 0, length);
           }
   
           myOutput.flush();
           myOutput.close();
           myInput.close();
       }
   
       public void openDataBase() throws SQLException {
           String dbPath = DATABASE_PATH + DATABASE_NAME;
           dataBase = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.OPEN_READWRITE);
       }
   
       private boolean checkDataBase() {
           SQLiteDatabase checkDB = null;
           boolean exist = false;
           try {
               String dbPath = DATABASE_PATH + DATABASE_NAME;
               checkDB = SQLiteDatabase.openDatabase(dbPath, null,
               SQLiteDatabase.OPEN_READONLY);
           } catch (SQLiteException e) {
               Log.v("db log", "database does't exist");
           }
   
           if (checkDB != null) {
               exist = true;
               checkDB.close();
           }
           return exist;
       }
   }
   

Answer 2

Nice article on ReignDesign blog titled Using your own SQLite database in Android applications. Basically you precreate your database, put it in your assets directory in your apk, and on first use copy to "/data/data/YOUR_PACKAGE/databases/" directory.

Answer 3

I've done this in the past by storing a JSON file in the application in the res/raw resources and then loading the file on first load. From there, you can use the bulk insert mode to batch-import entries. See my database loader for Units as an example of this technique. One benefit of the res/raw style is that you can use the Android resource selecting system to localize the data to different regions, so you could have a different database (or part thereof) for different languages or regions.

You could also put a raw SQL dump in a similar file and load that on first load. You can get the file from the resources by using openRawResource(int). I'd recommend this instead of storing a pre-made sqlite database file to increase compatibility between versions of sqlite, as well as make maintaining the database easier (from an app-development lifecycle POV).

When loading things in bulk, make sure to use transactions, as that'll help speed things up and make the loading more reliable.

Answer 4

In a comment to this answer, CommonsWare recommends against the same method proposed by @evilone here (ie, copying the database from assets yourself). Instead you should use the tried and tested SQLiteAssetHelper. I've used it in Android Studio and it works well.

The directions are fairly clear. Here are a few key excerpts:

If you are using the Gradle build system, simply add the following dependency in your build.gradle file:

dependencies {
    compile 'com.readystatesoftware.sqliteasset:sqliteassethelper:+'
}

Extend SQLiteAssetHelper as you would normally do SQLiteOpenHelper, providing the constructor with a database name and version number:

public class MyDatabase extends SQLiteAssetHelper {

    private static final String DATABASE_NAME = "northwind.db";
    private static final int DATABASE_VERSION = 1;

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

Put your database in assets/databases/northwind.db (using whatever database name you have).

The database is made available for use the first time either getReadableDatabase() or getWritableDatabase() is called.