Execute database query by using sql tag of JSTL SQL library
Here in this section we are going to create application that execute sql query given by user using JSTL SQL Library. 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>
|
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:query>
|
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: |
executeQuery_JstlSqlTag.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"
scope="session" />
<html>
<head>
<title>General Query</title>
</head>
<body>
Please enter a query:
<br />
<form method="post"><textarea name="cmd" cols="40" rows="5"></textarea>
<br />
<input type="submit" /></form>
</body>
<c:if test="${pageContext.request.method=='POST'}">
<c:choose>
<c:when test="${param.cmd!=null}">
<c:set var="str" value="${param.cmd}" />
</c:when>
<c:otherwise>
<c:set var="str" value="select * from tableName" />
</c:otherwise>
</c:choose>
<font size="4" color="green">Query : </FONT>
<u><c:out value="${str}" /></u>
<c:catch var="e">
<sql:query var="users" dataSource="${dataSource}" sql="${param.cmd}" />
<table border="1" size="100%">
<c:forEach var="row" items="${users.rows}">
<tr>
<c:forEach var="col" items="${row}">
<td><c:out value="${col.value}" /></td>
</c:forEach>
</tr>
</c:forEach>
</table>
</c:catch>
<c:if test="${e!=null}">
<h3>Error</h3>
<c:out value="${e}" />
</c:if>
</c:if>
</html>
Steps to run this example :
1: Download the zip file of code and unzip this
file, you will get a folder named 'executeQuery_JstlSqlTag'.
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/executeQuery_JstlSqlTag/executeQuery_JstlSqlTag.jsp'
or click on this link.
Output of the program
When user enter sql query in the text area and click on submit query button, response will be.....
