Thursday, December 13, 2012

Using a Pre-Populated SQLite Database in Android

Most Android code samples and manuals assume that a SQLite database is created at runtime. But what if we want to use an existing database—for instance, a database created and populated on a PC?

Creating the Pre-Populated Database

To make a database on a PC, you can use the SQLite Database Browser, which is available for most platforms. Let’s say you have already created a simple database in the yourdb.sqlite3 file with one table called friends, which contains a list of your friends and has two fields: id and name. To make your database work correctly on an Android device, you have to make two modifications.

1. Rename the id field of your table to “_id”. It can be easily done by clicking the Modify Table button, and then choosing the necessary table and field names.

2. Add the android_metadata table. To do that, open the Execute SQL tab and paste this simple code into the SQL string field:

CREATE TABLE android_metadata (locale TEXT);

INSERT INTO android_metadata VALUES ('en_US');

Click the Execute query button—and the database is ready. The database is now created and located in the assets folder. We'll show you how to use it in your Android app after the jump.

Implementing the ExternalDbOpenHelper Class

The ExternalDbOpenHelper class is inherited from SQLiteOpenHelper. The application databases are stored in the data/data/<name of your package>/databases/ folder on your device. That’s the folder where we need to copy our database. The commented code listing is shown below.



import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class ExternalDbOpenHelper extends SQLiteOpenHelper {

//Path to the device folder with databases
public static String DB_PATH;

//Database file name
public static String DB_NAME;
public SQLiteDatabase database;
public final Context context;

public SQLiteDatabase getDb() {
return database;

public ExternalDbOpenHelper(Context context, String databaseName) {
super(context, databaseName, null, 1);
this.context = context;
//Write a full path to the databases of your application
String packageName = context.getPackageName();
DB_PATH = String.format("//data//data//%s//databases//", packageName);
DB_NAME = databaseName;

//This piece of code will create a database if it’s not yet created
public void createDataBase() {
boolean dbExist = checkDataBase();
if (!dbExist) {
try {
} catch (IOException e) {
Log.e(this.getClass().toString(), "Copying error");
throw new Error("Error copying database!");
} else {
Log.i(this.getClass().toString(), "Database already exists");

//Performing a database existence check
private boolean checkDataBase() {
SQLiteDatabase checkDb = null;
try {
String path = DB_PATH + DB_NAME;
checkDb = SQLiteDatabase.openDatabase(path, null,
} catch (SQLException e) {
Log.e(this.getClass().toString(), "Error while checking db");
//Android doesn’t like resource leaks, everything should 
        // be closed
if (checkDb != null) {
return checkDb != null;

//Method for copying the database
private void copyDataBase() throws IOException {
//Open a stream for reading from our ready-made database
//The stream source is located in the assets
InputStream externalDbStream = context.getAssets().open(DB_NAME);

//Path to the created empty database on your Android device
String outFileName = DB_PATH + DB_NAME;

//Now create a stream for writing the database byte by byte
OutputStream localDbStream = new FileOutputStream(outFileName);

//Copying the database
byte[] buffer = new byte[1024];
int bytesRead;
while ((bytesRead = > 0) {
localDbStream.write(buffer, 0, bytesRead);
//Don’t forget to close the streams

public SQLiteDatabase openDataBase() throws SQLException {
String path = DB_PATH + DB_NAME;
if (database == null) {
database = SQLiteDatabase.openDatabase(path, null,
return database;

public synchronized void close() {
if (database != null) {
public void onCreate(SQLiteDatabase db) {}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}

This class can now be used in any Android application. All we need to do is pass the database file name and context (in most cases, it is Application) to the constructor.

Have Database, Will Travel

Let’s create a sample application with a simple architecture and minimal functionality. When launched, it will output a list of friend names from our database.

Note: Uninstall the application from the Android device before every test run. The database file is created only after the first run of the application.

The project has only one activity inherited from ListActivity with the following markup:

<!--?xml version="1.0" encoding="utf-8"?-->

and the following logic:


import java.util.ArrayList;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class PrepopSqliteDbActivity extends ListActivity {
    private static final String DB_NAME = "yourdb.sqlite3";
    //A good practice is to define database field names as constants
private static final String TABLE_NAME = "friends";
private static final String FRIEND_ID = "_id";
private static final String FRIEND_NAME = "name";

private SQLiteDatabase database;
private ListView listView;
private ArrayList friends;

    public void onCreate(Bundle savedInstanceState) {

        //Our key helper
        ExternalDbOpenHelper dbOpenHelper = new ExternalDbOpenHelper(this, DB_NAME);
        database = dbOpenHelper.openDataBase();
        //That’s it, the database is open!

private void setUpList() {
//We use a standard adapter and an element layout for brevity’s sake
setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, friends));
  listView = getListView();

//Let’s set a message shown upon tapping an item
listView.setOnItemClickListener(new OnItemClickListener() {
public void onItemClick(AdapterView parent, View view,
int position,long id) {
((TextView) view).getText() +
          " could be Softeq's friend",

//Extracting elements from the database
private void fillFreinds() {
friends = new ArrayList<String>();
Cursor friendCursor = database.query(TABLE_NAME, new String[] {FRIEND_ID,
            FRIEND_NAME}, null, null, null, null, FRIEND_NAME);
if(!friendCursor.isAfterLast()) {
do {
String name = friendCursor.getString(1);
} while (friendCursor.moveToNext());

That’s it! If you used our database example, you will now see the following picture on your screen:

You can download the complete example source here:

Happy coding!