# jsp는 jsp서버에서 실행되고, 자바스크립트는 웹 서버에서 실행됨...
* 사원테이블에서 사원의 정보를 가져와 웹 문서로 출력 <selectEmp>
- [EmpVO.java]
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
package vo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class EmpVO {
int empno;
String ename;
String job;
int mgr;
String hiredate;
int sal;
int comm;
int deptno;
}
|
cs |
- [EmpDAO.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
65
66
67
68
69
70
71
|
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.EmpVO;
public class EmpDAO {
// 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 EmpDAO() {
// 2.
try {
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<EmpVO> selectAll() {
ArrayList<EmpVO> list = new ArrayList<EmpVO>();
// 4. SQL문장
sb.append("SELECT * FROM emp");
// 5. 문장 객체
try {
pstmt = conn.prepareStatement(sb.toString());
// 6. 실행
rs = pstmt.executeQuery();
// 7. 레코드별 로직 처리
while(rs.next()) {
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
String job = rs.getString("job");
int mgr = rs.getInt("mgr");
String hiredate = rs.getString("hiredate");
int sal = rs.getInt("sal");
int comm = rs.getInt("comm");
int deptno = rs.getInt("deptno");
EmpVO vo = new EmpVO(empno, ename, job, mgr, hiredate, sal, comm, deptno);
list.add(vo);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 7. 레코드별 로직 처리
return list;
}
}// EmpDAO end
|
cs |
- [selectEmp.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
|
<%@page import="vo.EmpVO"%>
<%@page import="java.util.ArrayList"%>
<%@page import="dao.EmpDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>selectEmp.jsp</title>
<style type="text/css">
table, td, th{
border: 1px solid gray;
border-collapse: collapse;
}
table {
text-align: center;
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<table>
<tr>
<th>EMPNO</th>
<th>ENAME</th>
<th>JOB</th>
<th>MGR</th>
<th>HIREDATE</th>
<th>SAL</th>
<th>COMM</th>
<th>DEPTNO</th>
</tr>
<%
EmpDAO dao = new EmpDAO();
ArrayList<EmpVO> list = dao.selectAll();
for (EmpVO vo : list) {
%>
<tr>
<td><%=vo.getEmpno() %></td>
<td><%=vo.getEname() %></td>
<td><%=vo.getJob() %></td>
<td><%=vo.getMgr() %></td>
<td><%=vo.getHiredate() %></td>
<td><%=vo.getSal() %></td>
<td><%=vo.getComm() %></td>
<td><%=vo.getDeptno() %></td>
</tr>
<%
}
%>
</table>
</body>
</html>
|
cs |
* 사원테이블과 부서 테이블에서 정보를 가져와 웹 문서로 출력 <selectEmpDept>
- [EmpDeptVO.java]
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
package vo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class EmpDeptVO {
int empno;
String ename;
int sal;
int deptno;
String dname;
String loc;
}
|
cs |
- [EmpDeptDAO.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.EmpDeptVO;
public class EmpDeptDAO {
// 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 EmpDeptDAO() {
try {
Class.forName(driver);
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<EmpDeptVO> selectAll() {
ArrayList<EmpDeptVO> list = new ArrayList<EmpDeptVO>();
sb.append("SELECT e.empno, e.ename, e.sal, d.deptno, d.dname, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno");
try {
pstmt = conn.prepareStatement(sb.toString());
rs = pstmt.executeQuery();
while(rs.next()) {
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
int sal = rs.getInt("sal");
int deptno = rs.getInt("deptno");
String dname = rs.getString("dname");
String loc = rs.getString("loc");
EmpDeptVO vo = new EmpDeptVO(empno, ename, sal, deptno, dname, loc);
list.add(vo);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
|
cs |
- [selectEmpDept.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
|
<%@page import="java.util.ArrayList"%>
<%@page import="vo.EmpDeptVO"%>
<%@page import="dao.EmpDeptDAO"%>
<%@page import="dao.DeptDAO"%>
<%@page import="dao.EmpDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>selectEmpDept.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>EMPNO</th>
<th>ENAME</th>
<th>SAL</th>
<th>DEPTNO</th>
<th>DNAME</th>
<th>LOC</th>
</tr>
<%
EmpDeptDAO dao = new EmpDeptDAO();
ArrayList<EmpDeptVO> list = dao.selectAll();
for (EmpDeptVO vo : list) {
%>
<tr>
<td><%=vo.getEmpno()%></td>
<td><%=vo.getEname()%></td>
<td><%=vo.getSal()%></td>
<td><%=vo.getDeptno()%></td>
<td><%=vo.getDname()%></td>
<td><%=vo.getLoc()%></td>
</tr>
<%
}
%>
</table>
</body>
</html>
|
cs |
'web' 카테고리의 다른 글
| [jsp] cookie 처리(생성, 읽기, 삭제) + 자바스크립트로! (0) | 2023.04.30 |
|---|---|
| [jsp] jsp 파일 불러오기 - include & action tag (0) | 2023.04.30 |
| [jsp] jspService의 지역 변수 / forward&redirect / 로그인, 회원가입 페이지 만들기 (1) | 2023.04.30 |
| [JSP] 데이터베이스 파일(DEPT) 가져와서 출력<VO/DAO> (0) | 2023.04.30 |
| [jsp] <로그인 페이지 만들기(db 연결!)> : member 테이블 사용 + lombok 다운로드 (0) | 2023.04.30 |