Table of Contents

• Overview
• Simple Usage Example
• Tags in Detail
• Connection Tags
• Statement Tags
• PreparedStatement Tags
• ResultSet Tags
• Requirements
• Configuration
• Tag Summary
• Tag Reference
• Examples
• Javadocs
• Revision History

The JDBC custom tag library contains tags which can be used to read to and write from a SQL database.

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

<%@ taglib uri="http://jakarta.apache.org/taglibs/jdbc" 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:getString position="1"/></td>
      <td><sql:getString position="2"/></td>
      <td><sql:getString 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 JDBC 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.

Connection Tags

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 page attribute containing a javax.sql.DataSource object:

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

</sql:connection>

The JDBC tag library itself does not instantiate DataSource objects, but there are other good ways to get a DataSource (e.g., the JNDI tag library).

Closing connections

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

<%-- unless you're performing your own connection pooling, 
always close your connection --%>
<sql:closeConnection id="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:setInt position="1">3</sql:setInt>
    <sql:setString position="2"><%=request.getParameter("book_title")%></sql:setString>
  </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. Also, prepared statements are the only way to insert complex values such as Blobs and Clobs into a database. However, be aware that standard statements may be more performant for databases and drivers that do not peform pooling of connections and prepared statements.

set* tags

You can put the set* 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 set* tags inside may prevent unnecessary whitespace.

There are two patterns for using "set*" tags. All set* tags (except setNull) allow you to set their value from an existing page attribute using the "name" tag attribute:

<%-- you have to use objects, no primitives --%>
<% pageContext.setAttribute("theInt",new Integer(3)); %>
<%-- pass the name of the page attribute to the setter tag --%>
<sql:setInt position="1" name="theInt"/>

In addition, most tags will let you set the value from the body of the tag. (If a particular "set*" tag does not support body content, you will get a compile-time error using the standard TLD.) Here's an example of setting an integer from the tag body:

<sql:setInt position="1">3</sql:setInt>

You can find a complete list of the "set*" tags in the prepared statement section of the Tag Summary.

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

<%-- 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:getString position="1"/></td>
      <td><sql:getString position="2"/></td>
      <td><sql:getString 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>

the "wasNull" tag

The "wasNull" tag only executes its body if the previous "get*" tag encountered a null value in the database. You can only use the "wasNull" tag if you are inside a resultset and if a "get*" tag has already been executed. Setting the optional "value" attribute to "false" means that the tag body will only be executed if the previous get* tag did not produce a null.

"get*" tags

The "get*" tags behave similarly to the preparedStatement "set*" tags. All get* tags can assign the value of the database column to a page attribute via the "to" tag attribute. Unlike the set* tags, get* tags can also optionally assign a scope other than "page" for the attribute If the database column is null, the get* tags will not create an attribute. Here's a get* tag that creates an Integer request attribute:

<%-- Note that the request attribute will be an Integer object, not a primitive --%>
<sql:getInt position="1" to="theInt" scope="request"/>

Additionally, most tags can assign their value, as a String, directly to the JSP output by not setting a "to" attribute. (If a particular "get*" tag does not support this, it will throw a compile-time error using the standard TLD.) If the database column is null, the get* tag will print out nothing. Here's a get* tag that prints an Integer to the JSP output:

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

You can find a complete list of the "get*" tags in the resultset section of the Tag Summary.

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>

JSP requirements

This custom tag library requires a servlet container that supports the JavaServer Pages Specification, version 1.2.

The tag library also works in some JSP version 1.1 servlet containers, such as Tomcat, but not in others, such as Weblogic. The tags in this tag library are designed according to the JSP 1.2 specification, which makes this requirement of the <jsp:getProperty ... /> tag:

The value of the name attribute in jsp:setProperty and jsp:getProperty will refer to an object that is obtained from the pageContext object through its findAttribute() method.

The JSP 1.1 specification does not require this behaviour, and while Tomcat happens to support it, Weblogic does not. Note that it is fairly straightforward to write a custom tag that emulates this behaviour for Weblogic users. Sample source code for such a tag can be found here.

JDBC requirements

The JDBC tag library supports the use of DataSource objects, which are not part of the Java 2 Standard Edition. In order to use DataSources, either use Java 2 Enterprise Edition, or download the Optional API for JDBC 2.0.

Configuration

Follow these steps to configure your web application with this tag library:

• Copy the tag library descriptor file (jdbc/jdbc.tld) to the /WEB-INF subdirectory of your web application.
• Copy the tag library JAR file (jdbc/jdbc.jar) to the /WEB-INF/lib subdirectory of your web application.
• Add a <taglib> element to your web application deployment descriptor in /WEB-INF/web.xml like this:
  
  

  <taglib>
    <taglib-uri>http://jakarta.apache.org/taglibs/jdbc</taglib-uri>
    <taglib-location>/WEB-INF/jdbc.tld</taglib-location>
  </taglib>
  

To use the tags from this library in your JSP pages, add the following directive at the top of each page:

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

where "sql" is the tag name prefix you wish to use for tags from this library. You can change this value to any prefix you like. For the examples below the prefix "sql" is used.

<%-- 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>

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

<%-- open a database connection --%>
<sql:connection id="conn1">
  <sql:url>jdbc:mysql://localhost/test</sql:url>  
</sql:connection>

<%-- 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 connection --%>
<sql:connection id="conn1">
  <sql:url>jdbc:mysql://localhost/test</sql:url>
  <sql:userId>root</sql:userId>   
</sql:connection>

<%-- open a database connection --%>
<sql:connection id="conn1">
  <sql:url>jdbc:mysql://localhost/test</sql:url>
  <sql:userId>root</sql:userId>   
  <sql:password>notVerySecure</sql:password>   
</sql:connection>

<%-- open a database connection --%>
<sql:connection id="conn1">
  <sql:url>jdbc:mysql://localhost/test</sql:url>
  <sql:userId>root</sql:userId>   
  <sql:password>notVerySecure</sql:password>   
</sql:connection>

<%-- statement tags go here --%>

<sql:closeConnection conn="conn1"/>

<%-- 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>

<%-- 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>

<%-- 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>

<%-- 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>

<%-- insert a row into the database --%>
<sql:preparedStatement id="stmt1" conn="conn1">
  <sql:query>
    insert into test_books (id, name) 
      values (?, ?)
  </sql:query>    
  <sql:execute>
    <sql:setInt position="1">3</sql:setInt>
    <sql:setString position="2"><%=request.getParameter("book_title")%></sql:setString>
  </sql:execute> 
</sql:preparedStatement>

See the example application jdbc-examples.war for examples of the usage of the tags from this custom tag library.

Java programmers can view the java class documentation for this tag library as javadocs.

Review the complete revision history of this tag library.