By Muhammad Umar


2012-02-02 08:54:28 8 Comments

I have already created an SQLite database. I want to use this database file with my Android project. I want to bundle this database with my application.

Instead of creating a new database, how can the application gain access to this database and use it as its database?

5 comments

@Yaqub Ahmad 2012-02-02 09:16:24

NOTE: Before trying this code, please find this line in the below code:

private static String DB_NAME ="YourDbName"; // Database name

DB_NAME here is the name of your database. It is assumed that you have a copy of the database in the assets folder, so for example if your database name is ordersDB, then the value of DB_NAME will be ordersDB,

private static String DB_NAME ="ordersDB";

Keep the database in assets folder and then follow the below:

DataHelper class:

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

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

public class DataBaseHelper extends SQLiteOpenHelper
{
    private static String TAG = "DataBaseHelper"; // Tag just for the LogCat window
    //destination path (location) of our database on device
    private static String DB_PATH = "";
    private static String DB_NAME ="YourDbName";// Database name
    private SQLiteDatabase mDataBase;
    private final Context mContext;

    public DataBaseHelper(Context context)
    {
        super(context, DB_NAME, null, 1);// 1? Its database Version
        if(android.os.Build.VERSION.SDK_INT >= 17){
           DB_PATH = context.getApplicationInfo().dataDir + "/databases/";
        }
        else
        {
           DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
        }
        this.mContext = context;
    }

    public void createDataBase() throws IOException
    {
        //If the database does not exist, copy it from the assets.

        boolean mDataBaseExist = checkDataBase();
        if(!mDataBaseExist)
        {
            this.getReadableDatabase();
            this.close();
            try
            {
                //Copy the database from assests
                copyDataBase();
                Log.e(TAG, "createDatabase database created");
            }
            catch (IOException mIOException)
            {
                throw new Error("ErrorCopyingDataBase");
            }
        }
    }

    //Check that the database exists here: /data/data/your package/databases/Da Name
    private boolean checkDataBase()
    {
        File dbFile = new File(DB_PATH + DB_NAME);
        //Log.v("dbFile", dbFile + "   "+ dbFile.exists());
        return dbFile.exists();
    }

    //Copy the database from assets
    private void copyDataBase() throws IOException
    {
        InputStream mInput = mContext.getAssets().open(DB_NAME);
        String outFileName = DB_PATH + DB_NAME;
        OutputStream mOutput = new FileOutputStream(outFileName);
        byte[] mBuffer = new byte[1024];
        int mLength;
        while ((mLength = mInput.read(mBuffer))>0)
        {
            mOutput.write(mBuffer, 0, mLength);
        }
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }

    //Open the database, so we can query it
    public boolean openDataBase() throws SQLException
    {
        String mPath = DB_PATH + DB_NAME;
        //Log.v("mPath", mPath);
        mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);
        //mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
        return mDataBase != null;
    }

    @Override
    public synchronized void close()
    {
        if(mDataBase != null)
            mDataBase.close();
        super.close();
    }
}

Write a DataAdapter class like:

import java.io.IOException;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class TestAdapter
{
    protected static final String TAG = "DataAdapter";

    private final Context mContext;
    private SQLiteDatabase mDb;
    private DataBaseHelper mDbHelper;

    public TestAdapter(Context context)
    {
        this.mContext = context;
        mDbHelper = new DataBaseHelper(mContext);
    }

    public TestAdapter createDatabase() throws SQLException
    {
        try
        {
            mDbHelper.createDataBase();
        }
        catch (IOException mIOException)
        {
            Log.e(TAG, mIOException.toString() + "  UnableToCreateDatabase");
            throw new Error("UnableToCreateDatabase");
        }
        return this;
    }

    public TestAdapter open() throws SQLException
    {
        try
        {
            mDbHelper.openDataBase();
            mDbHelper.close();
            mDb = mDbHelper.getReadableDatabase();
        }
        catch (SQLException mSQLException)
        {
            Log.e(TAG, "open >>"+ mSQLException.toString());
            throw mSQLException;
        }
        return this;
    }

    public void close()
    {
        mDbHelper.close();
    }

     public Cursor getTestData()
     {
         try
         {
             String sql ="SELECT * FROM myTable";

              Cursor mCur = mDb.rawQuery(sql, null);
             if (mCur!=null)
             {
                mCur.moveToNext();
             }
             return mCur;
         }
         catch (SQLException mSQLException)
         {
             Log.e(TAG, "getTestData >>"+ mSQLException.toString());
             throw mSQLException;
         }
     }
}

Now you can use it like:

TestAdapter mDbHelper = new TestAdapter(urContext);
mDbHelper.createDatabase();
mDbHelper.open();

Cursor testdata = mDbHelper.getTestData();

mDbHelper.close();

EDIT: Thanks to JDx

For Android 4.1 (Jelly Bean), change:

DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";

to:

DB_PATH = context.getApplicationInfo().dataDir + "/databases/";

in the DataHelper class this code will work on JB 4.2 multi users.

@JDx 2013-01-07 10:01:46

This doesn't work on Jellybean 4.2 using multiple users. Is there any workaround?

@JDx 2013-01-07 10:55:07

OK, no problem. I figured it out anyway, If you change: DB_PATH = "/data/data/" + context.getPackageName() + "/databases/"; to: DB_PATH = context.getApplicationInfo().dataDir + "/databases/"; in the DataHelper class this code will work on JB 4.2 multi users.

@user1877082 2013-01-25 19:19:32

I have been trying to write to this database, I am new to android and have downloaded the new adt bundle. I have been searching the web for a resolution and have seen how JDx changed the DB path, I have also tried to change to this path but it wont work! Please has someone any recommendations so I can write to my database. thank you

@NagarjunaReddy 2013-02-06 08:49:39

@YaqubAhmad in my database file store in Assects folder in that lot of data is available my requirement is how to get that data and display in listview Any idea of this one....

@NagarjunaReddy 2013-02-08 06:19:12

@YaqubAhmad what is file size any limitations. what is maximum size of .sqlite

@Zyoo 2013-02-21 15:09:26

Some scenario: User opens application, then leaves the app by pressing home button. User go to Setting - Apps - <your_app> and then click the Clear data button. What will happen when the user comes back to your application? Shouldn't you override the method getReadableDatabase()?

@Prateek 2013-03-13 11:27:18

What if I want to encrypt db file in assets folder before copying to sd card.

@Cjames 2013-03-18 03:57:41

I'm using this code but still can't resolved the FIRST run error of the application and still the "table not found". I used sqlite database. I've defined the database extension in the DB_NAME e.g : Example.sqlite and the File name on my databases folder under the assests folder is Example.sqlite .. Hope you would help me sir ;D

@NetStarter 2013-05-28 09:19:13

@YaqubAhmad does the edited code for jelly bean will work for the versions below it ?

@TronicZomB 2013-05-31 14:47:16

You need to use the API number in the comparison, not 4.2, so >= 17.

@Jamshid 2013-12-03 06:28:35

it's good example, but it didn't work on some devices. some changes: File dbFile = new File(myPath); myDataBase = SQLiteDatabase.openOrCreateDatabase(dbFile, null); // myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

@Alex Semeniuk 2014-02-10 10:16:52

to JDx and others: There is a method Context.getDatabasePath() which is there to show you exact location of specific database (without the need for DB_PATH = "/data/data/" + context.getPackageName() + "/databases/"; and other trash like this) developer.android.com/reference/android/content/…

@Alex Semeniuk 2014-02-10 10:19:03

What about the situation when you use database in conjunction with ContentProvider (defined in manifest file). How to make the provider use custom database?

@Fantômas 2014-06-30 15:32:08

IMO this one should be the accepted answer.

@AK Joshi 2014-11-20 10:51:19

@YaqubAhmad ..thanks Buddy

@Salmaan 2015-01-07 10:27:31

What should i do if in next release i have to add a new table or new column to a table ??? keeping the previous data save ....

@Machado 2015-03-10 20:07:02

The source code (sample project) link is out of date.

@Mumfi 2015-05-05 06:23:51

@Cjames Did you ever solve it? Or anyone else? I'm getting no such table errors no matter what I do.

@Mike 2015-07-31 16:13:22

@Cjames I had this problem. Something went wrong when copying the database from the assets folder the first time I ran this. Every subsequent run would try reading from the empty database and would fail when trying to select from a table. If you uninstall, clean, and then run again it should re-copy the database. Check for any copying errors.

@Cjames 2015-08-05 05:13:06

@Mike PM me your email address I'll send you a sample code

@Cjames 2015-08-05 05:16:00

@Mumfi May PM me your email address. I'll send you a sample code

@Zain ul abdeen 2015-10-24 15:35:42

Database created (Y). I am new to this just wanna know what if I want to write something in this database? How can I do that?. Thanks

@Jitendra Kumar. Balla 2016-08-08 09:28:08

This ans is copied from blog.reigndesign.com/blog/…

@Blasco73 2017-03-01 12:11:51

works perfectly.... thank a lot!

@Akash Dubey 2017-04-22 09:00:29

kindly make this change private static String DB_NAME ="YourDbName"; to private static String DB_NAME ="YourDbName.db";

@Jesus Almaral 2018-12-03 20:45:41

The new database appears empty, but it is not, did anyone else have this problem?

@Manoj Fegde 2012-12-28 12:35:35

If you are having pre built data base than copy it in asset folder and create an new class as DataBaseHelper which implements SQLiteOpenHelper Than use following code:

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

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

public class DataBaseHelperClass extends SQLiteOpenHelper{
 //The Android's default system path of your application database.
private static String DB_PATH = "/data/data/package_name/databases/";
// Data Base Name.
private static final String DATABASE_NAME = "DBName.sqlite";
// Data Base Version.
private static final int DATABASE_VERSION = 1;
// Table Names of Data Base.
static final String TABLE_Name = "tableName";

public Context context;
static SQLiteDatabase sqliteDataBase;

/**
 * Constructor
 * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
 * @param context
 * Parameters of super() are    1. Context
 *                              2. Data Base Name.
 *                              3. Cursor Factory.
 *                              4. Data Base Version.
 */
public DataBaseHelperClass(Context context) {       
    super(context, DATABASE_NAME, null ,DATABASE_VERSION);
    this.context = context;
}

/**
 * Creates a empty database on the system and rewrites it with your own database.
 * By calling this method and empty database will be created into the default system path
 * of your application so we are gonna be able to overwrite that database with our database.
 * */
public void createDataBase() throws IOException{
    //check if the database exists
    boolean databaseExist = checkDataBase();

    if(databaseExist){
        // Do Nothing.
    }else{
        this.getWritableDatabase();         
        copyDataBase(); 
    }// end if else dbExist
} // end createDataBase().

/**
 * Check if the database already exist to avoid re-copying the file each time you open the application.
 * @return true if it exists, false if it doesn't
 */
public boolean checkDataBase(){
    File databaseFile = new File(DB_PATH + DATABASE_NAME);
    return databaseFile.exists();        
}

/**
 * Copies your database from your local assets-folder to the just created empty database in the
 * system folder, from where it can be accessed and handled.
 * This is done by transferring byte stream.
 * */
private void copyDataBase() throws IOException{ 
    //Open your local db as the input stream
    InputStream myInput = context.getAssets().open(DATABASE_NAME); 
    // Path to the just created empty db
    String outFileName = DB_PATH + DATABASE_NAME; 
    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName); 
    //transfer bytes from the input file to the output file
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
        myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close(); 
}

/**
 * This method opens the data base connection.
 * First it create the path up till data base of the device.
 * Then create connection with data base.
 */
public void openDataBase() throws SQLException{      
    //Open the database
    String myPath = DB_PATH + DATABASE_NAME;
    sqliteDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);  
}

/**
 * This Method is used to close the data base connection.
 */
@Override
public synchronized void close() { 
    if(sqliteDataBase != null)
        sqliteDataBase.close(); 
    super.close(); 
}

/**
* Apply your methods and class to fetch data using raw or queries on data base using 
* following demo example code as:
*/
public String getUserNameFromDB(){
    String query = "select User_First_Name From "+TABLE_USER_DETAILS;
    Cursor cursor = sqliteDataBase.rawQuery(query, null);
    String userName = null;
    if(cursor.getCount()>0){
        if(cursor.moveToFirst()){
    do{
                userName = cursor.getString(0);
            }while (cursor.moveToNext());
        }
    }
    return userName;
}


@Override
public void onCreate(SQLiteDatabase db) {
    // No need to write the create table query.
    // As we are using Pre built data base.
    // Which is ReadOnly.
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // No need to write the update table query.
    // As we are using Pre built data base.
    // Which is ReadOnly.
    // We should not update it as requirements of application.
}   
}

Hope this will help you...

@Dan Brough 2012-12-12 17:15:31

I had trouble with the other DatabaseHelpers regarding this problem, not sure why.
This is what worked for me:

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

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

public class DatabaseHelper extends SQLiteOpenHelper {

  private static final String TAG = DatabaseHelper.class.getSimpleName();

  private final Context context;
  private final String assetPath;
  private final String dbPath;

  public DatabaseHelper(Context context, String dbName, String assetPath)
      throws IOException {
    super(context, dbName, null, 1);
    this.context = context;
    this.assetPath = assetPath;
    this.dbPath = "/data/data/"
        + context.getApplicationContext().getPackageName() + "/databases/"
        + dbName;
    checkExists();
  }

  /**
   * Checks if the database asset needs to be copied and if so copies it to the
   * default location.
   * 
   * @throws IOException
   */
  private void checkExists() throws IOException {
    Log.i(TAG, "checkExists()");

    File dbFile = new File(dbPath);

    if (!dbFile.exists()) {

      Log.i(TAG, "creating database..");

      dbFile.getParentFile().mkdirs();
      copyStream(context.getAssets().open(assetPath), new FileOutputStream(
          dbFile));

      Log.i(TAG, assetPath + " has been copied to " + dbFile.getAbsolutePath());
    }

  }

  private void copyStream(InputStream is, OutputStream os) throws IOException {
    byte buf[] = new byte[1024];
    int c = 0;
    while (true) {
      c = is.read(buf);
      if (c == -1)
        break;
      os.write(buf, 0, c);
    }
    is.close();
    os.close();
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  }
}

@Andy 2012-02-02 09:10:57

If you already have a database, keep it in your asset folder and copy it in your application. For more detail, see Android database basics.

@jeet 2012-02-02 09:03:19

You can do this by using a content provider. Each data item used in the application remains private to the application. If an application want to share data accross applications, there is only technique to achieve this, using a content provider, which provides interface to access that private data.

Related Questions

Sponsored Content

59 Answered Questions

[SOLVED] How do I read / convert an InputStream into a String in Java?

13 Answered Questions

[SOLVED] Proper use cases for Android UserManager.isUserAGoat()?

20 Answered Questions

[SOLVED] How do I check in SQLite whether a table exists?

  • 2009-10-21 14:22:10
  • PoorLuzer
  • 389422 View
  • 799 Score
  • 20 Answer
  • Tags:   sqlite

47 Answered Questions

[SOLVED] Stop EditText from gaining focus at Activity startup

106 Answered Questions

[SOLVED] R cannot be resolved - Android error

17 Answered Questions

16 Answered Questions

[SOLVED] Ship an application with a database

29 Answered Questions

[SOLVED] How do I add a library project to Android Studio?

9 Answered Questions

[SOLVED] Improve INSERT-per-second performance of SQLite?

38 Answered Questions

[SOLVED] Is quitting an application frowned upon?

  • 2010-01-09 15:59:46
  • Ted
  • 262700 View
  • 1076 Score
  • 38 Answer
  • Tags:   android

Sponsored Content