SQLite Wrapper Language

DBFlow's SQLite wrapper language attempts to make it as easy as possible to write queries, execute statements, and more.

We will attempt to make this doc comprehensive, but reference the SQLite language for how to formulate queries, as DBFlow follows it as much as possible.

SELECT

The way to query data, SELECT are started by:


SQLite.select().from(SomeTable.class)

Projections

By default if no parameters are specified in the select() query, we use the * wildcard qualifier, meaning all columns are returned in the results.

To specify individual columns, you must use Property variables. These get generated when you annotate your Model with columns, or created manually.


SQLite.select(Player_Table.name, Player_Table.position)
    .from(Player.class)

To specify methods such as COUNT() or SUM() (static import on Method):


SQLite.select(count(Employee_Table.name), sum(Employee_Table.salary))
    .from(Employee.class)

Translates to:


SELECT COUNT(`name`), SUM(`salary`) FROM `Employee`;

There are more handy methods in Method.

Operators

DBFlow supports many kinds of operations. They are formulated into a OperatorGroup, which represent a set of SQLOperator subclasses combined into a SQLite conditional piece. Property translate themselves into SQLOperator via their conditional methods such as eq(), lessThan(), greaterThan(), between(), in(), etc.

They make it very easy to construct concise and meaningful queries:


int taxBracketCount = SQLite.select(count(Employee_Table.name))
    .from(Employee.class)
    .where(Employee_Table.salary.lessThan(150000))
    .and(Employee_Table.salary.greaterThan(80000))
    .count();

Translates to:


SELECT COUNT(`name`) FROM `Employee` WHERE `salary`<150000 AND `salary`>80000;

DBFlow supports IN/NOT IN and BETWEEN as well.

A more comprehensive list of operations DBFlow supports and what they translate to:

is(), eq() -> =
isNot(), notEq() -> !=
isNull() -> IS NULL / isNotNull() -> IS NOT NULL
like(), glob()
greaterThan(), greaterThanOrEqual(), lessThan(), lessThanOrEqual()
between() -> BETWEEN
in(), notIn()

Nested Conditions

To create nested conditions (in parenthesis more often than not), just include an OperatorGroup as a SQLOperator in a query:


SQLite.select()
  .from(Location.class)
  .where(Location_Table.latitude.eq(home.getLatitude()))
  .and(OperatorGroup.clause()
      .and(Location_Table.latitude
        .minus(PropertyFactory.from(home.getLatitude())
        .eq(1000L))))

Translates to:


SELECT * FROM `Location` WHERE `latitude`=45.05 AND (`latitude` - 45.05) = 1000

Nested Queries

To create a nested query simply include it as a Property via PropertyFactory.from(BaseQueriable):


.where(PropertyFactory.from(SQLite.select().from(...).where(...))

This appends a WHERE (SELECT * FROM {table} ) to the query.

JOINS

For reference, (JOIN examples).

JOIN statements are great for combining many-to-many relationships. If your query returns non-table fields and cannot map to an existing object, see about query models

For example we have a table named Customer and another named Reservations.

SELECT FROM `Customer` AS `C` INNER JOIN `Reservations` AS `R` ON `C`.`customerId`=`R`.`customerId`

// use the different QueryModel (instead of Table) if the result cannot be applied to existing Model classes.
List<CustomTable> customers = new Select()   
  .from(Customer.class).as("C")   
  .join(Reservations.class, JoinType.INNER).as("R")    
  .on(Customer_Table.customerId
      .withTable(NameAlias.builder("C").build())
    .eq(Reservations_Table.customerId.withTable("R"))
    .queryCustomList(CustomTable.class);

The IProperty.withTable() method will prepend a NameAlias or the Table alias to the IProperty in the query, convenient for JOIN queries:

SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
      ON COMPANY.ID = DEPARTMENT.EMP_ID

in DBFlow:

SQLite.select(Company_Table.EMP_ID, Company_Table.DEPT)
  .from(Company.class)
  .leftOuterJoin(Department.class)
  .on(Company_Table.ID.withTable().eq(Department_Table.EMP_ID.withTable()))

Order By


// true for 'ASC', false for 'DESC'
SQLite.select()
  .from(table)
  .where()
  .orderBy(Customer_Table.customer_id, true)

  SQLite.select()
    .from(table)
    .where()
    .orderBy(Customer_Table.customer_id, true)
    .orderBy(Customer_Table.name, false)

Group By

SQLite.select()
  .from(table)
  .groupBy(Customer_Table.customer_id, Customer_Table.customer_name)

HAVING

SQLite.select()
  .from(table)
  .groupBy(Customer_Table.customer_id, Customer_Table.customer_name))
  .having(Customer_Table.customer_id.greaterThan(2))

LIMIT + OFFSET

SQLite.select()
  .from(table)
  .limit(3)
  .offset(2)

UPDATE

DBFlow supports two kind of UPDATE:

Model.update()
SQLite.update()

For simple UPDATE for a single or few, concrete set of Model stick with (1). For powerful multiple Model update that can span many rows, use (2). In this section we speak on (2). Note: if using model caching, you'll need to clear it out post an operation from (2).


UPDATE Ant SET type = 'other' WHERE male = 1 AND type = 'worker';

Using DBFlow:


// Native SQL wrapper
SQLite.update(Ant.class)
  .set(Ant_Table.type.eq("other"))
  .where(Ant_Table.type.is("worker"))
    .and(Ant_Table.isMale.is(true))
    .async()
    .execute(); // non-UI blocking

The Set part of the Update supports different kinds of values:

ContentValues -> converts to key/value as a SQLOperator of is()/eq()
SQLOperator, which are grouped together as part of the SET statement.

DELETE

DELETE queries in DBFlow are similiar to Update in that we have two kinds:

Model.delete()
SQLite.delete()

For simple DELETE for a single or few, concrete set of Model stick with (1). For powerful multiple Model deletion that can span many rows, use (2). In this section we speak on (2). Note: if using model caching, you'll need to clear it out post an operation from (2).


// Delete a whole table
Delete.table(MyTable.class);

// Delete multiple instantly
Delete.tables(MyTable1.class, MyTable2.class);

// Delete using query
SQLite.delete(MyTable.class)
  .where(DeviceObject_Table.carrier.is("T-MOBILE"))
    .and(DeviceObject_Table.device.is("Samsung-Galaxy-S5"))
  .async()
  .execute();

INSERT

INSERT queries in DBFlow are also similiar to Update and Delete in that we have two kinds:

Model.insert()
SQLite.insert()

For simple INSERT for a single or few, concrete set of Model stick with (1). For powerful multiple Model insertion that can span many rows, use (2). In this section we speak on (2). Note: using model caching, you'll need to clear it out post an operation from (2).


// columns + values separately
SQLite.insert(SomeTable.class)
  .columns(SomeTable_Table.name, SomeTable_Table.phoneNumber)
  .values("Default", "5555555")
  .async()
  .execute()

// or combine into Operators
  SQLite.insert(SomeTable.class)
    .columnValues(SomeTable_Table.name.eq("Default"),
     SomeTable_Table.phoneNumber.eq("5555555"))
    .async()
    .execute()

INSERT supports inserting multiple rows as well.


// columns + values separately
SQLite.insert(SomeTable.class)
  .columns(SomeTable_Table.name, SomeTable_Table.phoneNumber)
  .values("Default1", "5555555")
  .values("Default2", "6666666")
  .async()
  .execute()

// or combine into Operators
  SQLite.insert(SomeTable.class)
    .columnValues(SomeTable_Table.name.eq("Default1"),
     SomeTable_Table.phoneNumber.eq("5555555"))
    .columnValues(SomeTable_Table.name.eq("Default2"),
     SomeTable_Table.phoneNumber.eq("6666666"))
    .async()
    .execute()

Trigger

Triggers enable SQLite-level listener operations that perform some operation, modification, or action to run when a specific database event occurs. See for more documentation on its usage.


Trigger.create("SomeTrigger")
                .after().insert(ConditionModel.class).begin(new Update<>(TestUpdateModel.class)
                        .set(TestUpdateModel_Table.value.is("Fired"))).enable(); // enables the trigger if it does not exist, so subsequent calls are OK

Case

The SQLite CASE operator is very useful to evaluate a set of conditions and "map" them to a certain value that returns in a SELECT query.

We have two kinds of case:

Simple
Searched

The simple CASE query in DBFlow:


SQLite.select(CaseModel_Table.customerId,
        CaseModel_Table.firstName,
        CaseModel_Table.lastName,
        SQLite._case(CaseModel_Table.country)
                .when("USA").then("Domestic")
                  ._else("Foreign")
                .end("CustomerGroup")).from(CaseModel.class)

The CASE is returned as CustomerGroup with the valyes of "Domestic" if the country is from the 'USA' otherwise we mark the value as "Foreign". These appear alongside the results set from the SELECT.

The search CASE is a little more complicated in that each when() statement represents a SQLOperator, which return a boolean expression:


SQLite.select(CaseModel_Table.customerId,
    CaseModel_Table.firstName,
    CaseModel_Table.lastName,
    SQLite.caseWhen(CaseModel_Table.country.eq("USA"))
            .then("Domestic")
            ._else("Foreign").end("CustomerGroup")).from(CaseModel.class);

SQLite Wrapper Language

SQLite Wrapper Language

SELECT

Projections

Operators

Nested Conditions

Nested Queries

JOINS

Order By

Group By

HAVING

LIMIT + OFFSET

UPDATE

DELETE

INSERT

Trigger

Case

results matching ""

No results matching ""