Room Dynamic DAO
A simple query builder for Google's ROOM ORM for Android
Import
allprojects {
repositories {
maven { url 'https://maven.andob.info/repository/open_source' }
}
}
dependencies {
implementation 'ro.andob.roomdynamicdao:dyndao:1.2.2'
implementation 'ro.andob.fieldschema:annotations:0.3.4'
kapt 'ro.andob.fieldschema:fs-processor:0.3.4'
kapt 'ro.andob.fieldschema:ts-processor-room:0.3.4'
}
A query builder? Why?
Because in ROOM, DAO methods are very limited due to the fact that you write the query directly into an annotation's argument. Consider the following:
@Entity
class Restaurant
{
@PrimaryKey(autoGenerate = true)
@NotNull
var id : Int = 0
@ColumnInfo
var name : String = ""
@ColumnInfo
var rating : Int = 0
@ColumnInfo
var latitude : Double = 0.0
@ColumnInfo
var longitude : Double = 0.0
}
class RestaurantFilter
{
var rating : Int? = null
var boundingBox : BoundingBox? = null
}
class BoundingBox
(
val northWestLat : Double,
val northWestLng : Double,
val southEastLat : Double,
val southEastLng : Double
)
We want to show a list with all restaurants. The user can filter them by rating and can search by restaurant's name. We also have a map, showing only restaurants in some specific zone (defined by the BoundingBox
). You will probably write something like:
@Dao
interface RestaurantDao
{
@Query("select * from Restaurant")
fun getAll() : List<Restaurant>
@Query("select * from Restaurant where name like :search")
fun search(search : String) : List<Restaurant>
@Query("select * from Restaurant where rating = :rating")
fun getAllWithRating(rating : Int) : List<Restaurant>
@Query("select * from Restaurant where rating = :rating and name like :search")
fun searchWithRating(rating : Int, search : String) : List<Restaurant>
@Query("select * from Restaurant where latitude >= :southEastLat and latitude <= :northWestLat and longitude >= :northWestLng and longitude <= :southEastLng")
fun getAllAroundPoint(northWestLat : Double, northWestLng : Double, southEastLat : Double, southEastLng : Double) : List<Restaurant>
@Query("select * from Restaurant where latitude >= :southEastLat and latitude <= :northWestLat and longitude >= :northWestLng and longitude <= :southEastLng and name like :search")
fun searchAroundPoint(northWestLat : Double, northWestLng : Double, southEastLat : Double, southEastLng : Double, search : String) : List<Restaurant>
@Query("select * from Restaurant where latitude >= :southEastLat and latitude <= :northWestLat and longitude >= :northWestLng and longitude <= :southEastLng and rating = :rating")
fun getAllAroundPointWithRating(northWestLat : Double, northWestLng : Double, southEastLat : Double, southEastLng : Double, rating : Int) : List<Restaurant>
@Query("select * from Restaurant where latitude >= :southEastLat and latitude <= :northWestLat and longitude >= :northWestLng and longitude <= :southEastLng and name like :search and rating = :rating")
fun searchAroundPointWithRating(northWestLat : Double, northWestLng : Double, southEastLat : Double, southEastLng : Double, search : String, rating : Int) : List<Restaurant>
}
val restaurants = if (filter == null)
{
if (search != null)
database.restaurantDao().search(search)
else database.restaurantDao().getAll()
}
else if (filter.boundingBox != null && filter.rating != null)
{
if (search != null)
database.restaurantDao().searchAroundPointWithRating(filter.boundingBox?.northWestLat?:0.0, filter.boundingBox?.northWestLng?:0.0, filter.boundingBox?.southEastLat?:0.0, filter.boundingBox?.southEastLng?:0.0, search, filter.rating?:0)
else database.restaurantDao().getAllAroundPointWithRating(filter.boundingBox?.northWestLat?:0.0, filter.boundingBox?.northWestLng?:0.0, filter.boundingBox?.southEastLat?:0.0, filter.boundingBox?.southEastLng?:0.0, filter.rating?:0)
}
else if (filter.boundingBox != null)
{
if (search != null)
database.restaurantDao().searchAroundPoint(filter.boundingBox?.northWestLat?:0.0, filter.boundingBox?.northWestLng?:0.0, filter.boundingBox?.southEastLat?:0.0, filter.boundingBox?.southEastLng?:0.0, search)
else database.restaurantDao().getAllAroundPoint(filter.boundingBox?.northWestLat?:0.0, filter.boundingBox?.northWestLng?:0.0, filter.boundingBox?.southEastLat?:0.0, filter.boundingBox?.southEastLng?:0.0)
}
else
{
if (search != null)
database.restaurantDao().searchWithRating(filter.rating?:0, search)
else database.restaurantDao().getAllWithRating(filter.rating?:0)
}
Very ugly and verbose! You cannot use if statements in DAOs. You cannot use complex types like BoundingBox
as an argument to a method from DAO.
Meet Dynamic Dao QueryBuilder
Annotate your model with FieldSchemaClass
. The annotation processor will generate a class called FS
(FieldSchema) with constants containing class field names and class names and another class called TS
(TableSchema) with table names and table column names.
@Entity
@FieldSchemaClass
class Restaurant
Create a filter model for your main model:
class RestaurantFilter : BaseFilter()
{
var rating : Int? = null
var boundingBox : BoundingBox? = null
}
BaseFilter
is a class from this library with the following structure:
open class BaseFilter
(
var search : String? = null,
var offset : Int = 0,
var limit : Int = BaseFilterDefaults.limit
) : Serializable
Create a class that extends QueryBuilder:
class RestaurantListQueryBuilder : QueryBuilder<RestaurantFilter>
{
constructor(filter : RestaurantFilter) : super(filter)
override fun tableName() : String? = TS.Restaurant
override fun where(conditions : QueryWhereConditions) : String?
{
if (filter.search != null)
conditions.addSearchConditions(filter.search, onColumns = arrayOf(TS.Restaurant_name))
if (filter.rating != null)
conditions.add("${TS.Restaurant_rating} = ${filter.rating}")
if (filter.boundingBox != null)
{
conditions.add("${TS.Restaurant_latitude} <= ${filter.boundingBox?.northWestLat}")
conditions.add("${TS.Restaurant_latitude} >= ${filter.boundingBox?.southEastLat}")
conditions.add("${TS.Restaurant_longitude} >= ${filter.boundingBox?.northWestLng}")
conditions.add("${TS.Restaurant_longitude} <= ${filter.boundingBox?.southEastLng}")
}
return conditions.mergeWithAnd()
}
}
@Dao
interface RestaurantDao
{
@RawQuery
fun getList(query : SupportSQLiteQuery) : List<Restaurant>
}
-- result:
select * from Restaurant where 1=1
select * from Restaurant where name like '%something%'
select * from Restaurant where rating = 5
select * from Restaurant where name like '%something%' and rating = 5
-- and so on, depending on the use case
val restaurants = database.restaurantDao().getList(
RestaurantListQueryBuilder(filter).build())
Sweet! Zero boilerplate!
Documentation
License
Copyright 2019-2020 Andrei Dobrescu
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.