Apache Jakarta and Beyond: A Java Programmeramp;#039;s Introduction [Electronic resources] نسخه متنی

18.11. The SQL Tags

The iteration, conditional, and display tags seen so far have all been used with beans, but they may also be used with other kinds of data. In particular, there is a set of tags that allow JSPs to embed SQL, and the results of these queries can be manipulated using the same tags.

Before examining these tags a word of caution is in order. Embedding SQL directly in pages is highly error prone, requires a lot of duplicate effort throughout a site, and makes site maintenance extremely difficult. These tags are suitable only for "quick and dirty" one-shot pages. For anything more complex a bean-based solution is called for, and one will be discussed a little later in this chapter.

The most basic of the SQL tags is sql:query, which allows a page to perform a select and display the results. Its use is demonstrated in Chapter 10.

Listing 18.19. 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:toolbook,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>

Chapter 17 for more on this option.

The sql parameter specifies the actual SQL to execute, which here is a simple select.

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 seen in conjunction with forms. Here artist will have one property for each column, which may be obtained using the normal 'dot' notation that has been 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 using th187 that encodes these charactersfor 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. 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. The artist's name is also passed along so it can be displayed on the following page. This is not strictly necessary because 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 to save the effort of doing an extra call to the database.

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

Listing 18.20. 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:toolbook,org.hsqldb.jdbcDriver,sa"
sql="select * from album 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="album_id" value="${cd.album_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 18.19; both specify a dataSource, var, and sql to run. However, in this example 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:paramtag, whose value is the very artist_id that was needed.

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

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

18.11.1. Inserting Data from JSPs

To make the little CD application more useful, it can be expanded to allow the user to add new artists, CDs, and tracks. Not surprisingly, the standard tag library provides another tag to facilitate this; the sql:update tag. Before seeing how this tag is used, consider what must be done to add a new artist.

First, the user will specify the name in a form, which will be sent to another JSP, which will use the new tag to perform an insert. It would be reasonable to expect that a sql:param will be used to pass the name to the query. This is all straightforward enough. However, it is important to keep in mind that the artist table has an artist_id field in addition to the name. This ID will have to come from somewhere.

One possibility would be to force the user to provide it along with the name. This is far from satisfactory. Because this ID is only used internally by the system to track data, it has no intrinsic meaning to the user, and hence the user should never see it. Plus, there is no clear way in which the user would know what value to use.

It therefore seems that the system should keep track of IDs. That is perfectly fine, because such information can easily be added to the database. It is just necessary to create another table of IDs, which will be called sequence because it will provide sequences of ID values. Its definition is simple:

create table sequence (
name  char(60),
id    int)
insert into sequence values('artist',0);
insert into sequence values('album',0);
insert into sequence values('track',0);

With this table in place, creating a new artist would take the following steps:

There is actually a further complication to this. If two different users try to add an artist at the same time, they might both get the same ID in step 1 before either can get to step 2 to update the current ID. Most modern databases have a way to prevent this, and it is supported by the tag library through a tag called jsp:transaction, but this is beyond the scope of the book.

Listing 18.21 shows everything that must be done in a JSP to add an artist to the database with a proper ID.

Listing 18.21. Using a JSP to add data to a database

<%@ taglib prefix="sql"
uri="http://java.sun.com/jstl/sql" %>
<sql:query
dataSource="jdbc:hsqldb:toobook,org.hsqldb.jdbcDriver,sa"
sql="select value from sequence where name='Artist'"
var="ids"/>
<sql:update
dataSource="jdbc:hsqldb:toobook,org.hsqldb.jdbcDriver,sa"
sql="insert into artist(artist_id,name) values(?,?)">
<sql:param value="${ids.rows[0].id}"/>
<sql:param value="${param.name}"/>
</sql:update>
<sql:update
dataSource="jdbc:hsqldb:toobook,org.hsqldb.jdbcDriver,sa"
sql="update sequence set value=? where name='Artist'">
<sql:param value="${ids.rows[0].id + 1}"/>
</sql:update>
New artist has been added!<p>
<a href="show_artists.jsp">Return to the
artist list</a>

The example exactly follows the previous steps outlined. The only noteworthy point is that the ID obtained from the select is referred to as ids.rows[0].id. Recall that rows is an array-like object, suitable for using in c:forEach tags, so element 0 of this object will be the first row.

18.11.2. SQL and Beans

By now the disadvantages of using the SQL tags should be readily apparent. They require page developers to know the details of the database, they place way too much model code in the view layer, and they are verbose.

The solution, as always, is to move the model layer where it belongs, into some Java beans. Chapter 10 discussed OJB, a toolkit for hiding database structure and access within beans. The beans from that chapter could be used in a JSP without modification as long as they extend the PersistentObject object from Listing 10.11. The methods in this base class will make it possible for JSP to load and save beans by setting special methods, as shown in Listing 18.22.

Listing 18.22. Retrieving data through a bean

<%@ taglib prefix="c"
uri="http://java.sun.com/jstl/core" %>
<jsp:useBean
id="artist"
/>
<jsp:setProperty name="artist" property="artistId"/>
<h2>Albums by
<c:out escapeXml="false" value="${artist.matching[0].name}"/>
</h2>
<ul>
<c:forEach items="${artist.matching[0].albums}" var="cd">
<li><a href="<c:url value="show_tracks.jsp">
<c:param name="album_id" value="${cd.albumId}"/>
<c:param name="name" value="${cd.name}"/>
</c:url>"><c:out value="${cd.name}"/></a>
</c:forEach>
</ul>

The only difference between Listing 18.22 and the version that used the SQL tags are that the sql:query tag has been replaced by jsp:useBean and jsp: setProperty tags. Now the iteration goes over cdBean.atching, which implies the set of CDs with the given artistId. While this is no shorter than the database version, there is a huge conceptual difference. Now this page does not know whether the data is coming from a database or a serialized bean, or is connecting to some Web site to get its information. The details of the model have therefore been hidden from the view, which is as it should be.

The difference is even more pronounced in the bean version of the page that adds an artist, which is shown in Listing 18.23.

Listing 18.23. Storing data through a bean

<%@ taglib prefix="c"
uri="http://java.sun.com/jstl/core" %>
<jsp:useBean
id="artistBean"
/>
<jsp:setProperty name="artistBean" property="name"/>
<jsp:setProperty
name="artistBean"
property="save"
value="true"/>
New artist has been added!<p>
<a href="show_artists_beans.jsp">Return to the
artist list</a>

Listing 18.23 does a much better job of hiding database and implementation details than Listing 18.21 did. All the view needs to do is load the data and then tell the model to save itself.

Note that OJB maintains its own version of the sequence table. Under no circumstances should a site mix OJB with pages that manually insert data because the difference in the way sequence numbers are maintained will inevitably lead to errors.