Although mobile apps do not rely on databases as much as, say, web applications, mobile app developers should still be interested in databases. There are better database solutions for Android apps than a native SQLite library; we’ll nominate ORMs (Object Relational Mapping) as one of them.

ORMs make developers’ lives easier. They let us avoid struggling with building queries by concatenating strings or manually handling the connection with database. Typos are less of threat to your queries. And, with ORMs taking care of security, you don’t have to worry about resistance to injection attacks. You can focus on your application functionalities and let the database do its job.

What You’ll Learn About Android ORMs

In this article, we’re going to take a look at some of the most popular Android ORMs:

We will also check out a new Vertabelo product – Vertabelo Mobile ORM.

Throughout this article, we’ll take the developer’s perspective by configuring the tool, adding it to an application, and using it on concrete examples. We’ll consider each ORM’s performance in the following areas:

To facilitate comparisons, we’ll examine this by category rather than reviewing each ORM individually.

We’re going to use the ORMs to represent this simple hospital-patient database model:

Let’s start with how easy each ORM is to configure.

Configuration

To get started with ORMLite, you need to download two JARs from the ORMLite Releases website (ormlite-core and ormlite-android) and add them to your project. Technically, ORMLite does not require any configuration; due to some internal Android problems, though, its authors suggest creating a configuration util, which should look like this:

public class DatabaseConfigUtil extends OrmLiteConfigUtil { public static void main(String[] args) throws SQLException, IOException { writeConfigFile("ormlite_config.txt"); } }

The purpose is to make the construction of DAOs (Data Access Objects) faster.

To use ActiveAndroid, you need to download and add a JAR to your project. Alternatively, you can install ActiveAndroid using Gradle or Maven. For Gradle, you have to include the code presented below in your build.gradle file.

repositories { mavenCentral() maven { url "https://oss.sonatype.org/content/repositories/snapshots/" } } compile 'com.michaelpardo:activeandroid:3.1.0-SNAPSHOT'

For configuration purposes, you must change the application’s name in AndroidManifest.xml. It should be set to com.activeandroid.app.Application . If your application class is already a custom one, just make it a subclass of com.activeandroid.app.Application and it will make ActiveAndroid work too.

Optionally, you can set your database’s name and version using metadata tags, as shown here:

...

greenDAO does not require any initial configuration – all you have to do is add it to your project using Maven or Gradle. If you’re not using these tools, you can download greendao and greendao-generator JARs from Maven Central and add them as a library.

Sugar ORM also has to be added to your project – you can use Gradle, Maven, or a JAR for that. Like ActiveAndroid, all we need to do is change the application’s name in AndroidManifest.xml. There are also some optional metadata tags that set the database’s name and version, enable the logging of queries, or help table creation by specifying the name of the package with entity classes.

... ...

Vertabelo Mobile ORM doesn’t require any initial configuration. You don’t even have to import any libraries. In the next section, we’re going to create a database model. Based on this model we will generate, download, and add source files to make Vertabelo Mobile ORM work.

ORM Configuration Summary

All the ORMs require very little or no configuration. Whichever we choose, we can start working with them almost immediately.

Entities

In ORMLite, you have to create entity classes manually. Details about their database representation are provided by annotation.

@DatabaseTable(tableName = "patient") public class Patient { @DatabaseField(generatedId = true) private Integer id; @DatabaseField private String name @DatabaseField(canBeNull = false, foreign = true) private Hospital hospital; Patient() { // ORMLite needs a no-arg constructor, at least with package visibility } // getters, setters and other constructors } @DatabaseTable(tableName = "hospital") public class Hospital { @DatabaseField(generatedId = true) private Integer id; @DatabaseField private String name Hospital() {} // getters, setters and other constructors }

ActiveAndroid also requires you to create entity classes on your own. Table and column properties are set using annotations. An auto-incrementing primary key id field is inherited from the Model class.

@Table(name = "patient") public class Patient extends Model { @Column(name = "name") public String name @Column(name = "hospital_id"); public Hospital hospital; public Patient() { // no-arg constructor is required } } @Table(name = "hospital") public class Hospital extends Model { @Column(name = "name") public String name; public Hospital() {} }

greenDAO differs a little bit from our two previous ORMs, since it generates entity classes for you. However, you do have to create an additional Java project, which provides information about your database model and triggers the class generation. Here’s some sample code I did for our data model:

public class ProjectGenerator { public static void main(String[] args) throws Exception { Schema schema = new Schema(1, "com.example.project"); // hospital table Entity hospital = schema.addEntity("Hospital"); hospital.addIdProperty(); hospital.addStringProperty("name"); // patient table Entity patient = schema.addEntity("Patient"); patient.addIdProperty(); patient.addStringProperty("name"); Property hospitalId = patient.addLongProperty("hospitalId").getProperty(); // patient has a one assigned hospital patient.addToOne(hospital, hospitalId); // hospital has many patients ToMany hospitalToPatients = hospital.addToMany(patient, hospitalId); hospitalToPatients.setName("patients"); // trigger generation with path to the Android project new DaoGenerator().generateAll(schema, "../project/src/main/java"); } }

The result of the generation should resemble the snippet below. Notice that the greenDAO generator has also added a patients field to the Hospital class.

// THIS CODE IS GENERATED BY greenDAO, DO NOT EDIT. Enable "keep" sections if you want to edit. /** * Entity mapped to table "PATIENT". */ public class Patient { private Long id; private String name private Long hospitalId; ... private Hospital hospital; ... } // THIS CODE IS GENERATED BY greenDAO, DO NOT EDIT. Enable "keep" sections if you want to edit. /** * Entity mapped to table "HOSPITAL". */ public class Hospital { private Long id; private String name; ... private List patients; ... }

Modeling entities in Sugar ORM must be done by manually creating entity classes. On the plus side, you don’t have to set any annotations; everything is handled automatically by extending the SugarRecord class.

public class Patient extends SugarRecord { private String name private Hospital hospital; public Patient() { // Sugar ORM needs a no-arg constructor } // getters, setters and other constructors } public class Hospital extends SugarRecord { private String name public Hospital() {} // getters, setters and other constructors }

Vertabelo Mobile ORM flips the standard approach. In the previous ORMs, database structure is driven by the object – creating classes and adding annotations models the structure of your database. greenDAO is an exception, but not as much as Vertabelo Mobile ORM.

With Vertabelo, you have to first create a valid and complete database model. To create entity tables, go to Vertabelo and model a database for your application. Having done that, you can generate an ORM, download it, and add the source files to your project.

Generated classes should resemble the snippet below. Take a look at the hospitalId field – we can see that Vertabelo Mobile ORM takes a different approach to database relationships. It doesn’t try to map them to the object representation but leaves the foreign key fields just as they were in the database model.

/** * POJO representing table patient. */ public class Patient implements java.io.Serializable { /** * Property representing column id */ protected Integer id; /** * Property representing column name */ protected String name; /** * Property representing column hospital_id */ protected Integer hospitalId; ... } /** * POJO representing table hospital. */ public class Hospital implements java.io.Serializable { /** * Property representing column id */ protected Integer id; /** * Property representing column name */ protected String name; ... }

Entities Summary

ORMs tend to take different approaches to entities. The most common method in our group is to map objects to relations. Entity classes are done manually, annotations are usually added for clarification, and then the ORM handles the database side of things. This is undoubtedly convenient – you can get what you need quickly, without worrying about database stuff.

However, in greenDAO and Vertabelo Mobile ORM things are done in reverse order. The object representation there is an effect of a predefined database structure – in greenDAO using the generator library and in Vertabelo Mobile ORM as a diagram of the database model. In this setting, we have much more control and awareness of the database working behind the application.

Another interesting difference is how ORMs attempt an object-relationship mapping. We see that Vertabelo Mobile ORM doesn’t even try to achieve that – it creates objects that directly reflect their database structure. For example, it does not replace foreign key fields with object references like other ORMs do.

Finally, each object-relationship mapping may have a different balance. ActiveAndroid and Sugar ORM are trying to take the majority of database responsibilities on themselves and let you forget about it; Vertabelo Mobile ORM is trying to bring your attention to it and make you mind the database while you work on the application.

Database Access

ORMLite provides database access by using DAOs. To connect them with an actual database, we need to create a database helper. It should extend OrmLiteSqliteOpenHelper and implement onCreate(SQLiteDatabase sqliteDatabase, ConnectionSource connectionSource) and onUpgrade(SQLiteDatabase database, ConnectionSource connectionSource, int oldVersion, int newVersion) methods. For convenience, we can also add access methods to the DAOs, like in the official ORMLite examples.

public class DatabaseHelper extends OrmLiteSqliteOpenHelper { private static final String DATABASE_NAME = "hospital.db"; private static final int DATABASE_VERSION = 1; private Dao hospitalDao; private Dao patientDao; public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION, R.raw.ormlite_config); } @Override public void onCreate(SQLiteDatabase sqliteDatabase, ConnectionSource connectionSource) { // handles database creation, it may look like the below try { TableUtils.createTable(connectionSource, Hospital.class); TableUtils.createTable(connectionSource, Patient.class); } catch (SQLException e) { Log.e(DatabaseHelper.class.getName(), "Could not create a database", e); } } @Override public void onUpgrade(SQLiteDatabase sqliteDatabase, ConnectionSource connectionSource, int oldVer, int newVer) { // handles database upgrade } public Dao getHospitalDao() throws SQLException { if (hospitalDao == null) { hospitalDao = getDao(Hospital.class); } return hospitalDao; } public Dao getPatientDao() throws SQLException { if (patientDao == null) { patientDao = getDao(Patient.class); } return patientDao; } }

To have straightforward access to the helper in activities, it is possible to extend the OrmLiteBaseActivity class, which provides you with a getHelper()method . There are also OrmLiteBaseListActivity , OrmLiteBaseService and OrmLiteBaseTabActivity classes, which represent the ones you already know from Android with an added getHelper() method.

Dao hospitalDao = getHelper().getHospitalDao(); Dao patientDao = getHelper().getPatientDao();

ActiveAndroid provides access to the database through entity objects and the query builder, which we’re going to use in the next sections. You don’t need to add anything more to your project at this step.

As you might conclude by its name, greenDAO uses DAOs to talk with a database. DAOs can be accessed in the following way:

DevOpenHelper helper = new DaoMaster.DevOpenHelper(this, "hospital-db", null); SQLiteDatabase db = helper.getWritableDatabase(); DaoMaster daoMaster = new DaoMaster(db); DaoSession daoSession = daoMaster.newSession(); HospitalDao hospitalDao = daoMaster.getHospitalDao(); PatientDao patientDao = daoMaster.getPatientDao();

Obviously, you don’t have to write all of this code for every database operation – you can store corresponding DAO objects in Application or Activity classes.

As you may have already noticed, ActiveAndroid and Sugar ORM are quite similar. Database access is no exception – just like ActiveAndroid, Sugar ORM gives you access straight away by using entity objects and the query builder.

You need to create your own database helper in Vertabelo Mobile ORM, as you do in ORMLite. However, all the helper has to do is to extend the SimpleSQLiteOpenHelper class (which comes with generated files), and implement the configure() method. This should add all the necessary creation and migration scripts. You can also add an extra script that inserts initial or test data into a database.

public class DatabaseHelper extends SimpleSQLiteOpenHelper { protected static final String DB_NAME = "hospital"; protected static final Integer VERSION = 1; public DatabaseOpenHelper(Context context) { super(context, DB_NAME, VERSION); } @Override void configure() { addCreationScript(R.raw.create_sql); } }

R.raw.create_sql is a resource ID for your database’s SQL creation script. It should be put in the res/raw/ directory. In Vertabelo, you can generate this script directly from your database model.

To access DAO objects, you can use the DAOProvider class.

// ‘this’ represents a current context DAOProvider provider = new DAOProvider(new DatabaseHelper(this)); HospitalDAO hospitalDao = provider.getHospitalDao(); PatientDAO patientDao = provider.getPatientDao();

As with greenDAO, you may store your DAO objects in the Activity or Application classes.

Basic Database Operations

In this section, we’re going to see how to perform very basic database operations using each ORM.

Insert, Update and Delete a Hospital From a Database

With these ORMs, we don’t have to bother about assigning IDs – each one handles it for us, so we can leave this class field empty and let the ORM’s magic fill it.

ORMLite // insert Hospital hospital = new Hospital(); hospital.setName("Calvary Hospital"); hospitalDao.create(hospital); // update hospital.setName("Lincoln Medical Center"); hospitalDao.update(hospital); // delete hospitalDao.delete(hospital); ActiveAndroid // insert Hospital hospital = new Hospital(); hospital.name = "Calvary Hospital"; hospital.save(); // update hospital.name = "Lincoln Medical Center"; hospital.save(); // delete hospital.delete(); greenDAO // insert Hospital hospital = new Hospital(); hospital.setName("Calvary Hospital"); hospitalDao.insert(hospital); // update hospital.setName("Lincoln Medical Center"); hospitalDao.update(hospital); // delete hospitalDao.delete(hospital); Sugar ORM // insert Hospital hospital = new Hospital(); hospital.setName("Calvary Hospital"); hospital.save(); // update hospital.setName("Lincoln Medical Center"); hospital.save(); // delete hospital.delete(); Vertabelo Mobile ORM // insert Hospital hospital = new Hospital(); hospital.setName("Calvary Hospital"); hospitalDao.insert(hospital); // update hospital.setName("Lincoln Medical Center"); hospitalDao.update(hospital); // delete hospitalDao.delete(hospital);

As you have probably noticed, these very basic operations look almost identical in every ORM – all of them were designed to perform elementary operations painlessly. However, we do see two approaches to connecting with a database: one does it directly via entity objects, while the other uses DAOs.

Add a New Patient to the Database

We want to add a Patient object to the database. This is a bit more complicated than inserting a hospital, as Patient contains a relationship with another object. Here is where the real ORM part begins, since we are now mapping from an object composition to the relational foreign keys.

ORMLite Patient patient = new Patient(); patient.setName("John Smith"); patient.setHospital(hospital); patientDao.create(patient); ActiveAndroid Patient patient = new Patient(); patient.name = "John Smith"; patient.hospital = hospital; patient.save(); greenDAO Patient patient = new Patient(); patient.setName("John Smith"); patient.setHospitalId(hospital.getId()); patientDao.insert(patient); Sugar ORM Patient patient = new Patient(); patient.setName("John Smith"); patient.setHospital(hospital); patient.save(); Vertabelo Mobile ORM Patient patient = new Patient(); patient.setName("John Smith"); patient.setHospitalId(hospital.getId()); patientDao.insert(patient);

Again the different approaches of ORMs become apparent in a very subtle way. To attach a Patient to the hospital, ORMLite, ActiveAndroid and Sugar ORM use references, following OOP principles. Conversely, greenDAO and Vertabelo Mobile ORM operate on hospital.ID , which is closer to the database representation of this relationship.

Note: greenDAO offers a setHospital() method like the other ORMs, but its implementation is based on taking the ID from the hospital object.

Delete All Patients Named "John Smith"

Now, the difference between ORMs becomes even more noticeable. This operation didn’t go as smoothly as the previous ones. ActiveAndroid and Sugar ORM lack a complete builder for a delete statement, so the ‘where’ condition is given as a raw query – something that we would like to avoid in this setting.

ORMLite has a method for an equality condition, but the name of the column is given as a raw string. This can complicate even a simple refactoring, such as changing a column name. Of course, you can avoid this by adding static fields with column names to entity classes.

However, with greenDAO and Vertabelo Mobile ORM there is a builder to handle conditions and automatically-generated properties with column names. With these ORMs, our statement become less error-prone without any effort from our side.

Delete Patient With ID = 42

Sugar ORM is the only one that does not provide a direct method to delete by ID. You either have to find a patient by ID and delete him, making two calls to the database, or build and execute a regular delete statement. Fortunately, the other ORMs perform such an operation without any trouble.

Basic Database Access Summary

Simple insert, update, or delete operations are not a problem for any ORM. However, things get more complicated when we want to add a condition to our statement – some ORMs deal with this better than others.

Select Queries

Get a List of All Hospitals

Only ActiveAndroid doesn’t have a dedicated method for this operation. We need to build a query for such a trivial operation. It’s a simple task, but an unneeded one in other ORMs.

Get Hospital With ID = 7

Getting a hospital by an ID turned out to be pretty straightforward for all the ORMs.

ActiveAndroid is the only ORM lacking a genuine builder for select queries. This time around, Sugar ORM has a reasonable and quite concise builder that does not have any problems with this query – in contrast to delete statements.

Get All Patients With IDs Less Than 30 and Not Named Adam Smith

ORMLite List patients = patientDao.queryBuilder() .where() .lt("id", 30) .and() .ne("name", "Adam Smith") .query(); ActiveAndroid List patients = new Select() .from(Patient.class) .where("Id < ? and name <> ?", "" + 30, "Adam Smith") .execute(); greenDAO List patients = patientDao.queryBuilder() .where(Properties.Id.lt(30), Properties.Name.notEq("Adam Smith")) .list(); Sugar ORM List patients = Select.from(Patient.class) .where(Condition.prop("id").lt(30), Condition.prop("name").notEq("Adam Smith")) .list() Vertabelo Mobile ORM List patients = patientDao.getPatientList( PatientDAO.ID.lt(30) .and(PatientDAO.NAME.ne("Adam Smith")));

Combining two conditions in one select query didn’t cause any problem to the ORMs – all of them are able to join conditions smoothly. In greenDAO and Sugar ORM, you don’t even see the word “and”; they join multiple conditions with the ‘AND’ operator by default; for the ‘OR’ operator, they have a whereOr method.

Get a Patient’s Hospital

While almost all the ORMs allow you to take the referenced object directly from the instance, Vertabelo Mobile ORM requires you to use a DAO. It’s not an appreciable difference, though.

Get All Hospital’s Patients

This is an interesting case, as it checks if there is any support for querying one-to-many relationships from the other side of the ORM. Unfortunately, this support is provided only by greenDAO and Vertabelo Mobile ORM. The other ORMs require us to build a standard query, without getting any significant profit from the previously defined relationship.

Android ORMs: The Takeaway

This article shows that some Android ORMs were built for different purposes than others. If you’re working on a simple application and you just need basic database functionality, either Sugar ORM or ActiveAndroid is a great choice. They are very simple to use, they offer convenient methods for elementary operations, and they are ready to use almost instantly. There’s no database knowledge required, so they’re perfect if you just want to save something in your app.

greenDAO and Vertabelo Mobile ORM represent a different approach. As these ORMs are closer to the relational structure of the data, they allow the creation of big and well-considered database architectures. This approach is particularly beneficial for bigger projects – those backed by a team of developers, with a database as its core. This is especially true with Vertabelo Mobile ORM: it can save hours of manually defining entity classes and relationships because it is directly linked with the Vertabelo database modeler.

ORMLite takes the middle ground. It requires modeling data structures like Sugar ORM and ActiveAndroid, but it’s definitely a more advanced and mature tool than those two. ORMLite is aimed at people who need a serious and reliable ORM for a large project, but who feel more comfortable with objects than relations.

In conclusion, the “right” Android ORM for your application is not about one being inherently better than the others. The best choice is the ORM that meets your needs and experience level. When you choose your ORM, keeping this in mind will help you select the most suitable offering.

A Note About Documentation

Surprisingly, sparse ORM documentations turned out to be a huge problem – huge enough to make me mention it here. Their website tutorials did not cover much, and to learn how things worked in the above ORMs, I had to frequently reach for their JavaDocs or source codes. Application examples provided by the ORMs’ authors were also pretty basic, covering only a fraction of possible database usages.

Fortunately, not all ORMs seem to have this problem – both ORMLite and Vertabelo Mobile ORM have really comprehensive documentation, with lots of usage examples. In my opinion, this is something that other Android ORMs should emulate.