<sql:query> Tag

The <sql:query> tag is used to run SQL SELECT statements and store the results in a scoped variable.

Syntax Format

<sql:query
  var="<string>"
  scope="<string>"
  sql="<string>"
  dataSource="<string>"
  startRow="<string>"
  maxRows="<string>"/>

Attributes

The <sql:query> tag has the following attributes:

Attribute	Description	Required	Default Value
sql	The SQL command to execute (returns a ResultSet object)	no	Body
dataSource	The database connection to use (overrides the default)	no	Default database
maxRows	Maximum number of results stored in the variable	no	Infinite
startRow	The row number from which to start recording results	no	0
var	Variable representing the database	no	Default settings
scope	Scope of the var attribute	no	Page

Program Example

First, create an Employees table in the TEST database, then add a few records to the table. The specific steps are as follows:

Step 1:

Open CMD and change the directory to the installation directory:

C:\>
C:\>cd "Program Files\MySQL\bin"
C:\Program Files\MySQL\bin>

Step 2:

C:\Program Files\MySQL\bin>mysql -u root -p
Enter password: ********
mysql>

Step 3:

Create the Employees table in the TEST database:

mysql> use TEST;
mysql> create table Employees
    (
     id int not null,
     age int not null,
     first varchar (255),
     last varchar (255)
    );
Query OK, 0 rows affected (0.08 sec)
mysql>

Create Data Records:

Finally, create a few records in the Employees table:

mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
Query OK, 1 row affected (0.05 sec)
 
mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');
Query OK, 1 row affected (0.00 sec)
 
mysql>

Now, write a JSP file that uses the <sql:query> tag to execute SQL SELECT statements:

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ 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>JSTL sql:query Tag</title>
</head>
<body>
 
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost/TEST"
     user="root"  password="pass123"/>

<sql:query dataSource="${snapshot}" var="result">
SELECT * from Employees;
</sql:query>
 
<table border="1" width="100%">
<tr>
<th>Emp ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
</tr>
<c:forEach var="row" items="${result.rows}">
<tr>
<td><c:out value="${row.id}"/></td>
<td><c:out value="${row.first}"/></td>
<td><c:out value="${row.last}"/></td>
<td><c:out value="${row.age}"/></td>
</tr>
</c:forEach>
</table>

</body>
</html>

The running result is as follows:

JSP 标准标签库 JSP Standard Tag Library

Other Extensions

JSP Advanced Tutorial