Skip to content
This repository has been archived by the owner on May 1, 2020. It is now read-only.

SquiDB's query builder

Jump to bottom
Sam Bosley edited this page Sep 22, 2015 · 4 revisions

Query

SquiDB includes a set of classes you can use to construct SQL statements in an object-oriented way with a SQL-like syntax. These "language" objects use a builder-like pattern and are meant to be a 1:1 correspondence with the SQL they are compiled into, making them easy to read and understand. The object you will probably encounter is most frequently is Query, which lets you construct SELECT statements.

Things you can select

To start building a Query object, use either the select() or selectDistinct() static methods. These methods take a variable number of Field objects, so naturally you can select anything that is or extends from Field (see Fields, Properties, and Functions). Most commonly this will be some list of model properties (see How to use model classes). To select all properties of a given model, you can use the PROPERTIES array that every model class has. To make a query of the form "select *" you can simply call either method with no arguments.

Query query = Query.select(Person.PROPERTIES); // Selects all columns from the person class
Query selectAll = Query.select(); // Equivalent to "select *"

To add more Fields to the select list, you can call selectMore() and pass in another variable number of Fields. Note that if you call Query.select() and then add more Fields using selectMore(), the query will no longer be of the form "select *" and will instead be "select [only the fields added by selectMore()]".

Criterion

Criterion represents a boolean predicate, suitable for use in WHERE clauses or join constraints. Usually you'll construct a Criterion object from a Field (or a Property, or a Function). All the basic SQLite comparison operators are supported via Java methods:

Criterion nameIsSam = Person.FIRST_NAME.eq("Sam"); // people.firstName = 'Sam'
Criterion eighteenOrOlder = Person.AGE.gte(18); // people.age >= 18

You can construct logical conjunctions with AND and OR using either of the following syntaxes:

// These two are equivalent
Criterion andV1 = Criterion.and(Person.FIRST_NAME.eq("Sam"), Person.AGE.gte(18));
Criterion andV2 = Person.FIRST_NAME.eq("Sam").and(Person.AGE.gte(18));
// Corresponding methods exist for OR in both forms

There are also two syntaxes you can use for getting the logical negation of a Criterion:

Criterion someCriterion = Person.FIRST_NAME.eq("Sam").and(Person.AGE.gte(18));
// These two are equivalent
Criterion notV1 = Criterion.not(someCriterion);
Criterion notV2 = someCriterion.negate();

Use the where() method to add a Criterion to a Query's where clause. Note that calling where() multiple times will result in all the Criterions being joined together with AND in between them.

// select * from users where users.firstName = 'Sam' and users.age >= 18
Query samsWhoCanVote = Query.select().from(Person.TABLE)
    .where(Person.FIRST_NAME.eq("Sam").and(Person.AGE.gte(18)));

Joins

Building a join query is very easy. This code constructs a left join and specifies the ON clause using a Criterion, where the right hand side of the expression is another field:

// select people.firstName, profileImages.url from people
//   left join profileImages on people._id = profileImages.person_id
//   where people.age >= 18
Query joinQuery = Query.select(Person.FIRST_NAME, ProfileImage.URL).from(Person.TABLE)
    .leftJoin(ProfileImage.TABLE, Person.ID.eq(ProfileImage.PERSON_ID))
    .where(Person.AGE.gte(18));

Compound selects

SquiDB's query object supports UNION, UNION ALL, INTERSECT, and EXCEPT. Each of these methods takes another Query object. All the standard SQL restrictions apply--each query must return the same number of result columns, the union/intersect/except query can't have group or order clauses, etc. See the SQLite docs for more information. That being said, they're pretty straightforward in SquiDB:

Query query1 = Query.select().from(Person.TABLE).where(Person.AGE.gte(18));
Query query2 = Query.select().from(Person.TABLE).where(Person.NAME.eq("Sam"));

// select * from people where people.age >= 18 except (select * from people where people.name = 'Sam')
query1.except(query2);

Order By

You can optionally specify any number of ordering terms to affect how the rows returned by the query are sorted. You can use the static methods of the Order class, but for convenience Fields have asc() and desc() methods to build Orders more fluidly.

// select * from people order by people.last_name asc, people.first_name asc;
Query.select().from(Person.TABLE).orderBy(Person.LAST_NAME.asc(), Person.FIRST_NAME.asc());

Limit and Offset

You can specify a limit and/or an offset for the query by calling limit(). Currently only integer values can be used for limit and offset. To clear the limit or offset, use Query.NO_LIMIT and Query.NO_OFFSET.

// get a single record with the first name = 'Sam'
// select * from people where people.first_name = 'Sam' limit 1;
Query.select().from(Person.TABLE).where(Person.FIRST_NAME.eq("Sam")).limit(1);

// get all records with first name = 'Sam' but skip the first 2
// select * from people where people.first_name = 'Sam' limit -1 offset 2;
Query.select().from(Person.TABLE).where(Person.FIRST_NAME.eq("Sam")).limit(Query.NO_LIMIT, 2);

Why should I use this instead of writing the SQL myself?

Great question! A few reasons:

  • You don't have to write ugly String concatenations. We think the SquiDB way is a lot cleaner and easier to read than code like this:
String sql = "select " + PersonColumns.AGE + ", " + ProfileImageColumns.URL + " from "
    + PERSON_TABLE + " left join " + PROFILE_IMAGE_TABLE + " on " + PersonColumns._ID
    + " = " + ProfileImageColumns.PERSON_ID + " where " + PersonColumns.NAME + " = ?"
    + " AND " + PersonColumns.AGE + " >= ?" + " ORDER BY " + PersonColumns.AGE + " ASC"
String[] sqlArgs = {"Sam", Integer.toString(18)};
  • The above example uses the '?' character as placeholders for arguments to the statement. This is good practice, but it makes the code harder to read and necessitates that extra string array for the arguments. SquiDB's query language inserts those placeholders for you when compiling the Query object and binds the arguments automatically at query time.
  • The above code is also prone to errors when updating the SQL adds, removes, or reorders the contents of sqlArgs. You must always count the number of '?'s to find the appropriate argument in the array. For large and complex queries, this can be difficult.
  • It prevents several classes of typos. You won't ever mistype a keyword or forget a space character somewhere.
  • You can't refer to a column you've removed from your schema. It would be a compile-time error.
  • Building a SELECT statement with Property objects adds type information that raw SQL can't provide.

The golden rule of SquiDB's Query is "It's just SQL." We don't try to optimize anything under the hood or generate any SQL that doesn't directly correspond to how you created the Query object. What you see is what you get.

See also:

Clone this wiki locally