JSTL SQL sql:query Tag

The <sql:query> tag is used to execute a query specified in sql attribute or in the body. It executes SQL statement and saves in the scoped variable. It executes sql query and stores result in specified variable.

Attributes of <sql: query>Tag

  • sql: It specifies SQL command that has to be executed.
  • var: It is used to specify variable name used to store result of query.
  • scope: It specifies scope of the variable.
  • datasource: It specify data source name or java.sql.DataSource object and also specifies which data source needed to execute given query.
  • maxRows: It specifies maximum number of rows that has to be stored in the variable.
  • startRows: It specifies the starting row number.

Example of <sql:query> Tag

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

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

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Sql:query Tag</title>
</head>
<body>

	<!--  Connect to database -->
	<sql:setDataSource var="ds" driver="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost/test" user="root" password="" />

	<!--  Execute the SQL statement -->

	<sql:query dataSource="${ds}" var="result">
      		select * from book_info;
     	 </sql:query>

    <!--  Creating the table -->
	<table border="1" width="300">
		<tr>
			<th>ISDN NO</th>
			<th>BOOK NAME</th>
			<th>BOOK AUTHOR</th>
		</tr>

	<!--  Displaying values in the table format -->
		<c:forEach var="row" items="${result.rows}">
			<tr>
				<td><c:out value="${row.ISDN}" /></td>
				<td><c:out value="${row.Book_Name}" /></td>
				<td><c:out value="${row.Author}" /></td>
			</tr>
		</c: forEach>
	</table>
</body>
</html>

Details of the Code

  • <sql: setDataSource var=”ds” driver=”com.mysql.jdbc.Driver” url=”jdbc: mysql: //localhost/test” user=”root” password=”” /> tag is used to connect to the data base and specify variable name for data source and driver name, url , user name and password of the database.
  • <sql:query dataSource=”${ds}” var=”result”> select * from book_info; tag is used to specify SQL statement to be run on the database and stores result of SQL statement in the variable.
  • <c:forEach var=”row” items=”${result.rows}”> tag is used to produce database table from SQL query and items attribute specifies collection of items to iterate in the loop.

Steps for Execution

  • Save the file as QueryExample.jsp in eclipse IDE.
  • Now select the jsp file, right click on mouse and select Run as -> Run on Server.

Output

After successful execution of the program we will get the following result:
sql_query

 

Previous Tutorial :  JSTL SQL Tags :: Next Tutorial : JSTL SQL sql:update Tag

Comments

comments

About Krishna Srinivasan

He is Founder and Chief Editor of JavaBeat. He has more than 8+ years of experience on developing Web applications. He writes about Spring, DOJO, JSF, Hibernate and many other emerging technologies in this blog.

Speak Your Mind

*