Ormlite is the our choice of database ORM. Although we have decided to stick to it for new Android applications, there are quite a few thing missing in the manual.

Name the Database

Normally you name the databas with user ID like this.


String databaseName = String.format("user_%d.db", mUserId);

Then you are screwed because you don't speak Persian/Arabian. In some languages, you cannot expect one as 1 and this wiki post will show you a new way to write numbers. You can reproduce this issue by switching the locale to Arabic on the phone and you must stay clam if the app cannot find the database. The fix is simple, just specify the locale when formatting the string.


String databaseName = String.format(Locale.ENGLISH,"user_%d.db", mUserId);

Name the Column

Well, you have the database now and go ahead to create tables. Take the example from the website:


@DatabaseTable(tableName = "accounts")
public class Account {
    @DatabaseField(id = true)
    private String name;

    @DatabaseField(canBeNull = false)
    private String password;
    ...
    Account() {
    	// all persisted classes must define a no-arg constructor with at least package visibility
    }
    ...
}

If you are going to run proguard, then you may get screwed because the column changes. There are two solutions to this, either naming the column explicitly or make all database object class excluded from proguard, as shown below.


APPROACH ONE - Name the column explicitly

@DatabaseTable(tableName = "accounts")
public class Account {
    @DatabaseField(id = true,columnName = "name")
    private String name;

    @DatabaseField(canBeNull = false, columnName = "password")
    private String password;
    ...
    Account() {
    	// all persisted classes must define a no-arg constructor with at least package visibility
    }
    ...
}

APPROACH TWO - Modify the proguard file

-keep class com.XXXXXX.bean.**{*;}

Are those results cached?

Now you have the table and it is time to query the data out from the database. ORM generally makes your life easier but it does not mean that the app can get the data with ease. Ormlite claims that the result is cached but you have to do the homework yourself by following this guide.

However it only works for 4.48+, in 4.46, you may write code like this

 mUserDao = DaoManager.createDao(getConnectionSource(), UserInfo.class);
 mUserDao.setObjectCache(true);

And we guarantee that it does NOT work at all.

Update the table

Database scheme always changes because PM always changes their mind.

OrmLite provides callback for you to decide how to upgrade.

@Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource, final int oldVersion, final int newVersion) {
        if (newVersion > oldVersion) {
    	   //run upgrade
        }
    }

We follow this guide here and we were happy until PM changes his mind on how to contact the customers.

 if (oldVersion < 8 && newVersion >= 8) {
 	TableUtils.createTableIfNotExists(connectionSource, Customer.class);
 }

 ...//A lot of thing happens here

 if (oldVersion < 12 && newVersion >= 12) {
      dao.executeRaw("ALTER TABLE `customer` ADD COLUMN contact_number VARCHAR DEFAULT ``;");
 }

We were screwed because the app could not complete the upgrade after upgrade. The issue here is that you should not use createTableIfNotExists to update the database. Let's assume that the user wants to upgrade his database from version 7 to version 8. He gonna hit the 1st statement to create the database and before the 2nd statement runs, he already has the column named customer. The definition of Customer changes over time! You should always write raw query to update the database becuase the query won't change.

 if (oldVersion < 8 && newVersion >= 8) {
    dao.executeRaw("CREATE TABLE `customer` ......");
 }

 ...//A lot of thing happens here

 if (oldVersion < 12 && newVersion >= 12) {
 	dao.executeRaw("ALTER TABLE `customer` ADD COLUMN contact_number VARCHAR DEFAULT ``;");
 }

Beyond OrmLite

Besides OrmLite, there are plenty of options out there and we tried some of them like greenDAO and sugar ORM. Both of them works well as long as you are familiar with routine. We also are interested to explore SQLCipher in the near future.

Last but not least, no matter whatever ORM you use, it will add dependency to the app/libraries. In the case of shared library packed as AAR for internal distribution, you may give second thoughts to whether you need an ORM solution or not. If you just need a table to cache the data, it will not take you much time to write everything in raw query. Yes, it is silly to write SELECT query without an ORM and I fully agree with you....