❮ Jstl Format Formatnumber Tag Jsp Debugging ❯

<sql:query> Tag

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

Syntax

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

Attributes

Attribute	Description	Required	Default Value
sql	The SQL command to be executed (returns a ResultSet object)	No	Body
dataSource	The database connection to be used (overrides the default)	No	Default database
maxRows	The maximum number of results to be stored in the variable	No	Infinite
startRow	The row number to start recording the results	No	0
var	The variable representing the database	No	Default setting
scope	The scope of the var attribute	No	Page

Example Program

First, you need to create an Employees table in the TEST database and add a few records. Follow these steps:

Step 1:

Open CMD and navigate to the installation directory:

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

Step 2:

Log into the database:

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 to execute an SQL SELECT statement 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>
</head>
<body>

&lt;sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost/TEST"
     user="root"  password="pass123"/>

&lt;sql:query dataSource="${snapshot}" var="result">
SELECT * from Employees;
</sql:query>

<table border="1" width="100%">
<tr>
<th>Employee ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
</tr>
&lt;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>

Execution result:

JSP Standard Tag Library ```

❮ Jstl Format Formatnumber Tag Jsp Debugging ❯