So, how do you use the SQLite database from your Android App? Go google! – well, been there done that. It took me many hours getting it to work – perhaps because I made some mistakes – so I will try and give a crash course in how to connect to a SQLite database from an Android Application.

The basic databaseHandler

There are properly tons of ways to make a databaseHandler and I will not discuss why and when to use what. Use my examples as you want or if you want to.

The databaseHandler is the class that handles the connection from the application to the database.

This is the most basic database handle class:


package huuah.db;

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

public class databaseHelper extends SQLiteOpenHelper {

  private static final String DBNAME = "myfancydatabase";
  private databaseHelper myDBHelper;
  private SQLiteDatabase myDB; 

  private final Context myContext;

  public databaseHelper(Context context) {
    super(context, DBNAME, null, 2);
    this.myContext = context;
  }

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

  public databaseHelper open() throws SQLException {
     myDBHelper = new databaseHelper(myContext);
     myDB = myDBHelper.getWritableDatabase();
     return this;
  }
}

When the open() function is called, it will activate the constructor and a new database (in this case named ‘myfancydatabase’) will be created inside the database path for your app.

The database path is located in /data/data/YOURPACKAGENAME/databases/.

To run the code add something like this to your application:

...
import huuah.db.databaseHelper;
...
databaseHelper dbCon = new databaseHelper(this);
dbCon.open();
...

Use the DDMS in Eclipse or the ADB tool to check if the database is created correctly.

Copying from assets to database path

What if we would like to distribute the application with a default database? Almost as simple as creating a new. Copy your SQLite database to the assets folder of your project. From here we will copy it to the correct database folder on the phone.

...
  public void createDatabase() throws IOException {
 
    InputStream assetsDB = myContext.getAssets().open("localdb");
    OutputStream dbOut = new FileOutputStream("/data/data/huuah.db/database/myfancydatabase");
 
    byte[] buffer = new byte[1024];
    int length;
    while ((length = assetsDB.read(buffer))>0){
      dbOut.write(buffer, 0, length);
    }

    dbOut.flush();
    dbOut.close();
    assetsDB.close();
}
...

This will copy the database ‘localdb’ found in the assets folder and copy it to the database folder of my huuah.db package and naming it myfancydatabase. All done.

Using DDMS or the ADB tool

Sometimes it is a good idea to check if your files and databases are created properly. This can be done with the DDMS in the Eclipse IDE or with the ADB tool.

Eclipse DDMS

Go to the Window-menu, select Open Perspective and click the “Other” option. Select the DDMS option and click OK. This will open up a File Explorer and some other windows. The File Explorer can be used to view, delete and copy to and from the emulator by pulling or pushing files.

ADB tool

The adb-tool can, among many things, be used for file exploring. Open a adb shell and list the content of the database folder like this:

$ adb shell
# cd data/data/huuah.db/databases
# ls
myfancydatabase
#

Creating a SQLite database with a Firefox plugin

If your are using Firefox, there is a great simple plugin that lets your create and edit SQLite database. It can be found here. When downloaded and installed, goto the tools menu in Firefox and select the SQLite manager and your are ready.

There is only one thing you must remember, when creating the databases from scratch like this, is to manually create the android structure. This is done by running these to SQL commands:

CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US');
INSERT INTO "android_metadata" VALUES ('en_US');

Close and save the database, and copy the file to your assets folder.

Where I got stuck

I ran into quite a few obstacles when making the database connection for the very first time. I did find some answers as well:

Problem: public type databaseHelper must be definded

Solution: Well, I wasn’t thinking when creating my databaseHelper, so I just wrote it in the same file as my main function. Don’t do this. Bad idea. New classes must be placed in its own .java file.

Problem: I was getting loads of “database failure 14″

Solution: Don’t manually create the /data/data/PACKAGENAME/databases directory. User rights on the databases folder will be wrong and your are not allowed to write to the directory from your Android App. Remove the directory and let the Android software create it for you.

The end

I hope your found my post useful. Please post any comments you may have.

16 Responses to “Using SQLite from your Android App”

jim Says:
april 29th, 2010 at 20:34
I’m having difficulty copying the database from my assets. I can open the input stream – I can even use .available() to find the number of bytes in the asset file (which is correct in my case). However, on my first read() of the input stream I get IOException….Puzzled beyond belief on this one. Any suggestions?

I’m using the 1.6 SDK and simulator.
jim Says:
april 29th, 2010 at 20:41
Well I solved it…

The problem is that my DB is sort of largish (> 1MB). It seems that it is compressed and that causes confusion in the Android read on the InputStream. The trick is to rename your asset to a file that the packager will NOT try to compress. Renaming my db file from xxx.db to xxx.mp3 did the trick

GAG!
js - huuah Says:
april 29th, 2010 at 22:28
Thank you for your input. It is always nice with the solution as well
miku Says:
maj 16th, 2010 at 13:46
Hi Huuah..

Thanks for your useful post. I m facing some concerns wrt the databases in android. There is one database with lot of tables. Few tables are huge with “already populated data” and the rest will have to be populated during the course of the application. Now my problem is to populate the db with those default data. That table is very huge. I have an excel sheet based on which the data has to be populated using insert statements is very hectic. HOw can i go about doing that? and still use the same db to populate some other tables programatically, from the code?
js - huuah Says:
maj 16th, 2010 at 14:16
Hi Miku

You could perhaps use the onCreate() function like this:

public void onCreate(SQLiteDatabase db) {
db.execSQL(“CREATE TABLE myTable (_id INTEGER PRIMARY KEY NOT NULL ,description TEXT);”);
db.execSQL(“INSERT INTO myTable (description) VALUES (‘value1)”);
db.execSQL(“INSERT INTO myTable (description) VALUES (‘value1)”);
…
}

I have to say, that this is untested. I have only used the onCreate for creating a database, but not populating it at the same time. I can’t see why it shouldn’t work, though.
Pat Says:
september 13th, 2010 at 08:54
Thank you jim. Your suggestion to rename the file to .mp3 saved my ass.
I literally have spent days trying to figure out why it threw an exception on the copying, yet on other projects it did not. I am really disappointed that this was not documented by google and/or fixed.
m3rc3r Says:
oktober 19th, 2010 at 19:04
I like the simplicity of this design, but it took me a few hours to figure everything out. For people new to this stuff like I am:

You must call the createDatabase class, which seems obvious to me now. Also,

In my program the code like:

OutputStream dbOut = new FileOutputStream(“/data/data/huuah.db/database/myfancydatabase”);

The path portion must read “databases”, not “database”. I found that out in the DDMS browser which was very helpful. Hope this saves someone some time.

Thanks for the article!
farhan Says:
oktober 24th, 2010 at 19:20
http://mfarhan133.wordpress.com/2010/10/24/database-crud-tutorial-for-android/

Its an excellent toturial
kishore Says:
oktober 25th, 2010 at 08:55
Hi Johnny, i tried u r code, database is created but it was not the one which is in assets folder. Can u tell why this is happening,
js - huuah Says:
oktober 30th, 2010 at 13:23
#8 kishore
I have recently upgraded my computer and I have not yet installed Eclipse and the Android environment. I can therefor not answer your question at this time, but I will return as soon as I have my setup up and running again.
js - huuah Says:
oktober 30th, 2010 at 13:27
#7 m3rc3r
Thank you for pointing out the typo regarding database vs databases.
Björn L Says:
januar 5th, 2011 at 00:43
Jim, thanks for sharing the solution with renaming the database file in the assets folder. Great tip!
Joemarie Amparo Says:
januar 26th, 2011 at 06:23
Hello,

I had made a a DB named “dbPractice” with the table named “User” and its attributes: “_id”,”firstName”, “middleName”, and “lastName”

I save this DB inside my /res folder of the project. My problem is I don’t know how to use/access this DB through codes.

Is this right that I put in

//since i put the db inside the /res folder of the project
private static String DB_PATH = “/res/”;
//db name
private static String DB_NAME = “dbPractice”;

?
How am i going to go after here.

Thanks.
Yunus Says:
marts 9th, 2011 at 13:53
Hi Huaah,
I m totally new to Android application development and trying to learn it. So can u provide me the full-fledge working example of Android and SQLite Connectivity along with some screeshots so it will be helpful to me a lot..
thanks and regards..
Cahyo Says:
april 22nd, 2011 at 04:47
Hi Huuah

i’m new in Android development. i just want to ask you, where i should put the “Copying from assets to database path” code to our project? in our main class or in databaseHandler?

thanks
Akshay Says:
maj 7th, 2011 at 09:11
Hi Huuah,
It’s a great tutorial,I want to tell you that you just put down the complete processes of connecting to database beacuse i have seen this example before few days at that time i have understood it but not able to execute it but now i am able to understand it and i have executed it.So If you explain it throwely then it will be easy for the freshor who is new to android.
Thanks for the nice tutorial.