JSTL - SQL <sql:query> Tag


Advertisements

The <sql:query> tag executes an SQL SELECT statement and saves the result in a scoped variable.

Attribute

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

Attribute Description Required Default
sql SQL command to execute (should return a ResultSet) No Body
dataSource Database connection to use (overrides the default) No Default database
maxRows Maximum number of results to store in the variable No Unlimited
startRow Number of the row in the result at which to start recording No 0
var Name of the variable to represent the database No Set default
scope Scope of variable to expose the result from the database No Page

Example

To start with the basic concept, let us create an Employees table in the TEST database and create few records in that table as follows −

Follow these steps to create the Employees table −

Step 1

Open a Command Prompt and change to the installation directory as follows −

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

Step 2

Login to the database as follows

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

Step 3

Create the Employee table in the TEST database as follows − −

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

We will now create few records in Employee table as follows −

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>

Let us now create a JSP which will make use of <sql:query> to execute an SQL SELECT statement as follows −

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

Access the above JSP, which should display the following result −

+-------------+----------------+-----------------+-----------------+
|    Emp ID   |    First Name  |     Last Name   |       Age       |
+-------------+----------------+-----------------+-----------------+
|     100     |    Zara        |     Ali         |       18        |
|     101     |    Mahnaz      |     Fatma       |       25        |
|     102     |    Zaid        |     Khan        |       30        |
|     103     |    Sumit       |     Mittal      |       28        |
+-------------+----------------+-----------------+-----------------+
jsp_standard_tag_library.htm

Useful Video Courses


Video

JSP, Servlet, JSLT + Hibernate: A complete guide

108 Lectures 11 hours

Chaand Sheikh

Video

Full Stack Java developer - Java + JSP + Restful WS + Spring

Best Seller

517 Lectures 57 hours

Chaand Sheikh

Video

Servlets and JSP Tutorial For Beginners!

41 Lectures 4.5 hours

Karthikeya T

Video

JSP and Servlets - The Complete Course

42 Lectures 5.5 hours

TELCOMA Global

Video

JSP and Servlets Bootcamp: Web Applications for Beginners

15 Lectures 3 hours

TELCOMA Global

Video

JSP (Java Server Pages) Certification Training

44 Lectures 15 hours

Uplatz

Advertisements