Vertabelo Mobile ORM - Query Language Guide for Android

1. Introduction

This tutorial will show you what a query language is and how to use it. A query language is a representation of the SQL language in the form of Java code in Android application.

The examples in this tutorial are based on a database consisting of the tables book, genre, author and book_author.

The examples will be presented in the following way. We'll take a look at a database task in standard SQL query form and the corresponding Java code for Vertabelo Mobile ORM. For example:

SQL:

select * from book;
            

Vertabelo Mobile ORM:

List<Book> books = bookDAO.getBookList();
            

2. A database model in Java code of Android application

Query-creating Java code (in the form of Java classes) falls into one of the following categories:

  • classes that map your database model
  • classes used to create queries, which are part of Vertabelo Mobile ORM (so-called runtime classes)

Database models are mapped using the following classes and code components:

  • class pl.epoint.mobiorm.android.runtime.query.TableExpression, which represents a table expression (a table or a view), for example: BookDAO.TABLE_EXPRESSION
  • fields in DAO interface, which represents a table column, for example: BookDAO.ID

The following classes (from the package pl.epoint.mobiorm.android.runtime.query) are used to create queries:

  • classes SelectQuery, UpdateStatement, InsertStatement, DeleteStatement - used to build statements of the given type
  • class LExp - used to create logical expressions
  • class AExp - used to create arithmetical expressions

3. Query language

This section covers supported constructions from SQLite and their representations.

3.1. Retrieving data (SELECT)

An object of the type pl.epoint.mobiorm.android.runtime.query.SelectQuery is used to build SELECT statements for retrieving data. To construct a query using this object, you can perform the following steps:

Create the object, stating which tables you are accessing. In this example, the table being accessed is called book.

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);
              

Construct your query (a more detailed description will follow):

selectQuery.setWhere(BookDAO.TITLE.like("sql"));
selectQuery.orderBy(BookDAO.ID);
              

Execute the query, using a DAO (data access object):

List<Book> books = bookDAO.getBookList(selectQuery);
              

This concludes the general example for pl.epoint.mobiorm.android.runtime.query.SelectQuery. More details will be given in the following sections.

3.1.1. Retrieving records

3.1.1.1. An entire table

Here is an example that shows how to retrieve the contents of an entire table from a database:

SQL:

select * from book;
                

Vertabelo Mobile ORM:

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);
List<Book> books = bookDAO.getBookList(selectQuery);
                

Or simply:

List<Book> books = bookDAO.getBookList();
                
3.1.1.2. Sorting (ORDER BY)

Suppose you would like to retrieve the contents of the same table again, but this time sorted by title (default sort is ascending):

SQL:

select * from book order by title;
                

Vertabelo Mobile ORM:

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

selectQuery.orderBy(BookDAO.TITLE);

List<Book> books = bookDAO.getBookList(selectQuery);
                

Or, more concisely:

List<Book> books = bookDAO.getBookList(BookDAO.TITLE);
                
3.1.1.3. Sorting (ORDER BY DESC)

You can set the sorting order with an additional parameter. Here is the correspondence between the parameter value and the SQL statement:

Parameter's value Sort direction SQL
OrderByDirection.DESC descending DESC
OrderByDirection.ASC ascending ASC

In this case, we would like to sort in descending order:

SQL:

select * from book order by title desc;
                

Vertabelo Mobile ORM:

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

selectQuery.orderBy(BookDAO.TITLE, OrderByDirection.DESC);

List<Book> books = bookDAO.getBookList(selectQuery);
                

Or, more concisely:

List<Book> books = bookDAO.getBookList(BookDAO.TITLE, OrderByDirection.DESC);
                

LIMIT and OFFSET

You can use limit() and offset() methods of SelectQuery class to limit a number of retrieved records:

SQL:

select * from book limit 10 offset 20
                

Vertabelo Mobile ORM:

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

selectQuery.limit(10);
selectQuery.offset(20);

List<Book> books = bookDAO.getBookList(selectQuery);
                

3.1.2. Retrieving records that fulfill a given condition (WHERE)

In Vertabelo Mobile ORM, like in SQL, the WHERE clause is used to select records according to certain criteria. For example, in order to select rows with an identifier smaller than 100:

SQL:

select * from book where id < 100;
                

Vertabelo Mobile ORM:

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

selectQuery.setWhere(BookDAO.ID.lt(100));

List<Book> books = bookDAO.getBookList(selectQuery);
                

To select rows that have identifiers in the range of 90 to 100:

SQL:

select * from book where id > 90 and id < 100;
                

Vertabelo Mobile ORM:

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

selectQuery.setWhere(BookDAO.ID.lt(100).and(BookDAO.ID.gt(90)));

List<Book> books = bookDAO.getBookList(selectQuery);
                

This next example shows how to retrieve the same subset, but omit the rows that have no description:

SQL:

select * from book where id between 90 and 100 and description is not null;
                

Vertabelo Mobile ORM:

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

selectQuery.setWhere(BookDAO.ID.between(90,100).and(BookDAO.DESCRIPTION.isNotNull());

List<Book> books = bookDAO.getBookList(selectQuery);
                

More information about the construction of expressions can be found at the end of this document (the LExp and AExp classes).

3.1.3 SQLite functions

In addition to retrieving records from database, you can also use SQL functions (e.g. aggregate functions). You can see the full list of funtions supported by SQLite in Documentation. To use any function you should call AExp.fun() method.

SQL:

select sum(book.number_of_pages) from book;
                

Vertabelo Mobile ORM:

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

query.setColumnExpression(AExp.fun("sum", BookDAO.NUMBER_OF_PAGES));

Integer result = bookDAO.select(query, new IntegerResultSetHandler()).getObjectList().get(0);
                

3.2. Adding rows (INSERT)

This section covers queries of the type INSERT.

3.2.1. Adding a single record

If you want to add a single record to a table, you must create a POJO object, fill it out with data, and then call the method insert(). For example:

SQL:

insert into book(title, isbn, publication_year, number_of_pages, genre_id) 
    values ('title', 'isbn', 1999, 200, 1); 
                

Vertabelo Mobile ORM:

Book book = new Book();
        
book.setTitle("title");
book.setIsbn("isbn");
book.setPublicationYear(1999);
book.setNumberOfPages(200);
book.setGenreId(1);

bookDAO.insert(book);
                

In this case the id field will be filled automatically by SQLite. You can find more info about it in SQLite documentation. This is only applicable for tables that have a numeric primary key (Integer, Long, etc); otherwise, you have to take care of it yourself.

3.2.2. A general mechanism for inserting rows

A general mechanism for building row-insertion queries is provided by the class pl.epoint.mobiorm.android.runtime.query.InsertStatement. With this class you have full control over the values being inserted.

Here is an example of inserting a row:

SQL:

insert into book(title, isbn, publication_year, number_of_pages, genre_id) 
      values ('title2', 'isbn2', 2000, 201, 2);
                

Vertabelo Mobile ORM:

InsertStatement statement = new InsertStatement(BookDAO.TABLE_EXPRESSION);
        
Map<Column,Object> vals = new HashMap<Column, Object>();
vals.put(BookDAO.TITLE, "title2");
vals.put(BookDAO.ISBN, "isbn2");
vals.put(BookDAO.PUBLICATION_YEAR, 2000);
vals.put(BookDAO.NUMBER_OF_PAGES, 201);
vals.put(BookDAO.GENRE_ID, 2);
        
statement.setValues(vals);
bookDAO.insert(statement);
                

3.3. Updating records (UPDATE)

In this section, you'll learn how to modify data in a database.

3.3.1. Updating a single record

To start with, here is the simplest example of how to modify a single row:

SQL:

update book set title = 'new'  where id = 1
                

Vertabelo Mobile ORM:

Book book =  bookDAO.getById(1);
book.setTitle("new");
bookDAO.update(book);
                

3.3.2. A general mechanism for data modification

Sometimes you may need to modify several rows in one statement, or you may want to modify selected fields. Use pl.epoint.mobiorm.android.runtime.query.UpdateStatement to do this.

In this example, you'll change a book's title for the given genre:

SQL:

update book set title = 'title3' where genre_id = 1;
                

Vertabelo Mobile ORM:

BookDAO bookDAO = new BookDAOImpl(dataSource);

UpdateStatement statement = new UpdateStatement(BookDAO.TABLE_EXPRESSION).setValue(BookDAO.TITLE, "title3");

statement.setWhere(BookDAO.GENRE_ID.eq(1));

bookDAO.update(statement);
                

3.4. Deleting rows (DELETE)

Our last topic will be deleting rows.

3.4.1. Deleting a single row

To start with, we'll look at deleting a single row.

SQL:

delete from book where id = 1;
                

In Vertabelo Mobile ORM there are several ways of doing this:

First, you can use a POJO object:

Book book = bookDAO.getById(1);
bookDAO.delete(book);
                

Second, you can use a conditional expression:

bookDAO.delete(BookDAO.ID.eq(1));
                

Third, you can use a query of the type DeleteStatement:

DeleteStatement statement = new DeleteStatement(BookDAO.TABLE_EXPRESSION);
statement.setWhere(BookDAO.ID.eq(1));
bookDAO.delete(statement);
                

3.4.2. Deleting multiple rows

Now we'll look at deleting multiple rows. This example removes all books about programming:

SQL:

delete from book where title like '%programming%';
                

Vertabelo Mobile ORM

bookDAO.delete(BookDAO.TITLE.like("%programming%"));
                

3.5 Expressions

We'll finish off the tutorial with a bit of grammar for how to code arithmetic and logical expressions in the Vertabelo Mobile ORM query language.

3.5.1. Arithmetic expressions

Arithmetic expressions are accessed via the static methods of the pl.epoint.mobiorm.android.runtime.query.AExp class. Here are the names of the arithmetic operators and their methods:

Operator Method name
+ AExp.add
- AExp.sub
* AExp.mul
/ AExp.div

If you want to use a constant, wrap it using the method AExp.exp. If you want to change the sign of an expression (negate it), use AExp.neg.

Let's see a couple of examples.

Example 1

SQL:

select id + 1000 from book;
                

Vertabelo Mobile ORM:

SelectQuery q = new SelectQuery(BookDAO.TABLE_EXPRESSION, AExp.add(BookDAO.ID,exp(10000)));

List<Integer> r = bookDAO.selectObjectList(q, new IntegerRowHandler()).getObjectList();
                

Example 2:

select - (id + 1000) * 10 from book;
                
SelectQuery q = new SelectQuery(BookDAO.TABLE_EXPRESSION, AExp.neg(BookDAO.ID.add(10000).mul(10)));

List<Integer> r = bookDAO.selectObjectList(q, new IntegerRowHandler()).getObjectList();
                

3.5.2. Logical expressions

Methods for creating logical expressions can be found in the class pl.epoint.mobiorm.android.runtime.query.LExp. It contains methods used to build logical expressions using relations beetween arithmetical expressions:

Relation Method
IS NULL LExp.isNull
IS NOT NULL LExp.isNotNull
= LExp.eq
<> (!=) LExp.ne
> LExp.gt
< LExp.lt
>= LExp.ge
<= LExp.le
LIKE LExp.like

Methods for creating logical expressions from other logical exrpressions:

Operation Method
negation (NOT) LExp.not
conjunction (AND) LExp.and
alternative (OR) LExp.or

Example 1

SQL:

id < 10;
                

Vertabelo Mobile ORM:

BookDAO.ID.le(10);
                

Example 2

SQL:

id < 10 and description is null;
                

Vertabelo Mobile ORM:

BookDAO.ID.le(10).and(LExp.isNull(BookDAO.DESCRIPTION));
                

Example 3

SQL:

SELECT Author.Id, Author.name FROM Author WHERE NOT (EXISTS (SELECT * FROM Book WHERE Author.Id = Book.author_Id))
                

Vertabelo Mobile ORM:

authorDAO.getAuthorList(LExp.not(LExp.exists(new SelectQuery(BookDAO.TABLE_EXPRESSION).where(AuthorDAO.ID.eq(BookDAO.AUTHOR_ID)))));