To provide a running example of query language programming, this chapter introduces Jaql, a general-purpose query language similar to SQL. The name Jaql is an acronym for "just another query language."
This section shows examples of Jaql queries and their results against the chip company's data. If you like, you can run the sample program and try your own queries. Using the files on the CD, run the Jaql environment:
> java sjm.examples.query.JaqlUe
The JaqlUe class starts up a UE ( user environment), as Figure 15.7 shows.
Figure 15.7. An environment for Jaql queries. This user environment accepts queries, applies them against a potato chip database, and shows the results.
Although Jaql is a general-purpose language, this UE always applies the queries it receives against the chip company's data. When the UE starts, it preloads a sample query, as Figure 15.7 shows. This query is an example of a valid query against the chip data. The bottom panel in the UE shows the metadata of the chip database. Metadata is data about data, specifically about the layout of the database. Displaying metadata in the UE gives the user clues about how to form valid queries. Each line of metadata shows, effectively, one table and the column names for that table. The sample select query chooses two columns from the Chip table, and it limits the results with a where clause.
Figure 15.8 shows the result of pressing the Go! button, which executes the query.
Figure 15.8. Query results. Pressing the Go! button executes the query.
Pressing Go! asks the UE to parse the user query into a Java query that the engine in sjm.engine understands. The UE feeds the engine the query and displays the results.
15.3.1 Jaql Syntax
A Jaql query contains
A where clause contains the word where followed by a series of comparisons. A comparison applies one of these operators
< > = <= >= !=
to two arguments, which may be variables, quoted strings, or mathematical expressions.
15.3.2 Jaql Joins
In Jaql, a query that specifies more than one table implies a join on columns that have the same name. Joining means that any variable that appears in two or more tables must take on the same value in a successful query. Figure 15.9 shows an example.
Figure 15.9. A multitable query. This query specifies all three of the tables in the chip database. Jaql automatically joins these tables on their common column names.
The query in Figure 15.9 uses the three tables customer , order , and chip . Jaql joins customer and order on CustomerID . Jaql first finds a customer from the customer table and then looks for an order from the order table that has the same value of CustomerID . Finding such an order, Jaql then looks for a chip from the chip table that has the same ChipID as the order. The results show a list of customers and the types of chips they have on order.
15.3.3 Jaql Expressions
Jaql allows mathematical expressions to appear in where clauses and select parameters. Figure 15.10 shows an example.
Figure 15.10. Jaql expressions. This query uses a mathematical expression as a select parameter to calculate the total amount of money a customer spends each month on a type of chip.
The query in Figure 15.10 multiplies the bags per month by the price per bag for a type of chip. Such mathematical expressions can use any combination of multiplication, division, addition, subtraction, and parentheses. These expressions can occur either as select parameters or in where clauses. Note that in a comparison, a string to compare to must appear in quotes. Also note that Jaql ignores case when looking for column and table names. For example, order and Order both refer to the order table.