* [selectDept.jsp] : 데이터베이스의 자료를 웹에 끌어와서 보여주기
|
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>selectDept.jsp</title>
<style type="text/css">
table, td, th {
border: 1px solid black;
border-collapse: collapse;
}
table {
text-align: center;
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<table>
<tr>
<th>부서번호</th>
<th>부서명</th>
<th>부서위치</th>
</tr>
<!-- 8가지 순서
1. 환경변수
2. 드라이버 로딩
3. connection
4. SQL문장
5. 문장 객체
6. 실행 ==> ResultSet
7. 레코드별 로직 처리
8. 자원 반납
-->
<%
/* 1. 환경변수 */
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn;
PreparedStatement pstmt;
ResultSet rs;
StringBuffer sb = new StringBuffer();
/* 2. 드라이버 로딩 */
Class.forName(driver);
/* 3. Connection */
conn = DriverManager.getConnection(url, user, password);
System.out.println("conn : " + conn);
/* 4. SQL 문장 */
sb.append("SELECT * FROM dept ");
// 5. 문장 객체
pstmt = conn.prepareStatement(sb.toString());
// 6. 실행 ==> ResultSet
rs = pstmt.executeQuery();
// 7. 레코드별 로직 처리
while (rs.next()) {
int deptno = rs.getInt("deptno");
String dname = rs.getString("dname");
String loc = rs.getString("loc");
%>
<tr>
<td><%= deptno %></td>
<td><%= dname %></td>
<td><%= loc %></td>
</tr>
<%
}
// 8. 자원 반납
if(rs!= null) rs.close();
if(pstmt!= null) pstmt.close();
if(conn!= null) conn.close();
%>
</table>
</body>
</html>
|
cs |
* [DeptVO.java] : 롬복 사용
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
package vo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DeptVO {
int deptno;
String dname;
String loc;
}
|
cs |
* [DeptDAO.java]
|
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import vo.DeptVO;
public class DeptDAO {
// 1.
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn ;
PreparedStatement pstmt ;
ResultSet rs ;
StringBuffer sb = new StringBuffer();
public DeptDAO() {
try {
// 2.
Class.forName(driver);
// 3. Connection
conn = DriverManager.getConnection(url, user, password);
System.out.println("conn : " + conn);
} catch (ClassNotFoundException e) {
System.out.println("드라이버 로딩 실패");
} catch (SQLException e) {
System.out.println("DB 연결 실패");
}
}
public ArrayList<DeptVO> selectAll() {
ArrayList<DeptVO> list = new ArrayList<DeptVO>();
// 4. SQL문장
sb.append("SELECT * FROM dept");
// 5. 문장 객체
try {
pstmt = conn.prepareStatement(sb.toString());
// 6. 실행 ==> ResultSet
rs = pstmt.executeQuery();
// 7. 레코드별 로직 처리
while (rs.next()) {
int deptno = rs.getInt("deptno");
String dname = rs.getString("dname");
String loc = rs.getString("loc");
DeptVO vo = new DeptVO(deptno, dname, loc);
list.add(vo);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 7. 레코드별 로직 처리
return list;
}
}
|
cs |
* [TestMain2.java] > DeptDAO 테스트
|
1
2
3
4
5
6
7
8
9
|
package dao;
public class TestMain2 {
public static void main(String[] args) {
DeptDAO dao = new DeptDAO();
}
}
|
cs |
* [selectDept2.jsp] : 데이터 가져와서 표로 보여주기
|
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
<%@page import="vo.DeptVO"%>
<%@page import="java.util.ArrayList"%>
<%@page import="dao.DeptDAO"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>selectDept2.jsp</title>
<style type="text/css">
table, td, th {
border: 1px solid black;
border-collapse: collapse;
}
table {
text-align: center;
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<table>
<tr>
<th>부서번호</th>
<th>부서명</th>
<th>부서위치</th>
</tr>
<%
DeptDAO dao = new DeptDAO();
ArrayList<DeptVO> list = dao.selectAll();
for (DeptVO vo : list) {
%>
<tr>
<td><%=vo.getDeptno()%></td>
<td><%=vo.getDname()%></td>
<td><%=vo.getLoc()%></td>
</tr>
<%
}
%>
</table>
</body>
</html>
|
cs |
'web' 카테고리의 다른 글
| [jsp] VO/DAO 실습(Emp/EmpDept) (0) | 2023.04.30 |
|---|---|
| [jsp] jspService의 지역 변수 / forward&redirect / 로그인, 회원가입 페이지 만들기 (1) | 2023.04.30 |
| [jsp] <로그인 페이지 만들기(db 연결!)> : member 테이블 사용 + lombok 다운로드 (0) | 2023.04.30 |
| [jsp] jsp (jsp의 스크립트 요소/button/text/for문/request&response) (0) | 2023.04.23 |
| [web] Servlet(GenericServlet / HTTPServlet) (0) | 2023.04.23 |