2010-04-14 - Jakarta Taglibs has been retired.

For more information, please explore the Attic.

Jakarta Project: DBTags Tag library

Version: 1.0.0

Table of Contents

Overview

The DBTags custom tag library contains tags which can be used to read from and write to a SQL database.NOTE: this taglib is deprecated - you should use JSTL's SQL instead!

Requirements

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.

DBTags requirements

The DBTags 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:

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/dbtags" 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.

Documentation

Simple Usage Example

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"/>

Tags in Detail

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.

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 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"/>

Statement Tags

"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.

PreparedStatement Tags

"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.

ResultSet Tags

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"/>

If neither the format nor locale attribute is set, output should be identical to getColumn.

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.

Tag Summary

Connection Tags
connection Get a java.sql.Connection object from the DriverManager or a DataSource.
url Sets the database URL of the enclosing connection tag.
jndiName Sets the JNDI named JDBC DataSource of the enclosing connection tag.
driver Sets the driver class name for the connection tag.
userIdSets the user id for the connection tag.
password Sets the password for the connection tag.
closeConnection Close the specified connection. The "conn" attribute is the name of a connection object in the page context.
 
Statement Only Tags
statement Create and execute a database query.
escapeSql Replaces each single quote in the tag body with a pair of single quotes.
 
Statement/PreparedStatement Tags
query Set a query for a statement or preparedStatement tag
execute Executes an insert, update or delete for a statement or preparedStatement tag
 
PreparedStatement Only Tags
preparedStatement Create and execute a tokenized database query
setColumn Set a field in a preparedStatement. Set the value as a String inside the tag body.
 
ResultSet Tags
resultSet JSP tag resulset, executes the query and loops through the results for the enclosing statement or preparedstatement tag. The body of this tag is executed once per row in the resultset. The optional "loop" attribute, which default to true, specifies whether to execute the tag body once per row "true", or to simply assign the ResultSet to the page attribute specified by "id".
wasNull Executes its body if the last getColumn tag received a null value from the database. You must be inside a resultset tag and there must be a previous getColumn tag, or an error will be generated.
wasNotNull Executes its body if the last getColumn tag did not encounter a null value from the database.
getColumn Gets the value, as a String, of a coulmn in the enclosing resultset. The column number is set via the "position" attribute. You can optionally set the value, as a String, to a serlvet attribute instead of the tag body with the "to" attribute. The scope of the servlet attribute is specified by the "scope" XML attribute (default = page).
getNumber Similar to getColumn, but provides more precise control over number formatting. 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 "_". If neither the format nor locale attribute is set, output should be identical to getColumn.
getTime Similar to getColumn, but provides more precise control over java.sql.Time formatting. The "format" attribute can be either a pattern as accepted by SimpleDateFormat or a style: "FULL", "LONG", "MEDIUM" or "SHORT". The "locale" attribute can have one to three components as accepted by the Locale constructor: language, country and variant. They are separated by "_". If neither the format nor locale attribute is set, output should be identical to getColumn.
getTimestamp Similar to getColumn, but provides more precise control over java.sql.Timestamp formatting. The "format" attribute can be either a pattern as accepted by SimpleDateFormat or a style: "FULL", "LONG", "MEDIUM" or "SHORT". The "locale" attribute can have one to three components as accepted by the Locale constructor: language, country and variant. They are separated by "_". If neither the format nor locale attribute is set, output should be identical to getColumn.
getDate Similar to getColumn, but provides more precise control over java.sql.Date formatting. The "format" attribute can be either a pattern as accepted by SimpleDateFormat or a style: "FULL", "LONG", "MEDIUM" or "SHORT". It is required. The "locale" attribute can have one to three components as accepted by the Locale constructor: language, country and variant. They are separated by "_".
wasEmpty 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.
wasNotEmpty Executes its body if the last ResultSet tag received more than 0 rows from the database. You must be after a ResultSet tag, or an error will be generated.
rowCount 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. Using the tag before the ResultSet will produce an error.
 

Tag Reference

connection Availability: 1.0

Get a java.sql.Connection object from the DriverManager or a DataSource.

Tag BodyJSP    
Restrictions None
AttributesNameRequired Runtime Expression Evaluation Availability
 id  Yes   No  1.0
 

Name of the resulting Connection attribute.

 dataSource  No   No  1.0
 

Name of an existing page attribute that contains a DataSource object.

 jndiName  No   No  1.0
 

Name used to find a datasource via jndi.

VariablesNameScopeAvailability
  id attribute value   End of tag to end of page  1.0
 
 PropertiesNameGetSetAvailability
   catalog yes yes 1.0
  

Set the catalog for this connection.

   closed yes no 1.0
  

False if the connection is open, true if it is not.

   readOnly yes yes 1.0
  

True if the connection has read-only permission.

Examples Method 1: using the DriverManager  
 



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

          

Method 2: using a DataSource  
 



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

          

Method 3: using a jndi named DataSource  
 



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

          

url Availability: 1.0

Sets the database URL of the enclosing connection tag.

Tag BodyJSP    
Restrictions

Use inside a connection tag.

AttributesNameRequired Runtime Expression Evaluation Availability
 initParameter  No   No  1.0
 

Optional attribute, indicating the name of an init parameter

VariablesNone
Examples
 



<%-- example 1: using the tag body --%>
<sql:connection id="conn1">
  <sql:url>jdbc:mysql://localhost/test</sql:url>  
</sql:connection>

<%-- example 2: using an init parameter --%>
<sql:connection id="conn1">
  <sql:url initParameter="dbURL"/>  
</sql:connection>
    
          

jndiName Availability: 1.0

Sets the JNDI named JDBC DataSource of the enclosing connection tag.

Tag BodyJSP    
Restrictions

Use inside a connection tag.

AttributesNameRequired Runtime Expression Evaluation Availability
 initParameter  No   No  1.0
 

Optional attribute, indicating the name of an init parameter

VariablesNone
Examples
 



<%-- example 1: using the tag body --%>
<sql:connection id="conn1">
  <sql:jndiName>java:/comp/jdbc/test</sql:jndiName>
</sql:connection>
                                                      
<%-- example 2: using an init parameter --%>
<sql:connection id="conn1">                 
  <sql:jndiName initParameter="jndiDataSource"/>          
</sql:connection>

          

driver Availability: 1.0

Sets the driver class name for the connection tag.

Tag BodyJSP    
Restrictions

Use inside a connection tag.

AttributesNameRequired Runtime Expression Evaluation Availability
 initParameter  No   No  1.0
 

Optional attribute, indicating the name of an init parameter.

VariablesNone
Examples
 



<%-- example 1: using the tag body --%>
<sql:connection id="conn1">
  <sql:url>jdbc:mysql://localhost/test</sql:url>
  <sql:driver>org.gjt.mm.mysql.Driver</sql:driver>  
</sql:connection>

<%-- example 2: using an init parameter --%>
<sql:connection id="conn1">
  <sql:url initParameter="dbURL"/>  
  <sql:driver initParameter="dbDriver"/>  
</sql:connection>
         
          

userId Availability: 1.0

Sets the user id for the connection tag.

Tag BodyJSP    
Restrictions

Use inside a connection tag.

AttributesNameRequired Runtime Expression Evaluation Availability
 initParameter  No   No  1.0
 

Optional attribute, indicating the name of an init parameter.

VariablesNone
Examples
 

   

<%-- example 1: using the tag body --%>
<sql:connection id="conn1">
  <sql:url>jdbc:mysql://localhost/test</sql:url>
  <sql:userId>root</sql:userId>   
</sql:connection>

<%-- example 2: using an init parameter --%>
<sql:connection id="conn1">
  <sql:url initParameter="dbURL"/>  
  <sql:userId initParameter="dbUserId"/>   
</sql:connection>

          

password Availability: 1.0

Sets the password for the connection tag.

Tag BodyJSP    
Restrictions

Use inside a connection tag.

AttributesNameRequired Runtime Expression Evaluation Availability
 initParameter  No   No  1.0
 

Optional attribute, indicating the name of an init parameter.

VariablesNone
Examples
 

   

<%-- example 1: using the tag body --%>
<sql:connection id="conn1">
  <sql:url>jdbc:mysql://localhost/test</sql:url>
  <sql:userId>root</sql:userId>   
  <sql:password>notVerySecure</sql:password>   
</sql:connection>

<%-- example 2: using an init parameter --%>
<sql:connection id="conn1">
  <sql:url initParameter="dbURL"/>  
  <sql:userId initParameter="dbUserId"/>   
  <sql:password initParameter="dbPassword"/>   
</sql:connection>
 
          

closeConnection Availability: 1.0

Close the specified connection. The "conn" attribute is the name of a connection object in the page context.

Tag Bodyempty    
Restrictions None
AttributesNameRequired Runtime Expression Evaluation Availability
 conn  Yes   No  1.0
 

Id of the connection you want to close.

VariablesNone
Examples
 



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

          

statement Availability: 1.0

Create and execute a database query.

Tag BodyJSP    
Restrictions None
AttributesNameRequired Runtime Expression Evaluation Availability
 id  Yes   No  1.0
 

Script variable id for use with standard jsp:getProperty tag.

 conn  Yes   No  1.0
 

id of the connection to use

VariablesNameScopeAvailability
  id attribute value   Nested within tag  1.0
 
 PropertiesNameGetSetAvailability
   fetchSize yes yes 1.0
  

the number of rows that should be fetched from the database when more rows are needed

   maxRows yes yes 1.0
  

the maximum number of rows that a ResultSet object can contain (handy!)

   queryTimeout yes yes 1.0
  

the number of seconds the driver will wait for a Statement object to execute

Examples
 



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

escapeSql Availability: 1.0

Replaces each single quote in the tag body with a pair of single quotes.

Tag BodyJSP    
Restrictions

Use inside a query tag.

AttributesNone
VariablesNone
Examples
 



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

query Availability: 1.0

Set a query for a statement or preparedStatement tag

Tag BodyJSP    
Restrictions

Use inside a statement or preparedStatement tag.

AttributesNone
VariablesNone
Examples
 



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

execute Availability: 1.0

Executes an insert, update or delete for a statement or preparedStatement tag

Tag BodyJSP    
Restrictions

Use inside a statement or preparedStatement tag.

AttributesNameRequired Runtime Expression Evaluation Availability
 ignoreErrors  No   Yes  1.0
 

Setting the "ignoreErrors" atttibute to true will instruct the page to continue in the event of a SQLException, otherwise by default exceptions will throw a JspTagException.

VariablesNone
Examples
 



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

preparedStatement Availability: 1.0

Create and execute a tokenized database query

Tag BodyJSP    
Restrictions

The scipt variable is not available until after the query tag is called.

AttributesNameRequired Runtime Expression Evaluation Availability
 id  Yes   No  1.0
 

Script variable id

 conn  Yes   No  1.0
 

id of the connection to use

VariablesNameScopeAvailability
  id attribute value   Nested within tag  1.0
 
 PropertiesNameGetSetAvailability
   fetchSize yes yes 1.0
  

the number of rows that should be fetched from the database when more rows are needed

   maxRows yes yes 1.0
  

the maximum number of rows that a ResultSet object can contain (handy!)

   queryTimeout yes yes 1.0
  

the number of seconds the driver will wait for a Statement object to execute

Examples
 



<%-- 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:setColumn position="1">3</sql:setColumn>
    <sql:setColumn position="2"><%=request.getParameter("book_title")%></sql:setColumn>
  </sql:execute> 
</sql:preparedStatement>
 
          

setColumn Availability: 1.0

Set a field in a preparedStatement. Set the value as a String inside the tag body.

Tag BodyJSP    
Restrictions

Use within the preparedStatement tag

AttributesNameRequired Runtime Expression Evaluation Availability
 position  Yes   No  1.0
 

Column position

VariablesNone
Examples
 



<%-- use the tag body --%>
<sql:setColumn position="1"><%= someValue %></sql:setColumn>
 
          

resultSet Availability: 1.0

JSP tag resulset, executes the query and loops through the results for the enclosing statement or preparedstatement tag. The body of this tag is executed once per row in the resultset. The optional "loop" attribute, which default to true, specifies whether to execute the tag body once per row "true", or to simply assign the ResultSet to the page attribute specified by "id".

Tag BodyJSP    
Restrictions

If a name attribute is not supplied, use within a statement or preparedStatement and after a query. If a name attribute is supplied, there are no restrictions.

AttributesNameRequired Runtime Expression Evaluation Availability
 id  Yes   No  1.0
 

Script variable id

 loop  No   No  1.0
 

True: execute the tag body once per row in the result set, automatically advancing the rows. False: execute the tag body once.

 name  No   No  1.0
 

Name of an attribute containing a ResultSet object. If you pull a ResultSet object from an attribute, it is not necessary to place this tag inside of a statement.

 scope  No   No  1.0
 

Scope (page, request, session, or application) to search for the ResultSet attribute indicated in the "name" attribute. If this is not supplied, we use the default findAttribute() behaviour.

VariablesNameScopeAvailability
  id attribute value   Nested within tag  1.0
 
 PropertiesNameGetSetAvailability
   fetchSize yes yes 1.0
  

the number of rows that should be fetched from the database when more rows are needed

Examples
 



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

wasNull Availability: 1.0

Executes its body if the last getColumn tag received a null value from the database. You must be inside a resultset tag and there must be a previous getColumn tag, or an error will be generated.

Tag BodyJSP    
Restrictions

Must be used following a getColumn tag.

AttributesNone
VariablesNone
Examples
 



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

wasNotNull Availability: 1.0

Executes its body if the last getColumn tag did not encounter a null value from the database.

Tag BodyJSP    
Restrictions

Must be used following a getColumn tag.

AttributesNone
VariablesNone
Examples
 



<%-- 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" to="description"/>
          <sql:wasNotNull>Description: <%= pageContext.getAttribute("description") %></sql:wasNotNull></td>
    </tr>
  </sql:resultSet>
</sql:statement>
 
          

getColumn Availability: 1.0

Gets the value, as a String, of a coulmn in the enclosing resultset. The column number is set via the "position" attribute. You can optionally set the value, as a String, to a serlvet attribute instead of the tag body with the "to" attribute. The scope of the servlet attribute is specified by the "scope" XML attribute (default = page).

Tag Bodyempty    
Restrictions

Use within the resultSet tag

AttributesNameRequired Runtime Expression Evaluation Availability
 position  No   No  1.0
 

Column position

 colName  No   No  1.0
 

Column name

 to  No   No  1.0
 

Optionally assign the String to an attribute rather than the JSP output.

 scope  No   No  1.0
 

Optionally change the scope of the attribute designated in "to" (default = page).

VariablesNone
Examples
 



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

getNumber Availability: 1.0

Similar to getColumn, but provides more precise control over number formatting. 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 "_". If neither the format nor locale attribute is set, output should be identical to getColumn.

Tag Bodyempty    
Restrictions

Use within the resultSet tag

AttributesNameRequired Runtime Expression Evaluation Availability
 position  No   No  1.0
 

Column position

 colName  No   No  1.0
 

Column name

 to  No   No  1.0
 

Optionally assign the String to an attribute rather than the JSP output.

 scope  No   No  1.0
 

Optionally change the scope of the attribute designated in "to" (default = page).

 locale  No   Yes  1.0
 

Format according to a particular locale.

 format  No   Yes  1.0
 

Specify a format for the number.

VariablesNone
Examples
 



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

getTime Availability: 1.0

Similar to getColumn, but provides more precise control over java.sql.Time formatting. The "format" attribute can be either a pattern as accepted by SimpleDateFormat or a style: "FULL", "LONG", "MEDIUM" or "SHORT". The "locale" attribute can have one to three components as accepted by the Locale constructor: language, country and variant. They are separated by "_". If neither the format nor locale attribute is set, output should be identical to getColumn.

Tag Bodyempty    
Restrictions

Use within the resultSet tag

AttributesNameRequired Runtime Expression Evaluation Availability
 position  No   No  1.0
 

Column position

 colName  No   No  1.0
 

Column name

 to  No   No  1.0
 

Optionally assign the String to an attribute rather than the JSP output.

 scope  No   No  1.0
 

Optionally change the scope of the attribute designated in "to" (default = page).

 locale  No   Yes  1.0
 

Format according to a particular locale.

 format  No   Yes  1.0
 

Specify a format for the time.

VariablesNone
Examples
 



<sql:getTime colName="time"/>
 
          

getTimestamp Availability: 1.0

Similar to getColumn, but provides more precise control over java.sql.Timestamp formatting. The "format" attribute can be either a pattern as accepted by SimpleDateFormat or a style: "FULL", "LONG", "MEDIUM" or "SHORT". The "locale" attribute can have one to three components as accepted by the Locale constructor: language, country and variant. They are separated by "_". If neither the format nor locale attribute is set, output should be identical to getColumn.

Tag Bodyempty    
Restrictions

Use within the resultSet tag

AttributesNameRequired Runtime Expression Evaluation Availability
 position  No   No  1.0
 

Column position

 colName  No   No  1.0
 

Column name

 to  No   No  1.0
 

Optionally assign the String to an attribute rather than the JSP output.

 scope  No   No  1.0
 

Optionally change the scope of the attribute designated in "to" (default = page).

 locale  No   Yes  1.0
 

Format according to a particular locale.

 format  No   Yes  1.0
 

Specify a format for the timestamp.

VariablesNone
Examples
 



<sql:getTimestamp colName="time"/>
 
          

getDate Availability: 1.0

Similar to getColumn, but provides more precise control over java.sql.Date formatting. The "format" attribute can be either a pattern as accepted by SimpleDateFormat or a style: "FULL", "LONG", "MEDIUM" or "SHORT". It is required. The "locale" attribute can have one to three components as accepted by the Locale constructor: language, country and variant. They are separated by "_".

Tag Bodyempty    
Restrictions

Use within the resultSet tag

AttributesNameRequired Runtime Expression Evaluation Availability
 position  No   No  1.0
 

Column position

 colName  No   No  1.0
 

Column name

 to  No   No  1.0
 

Optionally assign the String to an attribute rather than the JSP output.

 scope  No   No  1.0
 

Optionally change the scope of the attribute designated in "to" (default = page).

 locale  No   Yes  1.0
 

Format according to a particular locale.

 format  No   Yes  1.0
 

Specify a format for the date.

VariablesNone
Examples
 



<sql:getDate colName="time" format="FULL"/>
 
          

wasEmpty Availability: 1.0

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.

Tag BodyJSP    
Restrictions

Use after a ResultSet tag.

AttributesNone
VariablesNone
Examples
 



<%-- showing the contents of the table --%>
<table>
<tr><th>id</th><th>name</th><th>description</th></tr>
<sql:preparedStatement id="stmt6" conn="conn1"> 
  <sql:query>
    select id, name, description from test_books
  </sql:query>
  <sql:resultSet id="rset4">
    <tr>
      <td><sql:getColumn position="1"/></td>
      <td><sql:getColumn position="2"/></td>
      <td><sql:getColumn position="3" to="description"/></td>
    </tr>
  </sql:resultSet>
  <tr>
    <td colspan="3">
    <%-- show different text, depending on whether or not
    any rows were retrieved --%>
    <sql:wasEmpty>No rows retrieved.</sql:wasEmpty>
    <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty>
    </td>
  </tr>
</sql:preparedStatement>
 
          

wasNotEmpty Availability: 1.0

Executes its body if the last ResultSet tag received more than 0 rows from the database. You must be after a ResultSet tag, or an error will be generated.

Tag BodyJSP    
Restrictions

Use after a ResultSet tag.

AttributesNone
VariablesNone
Examples
 



<%-- showing the contents of the table --%>
<table>
<tr><th>id</th><th>name</th><th>description</th></tr>
<sql:preparedStatement id="stmt6" conn="conn1"> 
  <sql:query>
    select id, name, description from test_books
  </sql:query>
  <sql:resultSet id="rset4">
    <tr>
      <td><sql:getColumn position="1"/></td>
      <td><sql:getColumn position="2"/></td>
      <td><sql:getColumn position="3" to="description"/></td>
    </tr>
  </sql:resultSet>
  <tr>
    <td colspan="3">
    <%-- show different text, depending on whether or not
    any rows were retrieved --%>
    <sql:wasEmpty>No rows retrieved.</sql:wasEmpty>
    <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty>
    </td>
  </tr>
</sql:preparedStatement>
 
          

rowCount Availability: 1.0

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. Using the tag before the ResultSet will produce an error.

Tag Bodyempty    
Restrictions

Use inside or after a ResultSet tag (not before).

AttributesNone
VariablesNone
Examples
 



<%-- showing the contents of the table --%>
<table>
<tr><th>id</th><th>name</th><th>description</th></tr>
<sql:preparedStatement id="stmt6" conn="conn1"> 
  <sql:query>
    select id, name, description from test_books
  </sql:query>
  <sql:resultSet id="rset4">
    <tr>
      <td><sql:getColumn position="1"/></td>
      <td><sql:getColumn position="2"/></td>
      <td><sql:getColumn position="3" to="description"/></td>
    </tr>
  </sql:resultSet>
  <tr>
    <td colspan="3">
    <%-- show different text, depending on whether or not
    any rows were retrieved --%>
    <sql:wasEmpty>No rows retrieved.</sql:wasEmpty>
    <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty>
    </td>
  </tr>
</sql:preparedStatement>
 
          

Examples

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

Java Docs

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

Revision History

Review the complete revision history of this tag library.