Using SQL Components

# for SQL tutorial
library.url=jdbc:hsqldb:databases\\library
library.maxconn=2
library.user=sa
library.password=
library.transactionIsolation=READ_COMMITTED

You can see which connection pools you have available by opening:
File -> SQL Connections

For this example, the connection name is library.

Creating a Servlet Capsule that uses SQLSelect

In this example, you will build a servlet that uses an SQLSelect actor to perform a query that returns multiple rows of results, then displays the results in an HTML bullet list. The data will come from a query you design that looks for all books that are currently available in a library. You will configure it so that the books are sorted by name, and the results display the title and author of the available books.

Since a servlet generates and displays the results, you first need to create a capsule of type Servlet.

To create a servlet capsule:

1. From the File menu, choose New Capsule -> Servlet.
2. Name in the capsule in the outline Library.
3. Save the capsule as SelectExample.zac.
   

You want the servlet to respond to a GET request from a browser by sending an HTML document that contains the result of the query. To do this, you need to add an HTMLDocument actor to the capsule, and a ServletGet behavior to send the document in response to the GET request.

To add the HTMLDocument and ServletGet:

1. From the Palette, insert an HTMLDocument actor.
   Html -> HTMLDocument
2. Rename the HTMLDocument to BooksPage.
3. In the details pane, set Title to SQL Example.
4. Add a ServletGet behavior as a child of BooksPage.
   Insert -> Saved Group -> ServletGet

When your servlet receives a GET request, it should respond by sending the BooksPage back to the browser. The ServletGet behavior is configured by default to send its actor as the response to a GET request. You can expand ServletGet to see its child behaviors. In this case, the actor for ServletGet is BooksPage. BooksPage contains no information at this point, so if you run the capsule, you see a blank page displayed.

So far, your capsule outline should look like this:

Inserting and defining the SqlSelect Component

An SqlSelect component retrieves data out of a relational database. In this example, you want to retrieve some book titles and their authors out of a database table that is already defined. First, you need to insert an SqlSelect bean into the servlet capsule to do the query.

To add an SqlSelect component:

1. Select BooksPage in the outline.
2. Add an SQLSelect component from the Palette.
   Sql -> SqlSelect

The next step is to define the query that the SqlSelect bean executes. For this example, you want to select the title and author for all books whose status is On Shelf, meaning that the book is in the library. In addition, you want to order the list alphabetically by book title.

The first step is to choose the connection and table that the data resides in. For this example, use the table named LIBRARY.

To choose a connection and table:

1. In the Details pane for SqlSelect, click the Properties: Edit button.
2. Under Connection, click on Choose.
3. In the dialog that pops up, select library.
4. Click OK.
5. Under Tables, click on the button.
6. In the dialog that pops up, click on LIBRARY.
   

Do not close the SqlSelect editor yet.

Now you need to choose the columns that you want to display in the result. The only columns you want returned from the query are title and author.

To choose columns to include in the query:

1. Click on the Columns tab.
2. Highlight TITLE by clicking on it.
3. Click on the to move TITLE into the Select Columns pane.
4. Highlight AUTHOR by clicking on it.
5. Click on the to move AUTHOR into the Select Columns pane.

Notice that TITLE and AUTHOR have moved from the Available Columns pane to the Select Columns pane.

The next step is to define the Conditions of the query. This example query should retrieve all books out of the database whose STATUS is On Shelf. You can check the condition against any column, even if you did not choose it in the Columns tab to display with the results.

To add a condition:

1. Click on the Conditions tab.
2. Click on the button to pop up the Edit Condition dialog.
3. On the left side, choose STATUS for the column.
4. For the Operator, choose =
5. On the right side, use the drop down menu and choose SQL Syntax.
6. In the text area below SQL Syntax, enter 'On Shelf', including the single quotes.
   
7. Click OK.
   For every entry that is in the specified table, the query checks to see whether the value of the STATUS column is On Shelf. Notice the single quotes around the phrase 'On Shelf' in the editor.

The final part that you need for the SqlSelect component is a sort. A sort determines the order of the results of a query, using a column you designate, either in increasing (ascending) or decreasing (descending) order. You want to sort this query by the title in ascending alphabetical order.

To add a sort:

1. Click on the Sorts tab.
2. Click on the button to pop up the Edit Sort dialog.
3. Choose TITLE for the Column.
   
4. Choose Ascending for the Order.
   
5. Click OK.

You have now fully defined the SqlSelect component. Click on the Syntax tab to see the SQL syntax that executes when the component is activated:

Click OK to close the editor.

This is a good time to save your work.

In the next lesson you will use an HTMLDoMultiple actor to generate a list that displays the results of the query you created.