JSTL Database Tag LibraryS2C Home « JSTL Database Tag Library

In our fourth lesson on JSTL tag libraries we look at the Database tag library. The Database tag library consists of six actions that allow us to perform database queries, access query results, perform CRUD operations, create a simple DataSource for prototyping and provide nested database action elements with a shared Connection, set up to execute all statements as one transaction.

Although it is preferable to keep the database operations of a web application designed using the MVC paradigm within the model tier, there are occasions where page authors require database operations within their JSP pages, for example when testing or for small scale applications. The JSTL Database tag library allows authors to do this.

Each Database action can have mandatory attributes that have to be populated and each attribute may also allow a run-time expression to be used. If a run-time expression is allowed for an attribute this can be populated using a static string or more interestingly a dynamic value which can be an EL or Java expression or a value set using the <jsp:attribute> standard action. If no run-time expression is allowed for an attribute then you can only populate that attribute using a static string.

Database AccessTop

When using the Database tag library SQL actions operate on a data source, as defined within the javax.sql.DataSource Java class. A javax.sql.DataSource object provides connections to the physical data source it represents and any SQL statements are executed and results returned within the context of a connection retrieved from that data source. Data sources can be specified explicitly using the dataSource attribute in SQL actions, or can be made transparent to page authors by utilising the data source configuration setting which is javax.servlet.jsp.jstl.sql.dataSource.

Database access is required by the <sql:query> action for querying a database and the <sql:update> and <sql:transaction> action for updating a database.The steps taken to retrieve a database using a data source reference are outlined below.

1. Get a reference to a data source as follows:
   • If the dataSource attribute is specified, use the attribute value specified as the data source.
   • Otherwise, get the configuration setting associated with javax.servlet.jsp.jstl.sql.dataSource using Config.find() (which allows us to access context configuration parameters held within the DD). Use the value found as the data source if it is not null.
2. If a data source is obtained from step 1:
   • If it is a DataSource object, this is used by the action as the data source to access the database.
   • if it is a String object:
     • Assume this is a JNDI relative path and retrieve the data source from the container’s JNDI naming context by concatenating the specified relative path to the J2EE defined root which is java:comp/env/.
     • If the previous step fails and ther data source is not found using the JNDI relative path, assume the string specifies JDBC parameters needed by the JDBC DriverManager class, using the following syntax
          url[,[driver][,[user][,password]]]     and do as follows:
       1. If driver is specified, ensure it is loaded.
       2. Access the named URL through the DriverManager class, using an empty string for user or password if they're not specified.
       3. If previous step fails, throw an exception.
   • This is not a DataSource or String object so throw an exception.
3. If no data source is obtained from step 1 throw an exception:

Remember that any actions that open a connection to a database must close the connection as well as release any other associated resources prior to the action completing. This way we can be certain no connections are left open and that memory leaks are avoided when these actions are used with pooling mechanisms.

Query Database ActionTop

The most frequent use of databases is to use an action to query the database and display the results of the query. In JSTL we use the <sql:query> action for this purpose.

<sql:query>Top

The <sql:query> tag queries a database and returns a single result set containing rows of data that it stores in the scoped variable identified by the var and scope attributes.

If the query produces no results, an empty Result object is returned.

If the dataSource attribute is specified the <sql:query> must not be nested inside a <sql:transaction> action which is responsible for setting data source when used. If the dataSource attribute is null, a JspException is thrown.

The following code snippets shows the three variants of the <sql:query> tag where the square brackets indicate optional attributes and the curly bracers indicate a choice of options within them where the default is underlined.

// No body content
<sql:query sql="sqlQuery" var="varName" [scope="{page|request|session|application}"
        [dataSource="dataSource"] [maxRows="maxRows"] [startRow="startRow"]>

// Body content specifying query parameters
<sql:query sql="sqlQuery" var="varName" [scope="{page|request|session|application}"
        [dataSource="dataSource"] [maxRows="maxRows"] [startRow="startRow"] >
    <sql:param> param actions
</sql:query>

// Body content specifying query and optional query parameters
<sql:query sql="sqlQuery" var="varName" [scope="{page|request|session|application}"
        [dataSource="dataSource"] [maxRows="maxRows"] [startRow="startRow"] >
    query statement   
    optional <sql:param> param actions
</sql:query>

Query Database ExampleTop

The following code shows an example of using a query to extract some row data from a database file and output it to a HTML table.

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

<%-- Output SQL Query Results --%>
<p>Manager Gradings:</p>
<sql:query var="staff" dataSource="${dataSource}">
   SELECT * FROM staff
   WHERE title = 'Manager'
   ORDER BY grade
</sql:query>
<table>
   <c:forEach var="row" items="${staff.rows}">
      <tr>
         <td><c:out value="${row.grade}"/></td>
         <td><c:out value="${row.firstName}"/></td>
         <td><c:out value="${row.lastName}"/></td>
         <td><c:out value="${row.title}"/></td>
      </tr>
   </c:forEach>
</table>

Update Database ActionsTop

There are two update Database actions for formatting and parsing dates, these being <sql:update> which allows us to update a database and <sql:transaction> which allows us to nest several update statements into a transaction to safeguard database integrity.

SQL Statement Parameter SubstitutionTop

The JSTL database actions allow substitution of parameter values for parameter markers in SQL statements using the ? symbol, examples of this are shown in the <sql:update> and <sql:transaction> sections.

This method of parametric substitution is made possible by the SQLExecutionTag interface. The SQLExecutionTag interface is implemented by the tag handlers of the <sql:query> and <sql:update> actions and is made available to support custom parameter actions. Said custom actions may retrieve parameters from any source and process them before substituting them for a parameter marker in the SQL statement of the enclosing SQLExecutionTag action.

<sql:update>Top

The <sql:update> tag allows execution of SQL INSERT, UPDATE or DELETE statements.

If the dataSource attribute is specified the <sql:update> must not be nested inside a <sql:transaction> action which is responsible for setting data source when used. If the dataSource attribute is null, a JspException is thrown.

If the scope attribute is specified then the var attribute must also be specified.

Results of an <sql:update> are stored in in the var attribute if specified and represent the number of rows that were affected or zero if no rows were affected.

Any exceptions that occur during execution must be caught and rethrown as a JspException which must include the SQL statement and the caught exception as the root cause.

The following code snippets shows the three variants of the <sql:update> tag where the square brackets indicate optional attributes and the curly bracers indicate a choice of options within them where the default is underlined.

// No body content
<sql:update sql="update" [dataSource="dataSource"] 
        [var="varName"] [scope="{page|request|session|application}">

// Body content specifying query parameters
<sql:update sql="update" [dataSource="dataSource"] 
        [var="varName"] [scope="{page|request|session|application}" >
    <sql:param> param actions
</sql:update>

// Body content specifying query and optional query parameters
<sql:update sql="update" [dataSource="dataSource"] 
        [var="varName"] [scope="{page|request|session|application}" >>
    update statement   
    optional <sql:param> param actions
</sql:update>

<sql:transaction>Top

The <sql:transaction> tag creates a transaction context for nesting <sql:update> and <sql:transaction> actions.

Any nested <sql:query> or <sql:update> actions must not be specify the dataSource attribute. If the dataSource attribute is null, a JspException is thrown.

Any exception occurring during the execution of the <sql:transaction> action must be caught and rethrown after the transaction has been rolled back.

The following code snippet shows the syntax of the <sql:transaction> tag:

<sql:transaction [dataSource="dataSource"] [isolation="isolationLevel"] >
    <sql:query> and <sql:update> statements 
</sql:transaction>

Update Database ExamplesTop

The following code shows examples of using the <sql:update> and <sql:transaction> tags.

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

<%-- Update SQL Example --%>
<p>Change Salary:</p>
<sql:update dataSource="${dataSource}">
   UPDATE staff
   SET salary = ?
   WHERE staffNo = ?
      <sql:param value="${salary}"/>
      <sql:param value="${staffNo}"/>
</sql:update>

<%-- Transaction SQL Example --%>
<p>Change Stock Level and Update Stok Report:</p>
<sql:transaction dataSource="${dataSource}">
   <sql:update>
      UPDATE stock
      SET stockLevel = stockLevel - ?
      WHERE itemNo = ?
         <sql:param value="${itemsSold}"/>
         <sql:param value="${itemNumber}"/>
   </sql:update>

   <sql:update>
      UPDATE stockReport
      SET dateSold = ?
      SET stockSold = stockSold + ?
      WHERE itemNo = ?
         <sql:dateParam value="${selldate}"/>
         <sql:param value="${itemsSold}"/>
         <sql:param value="${itemNumber}"/>
   </sql:update>
</sql:transaction>

DataSource Database ActionsTop

There is one data source Database action <sql:setDataSource> which allows us export a data source as either a scoped variable or as the data source configuration variable of type javax.servlet.jsp.jstl.sql.dataSource.

<sql:setDataSource>Top

If the var attribute is specified then the <sql:setDataSource> action exports the data either as a DataSource or String object as a scoped variable. if this is not the case then the data source is exported in the javax.servlet.jsp.jstl.sql.dataSource configuration variable.

If the dataSource attribute is null, a JspException is thrown.

The following code snippet shows the syntax of the <sql:update> tag where the square brackets indicate optional attributes and the curly bracers indicate a choice of options within them where the default is underlined.

<sql:setDataSource {dataSource="dataSource" |
            url="jdbcUrl" [driver="driverClassName"] [user="userName"] [password="password"]}
    [var="varName"] [scope="{page|request|session|application}">

Parameter Database ActionsTop

There are two parameter Database actions for passing parameters and date parameters, these being <sql:param> which sets the values of parameter markers ("?") in a SQL statement and the <sql:dateParam> action which sets the values of parameter markers ("?") in a SQL statement for values of type java.util.Date.

<sql:param>Top

The <sql:param> tag sets the values of parameter markers ("?") in a SQL statement.

Subtag of actions <sql:query> and <sql:update>.

If the value attribute is null, the parameter is set to the SQL value NULL

The following code snippets shows the two variants of the <sql:param> tag.

// Parameter value specified in the value attribute
<sql:param value="value">

// Parameter value specified in the body content
<sql:param>
    parameter value
</sql:param>

<sql:dateParam>Top

The <sql:dateParam> tag sets the values of parameter markers ("?") in a SQL statement for values of type java.util.Date.

Subtag of actions <sql:query> and <sql:update>.

If the value attribute is null, the parameter is set to the SQL value NULL

The following code snippets shows the syntax of the <sql:dateParam> tag.

// Store time zone
<sql:dateParam value="value" [type="{timestamp|time|date}"]>

Lesson 5 Complete

In our fourth lesson on JSTL tag libraries we looked at the Database tag library.

What's Next?

In our final lesson on JSTL tag libraries we look at the Functions tag library.

Homepage Top