JSP SQL JSTL

JSP SQL JSTL

  • The JSP Standard Tag Library (JSTL) adds essential features to JSP that enable JSP programming without the need for embedded Java code.
  • It is a collection of standard JSP tags that perform several common tasks.
  • This frees us from having to develop custom tags for these tasks, or from using a mix of tags from several organizations to do our work.
  • The SQL JSTL contains tags for interacting with relational databases. This can be for instance MySQL, PostgreSQL, Oracle or Microsoft SQL server.
  • The SQL JSTL tags are designed for light weight web application concerning database access.
  • Any serious database access should be done with more flexible tool for database access such as JDBC.
  • Before using the SQL JSTL, the following directive needs to be added to the JSP page:
  • <%@ taglib prefix="sql" uri=”http://java.sun.com/jsp/jstl/sql" %>

Here is a summary of all SQL JSTL tags

  • Tag Description
    dateParam Sets a parameter in a SQL statement to a specified java.util.Date value.
    param Sets a parameter in a SQL statement to a specified value.
    query Executes a SQL query.
    setDataSource Specifies a data source for the database connection.
    transaction Provides one connection to all nested database actions so that they can be executed as a transaction.
    update Executes a SQL update.

    You will find an example that includes several SQL JSTL tags here.

The following is a detail presentation of each SQL JSTL tag

  • dateParam

    The <sql:dateParam> is a body tag to a query or update tag. It is used to pass java.util.Date parameters to a SQL statement.
    The syntax is as follows:
    <sql:dateParam value="date type value"
    [type="date|time|timestamp"] />
    Attribute(s):
    Attribute Required Description
    value Yes The parameter value for the SQL statement. This can be a date, time, or timestamp.
    type No(default=timestamp) The type of parameter. This can be date, time, or timestamp.
    Example:
    <sql:query sql="select * from employee where join_date > ?">
    <sql:dateParam value="${cutoff_date}" type="date"/>
    </sql:query>
  • param

    The <sql:param>is a body tag to a query or update tag. It is used to pass parameters to a SQL statement.
    The syntax is as follows:
    <sql:param [value="parameter value"]>
    JSP body 
    </sql:param>
    <c:when> tag attribute(s):
    Attribute Required Description
    value No The parameter value for the SQL statement.
    Example:
    <sql:query sql="select * from bookstore  where price > ?">
       <sql:param value="${lowPrice}"/>
    </sql:query>
  • query

    The <sql:query> tag executes an SQL query statement. It’s possible to pass strings of SQL to the tag either through body content or by using sql attribute.
    The syntax is as follows:
    <sql:query var="variable name"
      [scope="page|request|session|application"]
      [sql="SQL query statement"]
      [startRow="starting row"]
      [maxRows="maximum number of rows"]
      [dataSource="DataSource"]>
      JSP body (Instead of using the sql attribute, the SQL query statement can be entered here)
    </sql:query>
    
    Attribute(s):
    Attribute Required Description
    var Yes This specifies the variable that contains the result of the SQL query statement.
    scope No (default=page) Scope of the variable specified in the var attribute. This can be page, request, session, or application.
    sql No The SQL query statement to execute.
    startRow No (default=0) The starting row for the returned results.
    maxRows No The maximum number of rows to return. The default is no maximum limit.
    dataSource No The dataSource attribute is used to reference a DataSource that was configured by using the </sql:setDataSource> action. If not specified, the default JSTL DataSource is used.
    Example:
     <sql:query var="sqlresult" sql="select * from bookstore  where price < ?"
         dataSource="jdbc:mysql://localhost:3306/customerdb, com.mysql.jdbc.Driver, root, root">
       <sql:param value="${fromPrice}" />
     </sql:query>

    The result of the query (here sqlresult) is of type javax.servlet.jsp.jstl.sql.Result. From this you can retrieve data with core JSTL.

    Result information to retrieve:
    Information Description
    rows Returns the result of the query as an array of SortedMap objects.
    rowsByIndex Returns the result of the query as an array of arrays.
    columnNames Returns the names of the columns in the result as an String array.
    rowCount Returns the number of rows in the cached ResultSet.
    limitedByMaxRows Returns true if the query was limited by a maximum row setting.

    An example of how to use this:
    <c:forEach var = "row" items = "${sqlresult.rows}">
      <tr>
         <td> <c:out value="${row.fieldName1}"/></td>
         <td> <c:out value="${row.fieldName2}"/></td>
         <td> <c:out value="${row.fieldName3}"/></td>
         <td> <c:out value="${row.fieldName4}"/></td>
       </tr>
    </c:forEach>
    
  • setDataSource

    The <sql:setDataSource> tag provides a DataSource for connecting to a database used by all action in the sql tag library.
    The syntax is as follows:
    <sql:setDataSource [var="variable name"]
      [scope="page|request|session|application"]
      [driver="JDBC driver"]
      [url="JDBC URL for database"]
      [user="Username to connect to the database"]
      [password="Password to connect to the database"]
      [dataSource="DataSource"]/>
    Attribute(s):
    Attribute Required Description
    var No This specifies the variable that contains the specified DataSource.
    scope No (default=page) Scope of the variable specified in the var attribute. This can be page, request, session, or application.
    driver No JDBC driver class name.
    url No JDBC URL for database.
    user No Username to connect to the database.
    password No Password to connect to the database.
    dataSource No The DataSource for the database to be accessed.
    Example:
    <sql:setDataSource 
      var="dataSource" 
      driver="org.acme.sql.driver"
      url="jdbc:msql://localhost/tempDB" 
      user="Dan" 
      password="pwd"/>
  • transaction

    The <sql:transaction> tag will have nested sql tags and cause them to be executed as a transaction.
    The syntax is as follows:
    <sql:transaction [dataSource="DataSource"]
         [isolation="read_committed|read_uncommitted|repeatable_read|serializable"] >
      JSP body 
    </sql:transaction>
    Attribute(s):
    Attribute Required Description
    dataSource No The DataSource for the database to be accessed.
    isolation No Isolation level for the transaction. This can be read_committed, read_uncommitted, repeatable_read, or serializable. This is an optional attribute and defaults to the DataSource isolation level.
    Example:
    <sql:transaction>
      <sql:update sql="update bookstore set price = price+? where isbn_no = ?">
        <sql:param value="${price_increase}"/>
        <sql:param value="${id}"/>
      </sql:update>
      <sql:update sql="update bookstore set price = price-? where isbn_no = ?">
        <sql:param value="${price_reduction}"/>
        <sql:param value="${id}"/>
      </sql:update>
    </sql:transaction>
  • update

    The <sql:update> executes an SQL update statement specified either via an attribute or embedded in the body of the tag. An update tag can have nested param tags to pass parameters to the SQL statement.
    The syntax is as follows:
    <sql:update [var="variable name"]
      [scope="page|request|session|application"]
      [sql="SQL update statement"]
      [dataSource="DataSource"]>
      JSP body 
    </sql:update>
    Attribute(s):
    Attribute Required Description
    var No This specifies the variable that contains the result of the SQL update statement
    scope No (default=page) Scope of the variable specified in the var attribute. This can be page, request, session, or application.
    sql No The SQL update statement to execute.
    dataSource No The DataSource for the database to be accessed.
    Example:
    <sql:update>
      UPDATE orders SET deliver_date = ?
      WHERE order_id = ?
      <sql:param value="${param.deliver_date}" />
      <sql:param value="${param.order_id}" />
    </sql:update>

Example of using SQL JSTL element.

In the example we use Netbeans IDE and Glassfish Server.

You can download this example here (needed tools can be found in the right menu on this page).

If you like to participate in the review of this example you must first create a Web project in Netbeans (the project name is SQLJSTL).

In this example, we will add two files, header.jsp and footer.jsp.

  • It is customary to place all include files, which are used several times in the WEB-INF folder. Some would also like the include files should have the extension .jspf, but this is not a requirement.
    Here is the file we want to include at the top of all pages:
    <h1>JSTL SQL demo</h1>
    <hr>

    For those who participate in the review: create a JSP file in Netbeans and replace generated code for the JSP with that shown above (the JSP file name is Header.jsp and folder should be WEB-INF).

    Here is the file we want to include at the bottom of all pages:
    <hr>
    Status on <%= (new java.util.Date()).toString() %>

    For those who participate in the review: create a JSP file in Netbeans and replace generated code for the JSP with that shown above (the JSP file name is Footer.jsp and folder should be WEB-INF).

We need a JSP file(s) to demonstrate the JSTL SQL tags.

  • Here is the JSP file:
    <%@ page contentType="text/html" pageEncoding="UTF-8"  import="java.util.*" %>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
    <%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
    <%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
    <!DOCTYPE html>
    <html>
      <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSTL SQL demo</title>
        <style>
          .gradientdown {
            background:  #e0e6ff;
            background: -webkit-linear-gradient(top, #e0e6ff 0%,#eeeeee 100%); /* Chrome10+,Safari5.1+ */
            background: -o-linear-gradient(#e0e6ff, #eeeeee); /* For Opera 11.1 to 12.0 */
            background: -moz-linear-gradient(top, #e0e6ff, #eeeeee); /* For Firefox 3.6 to 15 */
            background: linear-gradient(top, #e0e6ff 0%,#eeeeee 100%); /* W3C Standard syntax (must be last) */
          }
          table tr.data:nth-of-type(odd) { 
            background: #eeeeee; 
          }
          td {
            vertical-align: top;
          }
          h1 {
            color: blue;
            font-size: 22px;
            margin:  16px 0px 0px 0px;
          }      
          h2 {
            color: blue;
            font-size: 18px;
            margin:  16px 0px 0px 0px;
          }
          h3 {
            color: green;
            font-size: 16px;
            margin: 0px 0px 0px 0px;
          }
        </style>
      </head>
      <body>
        <sql:setDataSource var="customerdb"
                           url="jdbc:mysql://localhost:3306/customerdb"
                           driver="com.mysql.jdbc.Driver"
                           user="root"
                           password="root"/>
        <div style="width:560px; margin-left: 40px;  padding: 10px;">
          <c:import url="/WEB-INF/header.jsp" />
          <h2><sql:setDataSource>, <sql:query> and <sql:dateParam> Demo</h2>
          <c:set var="fromDate" value="<%= new GregorianCalendar(2007, 6, 16).getTime()%>"/>
          <h3>Orders after <fmt:formatDate pattern="dd-MM-yyyy" value="${fromDate}" /></h3>
          <sql:query var="sqlresult" sql="select * from orders  where deliver_date > ?"  dataSource="${customerdb}" >
            <sql:dateParam value="${fromDate}" />
          </sql:query>
          <table  width="100%">
            <tr class="gradientdown">
              <th>order_number</th>
              <th>order_date</th>
              <th>deliver_date</th>
              <th>order_description</th>
            </tr>
            <c:forEach var = "row" items = "${sqlresult.rows}">
              <tr class="data">
                <td> <c:out value = "${row.order_number}"/></td>
                <td> <fmt:formatDate pattern="dd-MM-yyyy" value="${row.order_date}" /></td>
                <td> <fmt:formatDate pattern="dd-MM-yyyy" value="${row.deliver_date}" /></td>
                <td> <c:out value = "${row.order_description}"/></td>
              </tr>
            </c:forEach>
          </table>
          <h2><sql:setDataSource>, <sql:query> and <sql:param> Demo</h2>
          <c:set var="fromPrice" value="35"/>
          <h3>Books more expensive than ${fromPrice}</h3>
          <sql:query var="sqlresult" sql="select * from bookstore  where price > ?"
                     dataSource="jdbc:mysql://localhost:3306/customerdb, com.mysql.jdbc.Driver, root, root">
            <sql:param value="${fromPrice}" />
          </sql:query>
          <table width="100%">
            <tr class="gradientdown">
              <th>ISBN_NO</th>
              <th>Short_desc</th>
              <th>Author</th>
              <th>Price</th>
            </tr>
            <c:forEach var = "row" items = "${sqlresult.rows}">
              <tr  class="data">
                <td> <c:out value = "${row.ISBN_NO}"/></td>
                <td> <c:out value="${row.Short_desc}" /></td>
                <td> <c:out value="${row.Author}" /></td>
                <td> <c:out value = "${row.Price}"/></td>
              </tr>
            </c:forEach>
          </table>      
           <h2><sql:setDataSource>, <sql:query>, <sql:param> , <sql:update> and  <sql:transaction> Demo</h2>
          <sql:transaction dataSource="${customerdb}">
            <c:set var="price_change" value="-5"/>
            <c:set var="isbn_no" value="0471777781"/>
            <sql:update sql="update bookstore set price = price*(100+?)/100 where isbn_no = ?" >
              <sql:param value="${price_change}"/>
              <sql:param value="${isbn_no}"/>
            </sql:update>
            <c:set var="price_change" value="+5"/>
            <c:set var="isbn_no" value="0596009747"/>
            <sql:update sql="update bookstore set price = price*(100+?)/100 where isbn_no = ?">
              <sql:param value="${price_change}"/>
              <sql:param value="${isbn_no}"/>
            </sql:update>
          </sql:transaction>
          <h3>Books after price change</h3>
          <sql:query var="sqlresult" sql="select * from bookstore"
                     dataSource="${customerdb}">
          </sql:query>
          <table width="100%">
            <tr class="gradientdown">
              <th>ISBN_NO</th>
              <th>Short_desc</th>
              <th>Author</th>
              <th>Price</th>
            </tr>
            <c:forEach var = "row" items = "${sqlresult.rows}">
              <tr  class="data">
                <td> <c:out value = "${row.ISBN_NO}"/></td>
                <td> <c:out value="${row.Short_desc}" /></td>
                <td> <c:out value="${row.Author}" /></td>
                <td> <c:out value = "${row.Price}"/></td>
              </tr>
            </c:forEach>
          </table>      
          <c:import url="/WEB-INF/footer.jsp" />
        </div>
      </body>
    </html><%@ page contentType="text/html" pageEncoding="UTF-8"  import="java.util.*" %>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
    <%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
    <%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
    <!DOCTYPE html>
    <html>
      <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSTL SQL demo</title>
        <style>
          .gradientdown {
            background:  #e0e6ff;
            background: -webkit-linear-gradient(top, #e0e6ff 0%,#eeeeee 100%); /* Chrome10+,Safari5.1+ */
            background: -o-linear-gradient(#e0e6ff, #eeeeee); /* For Opera 11.1 to 12.0 */
            background: -moz-linear-gradient(top, #e0e6ff, #eeeeee); /* For Firefox 3.6 to 15 */
            background: linear-gradient(top, #e0e6ff 0%,#eeeeee 100%); /* W3C Standard syntax (must be last) */
          }
          table tr.data:nth-of-type(odd) { 
            background: #eeeeee; 
          }
          td {
            vertical-align: top;
          }
          h1 {
            color: blue;
            font-size: 22px;
            margin:  16px 0px 0px 0px;
          }      
          h2 {
            color: blue;
            font-size: 18px;
            margin:  16px 0px 0px 0px;
          }
          h3 {
            color: green;
            font-size: 16px;
            margin: 0px 0px 0px 0px;
          }
        </style>
      </head>
      <body>
        <sql:setDataSource var="customerdb"
                           url="jdbc:mysql://localhost:3306/customerdb"
                           driver="com.mysql.jdbc.Driver"
                           user="root"
                           password="root"/>
        <div style="width:560px; margin-left: 40px;  padding: 10px;">
          <c:import url="/WEB-INF/header.jsp" />
          <h2><sql:setDataSource>, <sql:query> and <sql:dateParam> Demo</h2>
          <c:set var="fromDate" value="<%= new GregorianCalendar(2007, 6, 16).getTime()%>"/>
          <h3>Orders after <fmt:formatDate pattern="dd-MM-yyyy" value="${fromDate}" /></h3>
          <sql:query var="sqlresult" sql="select * from orders  where deliver_date > ?"  dataSource="${customerdb}" >
            <sql:dateParam value="${fromDate}" />
          </sql:query>
          <table  width="100%">
            <tr class="gradientdown">
              <th>order_number</th>
              <th>order_date</th>
              <th>deliver_date</th>
              <th>order_description</th>
            </tr>
            <c:forEach var = "row" items = "${sqlresult.rows}">
              <tr class="data">
                <td> <c:out value = "${row.order_number}"/></td>
                <td> <fmt:formatDate pattern="dd-MM-yyyy" value="${row.order_date}" /></td>
                <td> <fmt:formatDate pattern="dd-MM-yyyy" value="${row.deliver_date}" /></td>
                <td> <c:out value = "${row.order_description}"/></td>
              </tr>
            </c:forEach>
          </table>
          <h2><sql:setDataSource>, <sql:query> and <sql:param> Demo</h2>
          <c:set var="fromPrice" value="35"/>
          <h3>Books more expensive than ${fromPrice}</h3>
          <sql:query var="sqlresult" sql="select * from bookstore  where price > ?"
                     dataSource="jdbc:mysql://localhost:3306/customerdb, com.mysql.jdbc.Driver, root, root">
            <sql:param value="${fromPrice}" />
          </sql:query>
          <table width="100%">
            <tr class="gradientdown">
              <th>ISBN_NO</th>
              <th>Short_desc</th>
              <th>Author</th>
              <th>Price</th>
            </tr>
            <c:forEach var = "row" items = "${sqlresult.rows}">
              <tr  class="data">
                <td> <c:out value = "${row.ISBN_NO}"/></td>
                <td> <c:out value="${row.Short_desc}" /></td>
                <td> <c:out value="${row.Author}" /></td>
                <td> <c:out value = "${row.Price}"/></td>
              </tr>
            </c:forEach>
          </table>      
           <h2><sql:setDataSource>, <sql:query>, <sql:param> , <sql:update> and  <sql:transaction> Demo</h2>
          <sql:transaction dataSource="${customerdb}">
            <c:set var="price_change" value="-5"/>
            <c:set var="isbn_no" value="0471777781"/>
            <sql:update sql="update bookstore set price = price*(100+?)/100 where isbn_no = ?" >
              <sql:param value="${price_change}"/>
              <sql:param value="${isbn_no}"/>
            </sql:update>
            <c:set var="price_change" value="+5"/>
            <c:set var="isbn_no" value="0596009747"/>
            <sql:update sql="update bookstore set price = price*(100+?)/100 where isbn_no = ?">
              <sql:param value="${price_change}"/>
              <sql:param value="${isbn_no}"/>
            </sql:update>
          </sql:transaction>
          <h3>Books after price change</h3>
          <sql:query var="sqlresult" sql="select * from bookstore"
                     dataSource="${customerdb}">
          </sql:query>
          <table width="100%">
            <tr class="gradientdown">
              <th>ISBN_NO</th>
              <th>Short_desc</th>
              <th>Author</th>
              <th>Price</th>
            </tr>
            <c:forEach var = "row" items = "${sqlresult.rows}">
              <tr  class="data">
                <td> <c:out value = "${row.ISBN_NO}"/></td>
                <td> <c:out value="${row.Short_desc}" /></td>
                <td> <c:out value="${row.Author}" /></td>
                <td> <c:out value = "${row.Price}"/></td>
              </tr>
            </c:forEach>
          </table>      
          <c:import url="/WEB-INF/footer.jsp" />
        </div>
      </body>
    </html>

    For those who participate in the review: create a JSP file in Netbeans and replace generated code for the JSP with that shown above (the JSP file name is index).

Creating Deployment descriptor.

  • To run this JSP you have to deploy it to a web-server or a Application server. To deploy means to install the JSP with some instruction to a such server.
  • The instructions are mainly defined to be deployment descriptors. The standard part of the deployment descriptor should be in an XML-file with the name web.xml.

    You may need to create a Deployment descriptor file, web.xml in Netbeans.

  • The contents of the web.xml file should look like this:
    <?xml version="1.0" encoding="UTF-8"?>
    <web-app version="3.1" xmlns="http://xmlns.jcp.org/xml/ns/javaee" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee 
     http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd">
      <servlet>
        <servlet-name>SQLJSP</servlet-name>
        <jsp-file>/index.jsp</jsp-file>
      </servlet>
      <servlet-mapping>
        <servlet-name>SQLJSP</servlet-name>
        <url-pattern>/SQLJSP</url-pattern>
      </servlet-mapping>
      <session-config>
        <session-timeout>
          30
        </session-timeout>
      </session-config>
      <welcome-file-list>
        <welcome-file>SQLJSP</welcome-file>
      </welcome-file-list>
    </web-app>
  • This file starts with the normal xml tag for a XML file and the root tag for the deployment descriptor is web-app. Every ting inside the last tag is to tell the server about our application, which in this case is a JSP file.
  • With a servlet tag we give the JSP file a servlet name, which is used in the servlet-mapping tag to specify a url for the JSP file.
  • In this way we can have many urls for the same JSP file.
  • If no session-timeout (the server ends the service of the application after this time) is given a standard timeout for the server is used as timeout for the application.
  • The welcome-file tag specifies the startup for our application, which in this case and our application is the welcome file with url SQLJSP. Reorganize the welcome-file-list to what is shown above.

Creating Web-server Deployment descriptor.

  • The context-root (in example /SQLJSTL) for the application will in most cases be specified by a server vendor deployment descriptor.

    For those who participate in the review: create a Glassfish deployment descriptor in Netbeans.

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE sun-web-app PUBLIC "-//Sun Microsystems, 
    Inc.//DTD GlassFish Application Server 3.0 Servlet 3.0//EN" 
    "http://www.sun.com/software/appserver/dtds/sun-web-app_3_0-0.dtd">
    <sun-web-app error-url="">
      <context-root>/SQLJSTL</context-root>
      <class-loader delegate="true"/>
      <jsp-config>
        <property name="keepgenerated" value="true">
          <description>
            Keep a copy of the generated servlet class' java code.
          </description>
        </property>
      </jsp-config>
    </sun-web-app>

Run the application.

  • In the installation procedures for needed tools (that can be found in the right menu on this page) you will find an application that creates the database you need for this example. This DB-creation must be done before you continue.
  • For those who participate in the review: right click the Web-project and select Run.
  • This results in that the application will be deployed to the server and started.
  • The browser will display:
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.