Tuesday, July 23, 2013

Android - DBHelper

Android using Sqlite as primary storage for android application, below is the sample code for create, upgrade or delete database.

Usually you will require to create one Helper for 1 database, we create our first helper class name "DBHelper", this class extends SQLiteOpenHelper and you need implement at least 2 methods

  • onCreate()
  • onUpgrade()
Always create only single instance for any DBHelper to avoid open & close connection of DB.

   public static DBHelper getInstance() {
        if(instance == null) {
            instance = new UserDatabaseHelper();
        return instance;

After install and start the app, verify if the db created.

root@android:/data/data/com.androidtutorial/databases # ls
e3 Songs.db                                                                   <
SQLite version 3.7.16 2013-03-18 11:39:23 [www.ptsoft.org] [www.ptdave.com]
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
Songs             android_metadata
sqlite> select * from Songs;
sqlite> select * from Songs;

The songs.db is created.

One confusing about onUpgrade, if you upgrade the db version from 2~10, don't expect onUpgrade will handle for you, you need do some checking as below.

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, "onUpgrade ..." + oldVersion ); 
        oldVersion += 1;
        if(oldVersion < newVersion) {
            onUpgrade(db, oldVersion, newVersion);