JSTL 1.0: What JSP Applications Need, Part 2
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.