This short tutorial will take you through the basics of using Squiggle.

Creating a basic SELECT query

The core of Squiggle revolves around the SelectQuery class. When creating a new SELECT statement, you instantiate a new SelectQuery and add query attributes to it.

SelectQuery select = new SelectQuery();

Instances of Table are used to represent each table participating in a query. Table takes a name as a parameter to the constructor, which is the name of the table in the database.

Now columns can be added to the query using SelectQuery.addColumn(). This takes two parameters; the Table containing the column and the name of the column in the database.

select.addColumn(orders, "id");
select.addColumn(orders, "total_price");

The generated SQL can be obtained using SelectQuery.toString().

System.out.println(select.toString());

Which outputs:

SELECT
   orders_table.id ,
   orders_table.total_price
FROM
   orders_table

Adding WHERE criteria

The SelectQuery.addCriteria() method can be used to add criteria to the WHERE clause of a query, which takes a Criteria object as a parameter.

Criteria itself is an abstract class as there are many different types of criteria that can be appended to a WHERE clause.

MatchCriteria is used for specifying a simple match such as MY_COL = 'foo' or C <= 6.

    select.addCriteria(new MatchCriteria(orders, "status", MatchCriteria.EQUALS, "processed"));
    select.addCriteria(new MatchCriteria(orders, "items", MatchCriteria.LESS, 5));

The parameters for MatchCriteria constructor are:

• Table table: The table containing the column used in the match.
• String columnName: The database column name in the table.
• String matchType: The operator to use. MatchCriteria provides some constants for these.
• String/int/float/boolean value: The value to match against. There are multiple overloaded constructors for different types.

The query now looks like:

SELECT
   orders_table.id ,
   orders_table.total_price
FROM
    orders_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items

Another type of Criteria is the InCriteria, which is used for specifying MY_COL IN ('a','b','c',....) criteria. The values can be specified using an array or a collection.

select.addCriteria(new InCriteria(orders, "delivery", new String[] { "post", "fedex", "goat" } ));

Which produces:

SELECT
    orders_table.id ,
    orders_table.total_price
FROM
    orders_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items orders_table.delivery IN (
        'post','fedex','goat'
    )

Many other types of criteria are defined in the com.truemesh.squiggle.criteria package.

Joining tables

To add a new table to the query using a join, two Tables must be instantiated and then passed to the SelectQuery.addJoin() method.

The parameters for SelectQuery.addJoin() are:

• Table sourceTable
• String sourceColumn
• Table destTable
• String destColumn

So, this:

Table warehouses = new Table("warehouses_table");
select.addJoin(orders, "warehouse_id", warehouses, "id");

Modifies the SELECT statement to insert the SQL necessary to include the join:

SELECT
    orders_table.id ,
    orders_table.total_price
FROM
    orders_table ,
    warehouses_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items < 5 AND
    orders_table.warehouse_id = warehouses_table.id

Of course, joining the table is pretty useless on its own, so you can also use the table in the query in the same way you could use the first table.

select.addColumn(warehouses, "location");
select.addCriteria(new MatchCriteria(warehouses, "size", MatchCriteria.EQUALS, "big"));

Which adds a new column to the selection and a new match criteria:

SELECT
    orders_table.id ,
    orders_table.total_price ,
    warehouses_table.location
FROM
    orders_table ,
    warehouses_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items < 5 AND
    orders_table.warehouse_id = warehouses_table.id AND
    warehouses_table.size = 'big'

SelectQuery.addJoin() is a bit of convenient syntactic sugar: it adds a MatchCriteria that refers to columns of two different tables. You can add a MatchCriteria directly if its easier. The SelectQuery will still add the appropriate tables to the FROM clause when it generates SQL.

Performing sub-selects

To perform a sub-select (query within query), you build a separate query using a new instance of SelectQuery, and then add it to the first using an InCriteria.

So, here's a new select statement:

SelectQuery subSelect = new SelectQuery();
Table offers = new Table("offers_table");
subSelect.addColumn(offers, "location");
subSelect.addCriteria(new MatchCriteria(offers, "valid", MatchCriteria.EQUALS, true));

Which, on its own, produces:

SELECT
    offers_table.location
FROM
    offers_table
WHERE
    offers_table.valid = true

This can then be used as a sub-select in the original query by adding the new SelectQuery as a parameter to InCriteria:

select.addCriteria(new InCriteria(warehouses, "location", subSelect));

Which leaves us with:

SELECT
    orders_table.id ,
    orders_table.total_price ,
    warehouses_table.location
FROM
    orders_table ,
    warehouses_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items warehouses_table.location IN (
        SELECT
            offers_table.location
        FROM
            offers_table
        WHERE
            offers_table.valid = true
    )

Phew!

Next steps

This concludes the two minute tutorial. Maybe it took a bit longer, but hopefully not that much.

You should have a solid idea of how to use Squiggle now, including:

• Building SELECT statements.
• Adding WHERE criteria.
• Joining tables.
• Performing sub-selects.

From here, the best way to learn more about Squiggle is by looking at the examples, exploring the API, looking at the source code and experimenting.