Fun with SQLite and ORMLite

Need to store complex objects in SQLite with ease?
Need document-based database like MongoDB?

It's actually very easy to do. This approach is intended to be used in Android but also works well outside. Just to be clear - this article describes how to store complex objects with tons of sub-objects, arrays and other stuff in DB without need of multiple tables and foreign keys. This is ideal approach for beginners and applications with lightly-loaded DBs.

Disclaimer: don't use it if you have simple objects, heavy-loaded DBs or if you need relations between tables. In these cases it will gain nothing but negative impact on performance.

So if you're still here lets take for example following class:

class ComplexData {
  public String id;
  public long timestamp;

  // some complex data here
}

Depending on how complex data is, it might require few tables with foreign keys. But we can go another way. Create a table with id, timestamp and text columns and write serialized object into text column. Id and timestamp columns are taken just as an example in case we need to search objects by both values.

We'll need DAO and DBO in case we're using ORMLite. DBO will look something like this:

@DatabaseTable(tableName = "complex_data")
class ComplexDataDbo {
  private ComplexData mObject;

  @DatabaseField(id = true, useGetSet = true)
  private String id;

  @DatabaseField(useGetSet = true)
  private long timestamp;

  @DatabaseField(useGetSet = true)
  private String json;

  private ComplexDataDbo() {
  }

  public ComplexDataDbo(ComplexData data) {
    mObject = data;
  }

  public ComplexData getComplexData() {
    return mObject;
  }

  public String getId() {
    return mObject != null ? mObject.id : "";
  }

  public void setId(String id) {
  }

  public long getTimestamp() {
    return mObject != null ? mObject.timestamp : 0;
  }

  public void setTimestamp(long timestamp) {
  }

  public String getJson() {
    return PackUtils.pack(mObject); // I'm using Jackson for serialization
  }

  public void setJson(String json) {
    mObject = PackUtils.unpack(ComplexData.class, json);
  }
}

Now we need to create DAO. It is very simple with help of ORMLite:

private Dao<ComplexDataDbo, String> mComplexDataDao;

// ...

ConnectionSource connection = new AndroidConnectionSource(...);

mComplexDataDao = DaoManager.createDao(connection, ComplexDataDbo.class);
TableUtils.createTableIfNotExists(connection, ComplexDataDbo.class);

Now we can add objects to DB like this:

mComplexDataDao.createOrUpdate(new ComplexDataDbo(complexData));

And fetch them:

ComplexData complexData = mComplexDataDao.queryForId(id).getComplexData();

For more advanced search we can use:

List<ComplexDataDbo> dbos = mComplexDataDao.queryBuilder()
    .offset(offset).limit(limit)
    .where().lt("timestamp", timestamp)
    .query();

Thats it! I've already used this approach with success in two project so be sure - it is tested and performs well.