Home › A JSTL Tutorial by Examples › JSTL SQL query tags

JSTL SQL query tags 

Fang
Joined:
08/13/2009
Posts:
164

August 16, 2010 21:44:41    Last update: August 16, 2010 22:01:46
The tags

<sql:query>
Queries a database.

Syntax: 
<sql:query sql="sqlQuery"
var="varName" [scope="{page|request|session|application}"]
[dataSource="dataSource"]
[maxRows="maxRows"]
[startRow="startRow"]>
optional <sql:param> or <sql:dateParam> actions
</sql:query>

or, put the query within the element body: 
<sql:query var="varName"
[scope="{page|request|session|application}"]
[dataSource="dataSource"]
[maxRows="maxRows"]
[startRow="startRow"]>
query
optional <sql:param> or <sql:dateParam> actions
</sql:query>


Attributes:
NameDynamic?TypeDescription
sqltrueStringSQL query statement.
dataSourcetruejavax.sql.DataSource or StringData source associated with the database to query. A String value represents a relative path to a JNDI resource or the JDBC parameters for the DriverManager class.
maxRowstrueintThe maximum number of rows to be included in the query result. If not specified, or set to -1, no limit on the maximum number of rows is enforced.
startRowtrueintThe returned Result object includes the rows starting at the specified index. The first row of the original query result set is at index 0. If not specified, rows are included starting from the first row at index 0.
varfalseStringName of the exported scoped variable for the query result. The type of the scoped variable is javax.servlet.jsp.jstl.sql.Result.
scopefalseStringScope of var. Defaults to "page".


Test it
You need JBoss to perform the following test. Use the default configuration (%JBOSS_HOME%\bin\run). Make these additions to the expanded test application:
  1. Create a new Java class SQLQuery: 
    package jstl.demo.handler;

import java.io.IOException;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.jsp.jstl.core.Config;

import jstl.demo.DemoHandler;

public class SQLQuery implements DemoHandler {
    public void handleRequest(HttpServletRequest req,
			      HttpServletResponse resp)
		throws IOException, ServletException {

	// Use JBoss hypersonic datasource
	try {
	    Context ctx = new InitialContext();
	    DataSource ds = (DataSource) ctx.lookup("java:/DefaultDS");
	    Config.set(req, Config.SQL_DATA_SOURCE, ds);
	}
	catch (NamingException e) {
	    throw new ServletException(e);
	}

	// set maximum # of rows to return
	Config.set(req, Config.SQL_MAX_ROWS, new Integer(100));

	RequestDispatcher d = req.getRequestDispatcher("/sqlquery.jsp");
	d.forward(req, resp);
    }
}


  2. Create a new JSP (sqlquery.jsp) under webapp: 
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<html>
<head>
<title>SQL Query Tags</title>
</head>

<body>
<h2>Setup</h2>
<sql:update>
    drop table if exists measure;
    create table measure (
	id int,
	name varchar(20)
    );

    drop table if exists conversion;
    create table conversion (
	from_measure int,
	to_measure int,
	ratio decimal,
	last_updated timestamp default null
    );

    insert into measure (id, name) values (1, 'Inch');
    insert into measure (id, name) values (2, 'Feet');
    insert into measure (id, name) values (3, 'Yard');
    insert into measure (id, name) values (4, 'Centimeter');
    insert into measure (id, name) values (5, 'Meter');

    insert into conversion (from_measure, to_measure, ratio) values(1, 2, 0.0833);
    insert into conversion (from_measure, to_measure, ratio) values(1, 3, 0.0278);
    insert into conversion (from_measure, to_measure, ratio) values(1, 4, 2.540);
    insert into conversion (from_measure, to_measure, ratio) values(1, 5, 0.0254);
    insert into conversion (from_measure, to_measure, ratio) values(2, 1, 12);
    insert into conversion (from_measure, to_measure, ratio) values(2, 3, 0.3333);
    insert into conversion (from_measure, to_measure, ratio) values(2, 4, 30.48);
    insert into conversion (from_measure, to_measure, ratio) values(2, 5, 0.3048);
    insert into conversion (from_measure, to_measure, ratio) values(3, 1, 36);
    insert into conversion (from_measure, to_measure, ratio) values(3, 2, 3);
    insert into conversion (from_measure, to_measure, ratio) values(3, 4, 91.44);
    insert into conversion (from_measure, to_measure, ratio) values(3, 5, 0.9144);
    insert into conversion (from_measure, to_measure, ratio) values(4, 1, 0.3937);
    insert into conversion (from_measure, to_measure, ratio) values(4, 2, 0.0328);
    insert into conversion (from_measure, to_measure, ratio) values(4, 3, 0.0109);
    insert into conversion (from_measure, to_measure, ratio) values(4, 5, 0.01);
    insert into conversion (from_measure, to_measure, ratio) values(5, 1, 39.37);
    insert into conversion (from_measure, to_measure, ratio) values(5, 2, 3.2801);
    insert into conversion (from_measure, to_measure, ratio) values(5, 3, 1.0936);
    insert into conversion (from_measure, to_measure, ratio) values(5, 4, 100);
</sql:update>
done.

<h2>Query</h2>
<sql:query var="conversionRatio">
    SELECT c.ratio, m2.name
    FROM measure m1, conversion c, measure m2
    WHERE m1.id = c.from_measure
    AND c.to_measure = m2.id
    AND m1.name = ?
    <sql:param value="Meter"/>
</sql:query>

1 Meter is:<br>
<ul>
<c:forEach var="row" items="${conversionRatio.rows}">
    <li> ${row.ratio} ${row.name}.</li>
</c:forEach>
</ul>
</body>
</html>


  3. Compile and package the WAR with: mvn package

  4. Deploy the WAR to a JBoss server of your choice (for example, JBoss 5.1.0 GA).

  5. Test the page with this URL (JBoss 5.1.0 GA running on port 8080):
    http://localhost:8080/jstl-demo/demo/SQLQuery

    You may adjust the URL if your servlet container runs on a different port or the web app is bound to a different context root.

Share |
| Comment  | Tags