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.

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

The examples will be presented in the following way. First, we'll show you a database task in standard SQL query form. Second, we'll show the corresponding Java code for OneWebSQL™. For example:

select * from book;

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

Repeatable, obvious code fragments have been omitted.

2. A database model in Java code

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

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

The following classes (from the package com.onewebsql.query) are used to create queries:

Here is an example of a typical SQL query of the type SELECT and its OneWebSQL™ counterpart classes:

3. Coding

Coding in the query language is facilitated by a number of factors. The API of the query language has been designed according to the fluent API specification. Functions that create queries return objects in such a way that they can be chained in a single logical sequence. The written code resembles SQL and is concise. Finally, you can use the autoexpansion in an IDE (for example, Eclipse) to make writing queries faster.

Static imports are another helpful feature which simplify the code.

import static com.onewebsql.query.LExp.*;
import static com.onewebsql.query.AExp.*;

Database access methods that operate on collections of objects use generic types; for example, the methods in BookDAO:

4. Query language

This section covers supported constructions from the SQL language, their representations, and their limitations.

4.1. Retrieving data (SELECT)

An object of the type com.onewebsql.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.ilike("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 com.onewebsql.query.SelectQuery. More details will be given in the following sections.

4.1.1. Retrieving records

4.1.1.1. An entire table

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

select * from book;

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

Or simply:

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

4.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):

select * from book order by title;

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);

4.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
false descending DESC
true ascending ASC

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

select * from book order by title desc;

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

selectQuery.orderBy(BookDAO.TITLE, false);

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

Or, more concisely:

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

4.1.1.4. Limiting the number of records (LIMIT, OFFSET)

If, for example, you want to retrieve just the first 10 records, use the following code:

select * from book order by title desc limit 10;

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

selectQuery.orderBy(BookDAO.TITLE, false);
selectQuery.limit(10);

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

And if you would like to read the succeeding ten rows (for the purpose of paging, for example), execute the following statement:

select * from book order by title desc limit 10 offset 10;

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

selectQuery.orderBy(BookDAO.TITLE, false);
selectQuery.limit(10,20);

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

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

In OneWebSQL™, 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:

select * from book where id < 100;

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:

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

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

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

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

Alternately, you could write the query like this:

select * from book where id between 90 and 100;

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

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

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

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

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

SelectQuery selectQuery = new SelectQuery(BookDAO.TABLE_EXPRESSION);

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

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).

4.1.3. Table joins (JOIN)

In this section we'll see how to search for a pattern in more than one table at once. For example, if you would like to select books that have either the author or title matching a given pattern, you will first create an object of the type com.onewebsql.query.Join. This object will represent the relationship between the tables. In the next step, you'll use it as a parameter in the constructor of an object of the type com.onewebsql.query.SelectQuery. The query conditions are the same as they were in the previous examples.

select * from book join author on (book.author_id = author.id) 
         where book.title like 'foo' or author.name like 'foo';

Join join = Join.join(BookDAO.TABLE_EXPRESSION,	AuthorDAO.TABLE_EXPRESSION,
                      BookDAO.AUTHOR_ID.eq(BookDAO.ID));
		
SelectQuery selectQuery = new SelectQuery(join);

selectQuery.setWhere(BookDAO.TITLE.like("foo").
                     or(AuthorDAO.NAME.like("foo")));

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

WARNING
Make sure that the first table expression is from the table you'll fetch objects from! Otherwise you'll have to use a custom RowHandler, as shown in the next section and in the JDBC Utilities Guide.

If you would like to create other queries of the JOIN type, you should use an appropriate method from the class com.onewebsql.query.Join:

SQL method from the class com.onewebsql.query.Join
LEFT JOIN leftJoin
RIGHT JOIN rightJoin
CROSS JOIN crossJoin
FULL JOIN fullJoin
NATURAL CROSS JOIN naturalCrossJoin
NATURAL FULL JOIN naturalFullJoin

4.1.4. Joining tables to select columns

In the previous example you selected all the rows from the table book, but in some cases you may be interested in columns from more than one table. For example, if you want to retrieve the author and title of a book:

select book.title, author.name from book join author on (book.author_id = author.id);

Join join = Join.join(BookDAO.TABLE_EXPRESSION,	AuthorDAO.TABLE_EXPRESSION,
BookDAO.AUTHOR_ID.eq(AuthorDAO.ID));
	
SelectQuery selectQuery = new SelectQuery(join);
selectQuery.setColumns(Arrays.asList(BookDAO.TITLE, AuthorDAO.NAME));
		
List<List<String>> r = bookDAO.selectObjectList(selectQuery, new ListRowHandler<String>()).getObjectList();

This example illustrates the use of the interface com.onewebsql.util.jdbc.RowHandler, or more precisely the implementing class com.onewebsql.util.jdbc.ListRowHandler. This interface is used to convert the results of JDBC queries (objects of the type java.sql.ResultSet) to objects that are of interest to you. You can find more informations in the JDBC Utilities Guide.

4.1.5. Aliases

Imagine a table that represents a tree structure. You would like to find every child whose parent has a certain name. The same table is going to appear in your query twice, but each time it's used, its role has to be specified. That's why we need aliases. In SQL, the query would look more or less like this:

select parent.name, child.name from node parent, node child where parent.id = child.parent_id and parent.name like ? 

// 1. tables and aliases
List<NodeDAOTableExpression> tables = asList(NodeDAO.TABLE_EXPRESSION, NodeDAO.TABLE_EXPRESSION);
List<String> tableAliases = asList("PARENT", "CHILD");

// 2. fetched columns 
Alias parent_name = (Alias) NodeDAO.NAME.aliasTable("PARENT");
Alias child_name = (Alias) NodeDAO.NAME.aliasTable("CHILD");

List<Alias> columns = asList(parent_name, child_name);

// 3. condition
Alias parent_id = (Alias) NodeDAO.ID.aliasTable("PARENT");
Alias child_parent_id = (Alias) NodeDAO.PARENT_ID.aliasTable("CHILD");

LExp condition = parent_id.eq(child_parent_id).and(parent_name.like("F%"));

// 4. query
SelectQuery query = new SelectQuery(tables);
query.setAliases(tableAliases);
query.setColumns(columns);
query.setWhere(condition)

Here's a description of the steps taken in the above example:

  1. Establish which tables are used in the query and what their aliases are (listed in the same order as the corresponding tables)
  2. Establish which columns are returned, using their aliases
  3. Construct the query condition, using the aliases of the columns
  4. Create an object of the type SelectQuery, based on the previous elements

4.1.6. Aggregate functions

To execute a query with an aggregate function, use the function selectObject and supply the appropriate RowHandler.

For example:

select count(*)  from book ;

SelectQuery query = new SelectQuery(BookDAO.TABLE_EXPRESSION, AExp.fun("count", new Alias(null, "*")));
Integer count = bookDAO.selectObject(query, new IntegerRowHandler());

In this case AExp.fun("count", new Alias(null, "*")) is the query column. The result of the count function will be a number, so the IntegerRowHandler used in the example will provide an object of the correct type.

DAOs provide a special method for calling aggregate functions. The above example can also be written in the following easier way:

Long count = bookDAO.aggregateFunction("count", new Alias(null, "*"));

If the count function is operating on an entire table, the code can be simplified even more:

Long count = bookDAO.getCount();

4.1.7. Grouping (GROUP BY, HAVING)

Here's an example of a query using grouping (GROUP BY) and filtering the outcome of an aggregate function. Specify the column that you want to "group by" using the method groupBy(AExp). Then specify the filter condition using the method setHaving(LExp).

select count(*) as x, author_id from book group by author_id having x > 3;

SelectQuery query = new SelectQuery(BookDAO.TABLE_EXPRESSION,
                                    Arrays.asList(new AExp[] { AExp.fun("count", new Alias(null, "*")), BookDAO.AUTHOR_ID}));

query.groupBy(BookDAO.AUTHOR_ID);
query.setHaving(AExp.fun("count", new Alias(null, "*")).gt(3));
		
List<List<Integer>> r = bookDAO.selectObjectList(query, new ListRowHandler<Integer>()).getObjectList();

Here, you retrieve records using the method selectObjectList(SelectQuery, RowHandler). The second parameter is set as an object of the type ListRowHandler<Integer>(), which specifies that the result is going to be a list of rows. Rows will have the form of a two-element list of objects of the type Integer.

4.1.8. Removing duplicate rows (DISTINCT)

You may want to remove duplicate rows from the query results. In SQL you can do this by adding the keyword distinct to your query. OneWebSQL™ uses a similar solution. Have a look at this example:

select dictinct title from book;

SelectQuery query = new SelectQuery(BookDAO.TABLE_EXPRESSION, BookDAO.TITLE);
query.setDistinct(true);
List<List<String>> r = bookDAO.selectObjectList(query, new ListRowHandler<String>()).getObjectList();

4.2. Adding rows (INSERT)

This section covers queries of the type INSERT.

4.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(Object). For example:

insert into book(id, author_id, edition_id, isbn, title) 
       values (nextval('book_seq'), 1, 1, 'isbn', 'title'); 

Book book = new Book();
		
book.setAuthorId(1);
book.setEditionId(1);
book.setIsbn("isbn");
book.setTitle("title");

bookDAO.insert(book);

In this case the id field will be filled in automatically based on a sequence. This is only applicable for tables that have a numeric primary key (Integer, Long, etc); otherwise, you have to take care of it yourself.

4.2.2. A general mechanism for inserting rows

A general mechanism for building row-insertion queries is provided by the class com.onewebsql.query.InsertQuery. With this class you have full control over the values being inserted. Note that you will have to set the primary key yourself.

Here is an example of inserting a row:

insert into book(id, author_id, edition_id, isbn, title) 
       values (100, 1, 1, 'isbn', 'title'); 

InsertQuery query = new InsertQuery(BookDAO.TABLE_EXPRESSION);
		
query.set(BookDAO.ID, 100);
query.set(BookDAO.AUTHOR_ID, 1);
query.set(BookDAO.EDITION_ID, 1);
query.set(BookDAO.TITLE, "title");
query.set(BookDAO.ISBN, "isbn");

bookDAO.insert(query);

Constants that you use as keys (e.g. BookDAO.ID) represent columns of the table book. You can achieve the same outcome with the following code:

InsertQuery query = new InsertQuery(BookDAO.TABLE_EXPRESSION);
		
Map<Column,Object> vals = new HashMap<Column, Object>();
vals.put(BookDAO.ID, 101);
vals.put(BookDAO.AUTHOR_ID, 1);
vals.put(BookDAO.EDITION_ID, 1);
vals.put(BookDAO.TITLE, "title");
vals.put(BookDAO.ISBN, "isbn2");
		
query.values(vals);

4.2.3. Adding rows using a subquery (INSERT FROM)

You may want to create rows in the database without having to read them into your Java code. To do this, use a query of the type INSERT that gets its data from a subquery. In this example you are going to create an extra row based on another row.

insert into book(select id + 1000 as id,
                        author_id as author_id,
                        title as title,
                        isbn + "-hardcover" as isbn,
                        null as description,
                        2 as edition_id,
                        null as cover_image
                 from book where edition_id = 1 and
                                 id = 1) 

// 1. select 
SelectQuery select = new SelectQuery(BookDAO.TABLE_EXPRESSION);

select.setColumns(asList(BookDAO.ID.add(1000),
                         BookDAO.AUTHOR_ID,
                         BookDAO.TITLE,
                         BookDAO.ISBN.add(AExp.exp("-hardcover")), 
                         new Alias(null, "null"),
                         AExp.exp(EditionDICT.HARDCOVER.getId()),
                         new Alias(null, "null")));

select.setColumnAliases(asList(BookDAO.ID.getName(),
                               BookDAO.AUTHOR_ID.getName(),
                               BookDAO.TITLE.getName(),
                               BookDAO.ISBN.getName(),
                               BookDAO.DESCRIPTION.getName(),
                               BookDAO.EDITION_ID.getName(),
                               BookDAO.COVER_IMAGE.getName())); 

select.where(BookDAO.EDITION_ID.eq(EditionDICT.PAPERBACK.getId()).and(BookDAO.ID.eq(1)));


// 2. insert
InsertQuery query = new InsertQuery(BookDAO.TABLE_EXPRESSION);		
query.values(select);
bookDAO.insert(query);

The above example consists of two parts:

  1. A subquery of the type SELECT - here you supply the columns, their aliases and the row filtering expression
  2. A query of the type INSERT, to which you pass the previously created subquery as its source of data

    Note: the order in which the columns are listed in the methods setColumns(List) and setColumnAliases(List) has to match their order in the database.

4.3. Updating records (UPDATE)

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

4.3.1. Updating a single record

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

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

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

4.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 com.onewebsql.query.UpdateQuery to do this.

In this example, you'll change a book's edition type for the given author:

update book set edition_id = 2 where author_id = 1;

BookDAO bookDAO = new BookDAOImpl(dataSource, dbAdapter);
		
UpdateQuery query = new UpdateQuery(BookDAO.TABLE_EXPRESSION);

query.set(BookDAO.EDITION_ID, EditionDICT.HARDCOVER.getId());
query.setWhere(BookDAO.AUTHOR_ID.eq(1));
		
bookDAO.update(query);

In this case you used the dictionary class EditionDICT. The expression EditionDICT.HARDCOVER.getId() has a value of 2.

Or you can use this version of the code:

UpdateQuery query = new UpdateQuery(BookDAO.TABLE_EXPRESSION);

Map<Column,Object> values = new HashMap<Column,Object>();
values.put(BookDAO.EDITION_ID, EditionDICT.HARDCOVER.getId());
query.set(values);
query.setWhere(BookDAO.AUTHOR_ID.eq(1));
		
bookDAO.update(query);

Or write it like this:

Map<Column,Object> values = new HashMap<Column,Object>();
values.put(BookDAO.EDITION_ID, EditionDICT.HARDCOVER.getId());
bookDAO.update(values, BookDAO.AUTHOR_ID.eq(1));

4.3.3. Updating with reference to another table (UPDATE ... FROM)

In a situation where you want to modify rows in one table, but base the condition for the update on another table, you can use a query of the type UPDATE ... FROM. Here's an example:

update book set edition_id = 2 from author
where author.name = 'Joe Public'  and 
      author.id = book.author_id;

Queries of this type are not directly supported by OneWebSQL™. You can write them as subqueries, however. Here is an example:

update book set edition_id = 2 where author_id =
   (select id from author where name = 'Joe Public');

UpdateQuery query = new UpdateQuery(BookDAO.TABLE_EXPRESSION);

query.set(BookDAO.EDITION_ID, EditionDICT.HARDCOVER.getId());
		
query.setWhere(BookDAO.AUTHOR_ID.eq(
                       new SelectQuery(AuthorDAO.TABLE_EXPRESSION,
                                       AuthorDAO.ID)
                           .where(AuthorDAO.NAME.eq("Joe Public"))));
	
bookDAO.update(query);

4.4. Deleting rows (DELETE)

Our last topic will be deleting rows.

4.4.1. Deleting a single row

To start with, we'll look at deleting a single row. There are several ways of doing this:

delete from book where id = 1;

First, you can use a POJO object:

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

Second, you can use a primary key:

bookDAO.delete(1);

Third, you can use a conditional expression:

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

Fourth, you can use a query of the type DeleteQuery:

DeleteQuery query = new DeleteQuery(BookDAO.TABLE_EXPRESSION);
query.setWhere(BookDAO.ID.eq(1));
bookDAO.delete(query);

4.4.2. Deleting multiple rows

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

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

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

Your condition may be more complex. In this example, we'll use a subquery:

delete from book where id = (select id from author where name = 'Joe Public');

bookDAO.delete(new SelectQuery(BookDAO.TABLE_EXPRESSION, BookDAO.ID)
                   .where(BookDAO.NAME.eq("Joe Public")));

4.5. Other constructs

Some queries are not directly supported by the API. The following query types are not supported:

Lack of ready-to-use statements does not, however, prevent us from executing those queries as SQL statements. For example, if you want to lock a row in a table, use the class JdbcUtil:

select id from book where id = 1 for update;

List<Integer> result = new JdbcUtil(dataSource).
	                            query("select id from book where id = 1 for update", 
                                    new ArrayList<Object>(),
                                    new NoOpParameterHandler(),
                                    new IntegerRowHandler());	

You can execute any SQL statement in a similar way. More information on how to use the JdbcUtil class can be found in the JDBC Utilities Guide.

4.6. Expressions

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

4.6.1. Arithmetic expressions

Arithmetic expressions are accessed via the static methods of the com.onewebsql.query.AExp class. Here are the names of the arithmetic operators and their methods:

operator method name
+ add
- sub
* mul
/ div

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

Let's see a couple of examples.

Example 1:

select id + 1000 from book;

SelectQuery q = new SelectQuery(BookDAO.TABLE_EXPRESSION, 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, neg(BookDAO.ID.add(exp(10000)).mul(10)));

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

4.6.2. Logical expressions

Methods for creating logical expressions can be found in the class com.onewebsql.query.LExp. It contains methods used to express the following logical relations:

relation method
IS NULL isNull
IS NOT NULL isNotNull
= eq
<> (!=) ne
> gt
< lt
>= ge
<= le
a > b AND a < c between
a IS BETWEEN b AND c between
LIKE like
ILIKE ilike

Methods for creating logical expressions:

operation method
negation (NOT) not
conjunction (AND) and
alternative (OR) or

Example 1:

id < 10;

BookDAO.ID.le(10);

Example 2:

id < 10 and description is null;

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

Example 3:

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

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