ONJava.com    
 Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples


SQL Database Access with DBTags SQL Database Access with DBTags

by Deepak Vohra
05/05/2004

In a J2SE or J2EE application, Java Database Connectivity (JDBC) can be used to create a connection with a SQL database, create database tables, retrieve result sets, and update the database. To use a database from a Java Server Page (JSP), one often uses a JSP scriptlet to create a JDBC connection via the getConnection() method.

Jakarta DBTags is a custom tag library that contains tags to perform the same tasks -- creating a database connection, creating tables, retrieving result sets, and updating tables with prepared statements -- all without the use of JSP scriptlets.

This tutorial illustrates the use of Jakarta DBTags in an example JSP. The example JSP illustrates obtaining a connection with a Oracle database, creating a table in the database, retrieving a result set from the database, and updating the database table with a prepared statement with the DBTags tags.

This tutorial is structured into the following sections:

  1. Preliminary Setup
  2. Obtaining a Database Connection
  3. Creating a Database Table
  4. Generating a ResultSet from a Database Table
  5. Updating a Database Table with a Prepared Statement

Preliminary Setup

The Apache Jakarta DBTags are designed for a web server that supports JavaServer Pages specification 1.2. In this tutorial, we use WebLogic 7.0 for the servlet container and Oracle 8.1.7 for the database, although you can use other databases and JSP-compliant web servers.

JDBC Pocket Reference

Related Reading

JDBC Pocket Reference
By Donald Bales

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 the web-app element of the web.xml deployment descriptor.

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

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("<driver class>");

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:

<sql:connection id="conn1">
  <sql:userId>SYS</sql:userId>
  <sql:password>change_on_install</sql:password>
  <sql:url>jdbc:oracle:oci8:@OracleDB</sql:url>
  <sql:driver>oracle.jdbc.driver.OracleDriver</sql:driver>
</sql:connection>

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 OracleCatalog is 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:statement id="stmt1" conn="conn1">
  <%--SQL query to create a table--%>
  <sql:query>
    CREATE TABLE OracleCatalog(Journal VARCHAR(25),
                               Publisher Varchar(25),
                               Edition VARCHAR(25),
                               Title varchar(45),
                               Author Varchar(25))
  </sql:query>
  <%-- execute the query --%>
  <sql:execute ignoreErrors="true"/>
</sql:statement>

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 id of 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:statement id="stmt2" conn="conn1">
  <%--SQL query to add values to a table--%>
  <sql:query>
    INSERT INTO  OracleCatalog VALUES('Oracle Magazine',
                                      'Oracle Publishing',
                                      'Jan-Feb 2004',
                                      'Designing Schemas',
                                      'Cameron O'Rourke')
  </sql:query>
  <%-- execute the query --%>
  <sql:execute ignoreErrors="true"/>
</sql:statement>

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.

<table BORDER>
    <tr>
      <td>JOURNAL</td>
      <td>PUBLISHER</td>
      <td>EDITION</td>
      <td>TITLE</td>
      <td>AUTHOR</td>
    </tr>
<sql:statement id="stmt4" conn="conn1"> 
  <sql:query>
    select JOURNAL, PUBLISHER, EDITION, TITLE, AUTHOR
            from OracleCatalog
  </sql:query>
  <sql:resultSet id="rset1">
    <tr>
      <td><sql:getColumn position="1"/></td>
      <td><sql:getColumn position="2"/></td>
      <td><sql:getColumn position="3"/></td>
      <td><sql:getColumn position="4"/></td>
      <td><sql:getColumn position="5"/></td>
    </tr>
  </sql:resultSet>
</sql:statement>
</table>

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 (<td>), we use sql:getColumn to populate the cell value. The result looks like the following:

JOURNALPUBLISHEREDITIONTITLEAUTHOR
Oracle MagazineOracle PublishingNov-Dec 2003Updating XQueryJason Hunter
Oracle MagazineOracle PublishingJan-Feb 2004Designing SchemasCameron 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 table OracleCatalog is updated with a prepared statement, as follows:

<sql:preparedStatement id="stmt5" conn="conn1">
  <%-- prepared statement SQL query --%>
  <sql:query>
    UPDATE OracleCatalog SET TITLE=?, AUTHOR=? WHERE TITLE=? 
  </sql:query>
  <sql:execute>
    <sql:setColumn position="1"><%=title%></sql:setColumn>
    <sql:setColumn position="2"><%=author%></sql:setColumn>
    <sql:setColumn position="3">Updating XQuery</sql:setColumn>
  </sql:execute>
</sql:preparedStatement>

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:

JOURNALPUBLISHEREDITIONTITLEAUTHOR
Oracle MagazineOracle PublishingNov-Dec 2003Unicode Enables GlobalizationJonathan Gennick
Oracle MagazineOracle PublishingJan-Feb 2004Designing SchemasCameron O'Rourke

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

<sql:closeConnection conn="conn1"/>

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.

Resources

Deepak Vohra is a NuBean consultant and a web developer.


Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.