Whether you're building a prototype or testing database access, you must deal with database queries on a daily basis. Operations like querying and updating a database or grouping operations into a single transaction should be a simple matter. In this article, I show you how to use the Java™Server Pages Standard Tag Library (JSTL) 1.1 SQL tag library to easily access and manipulate data in the Apache Derby relational database. If you'd like to follow along, in addition to JDK 1.4, you need to download and install the following technologies used in the examples:

• The JSTL libraries: Download and extract the contents into a temporary location such as C:\temp.
• Tomcat 5.5: The JSTL 1.1 maintenance release requires a JSP container that supports the Servlet 2.4 and JSP 2.0 specifications. Because Apache Tomcat 5.x supports these specifications, I've used it as the JSP container for my examples.
• Apache Derby 10.1.2.1: Apache Derby is a popular open source relational database management system. Download the bin distribution package db-derby-10.1.2.1-bin.zip and extract the package. You'll find a PDF under the \docs\pdf\getstart folder that describes the steps to install Apache Derby.

This article assumes you are familiar with JSP technology, JSTL SQL tags, and simple database queries.

Configuring JSTL in Tomcat

First, you need to configure the JSTL libraries in Tomcat. Start by creating a folder called "tlds" under $CATALINA_HOME\webapps\ROOT\WEB-INF\ and copy the tld files C:\temp\jakarta-taglibs\standard\tld\c.tld and C:\temp\jakarta-taglibs\standard\tld\sql.tld to the $CATALINA_HOME\webapps\ROOT\WEB-INF\tlds directory.

Next, copy the extracted JAR files C:\temp\standard\lib\jstl.jar and C:\temp\standard\lib\standard.jar to the $CATALINA_HOME\webapps\ROOT\WEB-INF\lib directory. Note that CATALINA_HOME refers to the Tomcat installation directory.

Finally, add the entries shown in Listing 1 to the $CATALINA_HOME\webapps\ROOT\WEB-INF\web.xml deployment descriptor (or to the web.xml deployment descriptor of your Web application):

 
 
<taglib>
       <taglib-uri> http://java.sun.com/jsp/jstl/core </taglib-uri>
       <taglib-location> /WEB-INF/tlds/c.tld </taglib-location>
   </taglib>
   <taglib>
       <taglib-uri> http://java.sun.com/jsp/jstl/sql </taglib-uri>
       <taglib-location> /WEB-INF/tlds/sql.tld </taglib-location>
  </taglib>
  

Back to top

Configuring Apache Derby

Apache Derby comes with a tool called ij (interactive java) that allows you to create a database and execute SQL statements. To run the ij tool, follow these steps (note that <DERBY_INSTALL_HOME> is the directory where you have extracted the Derby zip package):

1. Include the <DERBY_INSTALL_HOME>\frameworks\embedded\bin and the JDK path (for example, C:\jdk1.4\bin) in the PATH variable.
2. Set the DERBY_INSTALL variable to <DERBY_INSTALL_HOME> (for example, C:\Derby\db-derby-10.1.2.1-bin).
3. Set the classpath to <DERBY_INSTALL_HOME>\lib\derby.jar and <DERBY_INSTALL_HOME>\lib\derbytools.jar.

You can also define the system directory (that is, what databases are stored, where to create new databases, and what configuration parameters to use) when Derby starts up by specifying a Java system property called derby.system.home. For example, you could set the directory as derby.system.home =C:\Derby\databases. If you do not specify the system directory when you start up Derby, the current directory becomes the system directory.

Creating a database and schema

Listing 2 shows how to create a database called ITEMSDB, a schema called PROJECTSCHEMA, and a table called ITEMDESC_TBL using the ij utility:

 
 
ij> connect 'jdbc:derby:ITEMSDB;create=true';
ij>CREATE SCHEMA PROJECTSCHEMA;
ij> CREATE TABLE PROJECTSCHEMA.ITEMDESC_TBL(ITEMNUMBER INTEGER,ITEMNAME VARCHAR(50),
    ITEMDATE DATE, ITEMOWNER VARCHAR(50));
ij>INSERT INTO PROJECTSCHEMA.ITEMDESC_TBL(ITEMNUMBER,ITEMNAME,ITEMDATE,ITEMOWNER) 
    VALUES(001,'KEYBOARD','2006-05-05','SUSAN');
ij>INSERT INTO PROJECTSCHEMA.ITEMDESC_TBL(ITEMNUMBER,ITEMNAME,ITEMDATE,ITEMOWNER) 
  VALUES(002,'MOUSE','2006-05-15','AMIT');
ij>INSERT INTO PROJECTSCHEMA.ITEMDESC_TBL(ITEMNUMBER,ITEMNAME,ITEMDATE,ITEMOWNER) 
    VALUES(003,'MONITOR','2005-05-10','AKU');
ij>INSERT INTO PROJECTSCHEMA.ITEMDESC_TBL(ITEMNUMBER,ITEMNAME,ITEMDATE,ITEMOWNER) 
    VALUES(004,'PAPERTRAY','2006-05-20','BILL');

Figure 1 shows a snapshot of how to issue the commands at the ij prompt:

Remember to exit!

After you're done creating tables and records, be sure to exit ij by typing ij>exit;. Two separate instances of Derby must not access the same database. For example, in an embedded environment, an application that accesses Derby databases starts up the local JDBC driver, which starts up an instance of Derby. Starting another application, such as ij, and connecting to the same database could severely corrupt the database. See the Derby Dev Guide (derbydev.pdf ) in <DERBY_INSTALL_HOME>\docs\pdf\devguide to learn more.

Back to top

Configuring a datasource in Tomcat

The next step is to configure a datasource in Tomcat. You need to use one of the following JDBC drivers depending on the environment you choose for Derby:

• Use org.apache.derby.jdbc.EmbeddedDriver for embedded environments where Derby runs in the same JVM as the application.
• Use org.apache.derby.jdbc.ClientDriver for the network server environment, which sets up a client/server environment.

For the sake of the examples in this article, I'm using the embedded driver. The derby.jar file in <DERBY_INSTALL_HOME>\lib contains the org.apache.derby.jdbc.EmbeddedDriver class. Copy this JAR file to the $CATALINA_HOME\common\lib path. Edit $CATALINA_HOME \conf\server.xml and add the entry shown in Listing 3:

 
 

<Context path="" docBase="ROOT" debug="0">
  <Resource name="jdbc/derbyds" auth="Container" type="javax.sql.DataSource"
       maxActive="100" maxIdle="3" maxWait="10000" username="user1" password="user1"
	   driverClassName="org.apache.derby.jdbc.EmbeddedDriver" 
	      url="jdbc:derby:C:\\Derby\\databases\\ITEMSDB"/>
</Context>

Next you have to add the JSTL declaration and corresponding SQL tags in your JSPs. Just add the following code snippet in your JSP and your JSTL 1.1 application is ready to go:

 
 	
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
	<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
	--------------------------------
	-------------------------------	
	<sql:setDataSource dataSource="jdbc/derbyds"/>
	<sql:query var="entries"  sql="<sql query>"/>
	</sql:query>

The primary goal of the JSTL 1.1 release is to synchronize the JSTL specification with the JSP 2.0 specification. Accordingly, the taglib URI in JSTL 1.1 would have the additional "jsp" declaration whereas the taglib URI in JSTL 1.0 would not, as shown in Listing 5:

 
 		
<%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql" %>
		<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>

You'll need to restart the Tomcat server to reflect the changes you've just made. You can use Tomcat's Services panel to start and stop the server.

Back to top

Using JSTL SQL actions

Now that you have all the configuration out of the way, the fun begins. I'll show you how to use simple SQL tags to make database queries and updates, as well as how to group multiple database operations as a transaction.

Database queries (SELECT)

In Listing 6, I use the <sql:query> tag to query a database:

 
 
<sql:setDataSource dataSource="jdbc/derbyds"/> 
<sql:query var="items" sql="SELECT * FROM PROJECTSCHEMA.ITEMDESC_TBL">
</sql:query>
<table border="1" align="center" valign="center">
<c:forEach var="row" items="${items.rows}">
<tr>
<td><c:out value="${row.itemnumber}"/></td>
<td><c:out value="${row.itemname}"/></td>
<td ><c:out value="${row.itemdate}"/></td>
<td><c:out value="${row.itemowner}"/></td>
</c:forEach>
</table>

Figure 2 shows the results of this query:

Compare the code in Listing 6 with the code in Listing 7, which does not use JSTL, and you will be convinced of JSTL's user-friendly tags:

 
 
<% Context initctx=new InitialContext();
      Context envCtx=(Context)initctx.lookup("java:comp/env");
       javax.sql.DataSource ds=(javax.sql.DataSource)envCtx.lookup("jdbc/derbyds");
      Connection conn = ds.getConnection();
      Statement stmt = null;
      stmt = conn.createStatement();
 java.sql.ResultSet result =  
    stmt.executeQuery("SELECT * FROM  PROJECTSCHEMA.ITEMDESC_TBL ");
	java.sql.ResultSetMetaData metadata = result.getMetaData();
            int colCount = metadata.getColumnCount();
	int noRows=0;
                        while (result.next()) {
                            noRows++;
                            %>
                               <%
                            for (int i = 1; i <= colCount; i++) {
                                %>
                                    <%= result.getString(i) %>
                                <%
                            }
                            %>
                                
                            <%
                        }           
        conn.close(); %>

Database updates (INSERT, UPDATE, or DELETE)

The <sql:update> tag executes an SQL INSERT, UPDATE, or DELETE statement (Listings 8-10). In addition, SQL statements that return nothing, such as SQL DDL statements, can be executed.

 
 
<sql:update dataSource="jdbc/derbyds">
INSERT INTO PROJECTSCHEMA.ITEMDESC_TBL(ITEMNUMBER,ITEMNAME,ITEMOWNER)
VALUES(005 ,? ,?)
<sql:param value="iPod"/>
<sql:param value="AMY"/>
</sql:update>

 
 
<sql:setDataSource dataSource="jdbc/derbyds"/>
<sql:update>
UPDATE PROJECTSCHEMA.ITEMDESC_TBL
SET ITEMNAME = ?
WHERE ITEMNUMBER = ?
<sql:param value="PENHOLDER"/>
<sql:param value="1"/>
</sql:update>

 
 
<sql:update dataSource="jdbc/db2ds">
DELETE FROM  PROJECTSCHEMA.ITEMDESC_TBL
WHERE ITEMNUMBER = ?
<sql:param value="3"/></sql:update>

Multiple database operations as a transaction

The final component of the JSTL database tag library is the <sql:transaction> tag. Grouping query and update operations into a transaction ensures that either the operations all succeed, or (if one operation fails) are all rolled back. The operations within a transaction can either all succeed or all fail.

The permitted JDBC transaction levels are:

• none
• read_committed
• read_uncommitted
• repeatable_read
• serializable

The default is to leave the transaction isolation level of the connection at its present setting, which depends on the database or datasource implementation.

In Listing 11, the <sql:transaction> action establishes a transaction context for <sql:query> and <sql:update> subtags:

 
 
<sql:transaction>
<sql:update>UPDATE PROJECTSCHEMA.ITEMDESC_TBL
SET ITEMNAME = ?
WHERE ITEMNUMBER = ? 
    <sql:param value="MM"/>
    <sql:param value="2"/>
  </sql:update>
  <sql:update>UPDATE PROJECTSCHEMA.ITEMDESC_TBL
SET ITEMOWNER = ?
WHERE ITEMNUMBER = ?
    <sql:param value="JOHN"/>
    <sql:param value="2"/>
  </sql:update>
</sql:transaction>

Back to top

In conclusion

Now you know how to make database queries as easy as they should be, using the JSTL 1.1 SQL tag library, Tomcat 5.5, and the Apache Derby relational database. Because the JSTL 1.1 maintenance release requires a JSP container that supports the Servlet 2.4 and JSP 2.0 specifications, you can configure the JSTL libraries in Tomcat 5.5. You've also learned how easy it is to set up an Apache Derby database, schemas, and tables using the Interactive Java (ij) tool. In addition to learning the JDBC drivers used with Tomcat 5.5, you know how to use simple SQL actions for queries, updates, and grouping several operations into a single transaction.

Note that the latest release of JSTL, JSTL 1.2, requires a JSP 2.1 container and that from JSTL 1.2 onwards, JSTL forms a part of Java EE. See the Resources section to learn more about changes to the JSTL 1.2 release.

Back to top

Download

Information about download methods

Resources

Learn

• A JSTL primer, Part 1: The expression language (Mark Kolb, developerWorks, February 2003): A starting point for learning about JSTL and SQL queries.
  
  
• Integrate Cloudscape Version 10 or Derby with Tomcat (Lance Bader, developerWorks, August 2005): Discusses possible scenarios for integrating Cloudscape or Derby with Tomcat.
  
  
• Use Derby in a J2EE Server environment (Stanley Bradbury, developerWorks, June 2006): Learn how to integrate Cloudscape or Derby with the J2EE development environment.
  
  
• The JSTL specifications: Learn more about JSTL SQL actions.
  
  
• Tomcat home page: See the Tomcat documentation about configuring JDBC resources.
  
  
• Apache Derby: See the manuals to learn more about this popular relational database management system.
  
  
• The Java technology zone: Hundreds of articles about every aspect of Java programming.
  
  

Get products and technologies

• Tomcat 5.5: Download the binary distribution of Tomcat 5.5.
  
  
• Apache Derby: Download the relational database management system discussed in this article.
  
  
• JSTL and SQL: Get the latest JSTL build.
  
  

Discuss

• developerWorks blogs: Get involved in the developerWorks community.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Configuring JSTL in Tomcat
• Configuring Apache Derby
• Configuring a datasource in Tomcat
• Using JSTL SQL actions
• In conclusion
• Download
• Resources
• About the author
• Comments

Next steps from IBM

Rational Application Developer provides a comprehensive Integrated Development Environment (IDE) used to develop Web 2.0 applications requiring J2EE, Servlets and JavaServer Pages (JSPs).

---

• Try: Try the free Rational Application Developer (RAD), which helps developers quickly design, develop, test, analyze, and deploy high-quality Java, Javaserver pages, J2EE and Web 2.0 applications.
• Article: See how Rational Application Developer and Service Data Objects have been extended to JavaServer Pages to create data views and database queries using a simple, wizard-based, drag-and-drop technology.
• Tutorial: Take a look at developing a J2EE Web application and JavaServer Pages (JSPs) for both an HTML input form and the HTML output from the server, using Rational Application Developer.
• Demo: This demo highlights the enhanced Web 2.0, rich Internet application development features in Rational Application Developer including AJAX and Java Persistence API application development.
• Buy: Rational Application Developer for WebSphere Software

Dig deeper into Java on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers