Tip

Simplifying data access using Java Standard Tag Library


Jim Mason

Java Standard Tag Library concepts

Wouldn't it be great to rapidly script Web pages without having to learn a programming language such as RPG CGIDEV2, Basic, Java, etc? Imagine being able to query any data in your database, add, update and delete records! The Java Standard Tag Library ( JSTL ) lets developers use simple tags to script logic, access data, process XML documents and format data quickly in a Java Server Page (a Web page that uses Java tags) without the need to program in a traditional language! Here, I focus on quickly building a simple JSP to search an employee table using SQL to demonstrate how easily JSTL accesses data.

 
Java Standard Tag Library concepts 1
Where can you use the JSTL SQL access? 1
Overview of the JSTL packages 1
JSTL tags support Expression Language variables 2
JSTL tags for core and SQL packages 2
Setup server pre-requisites first: 3
MySQL database pool in WCE 3
Build JSP database pages overview: JSTL 3
Employee search by lastname SQL JSP example 3
Create a JSP using the database 3
Create a JSP using the JSP HTML template 3
Select the tag libraries to add to the page 3
Create an input variable for the JSP SQL search argument 3
Create an SQL Select statement to access a table 3
Create an HTML table to display the data using the JSTL forEach tags 3
Complete source code for JSTL SQL SELECT JSP 3
Data source configuration issues for JSTL 3
Author 3

Java Standard Tag Library concepts

Wouldn't it be great to rapidly script Web pages without having to learn a programming language such as RPG CGIDEV2, Basic, Java, etc? Imagine being able to query any data in your database, add, update and delete records! The Java Standard Tag Library ( JSTL ) lets developers use simple tags to script logic, access data, process XML documents and format data quickly in a Java Server Page (a Web page that uses Java tags) without the need to program in a traditional language! Here, I focus on quickly building a simple JSP to search an employee table using SQL to demonstrate how easily JSTL accesses data.

Where can you use the JSTL SQL access?

You can use JSTL for SQL access in any JSP on any Java application server (WebSphere, Tomcat, Geronimo and so on.) Sun, IBM and others note that JSTL is primarily targeted for low-volume application development ( prototypes, test cases, low-end Web sites) due to scalability constraints compared to other Java Web development options (using Java persistence frameworks like JDBC, SDO, Hibernate and so on. . From my testing so far, Web pages built with JSTL can work well IF you control paging for large results (details are beyond the scope of this article) and use server-based data sources offering the performance advantages of database connection pooling (versus dynamically described data sources in the JSP. )

Overview of the JSTL packages

The Java Standard Tag Library is delivered as two jar files ( jstl.jar and standard.jar. ) They are available directly from the Sun Web site and are also included with almost any J2EE application server today by default (WebSphere, Geronimo, Tomcat.) JSTL is divided into five logical packages or libraries shown in Figure XXXX. When you declare one or more JSTL libraries in a JSP, you provide a prefix to identify all the tags of that type to the JSP compiler. While you can use any prefix you want in the library declaration of the JSP, it's good practice to follow the prefix conventions that are commonly in use now to avoid confusion.

Figure XXXX – JSTL packages

JSTL tags support Expression Language variables

All of the JSTL tags support a start and an end tag combination. Some tags, optionally, don't require an end tag. Some of the tags are designed to encapsulate other tags or parts of the Web page. The documentation from Sun covers all this and more. The Query Tag example below shows the use of the sql:query tag. In addition to the attributes the query tag has to define it's behavior (a variable name for the result set and a name for the referenced server data source), it has a start tag (<sql:query .. >) and an end tag (</sql:query >) that enclose a block of other tags ( HTML or JSP ). The enclosed block has the text string for SQL SELECT statement to run as well as a runtime substitution parameter (marked by a ?) for a WHERE clause value in the SELECT statement.

Figure XXXX- a sample use of the JSTL SQL Query tag

<sql:query var="results" dataSource="jdbc/sample" >
select * from employee where lastname >= ? order by lastname
<sql:param value="${plastname}" />
</sql:query> xc

Notice the parameter tag ( <sql:param >) does not have a matched end tag. If a single tag is used, the end of the tag is marked by the /> symbol. Also, note the reference to the plastname variable is defined using Expression Language syntax for a variable. The variable (plastname) is enclosed in double quotes ("), has a $ sign as the first character and is enclosed by braces ( { .. } ). This is the syntax for the JSP expression language to reference variables on a JSP page. When the page is executed, the plastname variable value will be substituted for the ? in the SELECT statement WHERE clause. If multiple parameters are defined in the SQL statement using a ?, a corresponding sql:param tag needs to be defined for each. The value of each parameter is substituted in the order they are defined for the substitution parameters in the SQL statement.

JSTL tags for core and SQL packages

The table below is a simple listing of the current JSTL version 1.1 API for the core and SQL packages available from the online documentation at the Sun Web site. Tag names are shown with the common prefix for the tag group. Collectively, this set of tags covers many Web development application scenarios dramatically reducing the Java code you need to learn or program for simple Web applications.

bFigure xxxx: Core JSTL tags

Figure xxxx: SQL tags

Setup server pre-requisites first:

To build and run a Web application, I installed and configured: a database, WebSphere Community Edition (WCE: a no-charge version of the open-source Apache Geronimo J2EE application server.) and the Eclipse Web tools to build a sample JSP using JSTL. To setup the environment for the Web application, do the following:

1. Install a database with a JDBC driver
2. Install Eclipse WTP or the Eclipse Callisto release which includes WTP
3. Install Geronimo or WCE
4. Create a database pool in Geronimo
5. Install Geronimo Eclipse plugin

In my case, I run several databases (DB2 version 8.2, MySQL version 5.0, as well as DB2/400) so I didn't have to install one. Of course, installing WCE also automatically installs the embedded open-source Derby database that contains the WCE configuration files.xc

MySQL database pool in WCE

To use a MySQL database in WCE, I had to:

  • Install the MySQL JDBC driver as a common library using the wizard
  • Configure a MySQL datasource using the JDBC driver now available

    If you are using a different database, you'll need to do the same thing with that JDBC database driver jar file or zip file to create a data source in WCE.

    While your Web application defines a data source name in the web.xml file, for a Geronimo server (which WCE is), the data source name in the Web application is mapped to the deployment name assigned to the resource in Geronimo. For my data source name in the Web application ( jdbc/sample ), the geronimo-web.xml deployment file maps that reference to the database pool named: jdbc/mysql/sample.

    Figure xxxx: the database pool mapping for Web application deployment

    <naming:resource-ref>
    <naming:ref-name>jdbc/sample</naming:ref-name>
    <naming:resource-
    link>jdbc/mysql/sample</naming:resource-link>
    </naming:resource-ref>

    Build JSP database pages overview: JSTL

    It's very easy and productive to build Web pages using the JSTL libraries in any development toolset. I prefer the new Eclipse Web Tools Project offering excellent, dynamic context-sensitive "prompt and fill" support for using the JSTL tags (available from Eclipse ). With the Eclipse editors (also available in all the Rational tools from IBM: RAD version 6, WDSC version 6 etc), you get the following productivity features:

  • Browse the list of all available tags
  • Hover help on the detail definition for each tag
  • Prompt and fill support for each attribute for a tag

    After installing and testing the Eclipse Callisto RC1 integrated release that included the Web Tools Project (available at: Eclipse) and the Geronimo Eclipse plugin , to build and test a JSP that uses SQL database access, you need to:

    1. Create a database connection in Eclipse
    2. Create a web project designating a target server
    3. Create a server configuration for Geronimo
    4. Create a JSP using the database
    5. Deploy the application to Geronimo
    6. Test or debug the application on Geronimo

    Employee search by lastname SQL JSP example

    To show how WCE works using a JSP for basic data access, we'll use the Java Standard Tag Library ( JSTL ) to define our data access. Our application is very simple. The JSP:

    1. Provides a lastname input field prompt for the user to enter a starting lastname for the search
    2. Displays the data found in an HTML table from the SQL SELECT statement
    3. Provides a search button so the user can re-run the search with a new lastname

    Figure xxxx: the sample JSP for employees beginning with lastname of 'S'

    Create a JSP using the database

    To create a JSP using JSTL SQL tags to query a database table, I performed the following steps:

    1. Created a JSP using the JSP HTML template in Eclipse
    2. Selected the JSTL tag libraries to add to the page
    3. Created an SQL Select statement to access a table
    4. Created an HTML table to display the data using the JSTL forEach tags

    Create a JSP using the JSP HTML template

    In Eclipse, I selected the options: File > New > Other > Web > JSP to create the JSP file using an HTML template.

    Figure xxxx: the generated code for a Web JSP HTML file in Eclipse

    <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
    <title>List Employess</title>
    </head>
    <body>

    </body>
    </html>

    Select the tag libraries to add to the page

    I added the JSTL tag libraries I needed to the top of the JSP file. Note: I used the standard prefixes for the library tag types.

    Figure xxxx: JSTL tag libraries referenced in a JSP file

    <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/xml" prefix="sx"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>

    Create an input variable for the JSP SQL search argument

    First I added an input variable for lastname to the JSP so the user can enter a starting last name for the Employee search SQL query.

    <input name="lastname" type="TEXT" >

    Next, you should define the plastname request-scope variable using the c:set tag with a default value. Here, I used the letter G as the default starting value for my lastname search.

    <c:set var="plastname" scope="request" value="G" />
    <c:if test="${!empty param.lastname}">
    < c:set var="plastname" value="${param.lastname}" />
    </c:if>

    Then I test to see if the input field from the Web page form ( lastname ) is not empty ( a null value). If the user has entered a value, I set the plastname variable to the value entered by the user on the Web page, otherwise, the plastname value stays unchanged with the default of G.

    Create an SQL Select statement to access a table

    I created an SQL SELECT query in the JSP referencing my data source using the name in the Web application (jdbc/sample) and define a parameter for the search value passed in to the application. My SELECT statement searches an employee table by lastname given a starting lastname parameter value (defined earlier).

    <sql:query var="results" dataSource="jdbc/sample" >
    select * from employee where lastname >= ? order by lastname
    <sql:param value="${plastname}" />
    </sql:query>

    When the statement is encountered on the Web page, it executes. The result set returned is assigned to a variable: results. Results is not a standard java.sql.ResultSet interface type. It is actually a javax.servlet.jsp.jstl.sql.Result interface type. While it performs the same basic service as the ResultSet interface it isn't the same object so it unfortunately can't be easily used in the same way. This actually isn't an issue at all if you are ONLY using JSTL SQL tags for data access in a page. It COULD be an issue IF you are trying to mix both SQL tags and actual Java JDBC scripts in the same page. The only reason I experimented with that was to overcome a problem I had with my DB2 JDBC driver not properly binding to the generated JSTL sql:query tag code when I used parameters.

    Create an HTML table to display the data using the JSTL forEach tags

    I created an HTML table to display a header row of column headings from my SQL result set of data along with a row for each of the data rows found in the result set. My design for the table was both simple and generic. Using the simple value reference, I returned an unformatted value for each column found in a data row. Notice the use of the forEach tag three times. The first forEach tag creates the one header row with each columnName from the results columnNames property. The second forEach tag writes a data row for each row found in the results variable. The last forEach tag iterates over each column in the data row and lists it's unformatted value in a table cell. The expression ${value} accesses the value attribute of the current column element iterated by the last forEach tag for a given data row.

    Figure xxxx: building an HTML table with column headers and data rows

    <table>
    <tr>
    <c:forEach var="columnName" items="${results.columnNames}" >
    <th>
    <c:out value="${columnName}"></c:out>
    </th>
    </c:forEach>
    </tr>
    <c:forEach var="row" items="${results.rowsByIndex}" >
    <tr>
    <c:forEach var="value" items="${row}" >
    <td>
    ${value}
    </td>
    </c:forEach>
    </tr>
    </c:forEach>
    </table>

    To make the table more useful I should add two enhancements:

    1. Using the choose tag, display each type of value found in the columns of a data row with formatting appropriate to that data type ( eg, numbers, dates and so on).

    2. Add paging support to efficiently page the result set instead of returning all rows when the query runs.

    Because I didn't make the JSP more complex with paging actions ( buttons to page forward and back through the result set), my JSP will perform very badly on large queries.

    Complete source code for JSTL SQL SELECT JSP

    I've included the complete source code for the sample JSP SQL SELECT page. This makes it relatively simple to start out with a basic JSP HTML page in Eclipse and customize the code here to run your own SQL SELECT query and display the search results on the Web page.

    Figure xxxx: JSTL SQL SELECT JSP

    <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/xml" prefix="sx"% >
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
    <title>List Employess - MySQL</title>
    </head>
    <body>
    <form action="/qwx9622a/index.jsp">
    <c:set var="plastname" scope="request" value="G" />
    <c:if test="${!empty param.lastname}">
    <c:set var="plastname" value="${param.lastname}" />
    </c:if>
    <table border="0" width="100%" >
    <tr>
    <td>Employee List - MySQL ( qwx9622a )</td>
    <td>
    <a href="http://www.ebt-now.com" target="_blank" ><img alt=""
    src="./images/qw_apps_w4.jpg"></a>
    </td>
    </tr>
    <tr>
    <td>
    Enter last name to search for:
    </td>
    <td>
    <input name="lastname" type="TEXT" >
    <input type="submit" value="Search" onclick="submit">
    </td>
    <td>         </td>
    <td>         </td>
    <sql:query var="results" dataSource="jdbc/sample" >
    select * from employee where lastname >= ? order by lastname
    <sql:param value="${plastname}" />
    </sql:query>
    </tr>
    <tr>
    <table> <br>
    <tr>
    <c:forEach var="columnName" items="${results.columnNames}" >
    <th>
    <c:out value="${columnName}"></c:out>
    </th>
    </c:forEach>
    </tr>
    <c:forEach var="row" items="${results.rowsByIndex}" >
    <tr>
    <c:forEach var="value" items="${row}" >
    <td>
    ${value}
    </td>
    </c:forEach>
    </tr>
    </c:forEach>
    </table>
    </tr>
    </table>
    </form>
    </body>
    </html>

    Data source configuration issues for JSTL

    I ran the JSTL version 1.1 libraries that ship with the WebSphere Community Edition server, version 1.0.1.1.

    While DB2 UDB 8.2 supports JSTL, the specific build of 8.2 I had installed (with the exact service pack level) did not correctly bind using the Universal JDBC driver supplied in DB2 to the generated Java code from the sql:query tag using parameters. There really wasn't a way around this problem except to look for a set of service packs or fixes that addressed the problem or use a different data access mechanism other than JSTL, for example direct JDBC coding. Not finding service packs that would fix the problem, I did rewrite my JSP using embedded JDBC code with that specific version of DB2 and that worked correctly.

    MySQL, by comparison, was very simple to work with. I installed the MySQL driver using the Geronimo data source wizard easily. I referenced the MySQL datasource in the JSP page shown in this article and it worked the first time using the JSTL SQL tags.

    For iSeries access, generally I use the JDBC driver in the latest version of the Java Toolkit, included with all IBM iSeries tools for free. The same driver is also available as a download from the open-source project called JTOpen .

    ---------------------------------------

    Jim Mason is VP of Technical Services at ebt-now ebt-now, an iSeries Web enablement company, providing design, development, implementation and training services. Jim's creating a self-study course for RPG programmers that teaches "hands-on" rapid visual development for all types of iSeries Web applications without the need to become a Java expert. ebt-now hosts www.quickwebsupport.com, a free site for iSeries developers learning about rapid Web development. He's also leader of a virtual WebSphere user group for iSeries. You can reach Jim at jemason@ebt-now.com.

    This was first published in June 2006

  • There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.