How to use SQL JSP Standard Library

Date: 21-May-2003

After completing this how-to you should be able to use SQL JSTL to :

• Query Oracle database.

• Insert a row of data into Oracle database

• Update a column data

Introduction

This document demonstrates how to use SQL JSP Standard Tag Library (JSTL) to access Oracle database. JSTL is intended as a convenience for JSP page authors who are not familiar or not comfortable with scripting languages such as Java. Historically, scriptlets have been used in JSP pages to process dynamic data. With JSTL, the intent is for JSTL tag usage to replace the need for scriptlets. In this how to, we will look at how to use JSTL to access database. All SQL tags are provided as one bundle in http://java.sun.com/jstl/sql

Software Requirements

• Oracle9i Database version 9.0.1 or later. You can download the Oracle9i database from Oracle Technology Network. 

• JDK1.2.x or above This can be downloaded from here .

• Oracle9iAS Container for Java(OC4J) version 9.0.3 or later. It  is available for download at OTN.

• JSP Standard Tag Library. You can use the Standard Tag Library 1.0 Reference Implementation available from Apache.org

Using database JSTL tags

JSTL’s database library supports database queries, updates, and transactions. JSP pages can import this library with the following directive:
<%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql" %>

Preparing databases

For JSP pages that do not have a default database, <sql:setDataSource> can prepare a database for use.
The code below shows how to create a data source.

<sql:setDataSource
  var="example"
  driver="oracle.jdbc.driver.OracleDriver"
  url="jdbc:oracle:thin:@localhost:1521:ORCL"
  user="scott"
  password="tiger"
/>

The <sql:setDataSource> tag’s attributes are as follows:

Queries and updates

JSTL can read from databases with <sql:query> and write to them with <sql:update>. These tags support SQL commands with ? placeholders, which <sql:param> and <sql:dateParam> can fill in.

Querying from the database

<sql:query var="deejays">
  SELECT * FROM mytable
</sql:query>

<%-- Get the column names for the header of the table --%>
<c:forEach var="columnName" items="${deejays.columnNames}">
  <th><c:out value="${columnName}"/></th>
</c:forEach>

<%-- Get the value of each column while iterating over rows --%>
<c:forEach var="row" items="${deejays.rows}">
  <tr>
    <c:forEach var="column" items="${row}">
      <td><c:out value="${column.value}"/></td>
    </c:forEach>
  </tr>
</c:forEach>

<sql:query> tag is used to query the database and the the core tag <c:forEach> tag is used to loop through the Resultset. <c:forEach> tag fetches each row in the query. To get the values of each column in the row, you can use the column name. The core <c:out> tag is used to output the value.

Updating a column in the table

<sql:update var="updateCount" dataSource="${example}">
  UPDATE mytable SET name=? 
    <sql:param value="Scott Tiger"/> WHERE nameid=1
</sql:update>

<sql:update> tag is used for DML operations. An update query can have '?' in them. Then you can use <sql:parm> to associate a value to ?. The value for <sql:parm> can be obtained from a variable like HTTP parameters.

You can look at the complete source to see how to create a table and how to drop it.

Setup

To run the code, you need to configure OC4J to use JSTL.

• Unzip jakarta-taglibs-standard-1.0.zip, the file downloded from Apache to a directory, say D:\mydir.

• Copy the files in the directory D:\mydir\jakarta-taglibs\jstl-1.0\lib to <J2EE_HOME>\default-web-app\WEB-INF\lib. If the directory <J2EE_HOME>\default-web-app\WEB-INF\lib is not present, create it.

• Copy the file JstlSql.jsp to the directory <J2EE_HOME>\default-web-app\examples\jsp

• Run it from http://<host-name>:<port>/examples/jsp/JstlSql.jsp

References

• Oracle9iAS Containers for J2EE JSP Tag Libraries and Utilities Reference

• http://java.sun.com/products/jsp/jstl/

Summary

After going through this document you should have understood how to use JSTL tags to access the database.

Please enter your comments about this sample in the OTN Sample Code Discussion Forum.