Frustrations With Data Access on Android + Intro to OrmLite

Working in the .Net world during the day and doing some Android development in my spare time has a tendency to bias my opinion against Java. Don't get me wrong, I'll take Java over C/C++/Objective-C any day, but the lack of lambdas and delegates can get frustrating when you are spoiled by .Net's recent trend towards functional programming. This really becomes noticeable at the data access layer of Android applications where the API's for interacting with the onboard SQLite database can best be described as adequate.

Thankfully API problems, unlike core system architecture problems, are easily overcome by open source software and your fellow developers experiencing the same problems as you. 

After having just about enough inline SQL to drive me crazy I decided to investigate what Object Relational Mappers (ORM) are available for the Android OS. Since Android is based on its own implementation of the Java Virtual Machine there are a lot of Java applicable tools out there. 

The first and most popular you may run across when looking around is Hibernate. Quite possibly one of the older and more popular open source ORM's available, it was originally built for larger Java applications than a simple Android app. 

Another common one you will find is OrmLite. OrmLite is designed to be much more lightweight and smaller scale than Hibernate. This means that it does sacrifice some of the power that Hibernate has, but instead has simplicity and very small Jar files to add to your project.

Because of this I have arbitrarily started exploring OrmLite as my choice for Android development. Later on I may decide to go with Hibernate, but for now its OrmLite. 

So lets say you have run into this same problem and have decided that you too are sick of endless Cursor manipulation and are ready to spend more time writing Android apps and less time praying that the rawQuery statement you just wrote is valid SQL syntax.

1. The first thing you need to do is add the Jar's to your project. Go to the OrmLite website and download the ormlite-core and ormlite-android jar files. 
2. Add these to your project. Assuming you are using Eclipse, right click on the project name and choose Properties. Then under Java Build Path->Libraries choose Add External JARs... and select the two Jar's that you just downloaded.
3. First you want to create the model class that you are going to be working with. OrmLite relies on Annotations to make sense of your class. Use the @DatabaseTable annotation to mark your class as a table, and the @DatabaseField annotations to mark your properties as fields. Here is an example of a super simple data class. In it you can see that I set the id field to be the primary key for the table:
   
   

   import com.j256.ormlite.table.DatabaseTable;
   import com.j256.ormlite.field.DatabaseField;
   
   @DatabaseTable(tableName="data")
   public class Data {
    
    @DatabaseField(id = true)
    private long id;
    @DatabaseField
    private String value;
    
    public Data() {
     
    }
    
    public Data(long id, String value) {
     this.id = id;
     this.value = value;
    }
    
    public long getId() {
     return this.id;
    }
    
    public String getValue() {
     return this.value;
    }
   }
   

4. Now you need to create your database helper class with the verbosely named OrmLiteSqliteOpenHelper. If you have created SqliteOpenHelper classes before, this should feel very similar. Setup your constructor and override the onCreate and onUpgrade methods as you would a normal SqliteOpenHelper. One important note is that OrmLite relies on Data Access Objects to act as an intermediary layer on top of your database. This is what abstracts away the details you didn't want to deal with when you decided to go the ORM route. Because of this, it is recommended to give yourself a public method that returns yourself a Dao object specific to the type you are working with. The Dao is a generic class, where the first type is the type of the model you want to work with and the second type is the type of the property you marked as the id in your model. In my example I used a long for the id so I create a Dao<Data, Long>, but if you hadn't set an id property you should be able to use the Object type. Example class:
   
   

   import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
   import com.j256.ormlite.dao.Dao;
   import com.j256.ormlite.support.ConnectionSource;
   import com.j256.ormlite.table.TableUtils;
   
   public class DatabaseHelper extends OrmLiteSqliteOpenHelper {
   
    private static final String DATABASE_NAME = "test.db";
    private static final int DATABASE_VERSION = 1;
    
    private Dao mDao;
    
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);  
        mDao = null;
    }
    
    @Override
    public void onCreate(SQLiteDatabase db, ConnectionSource connectionSource) {
        try {
            TableUtils.createTable(connectionSource, Data.class);
        } catch(SQLException e) {
            Log.e(DatabaseHelper.class.getName(), "Can't create database.");
            throw new RuntimeException(e);
        }
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) {
        try {
            TableUtils.dropTable(connectionSource, Data.class, true);
     
            onCreate(db, connectionSource);
        } catch(SQLException e) {
             Log.e(DatabaseHelper.class.getName(), "Can't drop databases.");
            throw new RuntimeException(e);
        }
    }
    
    public Dao getDataDao() throws SQLException {
     if (mDao == null) {
      mDao = getDao(Data.class);
      
     }
     
     return mDao;
    }
    
   }
   

5. The easiest way to start using your DatabaseHelper from your Activity is to create an OrmLiteBaseActivity. This gives you a few added methods to simplify things for you. For example it gives you the getHelper() method that returns an instance of the DatabaseHelper you wanted to work with. Since you went ahead and created a public method to return yourself and instance of the Dao you also now have an easy way to get at a Dao for doing your data manipulation. Example class:
   
   

   import java.sql.SQLException;
   import java.util.List;
   
   import com.j256.ormlite.android.apptools.OrmLiteBaseActivity;
   import com.j256.ormlite.dao.Dao;
   import android.os.Bundle;
   import android.widget.TextView;
   
   public class Main extends OrmLiteBaseActivity {
    
       /** Called when the activity is first created. */
       @Override
       public void onCreate(Bundle savedInstanceState) {
           super.onCreate(savedInstanceState);
           try {
        Dao dao = getHelper().getDataDao();
               Data d = new Data(1, "Hello World");  
        dao.create(d);
    } catch (SQLException e) {
        e.printStackTrace();
    }
     
    try {
        Dao laterDao = getHelper().getDataDao();
        List result = laterDao.queryForMatching(new Data(1, "Hello World"));
               TextView tv = new TextView(this);
        tv.setText(result.get(0).getValue());
        setContentView(tv);
    } catch (SQLException e) {
        e.printStackTrace();
    }  
           
       }
       
   }