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 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:
<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.
Connection Tags | |
connection | Get a java.sql.Connection object from the DriverManager or a DataSource |
url | JDBC URL of the database |
driver | JDBC driver for the database |
userId | user id for the database |
password | password for the database |
closeConnection | Closes a java.sql.Connection |
Statement Only Tags | |
statement | create and execute a database query |
escapeSql | excape a String for an SQL query |
Statement/PreparedStatement Tags | |
query | declare an SQL query |
execute | execute an insert, update or delete statement |
PreparedStatement Only Tags | |
preparedStatement | create and execute a tokenized database query |
setArray | |
setAsciiStream | |
setBigDecimal | |
setBinaryStream | |
setBlob | |
setBoolean | |
setBytes | |
setByte | |
setCharacterStream | |
setClob | |
setDate | |
setDouble | |
setFloat | |
setInt | |
setLong | |
setNull | |
setObject | |
setRef | |
setShort | |
setString | |
setTimestamp | |
setTime | |
ResultSet Tags | |
resultSet | loop through the rows of a select statement |
wasNull | execute the tag body if the last "get" tag encountered a null in the database |
getArray | |
getAsciiStream | |
getBigDecimal | |
getBinaryStream | |
getBlob | |
getBoolean | |
getBytes | |
getByte | |
getCharacterStream | |
getClob | |
getDate | |
getDouble | |
getFloat | |
getInt | |
getLong | |
getObject | |
getRef | |
getShort | |
getString | |
getTimestamp | |
getTime |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Get a java.sql.Connection from the DriverManager or a DataSource | ||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||
Sets the database url for the connection tag. | |||||||||||||
|
|
|||||||||||||
Sets the driver class name for the connection tag. | |||||||||||||
|
|
|||||||||||||
Sets the user id for the connection tag. | |||||||||||||
|
|
|||||||||||||
Sets the password for the connection tag. | |||||||||||||
|
|
||||||||||||||||||||||||
Close a java.sql.Connection | ||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create and execute a database query | ||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||
SQL-escapes a String | |||||||||||||
|
|
|||||||||||||
Set a query for a statement or preparedStatement tag | |||||||||||||
|
|
|||||||||||||
Executes an insert, update or delete for a statement or preparedStatement tag | |||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create and execute a tokenized database query | ||||||||||||||||||||||||||||||||||||||||||||||||||||
|
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.