JavaServer Pages, Second Edition

6.2 A Language for Databases

For humans and databases to work together, they must speak a common language. Although in principle, every database manufacturer could define its own such language, doing so would cause problems for both users and database vendors. To avoid these problems, a standard called Structured Query Language (SQL, pronounced "sequel") that all database vendors support, although frequently with some enhancements specific to their products, has been defined.

Most databases provide a utility program that allows users to enter SQL commands interactively and get results back. That program for hsqldb's can be accessed by running the following:

java -cp hsqldb.jar org.hsqldb.util.DatabaseManager

One such command might be instructions to create a new table by specifying the names and types. The SQL commands to create the CD and track tables from Tables 6.1 and 6.2 are shown in Listing 6.1.

Listing 6.1 SQL commands to create tables

CREATE TABLE artist (
     artist_id int,
     name      char(40)
);

CREATE TABLE cd (
     album_id int,
     artist_id int,
     name      char(40)
);

CREATE TABLE track (
     album_id int,
     name     char(60)
);

These commands define the columns in each table by giving each column a name and a type. The semicolons here indicate the end of each SQL command. This is a common convention but is not universal. Some SQL interpreters require the word go after each command.

Once the tables have been created, data can be stored in them with SQL's insert command, as shown in Listing 6.2.

Listing 6.2 SQL commands to put data into tables

INSERT INTO artist VALUES(1,'Mors Syphilitica');

INSERT INTO cd VALUES(1,1,'Primrose');
INSERT INTO cd VALUES(2,1,'Feather and Fate');

INSERT INTO track VALUES(1,'Ungrateful Girl');
INSERT INTO track VALUES(1,'Remidy');

INSERT INTO track VALUES(2,'The Hues of Longing');
INSERT INTO track VALUES(2,'Naturally Cruel');

These commands build rows in the database by specifying the value for each column in that row. Astute readers will note that the name of the second track is misspelled; fortunately, there is a way to change data once it has been entered, and this will be shown shortly.

Of course, data is useful only if it can be retrieved, and the SQL command that does this is called select. It has a number of variations, but the simplest lists all data from a table. The following command would list all tracks for all albums:

SELECT * FROM track;

The asterisk indicates that all fields should be retrieved. If only the track name and duration were desired, the asterisk would be replaced by name,length.

Generally, pulling all the rows from a table is not that interesting. In this example, it would have pulled the tracks from both albums, which is unlikely to be of any particular interest. A SELECT command can be modified by a where clause, which imposes one or more conditions that must be true in order for the row to be retrieved. To see only the names of the tracks on "Primrose," the SQL command would look like this:

SELECT name from track WHERE album_id = 1;

This command will obtain the desired data, but in order to construct this query, it is necessary to know the album ID. This ID could be found by looking at the CD table, using the following query:

SELECT album_id from cd WHERE name='Primrose';

But this is cumbersome. Fortunately, it is unnecessary, as the two queries can be combined into a single command by selecting from the two tables simultaneously and imposing a condition that connects them. This kind of query is called a join because it joins two or more tables together. Here is the SQL to accomplish this:

SELECT track.name FROM cd, track
WHERE cd.album_id = track.album_id
AND   cd.name     = 'Primrose';

The field to select is specified as the table name, a dot, and then the column name. This is necessary because both the CD and track tables have a field called name, so it is necessary to clarify which table is intended. Without this clarification, the database would respond with an error about a "field ambiguity." The SELECT is done on both the CD and track tables, and they are joined by the condition that the album_id fields must match. An additional requirement is placed on the album name, so that only the tracks from that album will be returned.

The SELECT command has many more options. But this is enough to follow the examples throughout the book.

Other SQL commands delete and update rows. The DELETE command also takes a where clause and will delete all rows that satisfy the condition in the clause. The UPDATE command likewise takes a where clause, as well as a set of new values. For example, to change one of the track names, a SQL statement like this could be used:

UPDATE track
SET name='Remedy'
WHERE name='Remidy';

This will find all rows in which the title track is named "Remidy" and will replace the name with the correct spelling.