JavaServer Pages, Second Edition

6.3 Using SQL Directly from JSPs

The standard tag library contains tags that allow SQL commands to be embedded directly in a page. The most basic of these is the query tag, which allows a page to perform a select and display the results. The tag's use is demonstrated in Listing 6.3, which selects the list of artists from the table defined in Listing 6.1.

Listing 6.3 A page that gets data from a database

<%@ taglib prefix="c"
    uri="http://java.sun.com/jstl/core" %>
<%@ taglib prefix="sql"
    uri="http://java.sun.com/jstl/sql" %>

<sql:query
  dataSource="jdbc:hsqldb:jspbook,org.hsqldb.jdbcDriver,sa"
  sql="select * from artist"
  var="artists"/>
<ul>
<c:forEach items="${artists.rows}" var="artist">
<li><a href="<c:url value="show_cds.jsp">
  <c:param name="artist_id" value="${artist.artist_id}"/>
  <c:param name="name" value="${artist.name}"/>
</c:url>"><c:out escapeXml="false"
                 value="${artist.name}"/></a>
</c:forEach>
</ul>

This example starts by importing the core library and a new SQL library that contains the new tags. Immediately after loading the library, the query tag is used to load some data.

The query tag has many options, but the ones used here are the most common. First, the tag needs to be told how to connect to the database where the information lives, which is specified as the dataSource parameter. The exact form of this will make more sense after Chapter 9, but for now, think of it as naming three things: the location of the database, the kind of database, and the user name and password with which to connect to the database. These are all specified on one line, separated by commas.

The sql parameter specifies the SQL to execute. The SQL used here is a simple select command.

Finally, the var parameter names a variable in which the results of the query should be stored. This is somewhat similar to the var parameter in the c:forEach tag in that both make a value available elsewhere on the page.

Not coincidentally, the next place this variable is seen is in a c:forEach tag on the next line. Note that this variable is used as the items, because this one variable contains something like an array, each element of which will be one row of data. The artist variable, defined in the c:forEach tag, will hold each row in turn.

Within the body of the c:forEach tag, the artist variable acts like the param variable in Section 4.6, which has a different property for each value sent by a form. Similarly, artist will have one property for each column, which may be obtained by using the normal dot notation used with beans. The artist name, therefore, is obtained with

<c:out value="${artist.name}" escapeXml="false"/>

The escapeXml option to the c:out tag is new. Some bands have non-ASCII characters in their names, such as The Crüxshadows or Björk. Such names can be stored in the database by using the HTML that encodes these characters. For example, ü represents the character ü. However, by default, the c:out tag will itself encode any special characters it encounters, including ampersands. If this were allowed to happen, it would turn &#252 into &#252. Setting escapeXml="false" turns off this behavior and should be used whenever the c:out tag will be displaying data that has already been encoded for display.

The artist name should be a link to a page where all of that artist's albums will be shown. In order to do that, the url tag is used to construct a URL that will call the show_cds.jsp page and pass along the artist_id of interest. This works just like the Java News Today section list from the previous chapter. The artist's name is also passed along so that it can be displayed on the following page. This is not strictly necessary, as once the artist ID is available, the name could be obtained through another select. However, because the name is already available, it may as well be used from here in order to save the effort of doing an extra call to the database.

Listing 6.4 shows the show_cds.jsp page, which will once again use the sql:query tag. Whereas in Listing 6.3, the query was always the same, here there must be a way to build a where clause that includes the artist_id. Fortunately, the tag library allows for this.

Listing 6.4 A parameterized query

<%@ taglib prefix="c"
    uri="http://java.sun.com/jstl/core" %>
<%@ taglib prefix="sql"
    uri="http://java.sun.com/jstl/sql" %>

<sql:query
  dataSource="jdbc:hsqldb:jspbook,org.hsqldb.jdbcDriver,sa"
  sql="select * from cd where artist_id = ?"
  var="cds">

  <sql:param value="${param.artist_id}"/>
</sql:query>

<h2>Albums by <c:out escapeXml="false"
                     value="${param.name}"/></h2>

<ul>
<c:forEach items="${cds.rows}" var="cd">
<li><a href="<c:url value="show_tracks.jsp">
<c:param name="cd_id" value="${cd.cd_id}"/>
<c:param name="name" value="${cd.name}"/>
</c:url>"><c:out value="${cd.name}"/></a>
</c:forEach>
</ul>

The sql:query tag here looks very similar to the one in Listing 6.3; both specify a dataSource, var, and sql to run. In this example, however, the sql has a question mark where the artist_id passed in from the previous page might be expected. Correspondingly, the sql:query tag has a body containing a sql:param tag, whose value is the very artist_id that was needed.

This is another feature of the sql:query tag. Before the query is run, question marks within the sql parameter may be filled in with values from sql:param tags in the body. Because the values of sql:param come from scripts, queries can be dynamically altered as needed.

After the sql:query, the rest of the page is straightforward. Another c:forEach iterates all the CDs and provides a link to see the tracks on another page.