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:

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:

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

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…

…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.

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

Singel Object

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

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:

Now we can create our object:

… and then we save it to the database:

That’s it! We have stored data!

update

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

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.

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.

delete

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

query

To query for all users:

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

queryBuilder

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

To select objects where a field is equals to something:

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

To select objects where a field is between two values:

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:

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.

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

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.

And to access the Role object:

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.

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:

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

Lets list all users and their e-mail addresses:

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.

Then we need a relation class lets call it UserProject

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

And getting all the users for one project.

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”.

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.

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:

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

Source

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

5 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!

Trackbacks for this post

  1. Android – ORMLite | Best Android Libraries

Leave a Reply