SQL (Structured Query Language) is used to manipulate data in a relational database. You can use it to retrieve, insert, delete, or change data. AppComposer can attach to a database using JDBC (Java DataBase Connectivity) drivers or a JDBC-ODBC bridge. AppComposer also ships with some prebuilt SQL Components that allow access to data over a JDBC connection. These components include useful graphical interfaces to select, insert, delete, and update data. AppComposer also gives you the ability to write SQL statements directly for execution against a database.
AppComposer ships with the open source database engine HSQLDB that you can use for example applications. Before starting this tutorial, make sure that HSQL is enabled.
AppComposer provides a preconfigured connection pool for use with this example. This connects you properly with the database used for this example. If you look in the AppComposer/sql.properties file, you see the following entry:
# for SQL tutorial
You can see which connection pools you have available by opening:
File -> SQL Connections
For this example, the connection name is library.
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.
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.
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:
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.
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.
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.
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.
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.
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.