In this tutorial you will learn about the JSTL sql tag query.
JSTL sql query Tag
In this tutorial you will learn about the JSTL sql tag query.
<sql:query > tag is used for executing the SQL query written into its body or through the sql attribute.
Attributes of <sql:query> tag
- var : This is a required attribute that specifies the name of the variable or stores a value of the query result
- scope : This is an optioal attribute that specifies the scope of a variable.
- sql : This is an optional attribute specifies the statement of SQL query.
- dataSource : This is an optional attribute specifies the data source.
- startRow : This is an optional attribute that includes the specified starting index.
- maxRows : This is an optional attribute that specifies the maximum number of rows.
Example :
Here an example is being given below will demonstrate you about the use of the JSTL <sql:query> tag. In this example at first I have created a table named student(roll_no, address, course, name) where I have inserted records in each of the respective fields. Then created a JSP page where I have used the <sql:setDataSource> tag to create the data source, further I have used the <sql:query> to execute the SQL statements.
student table
CREATE TABLE `student` ( `roll_no` int(11) NOT NULL, `address` varchar(255) default NULL, `course` varchar(255) default NULL, `name` varchar(255) default NULL, PRIMARY KEY (`roll_no`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
JstlSqlQuery.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 PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>JSTL sql:query Example</title>
</head>
<body>
<sql:setDataSource var="ds" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://192.168.10.13/data"
user="root" password="root"/>
<sql:query var="rs" dataSource="${ds}">
select * from student
</sql:query>
<table border="1">
<tr>
<td><b>roll_no</b></td>
<td><b>address</b></td>
<td><b>course</b></td>
<td><b>name</b></td>
</tr>
<c:forEach var="rowValue" items="${rs.rows}">
<tr>
<td>${rowValue.roll_no}</td>
<td>${rowValue.address}</td>
<td>${rowValue.course}</td>
<td>${rowValue.name}</td>
</tr>
</c:forEach>
</table>
</body>
</html>
How to run this example
Here I am using an IDE Eclipse so I am giving the process of executing this example in perspective of Eclipse. Before executing this example you will have needed to add the following jar files :
- jstl.jar
- standard.jar
After adding of these jar files you may execute your program in the following ways :
- Select JstlSqlQuery.jsp file of your project in Project Explorer -> RightClick -> Run As -> Run On Server -> Choose your server -> Finish.
- On the Eclipse Editor go to your JstlSqlQuery.jsp -> RightClick -> Run As -> Run On Server -> Choose your server -> Finish.
- Go to Run button look at the toolbar in green color and click -> Choose your server -> Finish.
- A simplest way to execute the example in Eclipse is to use the CTRL+F11 key -> Run On Server -> Choose your server -> Finish
NOTE : In all of the above execution processes you may start the server first and stop the server each time after the execution if not, each time you will may prompted to a dialog box to Restart the server in Eclipse.
Output :
1. When you will execute the above JSP page you will get the output on your eclipse browser as follows :
[ 0 ] Comments