1. 게시판 작성에 앞서 emp list를 출력하고, 사원 정보를 입력하여 DB에 insert 하시오.
<입력 화면>
<출력 화면>
<empView.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
|
<body>
<h1>사원 정보 입력</h1>
<hr/>
<form action="empInsert.jsp">
<table>
<tr>
<td>사원번호</td>
<td><input type="number" name="empno"/></td>
</tr>
<tr>
<td>사원이름</td>
<td><input type="text" name="ename"/></td>
</tr>
<tr>
<td>사원직급</td>
<td><input type="text" name="job"/></td>
</tr>
<tr>
<td>매니저</td>
<td>
<select name="mgr">
<c:forEach var="man" items="${empDao.manangerSelect()}">
<option value="${man.empno}"> (${man.empno})${man.ename}</option>
</c:forEach>
</select>
</td>
</tr>
<tr>
<td>입사일</td>
<td><input type="date" name="date"/></td>
</tr>
<tr>
<td>급여</td>
<td><input type="number" name="sal"/></td>
</tr>
<tr>
<td>커미션</td>
<td><input type="number" name="comm"/></td>
</tr>
<tr>
<td>부서</td>
<td>
<select name="deptno">
<c:forEach var="dept" items="${deptDao.deptSelect()}">
<option value="${dept.deptno}"> (${dept.deptno})${dept.dname}</option>
</c:forEach>
</select>
</td>
</tr>
<tr>
<td colspan="2" style="text-align:center">
<input type="submit" value="입력"/>
</td>
</tr>
</table>
</form>
</body>
|
cs |
<empinsert.jsp>
|
1
2
3
4
5
6
7
8
9
10
|
<jsp:useBean id="empDao" class="edu.bit.ex.dao.EmpDao"/>
<jsp:useBean id="emp" class="edu.bit.ex.dto.EmpVO"/>
<jsp:setProperty name="emp" property="*"/>
<body>
<c:set var="result" value="${empDao.insertEmp(emp)}" />
<c:if test="${result > 0 }">
<h1>정보입력이 정상적으로 처리 되었습니다.</h1>
<a href="empList.jsp">리스트</a>
</c:if>
</body>
|
cs |
<EmpVO.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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
public class EmpVO {
private int empno;
private String ename;
private String job;
private int mgr;
private Timestamp hiredate;
private String date;
private int sal;
private int comm;
private int deptno;
public EmpVO() {
}
public EmpVO(int empno, String ename, String job, int mgr, Timestamp hiredate, int sal,
int comm, int deptno, String dname, String loc) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public EmpVO(int empno, String ename) {
this.empno = empno;
this.ename = ename;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public Timestamp getHiredate() {
return hiredate;
}
public void setHiredate(Timestamp hiredate) {
this.hiredate = hiredate;
}
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
public int getComm() {
return comm;
}
public void setComm(int comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = 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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
public class EmpDao {
DataSource dataSource;
public EmpDao() {
try {
Context context = new InitialContext();
dataSource = (DataSource) context.lookup("java:comp/env/jdbc/oracle");
} catch (Exception e) {
e.printStackTrace();
}
}
public ArrayList<EmpVO> manangerSelect() {
ArrayList<EmpVO> dtos = new ArrayList<EmpVO>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 커넥션풀에 있는 커넥션 메소드 호출
connection = dataSource.getConnection();
String query = "select m.EMPNO,m.ename from emp e , emp m where e.mgr = m.empno group by m.ename ,m.EMPNO";
preparedStatement = connection.prepareStatement(query);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int empno = resultSet.getInt("empno");
String ename = resultSet.getString("ename");
EmpVO dto = new EmpVO(empno, ename);
dtos.add(dto);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(resultSet != null) resultSet.close();
if(preparedStatement != null) preparedStatement.close();
if(connection != null) connection.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
return dtos;
}
public int insertEmp(EmpVO emp) {
Connection connection = null;
PreparedStatement preparedStatement = null;
int result = -1;
try {
connection = dataSource.getConnection();
String query = "insert into emp values(?,?,?,?,?,?,?,?)";
preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1,emp.getEmpno() );
preparedStatement.setString(2, emp.getEname());
preparedStatement.setString (3, emp.getJob());
preparedStatement.setInt(4, emp.getMgr());
preparedStatement.setDate(5,Date.valueOf(emp.getDate()));
preparedStatement.setInt(6, emp.getSal());
preparedStatement.setInt(7, emp.getComm());
preparedStatement.setInt(8, emp.getDeptno());
result = preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(preparedStatement != null) preparedStatement.close();
if(connection != null) connection.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
return result;
}
public ArrayList<EmpListVO> empList() {
ArrayList<EmpListVO> dtos = new ArrayList<EmpListVO>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = dataSource.getConnection();
String query = "select e.empno, e.ename, e.job, p.ename as mgr, e.hiredate, " +
"e.sal, nvl(e.comm, 0) as comm, e.deptno, d.dname, d.loc " +
"from emp e, emp p, dept d " +
"where e.mgr = p.empno and e.deptno = d.deptno " +
"order by e.ename";
preparedStatement = connection.prepareStatement(query);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int empno = resultSet.getInt("empno");
String ename = resultSet.getString("ename");
String job = resultSet.getString("job");
String mgr = resultSet.getString("mgr");
Timestamp hiredate = resultSet.getTimestamp("hiredate");
int sal = resultSet.getInt("sal");
int comm = resultSet.getInt("comm");
int deptno = resultSet.getInt("deptno");
String dname = resultSet.getString("dname");
String loc = resultSet.getString("loc");
EmpListVO dto = new EmpListVO(empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc);
dtos.add(dto);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(resultSet != null) resultSet.close();
if(preparedStatement != null) preparedStatement.close();
if(connection != null) connection.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
return dtos;
}
}
|
cs |
<emplist.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
|
<%
request.setCharacterEncoding("utf-8");
%>
<jsp:useBean id="empDao" class="edu.bit.ex.dao.EmpDao"/>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>EMP 직원 리스트 출력</title>
<style>
table{width:100%;}
</style>
</head>
<body>
<h1>EMP 테이블 직원 목록</h1>
<table border="1">
<tr>
<th>사원번호</th> <!-- empno -->
<th>사원이름</th> <!-- ename -->
<th>직급(업무)</th> <!-- job -->
<th>상사(이름)</th> <!-- mgr -->
<th>입사일</th> <!-- hiredate -->
<th>급여</th> <!-- sal -->
<th>커미션</th> <!-- comm -->
<th>부서번호</th> <!-- deptno -->
<th>부서이름</th> <!-- dname -->
<th>부서위치</th> <!-- loc -->
<th>관리</th>
</tr>
<c:forEach var="empList" items="${empDao.empList()}">
<tr>
<td>${empList.empno }</td>
<td>${empList.ename }</td>
<td>${empList.job }</td>
<td>${empList.mgr }</td>
<td>${empList.hiredate }</td>
<td>${empList.sal }</td>
<td>${empList.comm }</td>
<td>${empList.deptno }</td>
<td>${empList.dname }</td>
<td>${empList.loc }</td>
</tr>
</c:forEach>
</table>
</body>
</html>
|
cs |
2. MVC 형태로 emp 리스트를 출력하시오.
<구현 화면>
<FrontController.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
|
@WebServlet("*.do")
public class FrontController extends HttpServlet {
private static final long serialVersionUID = 1L;
public FrontController() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println("doGet");
actionDo(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println("doPost");
actionDo(request, response);
}
private void actionDo(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println("actionDo");
request.setCharacterEncoding("EUC-KR");
String viewPage = null;
Command command = null;
String uri = request.getRequestURI();
String conPath = request.getContextPath();
String com = uri.substring(conPath.length());
if(com.equals("/list.do")) {
command = new ListCommand();
command.execute(request, response);
viewPage = "list.jsp";
}
RequestDispatcher dispatcher = request.getRequestDispatcher(viewPage);
dispatcher.forward(request, response);
}
}
|
cs |
<Command.java>
|
1
2
3
|
public interface Command {
abstract void execute(HttpServletRequest request, HttpServletResponse response);
}
|
cs |
<ListCommand.java>
|
1
2
3
4
5
6
7
8
|
public class ListCommand implements Command{
@Override
public void execute(HttpServletRequest request, HttpServletResponse response) {
Dao dao = new Dao();
ArrayList<Dto> dtos = dao.list();
request.setAttribute("list", dtos);
}
}
|
cs |
<Dao.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
|
public class Dao {
DataSource dataSource;
public Dao() {
try {
Context context = new InitialContext();
dataSource = (DataSource)context.lookup("java:comp/env/jdbc/oracle");
}catch(Exception e) {
e.printStackTrace();
}
}
public ArrayList<Dto> list(){
ArrayList<Dto> dtos = new ArrayList<Dto>();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = dataSource.getConnection();
String query = "select * from emp,dept where emp.deptno=dept.deptno";
ps = con.prepareStatement(query);
rs = ps.executeQuery();
while(rs.next()) {
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
String job = rs.getString("job");
String mgr = rs.getString("mgr");
String hiredate = rs.getString("hiredate");
int sal = rs.getInt("sal");
int comm = rs.getInt("comm");
int deptno = rs.getInt("deptno");
String dname = rs.getString("dname");
String loc = rs.getString("loc");
Dto dto = new Dto(empno, ename, job, mgr, hiredate, sal, comm, deptno, dname, loc);
dtos.add(dto);
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if(rs != null) rs.close();
if(ps != null) ps.close();
if(con != null) con.close();
}catch(Exception e2) {
e2.printStackTrace();
}
}
return dtos;
}
}
|
cs |
<Dto.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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
public class Dto {
private int empno;
private String ename;
private String job;
private String mgr;
private String hiredate;
private int sal;
private int comm;
private int deptno;
private String dname;
private String loc;
public Dto() {}
public Dto(int empno, String ename, String job, String mgr, String hiredate, int sal, int comm, int deptno,
String dname, String loc) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public String getMgr() {
return mgr;
}
public void setMgr(String mgr) {
this.mgr = mgr;
}
public String getHiredate() {
return hiredate;
}
public void setHiredate(String hiredate) {
this.hiredate = hiredate;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
public int getComm() {
return comm;
}
public void setComm(int comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
}
|
cs |
<list.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
|
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title></title>
<style>
table{
width: 1000px;
}
#title{
font-size: 1.2em;
text-align:center;
}
</style>
</head>
<body>
<h1>EMP 테이블 직원 목록</h1>
<table border="1">
<tr id="title">
<td>사원번호</td>
<td>사원이름</td>
<td>직급(업무)</td>
<td>상사(이름)</td>
<td>입사일</td>
<td>급여</td>
<td>커미션</td>
<td>부서번호</td>
<td>부서이름</td>
<td>부서위치</td>
<td>관리</td>
</tr>
<c:forEach items="${list}" var="dto">
<tr>
<td>${dto.empno}</td>
<td>${dto.ename}</td>
<td>${dto.job}</td>
<td>${dto.mgr}</td>
<td>${dto.hiredate}</td>
<td>${dto.sal}</td>
<td>${dto.comm}</td>
<td>${dto.deptno}</td>
<td>${dto.dname}</td>
<td>${dto.loc}</td>
<td><a href="write_view.do">수정</a></td>
</tr>
</c:forEach>
</table>
</body>
|
cs |
'bitcamp > JSP' 카테고리의 다른 글
| JSP_MVC_연습문제 (0) | 2021.02.12 |
|---|---|
| JSP_MVC패턴 (0) | 2021.02.12 |
| SQL문 (0) | 2021.02.12 |
| JSP_게시판 작성을 위한 DB 맛보기! (0) | 2021.02.11 |
| JSP_bean_연습문제 (0) | 2021.02.11 |
