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 :
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
|
JSTL tags are grouped into five major categories :
- Core Tags
- Formatting Tags
- SQL Tags
- XML Tags
- 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:
<%@ 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" 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:
<%@ 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" 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:
<%@ 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" 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
<%@ 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" 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
<%@ 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" 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
<%@ 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" 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