Android – ORMLite

Android and databases are powerful and really good to have but it is also not so fun working with. I’m going to show if not a funny way at least an easier way of working with SQLLite. I’m using ORMLite that is a Lightweight Object Relational Mapping that gives you the most features you need in a simple way and still gives you access to the SQL if needed.

Setting up ORMLite

Setting up ORMLite for your project is easy just add the dependency to your app’s build.gradle file.

First add ORMLite dependencies in your gradle.build file:

dependencies {

    ...

    compile 'com.j256.ormlite:ormlite-android:4.48'
}

Database Helper

If you have worked with SQLite on Android you have worked with the SQLiteOpenHelper class. When working with ORMLite you need to extend the OrmLiteSqliteOpenHelper instead, but they are similar in the way that they have an onCreate, an onUpgrade ect. And you do the same kind of operations in them but with the help of ORMLite.

To create a table you use the TableUtils class like this:

TableUtils.createTable(connectionSource, User.class);

This line create a table from the domain object I have called User. To delete the same table you use this line:

TableUtils.dropTable(connectionSource, User.class, true);

The last boolean, ignoreErrors, is to indicate if ORMLite should stop on first error or keep trying the rest.

With ORMLite you also need to have a dao (data access object) to work with your data. You get this dao objects by calling on getDAO…

public Dao getUserDao() throws SQLException {
    if (mUserDao == null) {
        mUserDao = getDao(User.class);
    }

    return mUserDao;
}

…or getRuntimeExceptionDao even though it is easier to work with I would suggest ignoring that one, no body likes a runtime exception and don’t forget to nullify the reference to the dao object when closing the database.

public RuntimeExceptionDao getUserRuntimeExceptionDao() throws SQLException {
    if (mUserRuntimeExceptionDao == null) {
        mUserRuntimeExceptionDao = getRuntimeExceptionDao(User.class);
    }

    return mUserRuntimeExceptionDao;
}

The complete code for this database helper would look something like this:

public class DatabaseHelper extends OrmLiteSqliteOpenHelper {

    private static final String DATABASE_NAME    = "ormlite.db";
    private static final int    DATABASE_VERSION = 1;

    private Dao mUserDao = null;

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

    @Override
    public void onCreate(SQLiteDatabase db, ConnectionSource connectionSource) {
        try {
            TableUtils.createTable(connectionSource, User.class);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, 
                int oldVersion, int newVersion) {
        try {
            TableUtils.dropTable(connectionSource, User.class, true);
            onCreate(db, connectionSource);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /* User */

    public Dao getUserDao() throws SQLException {
        if (mUserDao == null) {
            mUserDao = getDao(User.class);
        }

        return mUserDao;
    }

    @Override
    public void close() {
        mUserDao = null;

        super.close();
    }
}

Singel Object

So to the object we like to store in the database, in this case it is called User.

@DatabaseTable(tableName = User.TABLE_NAME_USERS, daoClass = CustomDao.class)
public class User {

    public static final String TABLE_NAME_USERS = "users";

    public static final String FIELD_NAME_ID     = "id";
    public static final String FIELD_NAME_NAME   = "name";

    @DatabaseField(columnName = FIELD_NAME_ID, generatedId = true)
    private int mId;

    @DatabaseField(columnName = FIELD_NAME_NAME)
    private String mName;

    public User() {
        // Don't forget the empty constructor, needed by ORMLite.
    }

    /** Getters & Setters **/

    ...

}

It is here in your object you define the table, you can use annotations for setting up the name to use, if you skip annotations ORMLite will use the class and field names. I prefer to use annotations since it gives me a better control. The @DatabaseTable let me chose name for the table and the @DatabaseField let me chose name for the field and some extra settings as well.

I prefer to have the names as constants for easier access when doing queries later on.

Ok, so we have our helper and we have an object we like to store. Let store some data.

create

First thing is to obtain the dao object:

DatabaseHelper     helper  = new DatabaseHelper(this);
Dao userDao = null;
try {
    userDao = helper.getUserDao();
} catch (SQLException e) {
    e.printStackTrace();
}

Now we can create our object:

User user = new User().setName("Mike");

… and then we save it to the database:

try {
    userDao.create(object);
} catch (SQLException e) {
    e.printStackTrace();
}

That’s it! We have stored data!

update

If we update our object and need to save it we use the update method:

user.setName("Michael");

try {
    userDao.update(user);
} catch (SQLException e) {
    e.printStackTrace();
}

createOrUpdate

If we don’t know if the object already is saved or not we can use createOrUpdate, it is slower but if the object already exist it will be updated otherwise it will be created.

try {
    userDao.createOrUpdate(user);
} catch (SQLException e) {
    e.printStackTrace();
}

refresh

You might end up in a situation where you need to ensure the object you have is in sync with the database, then you can refresh the object.

try {
    userDao.refresh(user);
} catch (SQLException e) {
    e.printStackTrace();
}

delete

And delete what would a database be if we could not delete:

try {
    userDao.delete(user);
} catch (SQLException e) {
    e.printStackTrace();
}

query

To query for all users:

final List users = userDao.queryForAll();

Or if you just want one and you have the id:

User user = userDao.queryForId(userId);

queryBuilder

There is something called a query builder for more complex selections.

To select objects where a field is equals to something:

userDao.queryBuilder()
    .where()
    .eq(User.FIELD_NAME_NAME, "Mike")
    .query();

To select objects where a field is equals to something and/or something else:

userDao.queryBuilder()
    .where()
    .eq(User.FIELD_NAME_NAME, "Mike")
    .and() // :or()
    .eq(User.FIELD_NAME_EMAILS, "email@example.com")
    .query();

To select objects where a field is between two values:

userDao.queryBuilder()
    .where()
    .between(User.FIELD_NAME_ID, 0, 5)
    .query();

All this queries can be ended with .query() for a collection or with .queryForFirst() for a singel object.

You can also count the number or items in a selection:

userDao.queryBuilder()
    .where()
    .eq(User.FIELD_NAME_NAME, "Mike")
    .countOf();

There is a lot of more you can do read more about it here: ORMLite documentation and you will see a more complex query with joins further down under many-to-many relations.

Relationship

One-to-one

One-to-one relationships are easy, start by creating the foreign object we call it Role and don’t forget to add it to the database helper.

@DatabaseTable(tableName = Role.TABLE_NAME_ROLES)
public class Role {

    public static final String TABLE_NAME_ROLES = "roles";

    public static final String FIELD_NAME_ID   = "id";
    public static final String FIELD_NAME_ROLE = "role_name";

    @DatabaseField(columnName = FIELD_NAME_ID, generatedId = true)
    private int mId;

    @DatabaseField(columnName = FIELD_NAME_ROLE)
    private String mRoleName;

    public Role() {
        // Don't forget the empty constructor, needed by ORMLite.
    }

    /** Getters & Setters **/

    ...

}

When the foreign class is defined we add it to our User class.

@DatabaseTable(tableName = "users")
public class User {

    public static final String FIELD_NAME_ROLE     = "role";

    ...

    @DatabaseField(columnName = FIELD_NAME_ROLE, foreign = true, foreignAutoCreate = true,foreignAutoRefresh = true)
    private Role mRole;

    ...

}

We need to set some annotation properties so ORMLite knows what to do with the object. First we tell ORMLite that it is a foreign object with “foreign = true”.

The “foreignAutoCreate = true” tells ORMLite to automatically create the foreign object when the User object is created if the reference is not null meaning you still need to instantiate the object. You also need to have the Role objects id field set to be auto generated with “generatedId = true”

The “foreignAutoRefresh = true” means when the parent object (the User object) is refreshed the Role object will be as well.

Role role = new Role("Programmer");
User data = new User().setName("Per-Erik").setRole(role);

try {
    userDao.create(object);
} catch (SQLException e) {
    e.printStackTrace();
}

And to access the Role object:

User user = userDao.queryForId(userId);
Role role = user.getRole();

One-to-many

Lets give the user one or more email addresses. So it will have to be a collection of foreign objects. We get a little bit of help my the ForeignCollection. So lets start by adding a ForeignCollection to our User object.

@DatabaseTable(tableName = User.TABLE_NAME_USERS)
public class User {

    public static final String FIELD_NAME_EMAILS = "emails";

    ...

    // One-to-many
    @ForeignCollectionField(columnName = FIELD_NAME_EMAILS, eager = true)
    private ForeignCollection mEmails;

    ...

    public ForeignCollection getEmails() {
        return mEmails;
    }

}

The eager = true means that when the User is retrieved the emails will be retrieved as well.

To let ORMLite be able to find all emails belonging to a User the Email class need to have a foreign field connected to the User giving us an Email class looking like this:

@DatabaseTable(tableName = Email.TABLE_NAME_EMAIL)
public class Email {

    public static final String TABLE_NAME_EMAIL = "emails";

    public static final String FIELD_NAME_ID    = "id";
    public static final String FIELD_NAME_EMAIL = "email";
    public static final String FIELD_NAME_USER  = "user";

    @DatabaseField(columnName = FIELD_NAME_ID, generatedId = true)
    private int mId;

    @DatabaseField(columnName = FIELD_NAME_EMAIL)
    private String mEmail;

    @DatabaseField(columnName = FIELD_NAME_USER, foreign = true, foreignAutoRefresh = true)
    private User mUser;

    public Email() {
        // Don't forget the empty constructor, needed by ORMLite.
    }

    /** Getters & Setter **/

    ...

}

To get this to work the User object need to be created, otherwise we won’t have a user id for the email object.

try {
    User user = new User().setName("Clause");
    userDao.create(user);

    final Dao emailsDao = DatabaseHelper.getInstance().getEmailDao();
    emailsDao.create(new Email(user).setEmail("my@example.com"));
    emailsDao.create(new Email(user).setEmail("my2@example.com"));
} catch (SQLException e) {
    e.printStackTrace();
}

Lets list all users and their e-mail addresses:

try {
    final List userList = userDao.queryForAll();
    for (User user : userList) {
        System.out.println("user = " + user);
        final ForeignCollection emailList = user.getEmails();
        for (Email email : emailList) {
            System.out.println("email = " + email);
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Many-to-many

Many to Many relations are not covered by ORMLite but there is a way to do this anyway. First lets att a new class called Project.

@DatabaseTable(tableName = Project.TABLE_NAME_PROJECTS)
public class Project {

    public static final String TABLE_NAME_PROJECTS = "projects";

    public static final String FIELD_NAME_ID   = "id";
    public static final String FIELD_NAME_NAME = "name";

    @DatabaseField(columnName = FIELD_NAME_ID, generatedId = true)
    private int mId;

    @DatabaseField(columnName = FIELD_NAME_NAME)
    private String mName;

    public Project() {
        // Don't forget the empty constructor, needed by ORMLite.
    }

    /** Getters & Setters **/
    
    ...

}

Then we need a relation class lets call it UserProject

@DatabaseTable(tableName = UserProject.TABLE_NAME_USER_PROJECT)
public class UserProject {

    public static final String TABLE_NAME_USER_PROJECT = "user_project";

    public static final String FIELD_NAME_ID        = "id";
    public static final String FIELD_NAME_USER_ID   = "user_id";
    public static final String USER_NAME_PROJECT_ID = "project_id";

    @DatabaseField(columnName = FIELD_NAME_ID, generatedId = true)
    private int mId;

    @DatabaseField(foreign = true, columnName = FIELD_NAME_USER_ID)
    private User mUser;

    @DatabaseField(foreign = true, columnName = USER_NAME_PROJECT_ID)
    private Project mProject;

    public UserProject() {
        // Don't forget the empty constructor, needed by ORMLite.
    }

    /** Getters & Setters **/

    ...

}

Now for the tricky part to join these. First we look at getting all project one user is part of.

 public static List getProjects(User user) throws SQLException {
        if (sProjectsForUserQuery == null) {
            sProjectsForUserQuery = makeProjectsForUserQuery();
        }
        sProjectsForUserQuery.setArgumentHolderValue(0, user);
        return getProjectDao().query(sProjectsForUserQuery);
    }

    private static PreparedQuery makeProjectsForUserQuery() throws SQLException {
        QueryBuilder userPostQb = getUserProjectDao().queryBuilder();
        userPostQb.selectColumns(UserProject.USER_NAME_PROJECT_ID);
        userPostQb.where().eq(UserProject.FIELD_NAME_USER_ID, new SelectArg());
        QueryBuilder postQb = getProjectDao().queryBuilder();
        postQb.where().in(Project.FIELD_NAME_ID, userPostQb);
        return postQb.prepare();
    }

And getting all the users for one project.

public static List getUsers(Project project) throws SQLException {
        if (sUsersForPostQuery == null) {
            sUsersForPostQuery = makeUsersForProjectQuery();
        }
        sUsersForPostQuery.setArgumentHolderValue(0, project);
        return getUserDao().query(sUsersForPostQuery);
    }

    private static PreparedQuery makeUsersForProjectQuery() throws SQLException {
        QueryBuilder userPostQb = getUserProjectDao().queryBuilder();
        userPostQb.selectColumns(UserProject.FIELD_NAME_USER_ID);
        userPostQb.where().eq(UserProject.USER_NAME_PROJECT_ID, new SelectArg());
        QueryBuilder userQb = getUserDao().queryBuilder();
        userQb.where().in(Project.FIELD_NAME_ID, userPostQb);
        return userQb.prepare();
    }

foreign key

It happens that you need to use an external id as key. You can do that by replacing the “generatedId = true” with “id = true”.

@DatabaseField(columnName = FIELD_NAME_ID, id = true)

One example would be if you are downloading your data in a json format from a server, it is easy to map the json property names to the database just by adding gson annotations.

@DatabaseTable(tableName = User.TABLE_NAME_USERS)
public class User {

    ...

    @SerializedName("id")
    @DatabaseField(columnName = FIELD_NAME_ID, id = true)
    private int mId;

    @SerializedName("name")
    @DatabaseField(columnName = FIELD_NAME_NAME)
    private String mName;

    ...
 
}

Custom DAO

More often then not you need to get an update somewhere about changes in the database made somewhere else. One way of doing this is to create your own DAO object that sends an event when it updates the database. You can create your custom dao by extending BaseDaoImpl and eg. override the create method and send an event with Otto or EventBus or by creating your own callbacks.

A custom dao could look something like this:

public class CustomDao extends BaseDaoImpl {
    protected CustomDao(final Class dataClass) throws SQLException {
        super(dataClass);
    }

    ...

    @Override
    public int create(final T data) throws SQLException {
        int result = super.create(data);
        // Send an event with EventBus or Otto
        return result;
    }
}

To use this custom dao we need to point it out in the table annotation with the daoClass properties like this:

@DatabaseTable(tableName = User.TABLE_NAME_USERS, daoClass = CustomDao.class)
public class User {

    public static final String TABLE_NAME_USERS = "users";

    ...

}

Source

If you like to checkout the code go to: android-ormlite-tutorial.

This Post Has 8 Comments

  1. John Smith

    Thanks for the nice article. Many-to-many case is very tricky though. Also, seems like the code can get quite complicated if the number of persistent classes increase because we would need to create and use many Dao classes – one per persistent class.

  2. Luis Unitips

    Thanks, best post that I found about ORMLite because you explain the relation one-to-many very clearly.

  3. Erick

    Hello, Great tutorial! But there is a problem with the CustomDao, is throws in exception, did You solve that?

    Thanks, regards!

  4. Gabriel

    Thank you for this!

  5. ORMLiteBeginner

    Thanks, regards!

  6. Mary_W

    Thanks, nice post that I found about ORMLite! You can also try JDXA Android ORM in your project.

  7. Paulo

    Erro helper.getUserDao();

    Can’t find a no-arg constructor for class com.jayway.ormlite.model.Role

Leave a Reply