Handling Web page with DB Tag Library:

First, obtain the Jakarta DBTags binary distribution from the Apache Jakarta DBTags site. Copy dbtags.tld, the tag library descriptor file, to the /WEB-INF directory of your web application. Copy dbtags.jar, the tag library .jar file, to the /WEB-INF/lib directory.

Next, you need to modify the /WEB-INF/web.xml web application deployment descriptor file. Include the following taglib tag in theweb-app element of the web.xml deployment descriptor.

  http://jakarta.apache.org/taglibs/dbtags
  /WEB-INF/dbtags.tld

Then add the following directive to this article's example JSP file, SQLDBTags.jsp:

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

The prefix attribute of the taglib directive is the prefix of the tags used as they'll be used the JSP. The choice of sql is arbitrary, meaning you can use another name if you like.

Next we need to make the database driver classes available to the JSP. For Oracle, add the zip file /ora81/jdbc/lib/classes12.zip to the classpath. This completes the required configuration steps.

Obtaining a Database Connection

A database connection is required to work with the database. If a JSP scriptlet is used to obtain the connection, the JDBC driver class has to be loaded with:

Class.forName("");

and a JDBC connection is obtained with:

Connection conn =
    DriverManager.getConnection(url,
                                userName,
                                password);

With the DBTags tag library, a JDBC connection is obtained with the sql:connection tag. In the example JSP, SQLDBTags.jsp, a connection with a Oracle database is established with the Oracle OCI Type 2 driver as follows:

  SYS
  change_on_install
  jdbc:oracle:oci8:@OracleDB
  oracle.jdbc.driver.OracleDriver

The id attribute identifies the connection so that later tags can refer to it. The sql:driver is the database driver class name. Finally, the sql:userId, sql:password, and sql:url tags provide the other needed parameters to create a database connection; i.e., the same parameters needed for a getConnection() call.

In addition to the attributes and sql:connection tags used in the example JSP, the sql:connection tag syntax can also have the attributes dataSource, which specifies a DataSource object, and jndiName, which specifies a JNDI name of a datasource. A JNDI-named DataSource can also be specified through the tag sql:jndiName.

With the sql:connection tag, the JDBC connection is created and given an id that can be used by other tags in the JSP.

Creating a Database Table

To create a database table in a JSP scriptlet, a JDBC Statement has to be created with:

Statement stmt=conn.createStatement();

A database table can then created with:

stmt.executeUpdate("Create TABLE ..." );

With DBTags, a SQL database table is created with the sql:statement tag instead. In the example JSP, the table OracleCatalogis created with the columns Journal, Publisher, Edition, Title, and Author.

The sql:statement tag has an attribute, conn, which refers to the id of the SQL connection created in the previous section. The table is created as follows:

  <%--SQL query to create a table--%>
  
    CREATE TABLE OracleCatalog(Journal VARCHAR(25),
                               Publisher Varchar(25),
                               Edition VARCHAR(25),
                               Title varchar(45),
                               Author Varchar(25))
  
  <%-- execute the query --%>
  

The sql:statement tag has the attributes id and conn. id provides an identifier that can be used by other tags, and conn is the idof the connection we created earlier.

The SQL query to create the table is set with the sql:query tag. This query is then executed with the sql:execute tag, which has an attribute ignoreErrors. If this attribute is set to true, it ignores an SQLException generated during the compilation of a JSP, and does not throw a JSPTagException exception.

An sql:statement is also used to insert values into the newly created table.

  <%--SQL query to add values to a table--%>
  
    INSERT INTO  OracleCatalog VALUES('Oracle Magazine',
                                      'Oracle Publishing',
                                      'Jan-Feb 2004',
                                      'Designing Schemas',
                                      'Cameron O'Rourke')
  
  <%-- execute the query --%>
  

Generating a ResultSet from a Database Table

To run a query and get results in a JSP scriptlet, a ResultSet has to be created with:

ResultSet rs=stmt.executeQuery("SELECT ...");

The result set is iterated with code like the following:

while(rs.next()){
    String var=rs.getString("...");
}

With DBTags sql:resultSet tag is used to retrieve and iterate through a SQL query result set. The sql:getColumn tag retrieves the column values in a result set.

 
  
    select JOURNAL, PUBLISHER, EDITION, TITLE, AUTHOR
            from OracleCatalog
  
  


      
JOURNAL
      
PUBLISHER
      
EDITION
      
TITLE
      
AUTHOR
    

      

      

      

      

      

    

Inside of the sql:resultSet tag, we can use sql:getColumn tags to retrieve the value for a given column and put it in the HTML. In this case, we have a table row inside of the sql:resultSet, and inside each table cell (), we use sql:getColumn to populate the cell value. The result looks like the following:

JOURNAL	PUBLISHER	EDITION	TITLE	AUTHOR
Oracle Magazine	Oracle Publishing	Nov-Dec 2003	Updating XQuery	Jason Hunter
Oracle Magazine	Oracle Publishing	Jan-Feb 2004	Designing Schemas	Cameron O'Rourke

Updating a Database Table with a Prepared Statement

A prepared statement is a SQL query with ? symbols. The column values represented by the ? symbols are set with a separate set of tags. If a JSP scriptlet is used to update a database table with a prepared statement, a prepared statement has to be created with:

PreparedStatement stmt=conn.prepareStatement("UPDATE ...");

The column values are set with:

stmt.setString("...");
stmt.setInt("...");

The prepared statement is executed with:

stmt.executeUpdate();

A prepared statement with DBTags is generated with the sql:preparedStatement tag. In the example JSP, the database tableOracleCatalog is updated with a prepared statement, as follows:

  <%-- prepared statement SQL query --%>
  
    UPDATE OracleCatalog SET TITLE=?, AUTHOR=? WHERE TITLE=? 
  
  
    <%=title%>
    <%=author%>
    Updating XQuery
  

The column values in the prepared statement are set with the sql:setColumn tag. After updating the table, the example code query produces the following results:

JOURNAL	PUBLISHER	EDITION	TITLE	AUTHOR
Oracle Magazine	Oracle Publishing	Nov-Dec 2003	Unicode Enables Globalization	Jonathan Gennick
Oracle Magazine	Oracle Publishing	Jan-Feb 2004	Designing Schemas	Cameron O'Rourke

Finally, when the JSP is finished with the database connection, it closes it with the sql:closeConnection DBTags tag:

Conclusion

DBTags custom tag library is used for servlet specifications prior to Servlet 2.3. For Servlet 2.3 and 2.4 specification implementations, Jakarta's Standard Tag Library 1.1, which is an implementation of JavaServer Pages Standard Tag Library (JSTL) 1.1, should be used.