Latest web development tutorials

<Sql: query> tag

JSP Standard Tag Library JSP Standard Tag Library

<Sql: query> tag is used to run SQL SELECT statement, there is scope to store the result in a variable.

Syntax 

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

Attributes

<Sql: query> tag has the following attributes:

Attributes description If necessary Defaults
sql SQL command to be executed (returns a ResultSet object) no Body
dataSource Used by the database connection (to override the default value) no Default database
maxRows The maximum number of results stored in the variable no gigantic
startRow The number of rows begin recording the results of no 0
var Representatives variable database no default setting
scope Scope var attribute no Page

Program Example

First, you need to build in a TEST database Employees table, and then add a few records to the table, the steps are as follows:

step 1:

Open the CMD, go to the directory of the installation directory: 

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

Step 2:

Login Database: 

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

Step 3:

TEST establish Employees table in the 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 a data record:

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 JSP files, use the <sql: query> tag to execute SQL SELECT statement: 

<%@ 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>

Results are as follows:

sql-source

JSP Standard Tag Library JSP Standard Tag Library