Home Jstl Retrieve data from database by using sql tag of JSTL SQL library
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Retrieve data from database by using sql tag of JSTL SQL library

Advertisement
Here in this section we will see how retrieve data from database using sql query given by user.

Retrieve data from database by using sql tag of JSTL SQL library

     

Here in this section we will see how retrieve data from database using sql query given by user. To execute query first create a data source and then execute sql query for that data source.

<sql:setDataSource> : This tag is used to create data source for specified driver, user name and password of database, url etc. 
Attributes of the tag <sql:setDataSource> 
dataSource Optional attribute for explicitly specifying their connection factory, it may be a instance   javax.sql.DataSource interface.
driver  driver class name that is use to create connection.
url  url associated with given database.
user  It specifies user name of the database.
password  It specifies password of the database.
var  var is a variable that is used to store created data source.
Scope  Define the scope for declared variable like page or request or session or application.

After creating connection to database, now we are explaining how to execute sql query. To execute sql query we have used a sql tag <sql:query>, it executes sql and stores result in specified variable.

<sql:query> : This tag is used to create data source for specified driver, user name and password of database, url etc. 
Attributes of the tag <sql:setDataSource> 
dataSource  It specifies that for which data source you want to execute given query.
sql  It specifies sql query statement.
startRow  The returned Result object includes the rows starting at the specified index.
maxRows specifies max number of rows in the query result.
var  var is a variable that is used to store result after execution of given sql query.
Scope  Define the scope for declared variable like page or request or session or application.

Before run this code first create a database named 'mahendra' and table named 'employee_master' in same database by the sql query given below:

Query to create table:
CREATE TABLE `employee_master` (             
          `emp_id` int(10) NOT NULL auto_increment,
          `firstName` varchar(20) default NULL,      
          `lastname` varchar(20) default NULL,       
          `salary` double default NULL,              
          `tonus` double default NULL,               
            PRIMARY KEY  (`emp_id`)                    
         ) 

 

 

Structure of the table:


retrieve_database_SqlJstlTag.jsp

<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql"%>
<sql:setDataSource var="dataSource" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mahendra" user="root" password="root" />
<html>
<head>
<title>Query Example</title>
</head>
<body>
<sql:query var="users" dataSource="${dataSource}">
select* from employee_master;
</sql:query>
<table border=1>
<c:forEach var="row" items="${users.rows}">
<tr>
<td><c:out value="${row.emp_id}" /></td>
<td><c:out value="${row.firstName}" /></td>
<td><c:out value="${row.lastname}" /></td>
<td><c:out value="${row.salary}" /></td>
<td><c:out value="${row.bonus}" /></td>
</tr>
</c:forEach>
</table>
</body>
</html>

Steps to run this example :

1:  Download the zip file of code and unzip this file, you will get a folder named  'retrieve_database_SqlJstlTag'.
2:  Paste this folder in 'Apache Tomcat 6.0.16-->webapps' or generally in directory 'C:\apache-tomcat-6.0.16\webapps'.
3:  Start tomcat server by click on startup.bat file in 'C:\apache-tomcat-6.0.16\bin'.
4: Open browser and type url 'http://localhost:8080/retrieve_database_SqlJstlTag/retrieve_database_SqlJstlTag.jsp'
or click on this link.

Output of the program

Download Source Code

Advertisement

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: August 11, 2008

Ask Questions?    Discuss: Retrieve data from database by using sql tag of JSTL SQL library   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
tangara
September 8, 2011
JSTL

Hi, I followed the above example but it's not working. Hope someone can advise what happened. Many thanks. <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %> <%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%> <%@ taglib prefix="x" uri="http://java.sun.com/jsp/jstl/xml" %> <%@page language="java" import ="java.sql.*" %> <sql:setDataSource var="dataSource" driver="com.mysql.jdbc.Driver" <sql:query var="members" dataSource="${dataSource}" maxRow ="3"> Select *, GROUP_CONCAT(subject.subjectName SEPARATOR \", \") AS Subj from members LEFT JOIN subinter ON subinter.membersID = members.id LEFT JOIN subject ON subinter.subjectID = subject.id GROUP BY members.id"); </sql:query> <h5>Members Record</h5> <table border ="1" cellspacing="0" cellspacing="0" align="center" class="bordered"> <tr><td><b>Member No</b></td> <td><b>NRIC</b></td> <td><b>Name</b></td> <td><b>Email</b></td> <td><b>Address</b></td> <td><b>Subject</b></td> <c:forEach var="row" items="${tutors.rows}"> <tr> <td><c:out value="${row.ID}" /></td> <td><c:out value="${row.strMemberName}" /></td> <td><c:out value="${row.strNRICNO}" /></td> <td><c:out value="${row.strEmail}" /></td> <td><c:out value="${row.strAddress}" /></td> <td><c:out value="${row.Subj}" /></td> </c:forEach> </table> <% out.print("<td><a href=\"update.jsp?ID=" + ID + "\">Modify</a></td>");%> <% out.print("<td><a href=\"DeleteMember.jsp?ID=" + ID + "\">Delete</a></td>");%> <td> <%no++;%></td></tr> </table> tr> <td>Total Number of Members: <%=no - 1%> <br /> To return to login page : "<a href="login.jsp">Click here</a>"</td></tr> <br /> <tr> <td> To logout : "<a href="logout.jsp">Click here</a>"</td></tr> <br /> <br /> <td><a href="register.jsp">Click here to Register Member</a></td> </body> </html> The error I get is unterminated<sql:setDataSourcetag>
Shubham
July 7, 2012
Include options for delete and edit

Can anybody please provide the code for adding options for deleting and editing the records in respective rows such that if I click on delete or edit option then change is reflected in the table...............
DMCA.com