Using ORMLite in Android projects

Preamble:
(If you just want to start hacking right away please skip this philosophical piece and go straight to the Tutorial)
There are a lot of cases when it makes sense to store data in a mobile app. In iOS you have several ways to do it, including a powerful CoreData framework which allows persisting object graphs of a significant complexity.
In Android there are also several ways to do it, however the most advanced (in terms of flexibility and convenience) way to store structured data in Android is to use an SQLite relational database.

Probably everyone knows this, but still:
SQLite is basically a special file format and a set of APIs implemented in a library that ships with Android OS.  Like most relational databases management systems SQLite API allows to manipulate data with SQL queries.

The programming model in which one has to manipulate data with plain SQL queries inside the application business logic is tedious, inconvenient and outdated.  It should be forbidden to write spaghetti like code filled with SQL-queries in XXI century.  Even if you are completely forced to write SQL queries in code and if you are a good programmer - you will find yourself writing some sort of framework that encapsulates common query routines and allows you to easier do similar operations on different data entities.  What is more tedious, time-consuming and inconvenient - is dealing with SQLite API (anyone ever worked with SQLite C API ?).

The convenient programming model for me is:
- to be able to create/update objects in code and persist them by calling a method on a certain DB-manager object,
- to be able to fetch a set of objects with a certain predicate defined for one/several of the object's fields

This programming model is generally achieved with the help of any persistance/ORM framework such as Hibernate, however the latter is pretty heavy to be used in Android.  There is an excellent alternative called ORMLite.  As the name implies - it is a lightweight ORM framework, and it turns out to be well suited for Android.


Tutorial:
Let's do a simple tutorial that will help you get started with using ORMLite in your own project.
The full source code is hosted on GitHub: https://github.com/justadreamer/WishListManager.  I strongly encourage you to download the source code, import it to your Eclipse workspace and play with the ready-made app.  After this I advice to start the project from scratch in another IDE instance and use the source code only in case of uncertainty/confusion, otherwise following the tutorial steps.



Goal:
Develop a simple wish-list application.  All it will let you do - add lists and items to these lists.  Each item will have a name, description and URL.  Each list will just have a name.  There will be a standard set of actions for both lists and items in lists - add/delete/view/edit.

Analysis:
In this app we apparently will have two entities that can be mapped to corresponding classes: Item entity and List entity.  List is not a very good name as it is used as a name for a widely-used collection interface in Java.  So we will call it a WishList, and for the sake of consistency another entity will be called WishItem.  We will use ORMLite as promised for data persistance in the app.

Development:
Step 1.
Download the ORMLite libraries (jar files) - at the time of this writing from here.  I used release 4.31 shipped on Dec. 8 2011. You need ormlite-core and ormlite-android libraries.  It is also a good idea to download source and javadoc as well for both.


Step 2.
Create a new project in eclipse - call it WishListManager:


Let's set the app's target to Android 2.2 (API level 8):


And for the package name choose something simple like com.test


Step 3.
Add the downloaded ORMLite libraries into the project:
Under the WishListManager project tree create a folder 'libs':

Now either using your file-system exploring application or a command line copy the downloaded ormlite libraries into the 'libs' folder, tap F5 or right-click and choose Refresh on the 'libs' folder, and you should have something that looks like this:


Now we have to make sure our project class path is aware of these libraries, so right-click the project, select Properties, choose Java Build Path on the left and select the Libraries tab on the top.  Click 'Add JARs' button on the right, navigate to 'libs' folder and select ormlite-core-<ver>.jar and ormlite-android-<ver>.jar files:

After this - an optional but recommended step is to attach javadoc and source to these libraries:

Open the dropdown under ormlite-android-<ver>.jar - select source, click 'Edit' button on the right, then click Workspace button - to select the source archive from the Workspace - and select ormlite-android-<ver>-sources.jar.

To add javadoc - you click 'Edit' button, and then again select the Javadoc Archive option, and then specify a javadoc archive in your workspace:

Do the same thing with ormlite-core-<ver>.jar.  In the end you should have something similar to this:

Step 4. 

Now that we've added the ormlite libraries we can start coding classes that will correspond to our models.  Create a new package: com.test.model.  

And then inside this package two new classes: WishList and WishItem.  The code for them is below:

WishList.java:

package com.test.model;

import java.util.ArrayList;
import java.util.List;

import com.j256.ormlite.dao.ForeignCollection;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.field.ForeignCollectionField;
import com.j256.ormlite.table.DatabaseTable;

@DatabaseTable
public class WishList {
    @DatabaseField(generatedId=true)
    private int id;
    
    @DatabaseField
    private String name;
    
    @ForeignCollectionField
    private ForeignCollection<WishItem> items;

    public void setId(int id) {
        this.id = id;
    }

    public int getId() {
        return id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public void setItems(ForeignCollection<WishItem> items) {
        this.items = items;
    }

    public List<WishItem> getItems() {
        ArrayList<WishItem> itemList = new ArrayList<WishItem>();
        for (WishItem item : items) {
            itemList.add(item);
        }
        return itemList;
    }
}

WishItem.java:

package com.test.model;

import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;

@DatabaseTable
public class WishItem {
    @DatabaseField(generatedId=true)
    private int id;
    
    @DatabaseField
    private String name;

    @DatabaseField
    private String description;

    @DatabaseField(foreign=true,foreignAutoRefresh=true)
    private WishList list;

    public void setId(int id) {
        this.id = id;
    }

    public int getId() {
        return id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public void setList(WishList list) {
        this.list = list;
    }

    public WishList getList() {
        return list;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getDescription() {
        return description;
    }
}




Notice the use of annotations. 
@DatabaseTable annotation tells the ORMLite to create a corresponding table to keep records corresponding to objects of this class.
@DatabaseField annotation tells the ORMLite that the field corresponds to a column in a table.
Please also notice how we manage a relation between WishList 1 - M WishItem.  The special type ForeignCollection (with a special annotation @ForeignCollectionField) is defined in ORMLite library, therefore we encapsulate it in the method getItems() and return a plain java list of objects.  You might have more questions, however for the sake of this tutorial just copy the code and continue - it will all fit together eventually.
Step 5. Create a database helper class.
It is time to write some code that will actually wire our program to the database.  This will be a somewhat boilerplate code that you will have to copy in one form or the other from project to project.  So first we'll create a package called com.test.db.  And in this package we'll create a class DatabaseHelper, here is the code:
DatabaseHelper.java:
package com.test.db;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

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;
import com.test.model.WishItem;
import com.test.model.WishList;

public class DatabaseHelper extends OrmLiteSqliteOpenHelper {
    // name of the database file for your application -- change to something appropriate for your app
    private static final String DATABASE_NAME = "WishListDB.sqlite";

    // any time you make changes to your database objects, you may have to increase the database version
    private static final int DATABASE_VERSION = 1;

    // the DAO object we use to access the SimpleData table
    private Dao<WishList, Integer> wishListDao = null;
    private Dao<WishItem, Integer> wishItemDao = null;

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database,ConnectionSource connectionSource) {
        try {
            TableUtils.createTable(connectionSource, WishList.class);
            TableUtils.createTable(connectionSource, WishItem.class);
        } catch (SQLException e) {
            Log.e(DatabaseHelper.class.getName(), "Can't create database", e);
            throw new RuntimeException(e);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        
    }

    @Override
    public void onUpgrade(SQLiteDatabase db,ConnectionSource connectionSource, int oldVersion, int newVersion) {
        try {
            List<String> allSql = new ArrayList<String>(); 
            switch(oldVersion) 
            {
              case 1: 
                  //allSql.add("alter table AdData add column `new_col` VARCHAR");
                  //allSql.add("alter table AdData add column `new_col2` VARCHAR");
            }
            for (String sql : allSql) {
                db.execSQL(sql);
            }
        } catch (SQLException e) {
            Log.e(DatabaseHelper.class.getName(), "exception during onUpgrade", e);
            throw new RuntimeException(e);
        }
        
    }

    public Dao<WishList, Integer> getWishListDao() {
        if (null == wishListDao) {
            try {
                wishListDao = getDao(WishList.class);
            }catch (java.sql.SQLException e) {
                e.printStackTrace();
            }
        }
        return wishListDao;
    }

    public Dao<WishItem, Integer> getWishItemDao() {
        if (null == wishItemDao) {
            try {
                wishItemDao = getDao(WishItem.class);
            }catch (java.sql.SQLException e) {
                e.printStackTrace();
            }
        }
        return wishItemDao;
    }

}
As you can see the key points are onCreate and onUpdate methods.  onCreate method is called only once, when there was no database file on disk.  onUpdate method is called when the version of the current db file is different from the specified in the DATABASE_VERSION constant - this will happen when your app updates and the db schema has to change with the update - the stub implementation of the method gives you an idea on how to handle the data migration and the db schema changes.


There is also a misterios Dao object created for each entity.  Dao stands for Database Access Object - this is an object we'll actively use in the next steps to actually manage the stored entities.  Think of this object as its name tells you to - this is the gateway to the database.

We need one more object that will encapsulate all the interactions with the DAO - DatabaseManager.java in the same package, let's create a basic code for it, and then we will add methods gradually as we need them in code:


package com.test.db;

import java.sql.SQLException;
import java.util.List;

import android.content.Context;

import com.test.model.WishList;

public class DatabaseManager {

    static private DatabaseManager instance;

    static public void init(Context ctx) {
        if (null==instance) {
            instance = new DatabaseManager(ctx);
        }
    }

    static public DatabaseManager getInstance() {
        return instance;
    }

    private DatabaseHelper helper;
    private DatabaseManager(Context ctx) {
        helper = new DatabaseHelper(ctx);
    }

    private DatabaseHelper getHelper() {
        return helper;
    }

    public List<WishList> getAllWishLists() {
        List<WishList> wishLists = null;
        try {
            wishLists = getHelper().getWishListDao().queryForAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return wishLists;
    }
}


This is a singleton that encapsulates work with DAO objects and will be used as a another layer of abstraction in our app.  As you see we already added the getAllWishLists() convenience method, so that we could easily use it in the next step.  We will extend this class with other methods as we need them.


Step 6. Code the WishListManagerActivity. 
First let's discuss what functions will WishListManagerActivity fulfill.  It should allow list, edit, delete the WishList entities.  As we have all the wiring for the database and also data models implemented - we can go straight into the UI code. 
The UI for the main screen will look like this:










The code for the main.xml file is:


<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >
    <Button
        android:id="@+id/button_add"
        android:layout_alignParentBottom="true"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="Add wish list" />
    <ListView
        android:id="@+id/list_view"
        android:layout_above="@id/button_add"
        android:layout_width="fill_parent"
        android:layout_height="fill_parent" />
</RelativeLayout>





The code for the onCreate method of the WishListManagerActivity is as follows:


public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        DatabaseManager.init(this);

        ViewGroup contentView = (ViewGroup) getLayoutInflater().inflate(R.layout.main, null);
        listView = (ListView) contentView.findViewById(R.id.list_view);
        
        Button btn = (Button) contentView.findViewById(R.id.button_add);
        setupButton(btn);
        setContentView(contentView);
    }







We inflate the layout, find the ListView and save it in a class member variable to later use it in the setupListView which is called from the onStart method. 

private void setupListView(ListView lv) {
        final List<WishList> wishLists = DatabaseManager.getInstance().getAllWishLists();
        
        List<String> titles = new ArrayList<String>();
        for (WishList wl : wishLists) {
            titles.add(wl.getName());
        }

        ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, titles);
        lv.setAdapter(adapter);

        final Activity activity = this;
        lv.setOnItemClickListener(new OnItemClickListener() {

            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                WishList wishList = wishLists.get(position);
                Intent intent = new Intent(activity, WishItemListActivity.class);
                intent.putExtra(Constants.keyWishListId, wishList.getId());
                startActivity(intent);
            }
        });
    }

This piece is interesting because here we first get all WishList entities from DatabaseManager.   Then we create a list of names and provide this list to the ArrayAdapter<String>.  An interesting part is the OnItemClickListener. On click even we start a new WishItemListActivity and pass the id of the clicked wish list in the bundle - the WishList entity will have to be queried again from the DB in the spawned WishItemListActivity and the WishItem entities belonging to this WishList will have to be displayed - this I will demonstrate in our next step.  Please add all other necessary code to the WishListManagerActivity (see below) And let's go on to coding the AddWishListActivity, which will allow to add new WishList entities.


WishListManagerActivity.java:
package com.test;

import java.util.ArrayList;
import java.util.List;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.ListView;

import com.test.db.DatabaseManager;
import com.test.model.WishList;

public class WishListManagerActivity extends Activity {
    ListView listView;

    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        DatabaseManager.init(this);

        ViewGroup contentView = (ViewGroup) getLayoutInflater().inflate(R.layout.main, null);
        listView = (ListView) contentView.findViewById(R.id.list_view);
        
        Button btn = (Button) contentView.findViewById(R.id.button_add);
        setupButton(btn);
        setContentView(contentView);
    }

    @Override
    protected void onStart() {
        super.onStart();
        setupListView(listView);
    }

    private void setupListView(ListView lv) {
        final List<WishList> wishLists = DatabaseManager.getInstance().getAllWishLists();
        
        List<String> titles = new ArrayList<String>();
        for (WishList wl : wishLists) {
            titles.add(wl.getName());
        }

        ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, titles);
        lv.setAdapter(adapter);

        final Activity activity = this;
        lv.setOnItemClickListener(new OnItemClickListener() {

            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                WishList wishList = wishLists.get(position);
                Intent intent = new Intent(activity, WishItemListActivity.class);
                intent.putExtra(Constants.keyWishListId, wishList.getId());
                startActivity(intent);
            }
        });
    }
    
    private void setupButton(Button btn) {
        final Activity activity = this;
        btn.setOnClickListener(new OnClickListener() {
            public void onClick(View v) {
                Intent intent = new Intent(activity,AddWishListActivity.class);
                startActivity (intent);
            }
        });
    }
}



Step 7 Code AddWishListActivity to be able to create/edit new WishList entities
Create a new class AddWishListActivity.java in the package com.test.  Add this line to the AndroidManifest.xml:
<activity
            android:label="Add wish list" 
            android:name=".AddWishListActivity" />

We will use this class both for adding a new wish list and also for editing the name of the existing WishList.


First let's design the UI:





And the add_wish_list.xml looks like this:


<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >
    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Wish list name:" />
    <EditText
        android:id="@+id/edit"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >
        <requestFocus />
    </EditText>
    <Button
        android:id="@+id/button_save"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Save" />
</LinearLayout>


As you see very simple UI, so let's add the logic:

Let's start AddWishListActivity with this code:
package com.test;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.widget.Button;
import android.widget.EditText;

import com.test.db.DatabaseManager;
import com.test.model.WishList;

public class AddWishListActivity extends Activity {
    private EditText edit;
    private WishList wishList;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        ViewGroup contentView = (ViewGroup) getLayoutInflater().inflate(R.layout.add_wish_list, null);
        edit = (EditText) contentView.findViewById(R.id.edit);

        Button btn = (Button) contentView.findViewById(R.id.button_save);
        setupButton(btn);
        
        setupWishList();
        setContentView(contentView);
    }


As you see in onCreate() method we as usual get setup the view components, but most interesting stuff happens in setupWishList() method:





private void setupWishList() {
        Bundle bundle = getIntent().getExtras();
        if (null!=bundle && bundle.containsKey(Constants.keyWishListId)) {
            int wishListId = bundle.getInt(Constants.keyWishListId);
            wishList = DatabaseManager.getInstance().getWishListWithId(wishListId);
            edit.setText(wishList.getName());
        }
    }




The purpose of this method - is to check whether there was an wishListId passed - if it was then we are in the editing state, otherwise we are creating a new WishList.   


Here we check whether the bundle has been attached to the intent and whether it contains the object mapped to Constants.keyWishListId key.  By the way.  Constants is a special class that you need to add  - it just contains some globally visible constants, namely the String key identifiers:
Constants.java:


package com.test;

public class Constants {
    public static final String keyWishListId = "wishListId";
    public static final String keyWishItemId = "wishItemId";
}







Ok with this out of the way - we can go on with WishList entity that we get with the DatabaseManager method - getWishListWithId, here is the code for it:


    public WishList getWishListWithId(int wishListId) {
        WishList wishList = null;
        try {
            wishList = getHelper().getWishListDao().queryForId(wishListId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return wishList;
    }



As you see, all we do is ask the corresponding helper to get us the entity with the needed id and catch some exceptions :).  So once we acquired the WishList entity (if it was passed) we are in the editing state and need to set the correspondent EditText field value - to the name of the passed WishList entity.


Back to the AddWishListActivity - let's add the setupButton method:

    private void setupButton(Button btn) {
        final Activity activity = this;
        btn.setOnClickListener(new OnClickListener() {
            public void onClick(View v) {
                String name = edit.getText().toString();
                if (null!=name && name.length()>0) {
                    if (null!=wishList) {
                        updateWishList(name);
                    } else {
                        createNewWishList(name);
                    }
                    finish();
                } else {
                    new AlertDialog.Builder(activity)
                    .setTitle("Error")
                    .setMessage("Invalid name!")
                    .setNegativeButton("OK", new DialogInterface.OnClickListener() {
                        public void onClick(DialogInterface dialog, int which) {
                            dialog.dismiss();
                        }
                    })
                    .show();
                }
            }
        });
    }


 This is a fairly complex method, however, basically it just sets the OnClickListener for the button and inside the OnClickListener.onClick method it does a check - whether the name for the WishList is a valid (non empty) string, and in case it is not - displays an AlertDialog, otherwise it does one of the two things.  Namely - if wishList member variable is set - we are in the editing state, and thus we call the updateWishList method.  Otherwise we call createNewWishList method.  The code for these methods is below, add them to the activity class code:


    private void updateWishList(String name) {
        if (null!=wishList) {
            wishList.setName(name);
            DatabaseManager.getInstance().updateWishList(wishList);
        }
    }

    private void createNewWishList(String name) {
        WishList l = new WishList();
        l.setName(name);
        DatabaseManager.getInstance().addWishList(l);
    }


Again everything happens with a "little help of our friend" DatabaseManager, add these methods to it:
    public void addWishList(WishList l) {
        try {
            getHelper().getWishListDao().create(l);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateWishList(WishList wishList) {
        try {
            getHelper().getWishListDao().update(wishList);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


The implementation is trivial and needs no comments.  All we do here is create a new WishList entity or update an existing one - to persist the changes to DB.


Finally in the setupButton method we call activity's finish() method which closes it and we go back to WishListManagerActivity. The full source code for the AddWishListActivity is below:


package com.test;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.widget.Button;
import android.widget.EditText;

import com.test.db.DatabaseManager;
import com.test.model.WishList;

public class AddWishListActivity extends Activity {
    private EditText edit;
    private WishList wishList;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        ViewGroup contentView = (ViewGroup) getLayoutInflater().inflate(R.layout.add_wish_list, null);
        edit = (EditText) contentView.findViewById(R.id.edit);

        Button btn = (Button) contentView.findViewById(R.id.button_save);
        setupButton(btn);
        
        setupWishList();
        setContentView(contentView);
    }
    
    private void setupWishList() {
        Bundle bundle = getIntent().getExtras();
        if (null!=bundle && bundle.containsKey(Constants.keyWishListId)) {
            int wishListId = bundle.getInt(Constants.keyWishListId);
            wishList = DatabaseManager.getInstance().getWishListWithId(wishListId);
            edit.setText(wishList.getName());
        }
    }

    private void setupButton(Button btn) {
        final Activity activity = this;
        btn.setOnClickListener(new OnClickListener() {
            public void onClick(View v) {
                String name = edit.getText().toString();
                if (null!=name && name.length()>0) {
                    if (null!=wishList) {
                        updateWishList(name);
                    } else {
                        createNewWishList(name);
                    }
                    finish();
                } else {
                    new AlertDialog.Builder(activity)
                    .setTitle("Error")
                    .setMessage("Invalid name!")
                    .setNegativeButton("OK", new DialogInterface.OnClickListener() {
                        public void onClick(DialogInterface dialog, int which) {
                            dialog.dismiss();
                        }
                    })
                    .show();
                }
            }
        });
    }

    private void updateWishList(String name) {
        if (null!=wishList) {
            wishList.setName(name);
            DatabaseManager.getInstance().updateWishList(wishList);
        }
    }

    private void createNewWishList(String name) {
        WishList l = new WishList();
        l.setName(name);
        DatabaseManager.getInstance().addWishList(l);
    }
}


This concludes the first very basic part of the tutorial.  By now you should be able to launch the app and add/edit WishList entities.  


To be continued.