Most all of the Android examples and tutorials out there assume you want to create and populate your database at runtime and not to use and access an independent, preloaded database with your Android application.
The method I'm going to show you takes your own SQLite database file from the "assets" folder and copies into the system database path of your application so the SQLiteDatabase API can open and access it normally.
1. Preparing the SQLite database file.
Assuming you already have your sqlite database created, we need to do some modifications to it.
If you don't have a sqlite manager I recommend you to download the opensource SQLite Database Browser available for Win/Linux/Mac.
Open your database and add a new table called "android_metadata", you can execute the following SQL statement to do it:
CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US')
Now insert a single row with the text 'en_US' in the "android_metadata" table:
INSERT INTO "android_metadata" VALUES ('en_US')
Then, it is necessary to rename the primary id field of your tables to "_id" so Android will know where to bind the id field of your tables.
You can easily do this with SQLite Database Browser by pressing the edit table button 
, then selecting the table you want to edit and finally selecting the field you want to rename.
After renaming the id field of all your data tables to "_id" and adding the "android_metadata" table, your database it's ready to be used in your Android application.
Modified database
Note: in this image we see the tables "Categories" and "Content" with the id field renamed to "_id" and the just added table "android_metadata".
2. Copying, opening and accessing your database in your Android application.
Now just put your database file in the "assets" folder of your project and create a Database Helper class by extending the SQLiteOpenHelper class from the "android.database.sqlite" package.
Make your DataBaseHelper class look like this:
public class DataBaseHelper extends SQLiteOpenHelper{ //The Android's default system path of your application database. private static String DB_PATH = "/data/data/YOUR_PACKAGE/databases/"; private static String DB_NAME = "myDBName"; private SQLiteDatabase myDataBase; private final Context myContext; /** * Constructor * Takes and keeps a reference of the passed context in order to access to the application assets and resources. * @param context */ public DataBaseHelper(Context context) { super(context, DB_NAME, null, 1); this.myContext = context; } /** * Creates a empty database on the system and rewrites it with your own database. * */ public void createDataBase() throws IOException{ boolean dbExist = checkDataBase(); if(dbExist){ //do nothing - database already exist }else{ //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. this.getReadableDatabase(); try { copyDataBase(); } catch (IOException e) { throw new Error("Error copying database"); } } } /** * 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 */ private boolean checkDataBase(){ SQLiteDatabase checkDB = null; try{ String myPath = DB_PATH + DB_NAME; checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY); }catch(SQLiteException e){ //database does't exist yet. } if(checkDB != null){ checkDB.close(); } return checkDB != null ? true : false; } /** * 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 transfering bytestream. * */ private void copyDataBase() throws IOException{ //Open your local db as the input stream InputStream myInput = myContext.getAssets().open(DB_NAME); // Path to the just created empty db String outFileName = DB_PATH + DB_NAME; //Open the empty db as the output stream OutputStream myOutput = new FileOutputStream(outFileName); //transfer bytes from the inputfile to the outputfile 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(); } public void openDataBase() throws SQLException{ //Open the database String myPath = DB_PATH + DB_NAME; myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY); } @Override public synchronized void close() { if(myDataBase != null) myDataBase.close(); super.close(); } @Override public void onCreate(SQLiteDatabase db) { } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } // Add your public helper methods to access and get content from the database. // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy // to you to create adapters for your views. }
That's it.
Now you can create a new instance of this DataBaseHelper class and call the createDataBase() and openDataBase() methods. Remember to change the "YOUR_PACKAGE" to your application package namespace (i.e: com.examplename.myapp) in the DB_PATH string.
...
DataBaseHelper myDbHelper = new DataBaseHelper();
myDbHelper = new DataBaseHelper(this);
try {
myDbHelper.createDataBase();
} catch (IOException ioe) {
throw new Error("Unable to create database");
}
try {
myDbHelper.openDataBase();
}catch(SQLException sqle){
throw sqle;
}
...
Enjoyed this post? ReignDesign is a great team of tech-savvy developers providing RIA and mobile services. For more articles like this, subscribe to our blog feed.
Tags: Android, Databases, howto, Java, SQLite
Solid writeup. We should take a look at modifying a lightweight ORM to work on Android. Something similar to iBATIS (http://ibatis.apache.org) with special hooks to setup and upgrade the database.
Wow, much better then reading and executing 7000+ inserts. Brought an 18 second operation down to about 2 seconds.
Please note that on Firefox 3.0.7 on Ubuntu 8.10 the greater than sign in the while loop shows up as its html code (& g t
Well presented. But not a good solution for large databases (fine for small ones).
This DOUBLES the footprint of your database. A better solution for any sizeable database, is to download the database when the app is first run, as a secondary installation.
This, at least, only eats up X precious megabytes once.
Still suboptimal is the fact that the database must reside in /data/data/YOUR_PACKAGE/databases – because anything bulky should be on the sd card. I guess google will eventually let us access databases in other locations, or install our apps directly on the SD card.
There is an HTML error in the code preventing a Greater Than sign from appearing and the symbols %gt; instead. It is in the while loop of the copyDataBase method.
HTML error in the code fixed, thanks for the feedback.
This is exactly what I have been looking for.. But I am having an issue. I keep getting a failed to open the database errors. “sqlite3_open_v2(“/data/data/com.testapp/databases/database.db”, &handle, 1, NULL) failed”. I have tried this on both .db and .db3 files and neither of them are working. I followed you tutorial and placed the db in the assets directory, both in a folder called databases and just in the directory. Is there something else I am doing wrong?
I just realized the issue is not the loading of the database it is actually the first time it is read. Your tutorial is working properly thanks.
I came across one issue, based on the lack of complaints I think it’s fairly unique. The first SELECT I ran on a table in the copied DB crashed with Android claiming the table did not exist. I checked through adb and sqlite3 and the table *did* exist. Eventually I tried CREATEing the table just before I SELECTed from it (since it allegedly didn’t exist). Android’s response was that the database file was corrupt. The solution was to programmatically open the database like normal, immediatly close it, than open it again. Prior to that I did try copying a completely closed database to /assets; the first SELECT still crashed.
Here’s the first error for searchability:
03-12 01:17:22.810: ERROR/AndroidRuntime(512): Caused by: android.database.sqlite.SQLiteException: no such table: tblMyTable: , while compiling: SELECT …
To Will :
Try to add :
mOpenHelper.close();
Just before – SQLiteDatabase db = mOpenHelper.getReadableDatabase(); – in query function of your provider.
That’s solve the same problem for me.
For me I get an ioexception thrown on the first
read statement:
while ((length = myInput.read(buffer))>0){
Everything seems to get opened up just fine. I’ve named
the db ls.db in the assets folder. Should it be in the assets
folder per se or under some subdir? Any suggestions on how
to debug the failed read?
Thanks much
George,
Better if you get rid of the extension of your database file ( just “ls” ).
Your database file in the assets folder is right, then:
InputStream myInput = myContext.getAssets().open(“ls”);
it should open your database file, put a break point after this line to see if it’s working.
Also be sure that the path to your system application folder is right.
/data/data/your.package.name/databases/
then your output path should be
/data/data/your.package.name/databases/ls
good luck
Thanks for being the first post I have been able to find using multiple tables!
If you have time for another writeup, or an addition, it would be really useful to see how you are grabbing the data from a multi-table database. Using Cursors is obviously much harder than it is on one table, and I have not really found any help on a recommended way to do that.
Thanks again!
Hamy
Excelent post! Congratz!
George F., I was wondering if you have solved your problem reading the assets/your_db_name. I’m facing the same problem now, and I don’t know what is going on, because I can read it in terminal with sqlite command. Also I’m proceeding exactly as fluxa said.
Thanks in advance!
Wilson:
Check you database file size, there is a limitation around 1.2 Mb for files in the asset folder.
Wilson splitted up his db using Unix split command, added them in the res/raw folder, and then opened them up at install time to read. Then proceeded to splice them back together into the db and all worked fine.
Thanks George for this.
Thank you very much fluxa!
It was indeed the file size.. I was able to redesign the database, actually the problem was helpful somehow, because I cleaned the tables (the overall schema).
I’m dealing with a large amount of data, at first I was going to set up a web service to retrieve the info online, but my tutor didn’t like the idea of using internet… so… Ok, let’s try to downoad a little piece of internet and store it in a sqlite database. Seems absurd (and it is), but he was very reluctant :/
Thank you again and Thanks George for a clever solution!
Thanks for the tut, this is exactly what I need. I’m having a problem though
When it starts it recognises that there is no db and creates the empty db fine. Then in the copyDataBase method when it gets the the line
//Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);
I get the error
06-30 00:01:35.901: INFO/WTF(29581): java.io.FileNotFoundException: /data/data/bgh.com.spanishflashcards/databases/spanish
The path is correct. It has to be because it used the same path when creating the blank db. The blank db definitely exists. If I pause the code before this step I can see the blank db in DDMS.
Any reason why I would get that error?
THanks For the tutorial…
am also getting the same error like BGH says…have any reason…
BGH:Did u find the solution?
Hi,
thanks for addressing this important issue. Many apps need this flow.
one question,
what if my table just doesn’t have a column name “id”, what happens in this case?
here’s a sample schema of my table,
.schema OPR_CHEMISTRY_TAB
CREATE TABLE OPR_CHEMISTRY_TAB (ENTRY_ID text, INDEX_LETTER text, INDEX_ORD int, ENAME text, DESCR text, HAS_IMAGES int);
CREATE INDEX ENTRY_ENTRY_ID_IX on OPR_CHEMISTRY_TAB(ENTRY_ID asc);
CREATE INDEX ENTRY_ENTRY_LETTER_ORD_IX on OPR_CHEMISTRY_TAB(INDEX_LETTER asc, INDEX_ORD asc);
what does the native android table structure expects in this case?
thanx!
–tzurs
Hi, I just want to know how can I delete the table after I created it?
Is the android_metadata table necessary?
Hi Jimmy, yes it is.
Hi,
would it be possible to have a database that is kept on the sdcard?
I would like to have access to my database with other applications as well and I cannot access it, when it is stored in the /data/data/… directories. I’m not a superuser on my phone and I’m a little bit scared of loosing all my data when doing this goldcard business to become a su.
Therefore, I’m looking for a possibility of using the sql database in my applications but storing it’s data on the sdcard.
Will it be possible?
Johannes.
Try this,
Android Sqlite Databases
http://sarangasl.blogspot.com/2009/09/create-android-database.html
Thanks for the tut, this is exactly what I need. But I having a problem when I try to store file Image. I try to use some sqlite manager but not work. Please! Help me to solve this problem.
Anyway, thanks a lot.
I had problems with this code, it was not properly copied file into internal storage.
A added
this.close();
before copyDataBase();
and it works like charm.
@BGH:
This is a little late now, but I had the same problem, but I figured out the problem.
The issue comes up when this db is the FIRST db you try to create. If that’s the case, then data/data/YOURPACKAGE/databases doesn’t exist yet. You have to create the /databases directory or the method will fail. It’s pretty easy though:
File f = new File(DB_PATH);
if (!f.exists()) {
f.mkdir();
}
There is also a table called “sqlite_sequence”
in a Android SQLite DB…
This table has 2 columns : name and seq.
Do we need to take care of it ?
If I open the DB with:
public void openDataBase() throws SQLException{
String myPath = DB_PATH + DB_NAME;
myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
}
instead of using (OPEN_READONLY):
myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
to be able to add records in the DB.
I then noticed that “seq” within the table “sqlite_sequence” is not changing,
even as I add or delete records successfully from the DB ???
I viewed this by doing a “pull a file from the device” within the DDMS view /data/data/PACKAGE_NAME/databases/
(and then opening this file with SQLite Browser.
Is that normal/ ok ?
_id is set to INTEGER PRIMARY KEY (works fine to add records)
but should I also take care of “seq” an increase it by 1,
everytime a record is added to the DB ???
Txs for any advice on this.
hi,
I have more then 1.5 MB of DB so can anybody tell me how to connect the DB with the android because i cant put my DB in assests folder(Assets can take only upto 1.2 MB of DB).
Hello,
The database apparently loads perfectly but when I come to try and run a query I get the message:
11-17 20:54:03.126: ERROR/AndroidRuntime(749): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.allergycookbook/com.allergycookbook.AllergyCookBook}: android.database.sqlite.SQLiteException: no such table: recipes: , while compiling: SELECT _id, recipe_name FROM recipes
I double checked and the table exists in the database (as does the field). I think it might be the same problem that Will posted above, but I tried to close and then open the database and it didn’t work.
Perhaps it’s my shaky knowledge of SQL to blame? The code I am using to run the query was cribbed from the Android notepad tutorial and then modified accordingly. See below:
private void fillData()
{
// Get all of the rows from the database and create the item list
mNotesCursor = myDbHelper.fetchAllNotes();
startManagingCursor(mNotesCursor);
// Create an array to specify the fields we want to display in the list (only TITLE)
String[] from = new String[]{DataBaseHelper.KEY_TITLE};
// and an array of the fields we want to bind those fields to (in this case just text1)
int[] to = new int[]{R.id.text1};
// Now create a simple cursor adapter and set it to display
SimpleCursorAdapter notes =
new SimpleCursorAdapter(this, R.layout.recipe_row, mNotesCursor, from, to);
setListAdapter(notes);
}
If anyone can help me I’d be most grateful.
Hi Deep,
Actually I had this issue too. The anwser is simple : cut your big database file as several sub 1 Mo files !
I gave some explanations on this subject here :
http://androidblogger.blogspot.com/2009/05/how-to-ship-application-with-pre-baked.html
Hope it helps !
Hi Alocaly,
Thanks for your help but the issue is that my DB is in SQLite manager so how to cut SQLite DB so that we can able to connect it to our Android application. Please help me out of this problem.
Thanks
Deep
Thank you for this article! I succesfully use an embedded an sqlite database in my application, but at first I had many issues with android not wanting to open the database. I tried using most of the available opensource sqlite editors but they all seemed to produce incompatible sqlite files. In the end I ended up building up my database programmatically using a jdbc sqlite driver (http://www.zentus.com/sqlitejdbc) which seems to produces files android has no problem with!
The DB is not found… I have met the same problem too.Mybe we should think about changing permissions of the DB file. But how to do it in code?
Please help me out of the problem.
For Deep :
In my blog article, I explained how I cut my database, and remerge it at the first launch of the game !
http://androidblogger.blogspot.com/2009/05/how-to-ship-application-with-pre-baked.html
Hi Everyone,
I have the same error in the logcat as David… “sqlite3_open_v2(”/data/data/com.testapp/databases/database.db”, &handle, 1, NULL) failed”.. I have the method running on the initial activity that runs in my app and it force closes. I close, then open the app up and no error. I can’t figure out why I’m crashing only on the initial run…
Any help would be great!
Hi everyone,
i have follow the article, but i got some error message shows : java.lang.IlleagalStateException: database not open
but i did open the database before i access it.
anyone can help me ???
thanks .
I found a simplier way.
Just copy a good one and start with that.
Details:
1. Run the example called Events1. Its a bunch of example code (my Bible) from The Pragmatic Bookshelf.
2. From eclipse->DDMS, get the file in data/data/Events/databases/events.db
3. eclipse->DDMS->top corner, Click on “Pull a file from the device”, put it on your windows desktop, then click on “Push a file into the device”.
Thanks for this post. It really helped us with our project.
Mark
Hi all !
I have got a file /sdcard/data.db .How to insert record into file data.db
In order to waste less space, you should first compress your database file.
Then just reaplce:
//Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DB_NAME);
with
//Open your local db as the input stream
GZIPInputStream myInput = new GZIPInputStream( myContext.getAssets().open(DB_NAME) );
for gzip or if you use zip
//Open your local db as the input stream
ZipInputStream myInput = new ZipInputStream( myContext.getAssets().open(DB_NAME) );
myInput.getNextEntry();
My previous post on compressing the database to save storage space will not do much, since the apk is compressed anyway.
But you can still use this method as a convenient method to shrink the file under the 1MB limit (or you can just split the file in 1MB chunks and put it together when copying).
Cheers!
Hello,
It is a nice and a very helpful tutorial. Thank you.
I have seen a couple of replies here which say that we can split large database files and then join them on the device. I need to implement it since my database size after compression is around 3MB.
Can some one also let me know how to split and join large files?
Thank you.
Hi, I’ve followed the tutorial but cannot get it working. It crashes in the method copyDataBase(), on the line:
OutputStream myOutput = new FileOutputStream(outFileName);
I tried the 2 suggestions of checking directory exists (if (!f.exists()) {f.mkdir();}) and this.close();. But neither worked. How do I check the error logs in emulator? This is my first Android app, so sorry for basic question.
RE: size of db file – I thought this link might be useful:
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
If the db file contains any fragmentation the easiest way is to copy its contents to a new file (data is copied into a new unfragmented db)
(DOS / Win prompt)
> echo .dump | sqlite file1.db > file1.sql
> sqlite file2.db sqlite file1.db .dump > file1.sql
> sqlite file2.db < file1.sql
Found how to read log “adb logcat >C:\temp\log.txt”
The error during copydatabase is:
W/System.err( 704): Can’t dispatch DDM chunk 4d505251: no handler defined
E/Database( 704): sqlite3_open_v2(“/data/data/MyApp/databases/main”, &handle, 1, NULL) failed
Using Google I found 4d505251 is hex for MPRQ, or Method PRofiling Query. But still have no solution.
Hi, thank you very much for this great tutorial!
Thx for the tuto, but is there another way than with adb shell to see the folder containing the database with the emulator?
Because, I can’t find it on disk…and to use SQLite browser i need to specifit the place of the folder…
Help plz
I have some Newb questions:
The second half of the tutorial says declare the new helper like this:
DataBaseHelper myDbHelper = new DataBaseHelper();
However the error I get is that there is no constructor that takes no arguments.
So then I create my object all in one line:
DataBaseHelper myDbHelper = new DataBaseHelper(this);
Then I try to invoke this function:
myDbHelper.createDataBase();
And it says:
Syntax error on token “createDataBase”, Identifier expected after this token
What am I doing wrong?
Thanks!
Dman
Just so that you know, god kills a kitten every time you write code such as this:
return checkDB != null ? true : false;
All you actually need to write is this:
return checkDB != null;
Hi!!
i’ve followed all the stapes you’ve indicated
i’ve created a database baseSQLite.db contain’ 3 tables and the meta one
i’ve COPIED the code of dataHELPER in a class called BDAccess
i’ve created an activity containin’ a textViw label that will indicate the establishement of connexion to the database or the error msg
i’ve placed the database file into the asset folder
changed the name of DB_NAME (without puttin the extension .db”
in the activity i’ve created an instance of the BDClass , in one line cause i’ve not a constructor that accept no arguments
but i got an error while launchin, the app is not respondin’ and i’ve got to force it to close
i’ve checked the /DATA/DATA…. it contains the database but i don’t know if it’s empty (have you an idea how to check this)???
what shall i do
and THANKS!!!!!!