티스토리 뷰

프로그램

JSTL : sql 사용법

박대감 2014. 7. 4. 18:01








Attribute:

The <sql:query> tag has following attributes:

AttributeDescriptionRequiredDefault
sqlSQL command to execute (should return a ResultSet)NoBody
dataSourceDatabase connection to use (overrides the default)NoDefault database
maxRowsMaximum number of results to store in the variableNoUnlimited
startRowNumber of the row in the result at which to start recordingNo0
varName of the variable to represent the databaseNoSet default
scopeScope of variable to expose the result from the databaseNoPage

Example:

To start with basic concept, let us create a simple table Employees table in TEST database and create few records in that table as follows:

Step 1:

Open a Command Prompt and change to the installation directory as follows:

C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>

Step 2:

Login to database as follows

C:\Program Files\MySQL\bin>mysql -u root -p
Enter password: ********
mysql>

Step 3:

Create the table Employee in TEST database as follows:

mysql> use TEST;
mysql> create table Employees
    (
     id int not null,
     age int not null,
     first varchar (255),
     last varchar (255)
    );
Query OK, 0 rows affected (0.08 sec)
mysql>

Create Data Records

Finally you create few records in Employee table as follows:

mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
Query OK, 1 row affected (0.05 sec)
 
mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');
Query OK, 1 row affected (0.00 sec)
 
mysql>

Now let us write a JSP which will make use of <sql:query> to execute a SQL SELECT statement as follows:

<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>

<html>
<head>
<title>JSTL sql:query Tag</title>
</head>
<body>
 
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost/TEST"
     user="root"  password="pass123"/>

<sql:query dataSource="${snapshot}" var="result">
SELECT * from Employees;
</sql:query>
 
<table border="1" width="100%">
<tr>
<th>Emp ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
</tr>
<c:forEach var="row" items="${result.rows}">
<tr>
<td><c:out value="${row.id}"/></td>
<td><c:out value="${row.first}"/></td>
<td><c:out value="${row.last}"/></td>
<td><c:out value="${row.age}"/></td>
</tr>
</c:forEach>
</table>

</body>
</html>

Now try to access above JSP, which should display the following result:

Emp IDFirst NameLast NameAge
100ZaraAli18
101MahnazFatma25
102ZaidKhan30
103SumitMittal

28







저작자표시 비영리 변경금지

'프로그램' 카테고리의 다른 글

소프트웨어 공학: 구조적 방법론과 객체 지향 방법론의 차이  (0) 2014.12.16
JSTL 정수형 설정  (0) 2014.07.11
자바스크립트 CSS display block? none?  (0) 2014.04.01
MSSQL SELECT문을 이용한 UPDATE  (1) 2014.03.27
자바스크립트의 defer, async  (0) 2014.03.27
댓글