본문 바로가기
JSP/Java Script

2024_07_17_수

by 알케니브 2024. 7. 17.

오늘의 코딩순서

(폴더: och10_oracle)

  • och05  + ochResult2.jsp (현장 HW)
  • oraDeptinput.html + oraDeptInput1.jsp (현장 HW) + oraDeptInput2.jsp (현장 HW)
  • oraDeptUpdate.html + oraDeptRetrieve.jsp (현장 HW) + oraDeptIn.jsp (현장 HW) + oraUpdate.jsp (현장 HW)
  • deptDel.html + oraDelete.jsp 

(폴더: och10_mySql) >> lib 폴더에 mysql 자카르타 파일 4개 넣음

  • my01.jsp
  • division.html + my02.jsp (현장 HW) + my03.jsp (현장 HW) + my03Result.jsp (현장 HW)
  • Division.Class (현장 HW) + division.html + my04.jsp (현장 HW) + my04Result.jsp (현장 HW)
  • (전부 현장 HW) Professor.class + my05.jsp + my05Result.jsp + my05Result2.jsp 
  • myUpdate.html + myDivisionRetrieve.jsp + myDivisionInput.jsp + myUpdate.jsp

 

오늘의 코딩 포인트

(폴더: och10_oracle)

  • och05 (멀티 row 테스트) 
<%@page import="och10.Emp"%>
<%@page import="java.util.ArrayList"%>
<%@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 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";	//Oracle Emp 출력
	System.out.println("sql->"+sql);
	
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url,"scott","tiger");
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	
	//1. Collection ArrayList 생성
	ArrayList<Emp> al = new ArrayList<Emp>();	/* Emp 자동완성으로 삽입 */
	
// 이 밑의 각 row가 ArrayList에 append(추가)된다
//	7369	SMITH	CLERK	1000
//	7499	ALLEN	SALESMAN	1600
//	7521	WARD	SALESMAN	1250
//	7566	JONES	MANAGER	2975
//	7654	MARTIN	SALESMAN	1250
//	7698	BLAKE	MANAGER	2850
//	7782	CLARK	MANAGER	2450
//	7788	SCOTT	ANALYST	3000
//	7839	KING	PRESIDENT	5500
//	7844	TURNER	SALESMAN	1500
//	7876	ADAMS	CLERK	1100
//	7900	JAMES	CLERK	950
//	7902	FORD	ANALYST	3000
//	7934	MILLER	CLERK	1300
//	1601	홍미숙		3500
//	2001	황보슬	position2	3500
//	3000	유지원	SALESMAN	3850
//	3100	황정후	SALESMAN	3500
//	3200	홍길동2	SALESMAN	
//	1501	홍길동		
	if	(rs.next())	{
		do	{
			Emp emp = new Emp();
			emp.setEmpno(rs.getInt(1));	//1234: 위의 String sql의 순서값
			emp.setEname(rs.getString(2));
			emp.setJob(rs.getString(3));
			emp.setSal(rs.getInt(4));
			al.add(emp);
		}while(rs.next());
		request.setAttribute("al", al);
		rs.close();
		stmt.close();
		conn.close();	//여기까지 ArrayList Setting
		
		//RequestDispatcher rd = request.getRequestDispatcher("ora05Result.jsp");
		//setting된 ArrayList가 여기에 들어감
		RequestDispatcher rd = request.getRequestDispatcher("ora05Result2.jsp");
		rd.forward(request, response);
	} else	{
		out.println("데이터가 없습니당");
		stmt.close();
		conn.close();
	}
%>
</body>
</html>
  • ochResult2.jsp (현장 HW)
    Tip)
    • MVC Model 2⭐⭐⭐⭐⭐
<%@ 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>
<style type="text/css">
	tr:hover	{
	background-color: red;
	}
</style>
</head>
<body>
<h2>MVC Model 2 View</h2>
<!-- 현장HW. EL표기법 + JSTL  -->
	<table width="100%" bgcolor="yellow" border="1">
		<tr bgcolor="pink">
			<th>사번</th><th>이름</th><th>업무</th><th>급여</th>
		</tr>
		<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>
			
</body>
</html>

  • oraDeptinput.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form action="oraDeptInput1.jsp" method="post">
		부서코드 :	<input type="number"	name="deptno"	required="required"><p>
		부서명 :	<input type="text"		name="deptno"	required="required"><p>
		근무지 :	<input type="text"		name="loc"		required="required"><p>
		<input type="submit" value="입력완료">
		<input type="submit" value="입력취소">
	
	</form>
</body>
</html>
  • oraDeptInput1.jsp (현장 HW)
    Tip)
    • Insert절 형식 1: "Insert into 테이블명 values (%s, '%s', '%s')", 열 이름1, 열 이름2, 열 이름3);
    • executeUpdate: SQL의 INSERT, UPDATE, DELETE문을 실행할 때 사용됨
      이 메서드는 데이터베이스의 내용을 변경하는 SQL문을 실행하고, 영향받은 행의 개수를 int로 반환함
<%@page import="och10.Dept"%>
<%@page import="java.util.ArrayList"%>
<%@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> 
	<!-- 현장 HW. Dept Table row Insert
	스크릿틀릿 + Statement +sql문장( String.format) -->
<% 
	String deptno = request.getParameter("deptno");
	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 = String.format("Insert into dept values (%s, '%s', '%s')", deptno, dname, loc);
	//위의 방법처럼 string.format을 안하면 노가다 해야함
	//ex) String sql = String.format("Insert into dept values" ( + 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();	

%>	
</body>
</html>

  • oraDeptInput2.jsp (현장 HW)
    Tip)
    • Insert절 형식 2: "insert into 테이블명 values (?,?,?)";=> ?는 원하는 값 개수만큼
    • preparedStatement:0611일지 참고
<%@page import="java.sql.PreparedStatement"%>
<%@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>
	<!-- 현장 HW. Dept Table row Insert
	스크릿틀릿 + PreparedStatement +sql문장) -->
<%
	//request.setCharacterEncoding("utf-8");
	int deptno = Integer.parseInt(request.getParameter("deptno"));
	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>

  

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

</body>
</html>
  • oraDeptRetrieve.jsp (현장 HW)
    Tip) ora02참고
    • select절 형식 1: "select * from 테이블명 where 테이블 열 이름=" + 테이블 열 이름;
<%@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>
<!-- 현장 HW 
1. deptno 를 가지고 dept 조회
2. deptno ,dname , loc : request.setAttribute
3. oraDeptIn.jsp Page이동 => deptno ,dname , loc EL표기법으로 보여주기 -->

<%
	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("oraDeptIn.jsp");
		rd.forward(request, response);
	}
	stmt.close();
	conn.close();
%>
<script type="text/javascript">
	alert("헐~없는 부서야");
	location.href = "oraDeptUpdate.html";
    /* 전 페이지로 돌아가서 다시 입력하게 만들어주는 로직 */
</script>

</body>
</html>

 

  • oraDeptIn.jsp (현장 HW) 
<%@ 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>
<!-- 현장 HW. deptno ,dname , loc EL표기법으로 보여주기 -->
	<form action="oraUpdate.jsp">
		부서코드 : ${deptno }<p>	
		<!-- deptno는 primary key라 건드리면 데이터가 엉망이 되므로 수정불가하게 만들기 --> 
		<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 (현장 HW)
    Tip)
    • update절 형식: "update 테이블명 set 테이블열1 이름=?, 테이블열2 이름 =? where 테이블열3 이름 =?";
      where를 잘 활용해야함!
<%@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(sql);
	
	pstmt.setString(1, dname);
	pstmt.setString(2, loc);
	pstmt.setInt(3, deptno);
	
	int result = pstmt.executeUpdate();
	if	(result > 0) 
    	out.println("수정 성공ㅋㅋ");
	else 
    	out.println("수정 실패 ㅠㅠ");
	
	pstmt.close();
	conn.close();
	
%>
</body>
</html>

수정 전
수정 후

 

  • 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

(폴더: och10_mySql) 

mysql-connector-java-8.0.22.jar
2.28MB

 

  • my01.jsp
<%@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 driver 	= "com.mysql.cj.jdbc.Driver";
	String url		= "jdbc:mysql://localhost:3306/scottdb?serverTimezone=UTC";
	Class.forName(driver);
	Connection conn =DriverManager.getConnection(url,"root","mysql84");
	
	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>MySql 부서코드 입력</h2>
	<form action="my02.jsp">
		코드 : <input type="number" name="dno" required="required"><p>
		<input type="submit" value="확인">
	</form>
	
</body>
</html>
  • my02.jsp (현장 HW) 
<%@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 driver 	= "com.mysql.cj.jdbc.Driver";
	String url		= "jdbc:mysql://localhost:3306/scottdb?serverTimezone=UTC";
	String dno		= request.getParameter("dno");
	String sql		= "select*from division where dno="+dno;
	System.out.println("sql->"+sql);
	
	Class.forName(driver);
	Connection conn =DriverManager.getConnection(url,"root","mysql84");
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	
	if	(rs.next())	{
		String dname = rs.getString("dname");	//rs.getString(2);
		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 (현장 HW) 
<%@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>
<!-- 현장 HW -->
<%
	String driver 	= "com.mysql.cj.jdbc.Driver";
	String url		= "jdbc:mysql://localhost:3306/scottdb?serverTimezone=UTC";
	String dno		= request.getParameter("dno");
	String sql		= "select*from division where dno="+dno;
	System.out.println("sql->"+sql);
	
	Class.forName(driver);
	Connection conn =DriverManager.getConnection(url,"root","mysql84");
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	
	if	(rs.next())	{
		String dname = rs.getString("dname");	//rs.getString(2);
		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>");
		//1. request에 저장
		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();
	// page 이동 ==> my03Result.jsp
	RequestDispatcher rd = request.getRequestDispatcher("my03Result.jsp");
	rd.forward(request, response);

%>
</body>
</html>
  • my03Result.jsp (현장 HW) 
<%@ 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>
<!-- 현장 HW: EL 표기법 -->
<h2>부서 정보</h2>
	부서코드 :	${dno }<p>
	부서명 :	${dname }<p>
	전화번호 :	${phone }<p>
	근무지 :	${position }<p>
</body>
</html>

  • Division.Class (현장 HW) 
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;
	}
	
}
  • division.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h2>MySql 부서코드 입력</h2>
	<!-- <form action="my02.jsp"> -->
	<!-- <form action="my03.jsp"> -->
	<form action="my04.jsp"> 
		코드 : <input type="number" name="dno" required="required"><p>
		<input type="submit" value="확인">
	</form>
	
</body>
</html>
  • my04.jsp (현장 HW) (현장에서 제일 많이 사용하는 방법)
<%@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"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<!-- 현장 HW -->
<%
	String driver 	= "com.mysql.cj.jdbc.Driver";
	String url		= "jdbc:mysql://localhost:3306/scottdb?serverTimezone=UTC";
	String dno		= request.getParameter("dno");
	String sql		= "select*from division where dno="+dno;
	System.out.println("sql->"+sql);
	
	Class.forName(driver);
	Connection conn =DriverManager.getConnection(url,"root","mysql84");
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	// 1. division DTO 선언
	Division division = new Division();
	
	
	if	(rs.next())	{
		String dname = rs.getString("dname");	//rs.getString(2);
		String phone = rs.getString(3);
		String position = rs.getString(4);
		
		//2. division DTO request에 저장
		division.setDno(rs.getInt(1));
		division.setDname(dname);
		division.setPhone(phone);
		division.setPosition(rs.getString(4));
		request.setAttribute("division", division);
		
	}else
		out.println("그게 무슨 부서야 없는데");
	rs.close();	
	stmt.close();
	conn.close();
	// 3. page 이동 ==> my04Result.jsp
	RequestDispatcher rd = request.getRequestDispatcher("my04Result.jsp");
	rd.forward(request, response);

%>
</body>
</html>
  • my04Result.jsp (현장 HW) 
<%@page import="och10.Division"%>
<%@ 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 division = (Division)request.getAttribute("division");
%>
	부서코드 :	${division.dno }<p>
	부서명 :	${division.dname }<p>
	전화번호 :	${division.phone }<p>
	근무지 : 	${division.position }<p>

</body>
</html>

  • Professor.class (현장 HW) 
package och10;

import java.util.Date;

public class Professor {
	private int profno;
	private String name;
	private String userid;
	private String position;
	private int sal;
	private Date hiredate;
	private int comm;
	private int deptno;
	
	public int getProfno() {
		return profno;
	}
	public void setProfno(int profno) {
		this.profno = profno;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getUserid() {
		return userid;
	}
	public void setUserid(String userid) {
		this.userid = userid;
	}
	public String getPosition() {
		return position;
	}
	public void setPosition(String position) {
		this.position = position;
	}
	public int getSal() {
		return sal;
	}
	public void setSal(int sal) {
		this.sal = sal;
	}
	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 int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	
	

}
  • my05.jsp (현장 HW) (멀티 row 테스트)
    Tip
    • select절 형식 2: "select 테이블열 이름1, 테이블열 이름2, ..., 테이블열 이름n from 테이블명";
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="och10.Professor"%>
<%@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>
<%
	//현장HW
	//1. Professor에서 profno, name, position, sal 가져오기
	String driver 	= "com.mysql.cj.jdbc.Driver";
	String url		= "jdbc:mysql://localhost:3306/scottdb?serverTimezone=UTC";
	String sql		= "select profno,name,position,sal from professor";
	Class.forName(driver);
	Connection conn =DriverManager.getConnection(url,"root","mysql84");
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	
	ArrayList<Professor> al = new ArrayList<Professor>();
	
	//2. 모든 Professor Row를 al에 저장해서 Attribute로 보내기
	if	(rs.next())	{
		do	{
			Professor professor = new Professor();
			professor.setProfno(rs.getInt(1));
			professor.setName(rs.getString(2));
			professor.setPosition(rs.getString(3));
			professor.setSal(rs.getInt(4));
			al.add(professor);
		}while(rs.next());
		request.setAttribute("al", al);
		
		rs.close();
		stmt.close();
		conn.close();
	
		//3.my05Result.jsp이동
		RequestDispatcher rd = request.getRequestDispatcher("my05Result.jsp");
		rd.forward(request, response);
	}else{
		out.println("데이터가 없습니다");
		stmt.close();
		conn.close();
	}
	
%>
</body>
</html>

 

  • my05Result.jsp (현장 HW) 
<%@page import="och10.Professor"%>
<%@page import="java.util.ArrayList"%>
<%@ 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>
<style type="text/css">
	tr:hover	{
	background-color: pink;
	}
</style>
</head>
<body>
	<!-- 현장 HW -->
	<!-- Model 1 View -->
	<%
		//1.al 가져오기
		ArrayList<Professor> al = (ArrayList<Professor>)request.getAttribute("al");
	
	%>
	<table width="100%" bgcolor="skyblue" border="1">
	<tr><th>사번</th><th>이름</th><th>업무</th><th>급여</th></tr>
	<%
	//2. 가져온 al을 이용해 for문으로 보여주기
		for	(int i = 0; i < al.size(); i++)	{
			out.println("<tr><td>" + al.get(i).getProfno() + "</td>");
			out.println("<td>" + al.get(i).getName() + "</td>");
			out.println("<td>" + al.get(i).getPosition() + "</td>");
			out.println("<td>" + al.get(i).getSal() + "</td></tr>");
	}
	
	%>
	</table>

</body>
</html>

  • my05Result2.jsp (현장 HW) (MVC Model 2⭐⭐⭐⭐⭐)
<%@ 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>
<style type="text/css">
	tr:hover	{
	background-color: pink;
	}
</style>
</head>
<body>
<h2>사원 명단</h2>
<!-- 현장HW. EL표기법 + JSTL  -->
	<table width="100%" bgcolor="skyblue" border="1">
	<tr bgcolor="lemonyellow">
		<th>사번</th><th>이름</th><th>업무</th><th>급여</th>
	</tr>
	<c:forEach var="professor" items="${al }">
		<tr>
			<td>${professor.profno }</td>
			<td>${professor.name }</td>
			<td>${professor.position }</td>
			<td>${professor.sal }</td>
		</tr>

	</c:forEach>
		
</body>
</html>

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

</body>
</html>
  • myDivisionRetrieve.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>
<%
	//현장HW
	//1. dno를 받아 division을 조회하는 SQL작성하기
	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","mysql84");
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	
	//2. 각각 request 저장하는 로직 짜기 => dno, dname, phone, position
	if	(rs.next())	{
		request.setAttribute("dno", dno);
		request.setAttribute("dname", rs.getString(2));
		request.setAttribute("phone", rs.getString(3));
		request.setAttribute("position", rs.getString(4));
		
		rs.close();
		stmt.close();
		conn.close();
		
		//3. myDivisionInput.jsp Page이동
		
		RequestDispatcher rd = request.getRequestDispatcher("myDivisionInput.jsp");
		rd.forward(request, response);
	}
	stmt.close();
	conn.close();
%>
<script type="text/javascript">
	alert("없는 부서입니다")
	location.href = "myUpdate.html";	
</script>

</body>
</html>
  • myDivisionInput.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="myUpdate.jsp">
		부서코드 : ${dno }<p>
					<input type="hidden"	name="dno"	value="${dno }">
		부서명 : <input type="text"		name="dname"	value="${dname }"><p>
		전화번호 : <input type="text"	name="phone"	value="${phone }"><p>
		근무지 : <input type="text"		name="position"	value="${position }"><p>
		<input type="submit" value="수정완료">
	
	</form>
</body>
</html>
  • myUpdate.jsp

오늘의 숙제

  • oraDelete.jsp => 스크립틀릿 + Statement 삭제 Hw01
    Tip)
    • delete절 형식: "delete*from 테이블명 where 테이블 열 이름="+테이블 열 이름;

>>내가 한 것 : 못함

 

>>선생님이 한 것

<%@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 삭제 Hw01</h2>
	<%
		String driver 	= "com.mysql.cj.jdbc.Driver";
		String url		= "jdbc:mysql://localhost:3306/scottdb?serverTimezone=UTC";
		String deptno	= request.getParameter("deptno");
		String sql		= "delete*from dept where deptno="+deptno;
		
		Class.forName(driver);
		Connection conn =DriverManager.getConnection(url,"root","mysql84");
		Statement stmt = conn.createStatement();
		
		int result = stmt.executeUpdate(sql);
		if	(result > 0)
			out.println("삭제 성공");
		else
			out.println("삭제 실패");
		stmt.close();
		conn.close();
	
	%>
</body>
</html>
  • myUpdate.jsp

>> 내가 한 것

<%@page import="java.sql.PreparedStatement"%>
<%@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>스크릿틀릿+PreparedStatement</h2>
<% 
	//HW
	// 1. dno,dname,phone,position Get
	int dno = Integer.parseInt(request.getParameter("dno"));
	String dname = request.getParameter("dname");
	String phone = request.getParameter("phone");
	String position = request.getParameter("position");
	
	String sql = "update division set dname=?, phone=?, position=? where dno=?";
	String driver= "com.mysql.cj.jdbc.Driver";
	String url = "jdbc:mysql://localhost:3306/scottdb?characterEncoding=UTF-8&serverTimezone=UTC";
	
	Class.forName(driver);
	Connection conn =DriverManager.getConnection(url,"root","mysql84");
	PreparedStatement pstmt = conn.prepareStatement(sql);
	
	pstmt.setString(1, dname);
	pstmt.setString(2, phone);
	pstmt.setString(3, position);
	pstmt.setInt(4, dno);
	
	//2. Update
	   //1) 성공 -> mysql 수정 성공 ㅋㅋ
        //2) 실패 -> mysql 수정 헉 ㅠㅠ
	int result = pstmt.executeUpdate();
    if	(result > 0)
    	out.println("수정 성공");
    else
    	out.println("수정 실패");
    
    pstmt.close();
    conn.close();


%>

</body>
</html>

>>선생님이 한 것 : 같음

'JSP > Java Script' 카테고리의 다른 글

2024_07_19_금 ⭐⭐⭐  (0) 2024.07.19
2024_07_18_목  (0) 2024.07.18
2024_07_16_화  (0) 2024.07.16
2024_07_15_월  (0) 2024.07.15
2024_07_12_금  (0) 2024.07.12

관련글

티스토리툴바