Queries building

ReActiveAndroid provides very powerful and convenient query builder for creating SQL-statements. Query builder syntax most similar to normal SQL.

Query builder supports all four kinds of SQL-statements: SELECT , INSERT, UPDATE и DELETE. For each operation ReActiveAndroid has relevant classes - Insert, Select, Update and Delete. Let's look at each of these in turn.

Select

If you want select records from a table, you need to call Select class static method from and specify the table that you want to get records:

List<Note> notes = Select.from(Note.class).fetch();

In this case would be selected all table columns. To specify individual colums, before calling from method, we need to call columns static method and specify which columns should be in the query results:

List<Note> notes = Select.columns("id", "text").from(Note.class).fetch();

If you want to get a single record, use fetchSingle method:

Note note = Select.from(Note.class).fetchSingle();

The fetchSingle() method returns the first record from the query results.

Where

To specify the conditions for a query, you must call where method your expression as the first parameter. The second parameter is to pass the query arguments

Note note = Select.from(Note.class).where("id = ? AND title LIKE '?%'", 1, query).fetchSingle();

Joins

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

For example, we have Article and Author tables with the following scheme:

@Table(database = AppDatabase.class)
public class Order {

    @PrimaryKey
    public Long id;
    @Column(name = "name")
    public String name;
    @Column(name = "customer_id")
    public long customerId;

    ...

}

@Table(database = AppDatabase.class)
public class Customer {

    @PrimaryKey
    public Long id;
    @Column(name = "name")
    public String name;
    @Column(name = "city_id")
    public long cityId;

    ...

}

@Table(database = AppDatabase.class)
public class City {

    @PrimaryKey
    public Long id;
    @Column(name = "name")
    public String name;

    ...

}

We want to get full information about orders, i.e the order name, the customer name and the city, therefore we build the following query:

 List<Order> orderInfos = Select.columns("OrderItem.id AS order_id",
                "OrderItem.name AS order_name",
                "Customer.name AS customer_name",
                "City.name AS city_name")
                .from(Order.class).as("OrderItem")
                .join(Customer.class)
                .on("OrderItem.customer_id = Customer.id")
                .join(City.class)
                .on("City.id = Customer.id")
                .fetch();

But the Order class hasn't order_id, order_name, customer_name ant city_name fields, so we can't get data from these columns. In this case we must to use query with custom type. And for that, we need to create a new class, that will contain all necessary columns:

@QueryModel(database = TestDatabase.class)
public class OrderInfoQueryModel {

    @QueryColumn(name = "order_id")
    public long orderId;
    @QueryColumn(name = "order_name")
    public String orderName;
    @QueryColumn(name = "customer_name")
    public String customerName;
    @QueryColumn(name = "city_name")
    public String cityName;

}

As you can see we annotated the class with @QueryModel annotation. It means that the class is only used in queries, and that is not necessary to create a separate table.

 List<OrderInfoQueryModel> orderInfos = Select.columns("OrderItem.id AS order_id",
                "OrderItem.name AS order_name",
                "Customer.name AS customer_name",
                "City.name AS city_name")
                .from(Order.class).as("OrderItem")
                .join(Customer.class)
                .on("OrderItem.customer_id = Customer.id")
                .join(City.class)
                .on("City.id = Customer.id")
                .fetchCustom(OrderInfoQueryModel.class);

Classes with @QueryModel annotation have no effect on the database and only used to convert data from Cursor to Java-object.

GROUP BY and HAVING

Following are example of using GROUP BY and HAVING operators:

Select.from(Note.class).groupBy("id").having("id < 10").fetch();

LIMIT and OFFSET

Sometimes we need to get limited number of records. For that, we use LIMIT operator. Также вместе с ним часто используют оператор OFFSET:

Select.from(Note.class).limit(5).offset(10).fetch();

Insert

To insert a new record use the following code:

Insert.into(Note.class).columns("title", "text").values("Title", "Text").execute();

Также вы можете вставить запись в таблицу, вызвав метод save() у модели (если она наследуется от класса Model).

Update

Updating records is the same as inserting:

Update.table(Note.class).set("title = ?", "New title").where("id = ?", 1).execute();

Delete

The following code deletes all records in the specified table:

Delete.from(Note.class).execute();

Also we can delete specific records:

Delete.from(Note.class)
    .where("id=?", 1)
    .execute();