JSTL : sql 사용법

티스토리 뷰

프로그램

JSTL : sql 사용법

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

Attribute:

The <sql:query> tag has following attributes:

Attribute	Description	Required	Default
sql	SQL command to execute (should return a ResultSet)	No	Body
dataSource	Database connection to use (overrides the default)	No	Default database
maxRows	Maximum number of results to store in the variable	No	Unlimited
startRow	Number of the row in the result at which to start recording	No	0
var	Name of the variable to represent the database	No	Set default
scope	Scope of variable to expose the result from the database	No	Page

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:

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 ID	First Name	Last Name	Age
100	Zara	Ali	18
101	Mahnaz	Fatma	25
102	Zaid	Khan	30
103	Sumit	Mittal	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

공지사항

최근에 올라온 글

최근에 달린 댓글

Total

Today

Yesterday

링크

TAG more

« 2025/01 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

글 보관함

관성의 법칙

티스토리 뷰