JavaServer Pages, Second Edition

6.4 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: sql:update. Before jumping into seeing how this tag is used, it is worthwhile to step back and consider what will need to be done in order 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 we will use a sql:param in order 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 not only a name but also an artist_id. Where will this ID come from?

One possibility would be to force the user to provide it along with the name. But this is far from satisfactory; this ID is used only internally by the system to track data and has no intrinsic meaning to the user. Hence the user should never see it. In addition, 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, as such information can easily be added to the database. It is merely necessary to create another table of IDs, which will be called sequence, as 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 in fact a further complication. If two 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 the jsp:transaction tag, which is beyond the scope of the book.

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

Listing 6.5 Using a JSP to add data to a database

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

<sql:query
  dataSource="jdbc:hsqldb:jspbook,org.hsqldb.jdbcDriver,sa"
  sql="select value from sequence where name='Artist'"
  var="ids"/>

<sql:update
  dataSource="jdbc:hsqldb:jspbook,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:jspbook,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 steps outlined previously. 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 arraylike object, suitable for using in c:forEach tags; therefore, element 0 of this object will be the first row.