«   2025/04   »
일 월 화 수 목 금 토
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
Tags more
Archives
Today
Total
관리 메뉴

개발 노트

9/13 html 페이지 이동, request.setAttribute, MVC2모델 본문

프로젝트 기반 JAVA 응용 SW개발 : 22.07.19~23.01.20/JDBC

9/13 html 페이지 이동, request.setAttribute, MVC2모델

hayoung.dev 2022. 9. 19. 09:42

dept.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
 <h2>부서코드 입력 </h2>
 <form action="ora02.jsp">
<!--  <form action="ora03.jsp">
 <form action="ora04.jsp">
 <form action="ora05.jsp"> -->
	코드 : <input type="number" name="deptno" required="required"><p>
	<input type="submit" value="확인 ">
 </form>
</body>
</html>

oracle을 콘솔창이 아닌 웹 창으로 보여줌.

ora02.jsp

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@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>Insert title here</title>
</head>
<body>
<%
	String deptno = request.getParameter("deptno");
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String sql = "select * from dept where deptno="+deptno;
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url,"scott","tiger");
	Statement stmt = conn.createStatement();
	//select이므로 resultSet으로 값을 받아옴.
	ResultSet rs = stmt.executeQuery(sql);
	//deptno가 fk이므로 while문으로 돌리지 않음
	if (rs.next()) {
		String dname = rs.getString("dname");  // rs.getString(2);
		String loc = rs.getString(3);          // 숫자는 조회되는 컬럼 순서
		out.println("부서코드 : " + deptno +"<p>");
		out.println("부서명 : " + dname +"<p>");
		out.println("근무지 : " + loc +"<p>");		
	} else out.println("그게 무슨 부서야 없는데");
	rs.close();	
	stmt.close();	
	conn.close();
%>
</body>
</html>

출력 결과

 

ora03.jsp

<%@page import="java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<%
	String deptno = request.getParameter("deptno");
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String sql = "select * from dept where deptno="+deptno;
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url,"scott","tiger");
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	if (rs.next()) {
		String dname = rs.getString("dname");  
		String loc = rs.getString(3);         
		out.println("부서코드 : " + deptno +"<p>");
		out.println("부서명 : " + dname +"<p>");
		out.println("근무지 : " + loc +"<p>");	
		
		//페이지 이동하기 전 파라미터를 넣는 작업을 해야 함.
		request.setAttribute("deptno", deptno);
		request.setAttribute("dname", dname);
		request.setAttribute("loc", loc);
	} else out.println("그게 무슨 부서야 없는데");
	rs.close();	
	stmt.close();	
	conn.close();
	
	//RequestDispatcher : 스크릿틀릿에서 페이지 이동. 알아야 함.
	RequestDispatcher rd = request.getRequestDispatcher("ora03Result.jsp");
	rd.forward(request, response);	
%>

</body>
</html>

ora03Result.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<h2>부서정보</h2>
부서코드 : ${deptno}<p>
부서명 : ${dname }<p>
근무지 : ${loc }<p>
</body>
</html>

*oracle 켜놔야 실행됨.

출력 결과

 

[MVC2 모델]

1. 브라우저에서 컨트롤러로 리퀘스트함

컨트롤러가 servlet임.

 

2. 컨트롤러에서 model의 service를 호출함. 

Model의 service는 회사 업무, 비즈니스 처리를 함.

 

3. service에서 DAO로 데이터 저장, DML 작업

DAO : Data Access Object

 

4. DB와 주거니받거니 함. 이것이 DTO

DTO : Data Transfer Object

 

5. 전체 작업이 끝나고 return되면 view를 선택함

view가 html, jsp임.

 

6. response 함

 

model, view, controller의 앞글자를 따서 mvc라고 하고 이것이 아키텍쳐임.

이 구조는 mvc2임.

 

 

ora04.jsp

<%@page import="och10.Dept"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<%
	String deptno = request.getParameter("deptno");
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String sql = "select * from dept where deptno="+deptno;
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url,"scott","tiger");
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	
	//DTO (자바빈과 비슷함.)
	Dept dept = new Dept();		//선언
	
	
	if (rs.next()) {
		String dname = rs.getString("dname");
		String loc = rs.getString(3);          
		out.println("부서코드 : " + deptno +"<p>");
		out.println("부서명 : " + dname +"<p>");
		out.println("근무지 : " + loc +"<p>");	
		
		//Dept setting
		//dept.setDeptno(Integer.parseInt(deptno)); 이 하단 코드와 같음.
		dept.setDeptno(rs.getInt(1));
		dept.setDname(dname);
		dept.setLoc(loc);
		
		//Dept로 저장
		request.setAttribute("dept", dept);
		
		//파라미터 넣음
		request.setAttribute("depno", deptno);
		request.setAttribute("dname", dname);
		request.setAttribute("loc", loc);
	} else out.println("그게 무슨 부서야 없는데");
	rs.close();	
	stmt.close();	
	conn.close();
	
	//RequestDispatcher : 스크릿틀릿에서 페이지 이동.
	/* RequestDispatcher rd = request.getRequestDispatcher("ora03Result.jsp"); */
	RequestDispatcher rd = request.getRequestDispatcher("ora04Result.jsp");
	rd.forward(request, response);	
%>
</body>
</html>

src/main/java > new class > Dept.java

package och10;

//DTO는 Data를 전달하는 객체 단위이다.(일반적으로 Table 단위이다.)
public class Dept {
	private int deptno;
	private String dname;
	private String loc;
	
	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;
	}
}

ora04Result.jsp

<%@page import="och10.Dept"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<!-- 스크릿틀릿은 모델1이므로 모델2에서는 쓰지 않는다. -->
<%
	Dept dept = (Dept)request.getAttribute("dept");
%>
<h2>부서정보 Expression 방법</h2>
부서코드 : <%=dept.getDeptno()%> <p>
부서명 : <%=dept.getDname() %> <p>
근무지 : <%=dept.getLoc() %> <p>

<!-- 모델2에서 EL표기법을 사용한다. -->
<h2>EL 표기법</h2>
부서코드 : ${dept.getDeptno() } <p>
부서명 : ${dept.getDname() } <p>
근무지 : ${dept.getLoc() } <p>
</body>
</html>

출력 결과

 

 

src/main/java > new class > Emp.java

package och10;

import java.util.Date;

public class Emp {
	private int empno;
	private String job;
	private Date hiredate;
	private int comm;
	private String ename;
	private int mgr;
	private int sal;
	private int deptno;
	
	
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public int getComm() {
		return comm;
	}
	public void setComm(int comm) {
		this.comm = comm;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public int getMgr() {
		return mgr;
	}
	public void setMgr(int mgr) {
		this.mgr = mgr;
	}
	public int getSal() {
		return sal;
	}
	public void setSal(int sal) {
		this.sal = sal;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	
}

ora05.jsp

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="och10.Emp"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
	pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<%
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String sql = "select empno, ename, job, sal from emp";
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url, "system", "password");
	ArrayList<Emp> al = new ArrayList<Emp>(); //많이 쓰이므로 알아두기.
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);

	//DTO
	if (rs.next()) {
		do {
			Emp emp = new Emp(); //emp 만듦
			emp.setEmpno(rs.getInt(1));
			emp.setEname(rs.getString(2));
			emp.setJob(rs.getString(3));
			emp.setSal(rs.getInt(4));
			//통째로 al list에 row단위로 넣음.
			al.add(emp);
		} while (rs.next());
		
		//파라미터 넣음
		request.setAttribute("al", al);
		
		rs.close();
		stmt.close();
		conn.close();
		//RequestDispatcher rd = request.getRequestDispatcher("ora05Result.jsp");
		RequestDispatcher rd = request.getRequestDispatcher("ora05Result2.jsp");
		rd.forward(request, response);
		
	} else {
		out.println("데이터가 없습니당");
		stmt.close();
		conn.close();
	}
	%>

</body>
</html>

ora05Result.jsp

<%@page import="och10.Emp"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
<style type="text/css">
	/* 마우스를 가져다대면 */
	tr:hover {
		background-color:red;
		 }
</style>
</head>
<body>
<h2>Model 1으로 쓸 때의 View</h2>
<%
	ArrayList<Emp> al = (ArrayList<Emp>)request.getAttribute("al");
%>
<table width="100%" bgcolor="yellow" border="1">
<tr><th>사번</th><th>이름</th><th>업무</th><th>급여</th></tr>
<%
	for(int i=0; i<al.size(); i++) {
		out.println("<tr><td>"+al.get(i).getEmpno()+"</td>");
		out.println("<td>"+al.get(i).getEname()+"</td>");
		out.println("<td>"+al.get(i).getJob()+"</td>");
		out.println("<td>"+al.get(i).getSal()+"</td></tr>");
	}

%>
</table>

</body>
</html>

출력 결과 : 마우스를 위에 올리면 빨간색이 된다.

ora05Result2.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>     
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<style type="text/css">	
	tr:HOVER { background-color: red; }
</style>
<body>
	<!-- MVC 모델2 개발법 -->
   <h2>EL표기법 + JSTL   Model 2  View</h2>
   <table width="100%" bgcolor="yellow" border="1">
		<tr bgcolor="pink">
			<th>사번</th><th>이름</th><th>업무</th><th>급여</th>
		</tr>
		<!-- al이 list이기 때문에 forEach문을 돌려준다. -->
		<c:forEach var="emp" items="${al }">
			<tr><td>${emp.empno }</td><td>${emp.ename }</td>
				<td>${emp.job }</td><td>${emp.sal }</td></tr>
		</c:forEach>
	</table>
</body>
</html>

출력 결과 : 마우스를 위에 올리면 빨간색이 된다.

 

 

oraInput.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<!-- 스크릿틀릿 + sql 문장(String.format)) -->
<form action="oraInput1.jsp" method="post">
<!-- <form action="oraInput2.jsp" method="post"> -->
	부서코드 : <input type="number" name="deptno" required="required"><p>
	부서명 : <input type="text" name="dname" required="required"><p>
	근무지 : <input type="text" name="loc" required="required"><p>
	<input type="submit" value="입력완료">
	<input type="reset" value="입력취소">
</body>
</html>

oraInput1.jsp

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="och10.Dept"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.sql.DriverManager"%>
<%@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>Insert title here</title>
</head>
<body>
<%
	request.setCharacterEncoding("utf-8");
	String deptno = request.getParameter("detpno");
	String dname = request.getParameter("dname");
	String loc = request.getParameter("loc");
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	//deptno는 숫자이기 때문에''이 없다.
	String sql = String.format("Insert into dept values(%s, '%s', '%s')", deptno, dname, loc);
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url, "scott", "tiger");
	Statement stmt = conn.createStatement();
	int result = stmt.executeUpdate(sql);
	if (result > 0) out.println("입력 성공");
	else out.println("입력 실패");
	stmt.close();
	conn.close();
	//DTO
	%>

</body>
</html>

입력값

출력 결과 : fk 위배

 

 

oraInput2.jsp

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<!--PreparedStatement  -->
<%
	request.setCharacterEncoding("utf-8");
	int deptno = Integer.parseInt(request.getParameter("detpno")); 
	String dname = request.getParameter("dname");
	String loc = request.getParameter("loc");
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String sql = "Insert into dept values(?, ?, ?)";
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url, "scott", "tiger");
	PreparedStatement pstmt = conn.prepareStatement(sql);
	pstmt.setInt(1, deptno);
	pstmt.setString(2, dname);
	pstmt.setString(3, loc);
	int result = pstmt.executeUpdate();
	if (result > 0) out.println("입력 성공");
	else out.println("입력 실패");
	pstmt.close();
	conn.close();
%>

</body>
</html>

출력결과 : oraInput1.jsp와 같음

 

 

 

oraUpdate.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h2>수정할 부서코드 입력</h2>
<form action="oraRetrieve.jsp">
	부서코드 : <input type="number" name="deptno" required="required"><p>
	<input type="submit" value="확인">
	
</form>

</body>
</html>

dbError.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" isErrorPage="true"%>
<%	response.setStatus(200);	%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<h2>DB Error</h2>
<%=exception.getMessage() %>
</body>
</html>

oraRetrieve.jsp

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.Statement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" errorPage="dbError.jsp"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
	String deptno = request.getParameter("deptno");
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String sql = "select * from dept where deptno=" + deptno;
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url, "scott", "tiger");
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	if (rs.next()) {
		request.setAttribute("deptno", deptno);
		request.setAttribute("dname", rs.getString(2));
		request.setAttribute("loc", rs.getString(3));
		rs.close();
		stmt.close();
		conn.close();
		RequestDispatcher rd = request.getRequestDispatcher("oraResult.jsp");
		rd.forward(request, response);
	}
	stmt.close();
	conn.close();
%>
<!-- rs.next()가 없으면 실행됨 -->
<script type = "text/javascript">
	alert("없는 부서야");
	location.href = "oraUpdate.html";
</script>
</body>
</html>

oraResult.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h2>부서 정부 수정</h2>
<form action = "oraUpdate.jsp">
	<!-- fk는 수정하지 않는다. 하지만 사용해야하기 때문에 type을 hidden으로 지정한다.-->
	부서코드 : ${deptno }<p>
	<input type="hidden" name="deptno" value="${deptno }">
	부서명 : <input type="text" name="dname" value="${dname }"><p>
	근무지 : <input type="text" name="loc" value="${loc }"><p>
	<input type="submit" value="수정완료">
</form>

</body>
</html>

oraUpdate.jsp

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h2>스크릿틀릿 + PreparedStatement 수정 </h2>
<%
	int deptno = Integer.parseInt(request.getParameter("deptno"));
	String dname = request.getParameter("dname");
	String loc = request.getParameter("loc");
	//물음표는 읽는 순서.
	String sql = "update dept set dname=?, loc=? where deptno=?";
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url, "scott", "tiger");
	PreparedStatement pstmt = conn.prepareStatement();
	pstmt.setString(1, dname);
	pstmt.setString(2, loc);
	pstmt.setInt(3, deptno);
	int result = pstmt.executeUpdate(sql);
	if (result > 0) out.println("수정 성공");
	else out.println("허걱");
	pstmt.close(); 
	conn.close();
%>
</body>
</html>

 

실행되는 순서

oraUpdate.html : oraRetrieve.jsp 실행하고 부서코드를 화면에서 입력받음.

oraRetrieve.jsp : select해서 oraResult.jsp로 보냄, 만약 error나면 dbError.jsp 실행

oraResult.jsp : oraUpdate.jsp 실행하고 근무명, 근무지 입력받음

oraUpdate.jsp : update하고 수정 성공 출력함

 

출력 결과

deptDel.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h2>부서코드 삭제</h2>
	<form action="oraDelete.jsp">
		코드 : <input type="number" name="deptno" required="required"> <p>
			<input type="submit" value="확인">
	</form>

</body>
</html>

oraDelete.jsp

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@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>Insert title here</title>
</head>
<body>
	<h2>스크릿틀릿 + Statement 삭제</h2>
	<%
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String deptno = request.getParameter("deptno");
	String sql = "delete from dept where deptno=" + deptno;
	
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url, "scott", "tiger");
	Statement stmt = conn.createStatement();
	int result = stmt.executeUpdate(sql);
	if (result > 0) out.println("삭제 성공")
	else out.println("삭제 실패");
	stmt.close();
	conn.close();
%>
</body>
</html>

출력 결과

 

 

mySql 폴더 생성

mysql로 할 때는 mysql을 켜놔야 한다.

lib 폴더에 mysql-connector-java-8.0.22.jar 드라이버 넣기

my01.jsp

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" errorPage="../dbError.jsp"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
	String driver = "com.mysql.cj.jdbc.Driver";
	String url = "jdbc:mysql://localhost:3306/scottdb?serverTimezone=UTC";
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url, "root", "mysql80");
	if (conn != null) out.println("mysql 연결 성공");
	else out.println("mysql 연결 실패");
	conn.close();
%>

</body>
</html>

출력 결과

 

 

division.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h2>부서코드 입력</h2>
<!-- <form action="my02.jsp"> -->
<!-- <form action="my03.jsp"> -->
<!-- <form action="my04.jsp"> -->
<form action="my05.jsp">
	코드 : <input type="number" name="dno" required="required"><p>
	<input type="submit" value="확인">
</form>

</body>
</html>

my02.jsp

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" errorPage="../dbError.jsp"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
	String dno = request.getParameter("dno");
	String driver = "com.mysql.cj.jdbc.Driver";
	String url = "jdbc:mysql://localhost:3306/scottdb?serverTimezone=UTC";
	String sql = "select * from division where dno="+dno;
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url, "root", "mysql80");
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	if (rs.next()) {
		String dname = rs.getString("dname");
		String phone = rs.getString(3);
		String position = rs.getString(4);
		out.println("부서코드 : " + dno + "<p>");
		out.println("부서명 : " + dname + "<p>");
		out.println("전화번호 : " + phone + "<p>");
		out.println("근무지 : " + position + "<p>");
	} else out.println("그게 무슨 부서야 없는데");
	rs.close();
	stmt.close();
	conn.close();
%>

</body>
</html>

출력 결과

 

 

my03.jsp

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@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>Insert title here</title>
</head>
<body>
<%
	String dno = request.getParameter("dno");
	String driver = "com.mysql.cj.jdbc.Driver";
	String url = "jdbc:mysql://localhost:3306/scottdb?serverTimezone=UTC";
	String sql = "select * from division where dno="+dno;
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url, "root", "mysql80");
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	if (rs.next()) {
		String dname = rs.getString("dname");
		String phone = rs.getString(3); 
		String position = rs.getString(4);
		request.setAttribute("dno", dno);
		request.setAttribute("dname", dname);
		request.setAttribute("phone", phone);
		request.setAttribute("position", position);
	} else out.println("그게 무슨 부서야 없는데");
	rs.close();
	stmt.close();
	conn.close();
	RequestDispatcher rd=request.getRequestDispatcher("my03Result.jsp");
	rd.forward(request, response);
%>
</body>
</html>

my03Result.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h2>부서정보</h2>
	부서코드 : ${dno }
	<p>부서명 : ${dname }
	<p>전화번호 : ${phone }
	<p>근무지 : ${position }
	<p>
</body>
</html>

출력 결과

Division.java

package och10;

public class Division {
	private int dno;
	private String dname;
	private String phone;
	private String position;
	
	public int getDno() {
		return dno;
	}
	public void setDno(int dno) {
		this.dno = dno;
	}
	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getPosition() {
		return position;
	}
	public void setPosition(String position) {
		this.position = position;
	}	

}

my04.jsp

<%@page import="och10.Division"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" errorPage="../dbError.jsp"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
	String dno = request.getParameter("dno");
	String driver = "com.mysql.cj.jdbc.Driver";
	String url = "jdbc:mysql://localhost:3306/scottdb?serverTimezone=UTC";
	String sql = "select * from division where dno="+dno;
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url, "root", "mysql80");
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	Division division = new Division();
	if (rs.next()) {
		division.setDno(rs.getInt(1));
		division.setDname(rs.getString(2));
		division.setPhone(rs.getString(3));
		division.setPosition(rs.getString(4));
		//division 하나만 저장
		request.setAttribute("division", division);
		rs.close();
		stmt.close();
		conn.close();
		RequestDispatcher rd = request.getRequestDispatcher("my04Result.jsp");
		rd.forward(request, response);
	} else {
		out.println("없는 부서야");
		stmt.close();
		conn.close();
	}
%>
</body>
</html>

my04Result.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h2>부서 정보</h2>
부서 코드 : ${division.dno }<p>
부서 명 : ${division.dname }<p>
전화번호 : ${division.phone }<p>
근무지 : ${division.position }<p>

</body>
</html>

출력 결과

반응형

'프로젝트 기반 JAVA 응용 SW개발 : 22.07.19~23.01.20 > JDBC' 카테고리의 다른 글

9/16 파일 업로드 폼, 필터, ready 메서드  (0) 2022.09.20
9/15 html cookie, git 사용법  (0) 2022.09.19
9/14 데이터베이스 커넥션 풀(Database Connection pool), 모듈화, exERD 설치 및 사용, 순공학, 역공학  (0) 2022.09.19
8/4 JDBC(Select, Insert, Update, procedure, function)  (0) 2022.08.04
8/3 JDBC, 데이터베이스, 스키마, DB연결, JDBC 프로그래밍 단계 및 Class  (0) 2022.08.04
'프로젝트 기반 JAVA 응용 SW개발 : 22.07.19~23.01.20/JDBC' Related Articles more