JSP Database Connection JSP Standard Tag Library (JSTL)

<sql:query> Tag

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

Syntax format

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

Attribute

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

Attribute	Description	Is it necessary	Default value
Sql	The SQL command to be executed (returns a ResultSet object)	No	Body
DataSource	The database connection used (overriding the default value)	No	Default database
MaxRows	The maximum number of results stored in the variable	No	Infinite
StartRow	The number of rows starting from the record	No	0
Var	Represents a database variable	No	Default settings
Scope	The scope of the var attribute	No	Page

Program example

Firstly, you need to create an Employees table in the TEST database, and then add several records to the table. The specific operation steps are as follows:

Step1:

Open CMD, change the directory to the installation directory:

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

Step2:

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

Step3:

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 several 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 to execute SQL SELECT statements using the <sql:query> tag:

<%@ 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>/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 Standard Tag Library