Here is a JSP page that prints out the names of books in a table:

<%@ taglib uri="http://jakarta.apache.org/taglibs/dbtags" prefix="sql" %>

<%-- open a database connection --%>
<sql:connection id="conn1">
  <sql:url>jdbc:mysql://localhost/test</sql:url>
  <sql:driver>org.gjt.mm.mysql.Driver</sql:driver>
</sql:connection>

<%-- open a database query --%>
<table>
<sql:statement id="stmt1" conn="conn1"> 
  <sql:query>
    select id, name, description from test_books
    order by 1
  </sql:query>
  <%-- loop through the rows of your query --%>
  <sql:resultSet id="rset2">
    <tr>
      <td><sql:getColumn position="1"/></td>
      <td><sql:getColumn position="2"/></td>
      <td><sql:getColumn position="3"/>
          <sql:wasNull>[no description]</sql:wasNull></td>
    </tr>
  </sql:resultSet>
</sql:statement>
</table>

<%-- close a database connection --%>
<sql:closeConnection conn="conn1"/>

Here are general descriptions of the tags included in the DBTags tag library. Some details, such as the possible properties of the connection, statement, resultSet, and preparedStatement tags, are not covered here. An enumeration of all the configurable aspects of these tags is in the Tag Reference section.

Opening connections

There are two ways to open a database connection:

1. Use a database URL

The connection tag accepts a database URL that can obtain a Connection through Driver Manager:

<%-- open a database connection --%>
<sql:connection id="conn1">

  <%-- required --%>
  <sql:url>jdbc:mysql://localhost/test</sql:url>
  
  <%-- optional --%>
  <sql:driver>org.gjt.mm.mysql.Driver</sql:driver>
  
  <%-- optional --%> 
  <sql:userId>root</sql:userId>
  
  <%-- optional --%>
  <sql:password>notVerySecure</sql:password>

</sql:connection>

The "id" attribute is required by every "connection" tag. After the end tag, a java.sql.Connection object will be added as a pageContext attribute, and it can then be referenced by other tags, including statement, preparedStatement, and closeConnection.

Instead of including your database URL, driver name, user id, or password inside your tag body, you may optionally use the "initParameter" attribute:

<%-- store your connection info in the web.xml file --%>
<sql:connection id="conn1">
  <sql:url initParameter="dbURL"/> 
  <sql:driver initParameter="mysqlDriver"/>
  <sql:userId initParameter="dbUserId"/> 
  <sql:password initParameter="dbPassword"/>
</sql:connection>

2. Use a DataSource object

The connection tag also accepts a reference to a Servlet attribute containing a javax.sql.DataSource object. (The attribute is found via the findAttribute() method of PageContext.):

<%-- open a database connection --%>
<sql:connection id="conn1" dataSource="ds1">
  
  <%-- optional --%> 
  <sql:userId>root</sql:userId>
  
  <%-- optional --%>
  <sql:password>notVerySecure</sql:password>

</sql:connection>

3. Use a JNDI named JDBC DataSource

The Connection tag also accepts a JNDI named JDBC DataSource.

<%-- open a database connection --%>
<sql:connection id="conn1" jndiName="java:/comp/jdbc/test"/>

Closing connections

Close a connection by passing its reference to the "closeConnection" tag:

<%-- unless you're performing your own connection pooling, 
always close your connection --%>
<sql:closeConnection conn="conn1"/>

"Statements" are one of two ways to submit a query to the database. (The other method is the "preparedStatement".) The syntax of a statement-based query should be familiar to anyone with knowledge of SQL. In order to query the database, open a "statement" tag, pass it an sql "query", and then either "execute" the statement for inserts, updates, and deletes, or call the resultSet tag to iterate over a select statement. Here is a sample insert using the statement tag:

<%-- insert a row into the database --%>
<sql:statement id="stmt1" conn="conn1">
  <%-- set the SQL query --%> 
  <sql:query>
    insert into test_books (id, name) 
      values (3, '<sql:escapeSql><%=request.getParameter("book_title")%></sql:escapeSql>')
  </sql:query>
  <%-- execute the query --%>
  <sql:execute/>
</sql:statement>

escaping SQL

The "escapeSql" tag can be used inside a SQL query to SQL-escape your input values if they contain single quotes.

error handling

By default, errors caused by the execution of the SQL query (e.g. primary key violations, malformed SQL statements) will cause the JSP page to fail. You may optionally set the "ignoreErrors" attribute of the "execute" tag to true, which will print the SQL error to standard out without terminating the page:

<sql:statement id="stmt1" conn="conn1">

  <%-- this SQL query is malformed --%> 
  <sql:query>delete * from test_books</sql:query>
  
  <%-- the query will fail, but the page will continue --%>
  <sql:execute ignoreErrors="true"/>
  
</sql:statement>

whitespace handling

All statements and preparedStatements automatically trim whitespace from the body.

"Prepared statements" are a somewhat more advanced format for generating SQL queries. Instead of inserting values directly into the SQL statement, you include the '?' symbol in places where you want to set a value, and then you use a separate group of tags to actually set the value. Here is a version of the query we used in the statement section, but this version uses the preparedstatement tag and syntax instead:

<%-- insert a row into the database --%>
<sql:preparedStatement id="stmt1" conn="conn1">

  <%-- set the SQL query.  note the lack of quotes around the "name" value --%> 
  <sql:query>
    insert into test_books (id, name) 
      values (?, ?)
  </sql:query>
    
  <sql:execute>
    <sql:setColumn position="1">3</sql:setColumn>
    <sql:setColumn position="2"><%=request.getParameter("book_title")%></sql:setColumn>
  </sql:execute>
  
</sql:preparedStatement>

One advantage of prepared statements is that you do not need to perform sql escaping on text, as you have to do in a standard statement. However, be aware that standard statements may be more performant for databases and drivers that do not peform pooling of connections and prepared statements.

setColumn tag

You can put the setColumn tags of prepared statements either before the execute or resultset tag, or inside the execute tag. The execute tag never outputs its body so putting setColumn tags inside may prevent unnecessary whitespace.

Result sets are the product of a select statement. The resultSet tag automatically loops, once per row, through a result set. Use the "getColumn" tag to grab values from each row and either display them or store them away as a String:

<%-- print the rows in an HTML table --%>
<table>
<sql:statement id="stmt1" conn="conn1">
 
  <sql:query>
    select id, name, description from test_books
    order by 1
  </sql:query>
  
  <%-- loop through the rows of your query --%>
  <sql:resultSet id="rset2">
    <tr>
      <td><sql:getColumn position="1"/></td>
      <td><sql:getColumn position="2"/></td>
      <td><sql:getColumn position="3"/>
          <%-- print out a comment if the book has no description --%>
          <sql:wasNull>[no description]</sql:wasNull></td>
    </tr>
  </sql:resultSet>
  
</sql:statement>
</table>

"wasNull" and "wasNotNull" tags

The "wasNull" tag only executes its body if the previous "getColumn" tag encountered a null value in the database. You can only use the "wasNull" tag if you are inside a resultset and if a "getColumn" tag has already been executed. The "wasNotNull" tag executes its body if the previous getColumn tag did not produce a null. See the Tag Reference for examples.

"getColumn" tag

The getColumn tag performs one of two functions. You may either:

1. Write the column value directly to the JSP (default behavior)

   <%-- Print the value to the JSP output --%>
   <sql:getColumn position="1"/>

   , or

2. Write the column value, as a String, to a page attribute via the "to" attribute. If you want, you may optionally assign a scope other than "page" with the "scope" attribute. If the database column is null, the getColumn tag will not create an attribute. Here's a getColumn tag that creates an Integer request attribute:

   <%-- Note that the request attribute will be a String --%>
   <sql:getColumn position="1" to="someId" scope="request"/>

"getNumber" tag

When you want more precise control over number formatting, use the getNumber tag.

The "format" attribute can be either a pattern as accepted by the DecimalFormat constructor or a style: "CURRENCY", "PERCENT" or "NUMBER".

The "locale" attribute can have one to three components as accepted by the Locale constructor: language, country and variant. They are separated by "_". For example:

<%-- format a database value as English currency --%>
<sql:getNumber colName="id" format="CURRENCY" locale="en_GB"/>

time tags

There are several tags designed for displaying time-related data: getTime, getTimestamp and getDate.

The "format" attribute can be either a pattern as accepted by SimpleDateFormat or a style: "FULL", "LONG", "MEDIUM" or "SHORT". This attribute is optional.

The "locale" attribute can have one to three components as accepted by the Locale constructor: language, country and variant. They are separated by "_".

disabling looping

By default, the body of the result set tag is executed once per row in the ResultSet. By setting the option "loop" attribute to false, however, you may disable this feature and manipulate the ResultSet object manually, or pass it off to another custom tag.

<sql:statement id="stmt1" conn="conn1">
 
  <sql:query>
    select id, name, description from test_books
    order by 1
  </sql:query>
  
  <%-- disable looping in resultset tag --%>
  <sql:resultSet id="rset2" loop="false">
    <% 
       ResultSet rset = (ResultSet) pageContext.getAttribute("rset2");
       // manual result set manipulation here
    %>
  </sql:resultSet>
  
</sql:statement>

using RowSets

You may also use the resultSet tag with a RowSet object. By setting the option "name", the resultSet tag will look for a ResultSet object (including RowSets) stored under that name in the page, request, or session contexts. By setting the option "scope", you can specify which context contains your ResultSet/RowSet. Note that when you read a ResultSet/RowSet from an attribute, the resultSet tag does not need to be inside of a statement tag.

  <%-- loop through the rows of your ResultSet/RowSet, 
          wherever it came from --%>
  <sql:resultSet id="rset1" name="rsetAtt">
    <tr>
      <td><sql:getColumn position="1"/></td>
      <td><sql:getColumn position="2"/></td>
      <td><sql:getColumn position="3"/>
          <%-- print out a comment if the book has no description --%>
          <sql:wasNull>[no description]</sql:wasNull></td>
    </tr>
  </sql:resultSet>

"wasEmpty" and "wasNotEmpty" tags

The "wasEmpty" tag only executes its body if the last ResultSet tag received 0 rows from the database. You must be after a resultSet tag, or an error will be generated. The "wasNotEmpty" tag executes its body if the last ResultSet received more than 0 rows from the database. See the Tag Reference for examples.

"rowCount" tag

The "rowCount" tag prints out the number of rows retrieved from the database. It can be used inside a ResultSet tag to provide a running count of rows retreived, or after the ResultSet tag to display the total number. See the Tag Reference for examples. Using the tag before the ResultSet will produce an error.