Open In App

JSTL SQL Tags

Improve
Improve
Like Article
Like
Save
Share
Report

JSTL is a collection of custom tags that provide common functionalities like flow control, database operations, etc. JSTL tags can be embedded in Java Server Pages just like other HTML tags. It is convenient for front-end developers to work with HTML-like tags for including logic in webpages rather than writing Java code in scriplets. To use JSTL tags, the following dependencies must be included in pom.xml in a maven project :

XML




<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>jstl</artifactId>
    <version>1.2</version>
</dependency>


JSTL  tags are grouped into five major categories :

  1. Core Tags
  2. Formatting Tags
  3. SQL Tags
  4. XML Tags
  5. Function Tags

This article focuses on JSTL SQL tags.

JSTL SQL Tags

SQL tags in JSTL are used to communicate with a relational database to perform various database operations. 

  • prefix : sql
  • uri  : http://java.sun.com/jsp/jstl/sql

Include the <taglib> directive in the JSP as follows 

<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>

Following tags are included in the JSTL SQL tag library :

  • transaction 
  • query
  • param
  • update
  • dateParam
  • setDataSource

sql:setDataSource

It is used to create a simple data source by taking url, username, password, and driver class name as parameters. It is used for further database operations. 

  • Tag handler class: org.apache.taglibs.standard.tag.rt.sql.SetDataSourceTag
  • Body content: empty
  • Attributes:
    • var: It is used to provide the variable which holds the data source generated. 
    • scope: It is used to define the scope. 
    • driver: It is used to provide the name of the driver class using which connection is to be established. 
    • url: It is used to provide the url associated with the database. 
    • user: It is used to provide the username on whose behalf the connection is to be made. 
    • password: It is used to provide the password for the specified user account. 

Example:

setDataSource.jsp:

HTML




<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
   pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<!DOCTYPE html>
<html>
   <head>
      <meta charset="ISO-8859-1">
      <title>SetDataSource</title>
   </head>
   <body>
      <sql:setDataSource var="ds" driver="com.mysql.cj.jdbc.Driver" 
                         url="jdbc:mysql://localhost:3306/gfg" 
                         user="root" password="root"/>
      ${ds}
   </body>
</html>


Output:

org.apache.taglibs.standard.tag.common.sql.DataSourceWrapper@f8a78d1

sql:query

It is used to execute the query given in the body or the attribute using the specified data source and store the results in the variable provided in its attribute. It is used to execute the SELECT statement.

  • Tag handler class: org.apache.taglibs.standard.tag.rt.sql.QueryTag
  • Body content: jsp
  • Attributes:
    • var: It is used to provide the variable for storing the results of the query execution. 
    • scope: It is used to set the scope for the variable. 
    • dataSource: It is used to provide the data source that is to be used to execute the query. 
    • sql: It is used to provide the sql query. 
    • startRow: It is used to specify the start index of the row to be included in the result. If not specified, the result contains rows starting from 0.
    • maxRows: It is used to specify the maximum number of rows to be included in the result. If not specified, no limit is imposed. 

Example:

select.jsp:

HTML




<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
   pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
   <head>
      <meta charset="ISO-8859-1">
      <title>Select</title>
   </head>
   <body>
      <sql:setDataSource var="ds" driver="com.mysql.cj.jdbc.Driver" 
                         url="jdbc:mysql://localhost:3306/gfg"
                         user="root" password="root"/>
      <sql:query dataSource="${ds}" var="rs">
         SELECT * FROM student_details;
      </sql:query>
      <c:forEach var="student" items="${rs.rows}">
         ${student.Student_Id} ${student.Student_Name}<br>
      </c:forEach>
   </body>
</html>


Output:

101 Priya
103 Sneha

student_details

sql:update

It is used to execute the update query given in the body or the attribute using the specified data source. 

  • Tag handler class: org.apache.taglibs.standard.tag.rt.sql.UpdateTag
  • Body content: jsp
  • Attributes:
    • var: It is used to provide the variable for storing the results of the update query execution. 
    • scope: It is used to set the scope for the variable. 
    • dataSource: It is used to provide the data source that is to be used to execute the query. 
    • sql: It is used to provide the sql query. 

Example:

update.jsp:

HTML




<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
   pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
   <head>
      <meta charset="ISO-8859-1">
      <title>Update</title>
   </head>
   <body>
      <sql:setDataSource var="ds" driver="com.mysql.cj.jdbc.Driver" 
                         url="jdbc:mysql://localhost:3306/gfg" 
                         user="root" password="root"/>
      <sql:update dataSource="${ds}" var="rs">
         UPDATE student_details SET student_name=? WHERE student_id=?;
         <sql:param>Diksha</sql:param>
         <sql:param>101</sql:param>
      </sql:update>
      <c:if test="${rs==1}">
         Updated Successfully!
      </c:if>
   </body>
</html>


Output:

Updated Successfully!

student_details

sql:param

It is nested inside the <sql:query> or <sql:update> tags to provide the query parameters.

  • Tag handler class: org.apache.taglibs.standard.tag.rt.sql.ParamTag
  • Body content: jsp
  • Attributes:
    • value: It is used to provide the value of the parameter. 

Example:

insert.jsp

HTML




<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
   pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
   <head>
      <meta charset="ISO-8859-1">
      <title>Insert</title>
   </head>
   <body>
      <sql:setDataSource var="ds" driver="com.mysql.cj.jdbc.Driver" 
                         url="jdbc:mysql://localhost:3306/gfg" 
                         user="root" password="root"/>
      <sql:update dataSource="${ds}" var="rs">
         INSERT INTO student_details VALUES(?,?);
         <sql:param>104</sql:param>
         <sql:param>Karan</sql:param>
      </sql:update>
      <c:if test="${rs==1}">
         Inserted Successfully!
      </c:if>
   </body>
</html>


Output:

Inserted Successfully!

student_details

sql:dateParam

It is used to provide the date, time, or timestamp parameters in the query. 

  • Tag handler class: org.apache.taglibs.standard.tag.rt.sql.DateParamTag
  • Body content: empty
  • Attributes:
    • value: It is used to provide the value for the parameter. 
    • type: It is used to specify the type of value(date, time, or timestamp). 

Example:

insert.jsp

HTML




<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
   pageEncoding="ISO-8859-1" import="java.util.Date" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
   <head>
      <meta charset="ISO-8859-1">
      <title>Insert</title>
   </head>
   <body>
      <sql:setDataSource var="ds" driver="com.mysql.cj.jdbc.Driver" 
                         url="jdbc:mysql://localhost:3306/gfg" 
                         user="root" password="root"/>
      <sql:update dataSource="${ds}" var="rs">
         INSERT INTO emp VALUES(?,?,?,?);
         <sql:param>102</sql:param>
         <sql:param>Sneha</sql:param>
         <sql:param>33000</sql:param>
         <sql:dateParam value="<%=new Date() %>"/>
      </sql:update>
      <c:if test="${rs==1}">
         Inserted Successfully!
      </c:if>
   </body>
</html>


Output:

Inserted Successfully!

emp

sql:transaction 

It is used to execute multiple queries as one transaction. All the queries are nested inside the transaction. 

  • Tag handler class: org.apache.taglibs.standard.tag.rt.sql.TransactionTag
  • Body content: jsp
  • Attributes:
    • dataSource: It is used to provide the data source using which queries are to be executed.  

Example:

transaction.jsp

HTML




<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
   pageEncoding="ISO-8859-1" import="java.util.Date" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
   <head>
      <meta charset="ISO-8859-1">
      <title>Transaction</title>
   </head>
   <body>
      <sql:setDataSource var="ds" driver="com.mysql.cj.jdbc.Driver" 
                         url="jdbc:mysql://localhost:3306/gfg" 
                         user="root" password="root"/>
      <sql:transaction dataSource="${ds}">
         <sql:update var="rs">
            INSERT INTO student_details VALUES(?,?);
            <sql:param>105</sql:param>
            <sql:param>Jyoti</sql:param>
         </sql:update>
         <sql:update var="rs">
            DELETE FROM student_details WHERE student_id=?;
            <sql:param>101</sql:param>
         </sql:update>
      </sql:transaction>
   </body>
</html>


Output:

student_details



Last Updated : 23 Feb, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads