ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

JSTL 1.0: What JSP Applications Need, Part 2
Pages: 1, 2, 3, 4

Making a DataSource Available to JSTL



JSTL supports a number of ways to make a DataSource available to the database actions. In a Web container with Java Naming and Directory Interface (JNDI) support, a default DataSource can be defined as a JNDI resource with a context parameter in the web.xml file:

<context-param>
<param-name>
  javax.servlet.jsp.jstl.sql.dataSource
</param-name>
<param-value>
  jdbc/Production
</param-value>
</context-param>

The Web container's JNDI configuration tools must be used to configure a JNDI resource with the specified name; for instance, with a database account username and password, min and max connections in the pool, etc. How this is done varies between containers and is out of scope for this article (I cover, in detail, how to do it for Tomcat 4 in JavaServer Pages, 2nd Edition, though).

An alternative for containers that do not support JNDI is to let an application (servlet context) lifecycle listener create and configure a DataSource and set it as the default using the JSTL Config class:

import javax.servlet.*;
import javax.servlet.http.*;
import oracle.jdbc.pool.*;

public class AppListener implements ServletContextListener {

private OracleConnectionCacheImpl ds =null;

public void contextInitialized(ServletContextEvent sce){
  ServletContext application =sce.getServletContext();

  try {
	ds = new OracleConnectionCacheImpl();
	ds.setURL("jdbc:oracle:thin:@voyager2:1521:Oracle9i");
	ds.setMaxLimit(20);
	ds.setUser("scott");
	ds.setPassword("tiger");
  }
  catch (Exception e){
	application.log("Failed to create data source:"+
	e.getMessage());
  }
  Config.SQL_DATA_SOURCE;
}
...
}

The listener class in this example creates a DataSource with connection pool capabilities for an Oracle9i database, and makes it available as the default for the JSTL actions by using the Config class to set the corresponding configuration variable.

A third way, only suitable for prototyping or applications that are not so heavily used as to need connection pooling, is to use the <sql:setDataSource> action:

<sql:setDataSource
url="jdbc:mysql://dbserver/dbname"
driver="org.gjt.mm.mysql.Driver"
user="scott"
password="tiger" />

This action creates a simple data source, without pooling, for the specified JDBC URL, user and password, using the specified JDBC driver. You may use this action to get started, but I recommend that you use one of the other alternatives for a real site. Besides the lack of connection pooling for a data source created this way, it's not a good idea to include sensitive information like the database URL, username and password in a JSP page, since it may be possible for a Bad Guy to get access to the source of the page. Even though it shouldn't be possible, several bugs have been discovered in Web containers over the years that made this possible (as far as I know, all of them have been plugged in recent versions of the most commonly-used containers).

Reading Database Data

With a DataSource available, we can access the database. Here's how you read data from a database represented by the default DataSource:

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

<html>
<body>
  <h1>Reading database data</h1>
  <sql:query var="emps" sql="SELECT * FROM Employee" />
  ...
</body>
</html>

First you need to declare the JSTL library that contains the database actions, using the taglib directive at the top of this example. The <sql:query> action executes the SQL SELECT statement specified by the sql attribute (or as the body of the action element) and saves the result in the variable named by the var attribute.

The database query result is returned as a bean of the type javax.servlet.jsp.jstl.sql.Result with a number of read-only properties:

Property Java Type Description
rows java.util.SortedMap[] An array with a case-insensitive map per row with keys matching column names and values matching column values.
rowsByIndex Object[][] An array with an array per row with column values.
columnNames String[] An array with column names.
rowCount int The number of rows in the result.
limitedByMaxRows boolean true if not all matching rows are included due to reaching a specified max rows limit.

I showed you how to use the JSTL <c:forEach> action to display all or just some of the rows in part 1 of this article, so let's see how you can get just some of the rows and display them all in this part. Next and Previous links allow the user to ask for a different set. First, here's how to read a subset of the rows and then display the complete subset:

<c:set var="noOfRows" value="10" />

<sql:query var="emps" 
startRow="${param.start}" maxRows="${noOfRows}">
SELECT * FROM Employee
</sql:query>

<ul>
<c:forEach items="${emps.rows}" var="${emp}">
  <li><c:out value="${emp.name}" />
</c:forEach>
</ul>

The startRow attribute for the <sql:query> action is set to an EL expression that reads the value of a request parameter named start. You'll soon see how its value changes when clicking on the Next and Previous links. The first time the page is accessed, the parameter is not present at all, so the expression evaluates to 0. This means the query result contains rows starting with the first matching row (index 0). The maxRows attribute limits the total number of rows to the value of the noOfRows variable, set to 10 in this example. The <c:forEach> action loops through all rows in the result and generates a list item with one of the column values for each row.

We must also generate Next and Previous links to let the user grab a new set of rows:

<c:choose>
<c:when test="${param.start > 0}">
  <a href="emplist.jsp?start=<c:out 
	value="${param.start - noOfRows}"/>">Previous Page</a>
</c:when>
<c:otherwise>
  Previous Page
</c:otherwise>
</c:choose>
<c:choose>
<c:when test="${emps.limitedByMaxRows}">
  <a href="emplist.jsp?start=<c:out
	value="${param.start + noOfRows}"/>">Next Page</a>
</c:when>
<c:otherwise>
  Next Page
</c:otherwise>
</c:choose>

The first <c:choose> block is identical to the example in part 1; if the start request parameter is greater than zero, the current page shows a row subset other than the first, so a Previous link is added. The link points back to the same page, and includes the start parameter with a value that is its current value minus the number of rows displayed per page.

The second <c:choose> block takes advantage of the query result's limitedByMaxRows property. If this property is true, it means that the current result is truncated to the number of rows displayed per page. Hence, a Next link is generated with a start parameter value for the next row subset.

Pages: 1, 2, 3, 4

Next Pagearrow