If you’re building an application that has a considerable amount of local database usage with a lot of tables, you will come across this problem at least once where you need to write custom queries

Room Database is an excellent solution by Google which makes our life a lot easier using the Sqlite Database on Android Devices. It uses the approach of Spring style JPA objects called Entities and it does it very well. So each entity object = 1 table in the database. It can even handle joins and nested objects very well using @Relations and @Embedded annotations.

But what about custom queries and custom objects which don’t necessarily point to a single table? Let’s find out how.

Enter @RawQuery

According to Google,

RawQuery serves as an escape hatch where you can build your own SQL query at runtime but still use Room to convert it into objects.

It precisely means what it says. You write custom queries and you can map it to your custom object. Let’s see how.

Consider this Entity Object 

@Entity(tableName = "events")
class UserEventsEntity {

    @PrimaryKey
    @ColumnInfo(name = "event_id")
    var eventId: Int = 0

    @ColumnInfo(name = "event_type_id")
    var eventTypeId: Int = 0

    @ColumnInfo(name = "points")
    var points: Int = 0
}

You’ll probably guess, this table will store events. So naturally, it’ll have a lot of rows. Each event carries a point value.

Let’s say, we need to fetch a summary of these events with the points added up. The best use-case would be to show it to the user a summary of their points on the app.

Let’s write a query for that.

Query Formation 

SELECT event_type_id as eventType, sum(points) as totalPoints FROM events GROUP BY event_type_id

If you notice, I have used alias in the query. We’ll see why shortly.

So, this query returns 2 columns, one is EventType and the other Sum total of points GROUP BY EventType

Obviously, you can see, this is a custom result from an existing table events. You don’t have an Entity object for this and we don’t need one. Let’s create a pojo (poko, in this case ) to hold our data.

data class PointsSummaryEntity(
    var eventType: Int = 0,
    var totalPoints: Int = 0)
}

We have defined a data class i.e., a simple pojo to hold our data.

How it all fits

Now let’s integrate the query with Room.

We’ll make use of @RawQuery as mentioned before. The only caveat is, the RawQuery method accepts SupportSqliteQuery parameter only.

So your method will look like this

@RawQuery
fun calculatePointsSummaryInternal(SupportSQLiteQuery query) : List<PointsSummaryEntity>

@RawQuery doesn’t accept any query parameter. So how do we write our query? We’ll have to create another method which will create a SimpleSqliteQuery object and pass our query string to it.

Dao Method

fun getPointsSummary(): List<PointsSummaryEntity>? {
    val query = "SELECT event_type_id as eventType, sum(points) as totalPoints FROM events GROUP BY event_type_id"
    val simpleSQLiteQuery = SimpleSQLiteQuery(query, arrayOf<PointsSummaryEntity>())
    return calculatePointsSummaryInternal(simpleSQLiteQuery)
}

Either you create this in your Repository or maybe in the DAO itself, it’s up to you. So what is this method doing?

Very simple, we are passing our query string to the SimpleSQLiteQuery object and that is being passed as a parameter to the original method calculatePointsSummaryInternal.

And that’s it! Hit build and let Room do it’s magic.

Once the build is over, you’ll Room has successfully generated the code for the method calculatePointsSummaryInternal. Let’s inspect it, shall we?

Sometimes, it’s good to inspect the generated code by these tools and figure out what’s happening under the hood, whether it generated what you want or not.

Food for thought

@Override
public List<UserTypePoints> getUserPointsSummaryInternal(final SupportSQLiteQuery query) {
  final SupportSQLiteQuery _internalQuery = query;
  __db.assertNotSuspendingTransaction();
  final Cursor _cursor = DBUtil.query(__db, _internalQuery, false, null);
  try {

    final int _cursorIndexOfEventType = CursorUtil.getColumnIndex(_cursor, "eventType");
    final int _cursorIndexOfTotalPoints = CursorUtil.getColumnIndex(_cursor, "totalPoints");

    final List<UserTypePoints> _result = new ArrayList<UserTypePoints>(_cursor.getCount());
    while(_cursor.moveToNext()) {
      final UserTypePoints _item;
      final int _tmpEventType;
      if (_cursorIndexOfEventType == -1) {
        _tmpEventType = 0;
      } else {
        _tmpEventType = _cursor.getInt(_cursorIndexOfEventType);
      }
      final int _tmpTotalPoints;
      if (_cursorIndexOfTotalPoints == -1) {
        _tmpTotalPoints = 0;
      } else {
        _tmpTotalPoints = _cursor.getInt(_cursorIndexOfTotalPoints);
      }
      _item = new PointsSummaryEntity(_tmpEventType,_tmpTotalPoints);
      _result.add(_item);
    }
    return _result;
  } finally {
    _cursor.close();
  }
}

Well, that’s a lot of code! But remember the days, when we had to write this by hand ourselves.

Pay close attention to the bold part. It’s trying to fetch the columns eventType and totalPoints from the result set. Those are exactly the names of the variables in our pojo. So how did it figure that out?

Remember the alias in the query? 

SELECT event_type_id as eventType, sum(points) as totalPoints ...

It is very important to alias your custom queries if you want to map it to your custom object. The reason being, in the above query, the result set will have a column called sum(points) which cannot be a pojo member variable obviously.

It’s a good practice to alias your results to derive a better meaning from your queries.

So that’s it!. Access your function the normal way 

val points = database.eventDao().getPointsSummary()
points.forEach {
 // ... Map to Adapter or UI
}

That’s it! Happy coding!

Read more of our technology blogs here