Using an Existing SQLite Database with an Android Project

This article highlights how to use an Existing SQLite database with an Android Project.

Prerequisites

A sqlite3 database is required as it is the only db that android supports (probably because it is a single simple file). Converting between databases can be tricky sometimes. In this case I needed to convert a MSSQL database to SQLite so I made use of the following repos:

• MSSQL2MySQL – Microsoft SQL Server to MySQL Server Converter
• MySQL2SQLite – MySQL to SQLite script
• Sequeal – A Ruby Database Toolkit

Important Note Regarding ORM’s

Now you need to make a choice. Will you a using an ORM, an Object Relational Mapper. An ORM is a package that converts database records into objects. A popular approach is active record.

Some Android ORM’s:

• SugarORM – Very easy syntax, uses reflection to infer data (Tutorial)
• Siminov – Another viable alternative syntax
• greenDAO – Slightly different take (DAO vs ORM)
• ORMLite – Lightweight and speed is prioritized
• androrm – Newer but promising take on a simple ORM
• activeandroid – A popular Android ORM (Tutorial)
• cupboard – A lightweight Android ORM
• DBFlow – Simple and Fast Android ORM

Now if you have chosen to use an ORM take note of the following.

• It will create the database for you based on your models and hence can’t use existing data
• It will create tables exactly based on your models

Note: If the above is not true please comment

So basically use of an ORM is not applicable in this case as we want to ship the app with existing data.

Where is the Database Stored

The database is stored in /data/data/za.co.mydomain.myapp/databases/mydb.db.

So the database has to be copied over from the app to that location. We do this using the following package: Android SQLite Asset Helper. I am unaware of the ability to do this with the native Android SDK.

Where should I put the Existing SQLite file in the Android Project Structure

You need to place the database in: Project/app/src/main/assets.databases/mydb.db

Shipping your App with an existing SQLite database

First we need to add the dependency in build.gradle

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

So do the steps above. Now we need to create a java class that uses the SQLiteAssetHelper:

public class MyDb extends SQLiteAssetHelper{
  private static final String DB_NAME = "mydb.db";
  private static final int DB_VERSION = 1;

  public MyDB(Context context){
    super(context, DB_NAME, null, DB_VERSION);
  }
}

Awesome. Now to do the initial copy across. This is done when we first call getReadableDatabase().

So in MainActivity.java or wherever you want to do this.

MyDb db = new MyDb(this);
db.getReadableDatabase();

Querying – Getting Data with a Cursor

Now to Query data and get a Cursor (not an Object) add a function to MyDb.java:

public Cursor getData(){
      SQLiteDatabase db = getReadableDatabase();
        Cursor mCur = db.query(
                TABLE, //table
                //We Need a _id (It's required for CursorAdapter)
                new String[] {COLUMN_TREEID + " as _id", COLUMN_TREEGUID, COLUMN_ENDANGERED, COLUMN_FAMILY, COLUMN_ID, COLUMN_KRUGER, COLUMN_NAME, COLUMN_POISONOUS},
                null, //where clause
                null, //where parameters
                null, //groupby
                null, //having
                COLUMN_NAME + " ASC"
        );
        mCur.moveToFirst();
        return mCur;
    }

Now for getting the data:

Cursor myCursor = db.getTrees();

Alright, so that is that. It’s not the best solution so any comments please discus below.