Core Java| JSP| Servlets| XML| EJB| JEE5| Web Services| J2ME| Glossary| Questions?

Software Solutions and Services

  JDO Tutorials
  EAI Articles
  Struts Tutorials
  Java Tutorials
  Java Certification
  Java Applet

Questions

java
program concept behind inheritance in java
Java File
Hi Friend, Thank you for the support I got previously... Anyone please send me the Java Code for scanning a direct
password protected zip file creation exmpale coded neaded
am able to create a zip file and able to read the file but password protection need please help
java
any programs on combination generation pls...
deteminant
wap to find the determinant of any order matrix
java
i want to know the program for transversal sort. thanks
java
pl. let me know few progrograms on encapsulation. thanks
j2me jdbc connection
hello, whn i m importing sql package in mine midlet(tried for both javax.sql.* and java.sql.*;)..itz nt accepting it...
jvm
what is java virtual machine?
sort java
1. A statistics company wants to keep information of families. The information of a family is the family name, the numbe
All Questions...

Comments

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.

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>

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:query>

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:

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.....

Download Source Code

� View all related tutorials

Related Tags: c xml library file files stl orm ant form io user sed jstl tag parameter transform this js for add

Leave your comment:
Name:
Email:
URL:
Title:
Comments:
Enter Code:
	Note: Emails will not be visible or used in any way, and are not required. Please keep comments relevant. Any content deemed inappropriate or offensive may be edited and/or deleted. No HTML code is allowed. Line breaks will be converted automatically. URLs will be auto-linked. Please use BBCode to format your text.