코딩/4-JSP & SERVLET

C12_db_gisatest => 01_골프장

tree0505 2025. 6. 30. 16:45
반응형
    • 01_골프장
  • _1100_골프장_db셋팅.txt
-- # 자료형의 종류
-- 문자 : char, varchar
-- 정수 : int
-- 실수 : float, double

-- # 옵션
-- primary key : 중복데이터 금지 + 데이터를 넣지 않으면 오류 발생

create database _11_golf;
use _11_golf;

CREATE TABLE tbl_teacher_202201(
	teacher_code CHAR(3),
	teacher_name VARCHAR(15),
	class_name VARCHAR(20),
	class_price INT(8),
	teacher_regist_date VARCHAR(8),
	PRIMARY KEY(teacher_code)
);
INSERT INTO tbl_teacher_202201 VALUES('100', '이초급', '초급반', 100000, '20220101');
INSERT INTO tbl_teacher_202201 VALUES('200', '김중급', '중급반', 200000, '20220102');
INSERT INTO tbl_teacher_202201 VALUES('300', '박고급', '고급반', 300000, '20220103');
INSERT INTO tbl_teacher_202201 VALUES('400', '정심화', '심화반', 400000, '20220104');
SELECT * FROM tbl_teacher_202201;

CREATE TABLE tbl_member_202201(
	c_no CHAR(5),
	c_name VARCHAR(15),
	phone VARCHAR(11),
	address VARCHAR(50),
	grade VARCHAR(6),
	PRIMARY KEY(c_no)
);
INSERT INTO tbl_member_202201 VALUES('10001', '홍길동', '01011112222', '서울시 강남구', '일반');
INSERT INTO tbl_member_202201 VALUES('10002', '장발장', '01022223333', '성남시 분당구', '일반');
INSERT INTO tbl_member_202201 VALUES('10003', '임꺽정', '01033334444', '대전시 유성구', '일반');
INSERT INTO tbl_member_202201 VALUES('20001', '성춘향', '01044445555', '부산시 서구', 'VIP');
INSERT INTO tbl_member_202201 VALUES('20002', '이몽룡', '01055556666', '대구시 북구', 'VIP');
SELECT * FROM tbl_member_202201;

CREATE TABLE tbl_class_202201(
	regist_month VARCHAR(6),
	c_no CHAR(5),
	class_area VARCHAR(15),
	tuition INT(8),
	teacher_code CHAR(3),
	PRIMARY KEY(regist_month, c_no)
);
INSERT INTO tbl_class_202201 VALUES('202203', '10001', '서울본원', 100000, 100);
INSERT INTO tbl_class_202201 VALUES('202203', '10002', '성남분원', 100000, 100);
INSERT INTO tbl_class_202201 VALUES('202203', '10003', '대전분원', 200000, 200);
INSERT INTO tbl_class_202201 VALUES('202203', '20001', '부산분원', 150000, 300);
INSERT INTO tbl_class_202201 VALUES('202203', '20002', '대구분원', 200000, 400);
SELECT * FROM tbl_class_202201;
  • _1101_골프장_문제1.jsp
<%@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>Insert title here</title>
</head>
<body>
	<%--
		[문제]_1101_골프장_정답1.png 이미지와 같은 결과가 나오도록 처리하시오.
	 --%>
	 
 <%
 	Connection conn = null;
 	PreparedStatement pstmt = null; 
 	ResultSet rs = null; 
 	
 	int teatcherSize=0; 
 	
 	try{
 		String jdbcUrl = "jdbc:mysql://localhost:3306/_11_golf?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcUrl, dbId, dbPw);
		
		String sql = "SELECT COUNT(*) FROM tbl_teacher_202201";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			teatcherSize = rs.getInt(1);
		}else{
			System.out.println("검색데이터가 없습니다.");
		}
 	}catch(Exception e){
 		e.printStackTrace();
 	}finally{
		if(conn != null){
			conn.close();			
		}
		if(pstmt != null){
			pstmt.close();	
		}
		if(rs != null){
			rs.close();
		}
 	}
 	
 	String[][] teacherList = new String[teatcherSize][5];
 	try{
 		String jdbcUrl = "jdbc:mysql://localhost:3306/_11_golf?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcUrl, dbId, dbPw);
		
		String sql = "SELECT * FROM tbl_teacher_202201";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		
		int i = 0; 
		while(rs.next()){
			teacherList[i][0] = rs.getString(1);
			teacherList[i][1] = rs.getString(2);
			teacherList[i][2] = rs.getString(3);
			teacherList[i][3] = rs.getInt(4) +""; 
			
			String date = rs.getString(5);
			String year = date.substring(0,4);
			String month = date.substring(4,6);
			String day = date.substring(6,8);
			teacherList[i][4] = year + "년" +month +"월"+day+"일";
			
			i += 1; 
		}
		if(i == 0){
			System.out.println("검색데이터가 없습니다.");
		}
		
 	}catch(Exception e){
 		e.printStackTrace();
 	}finally{
		if(conn != null){
			conn.close();			
		}
		if(pstmt != null){
			pstmt.close();	
		}
		if(rs != null){
			rs.close();
		}
 	}
 
 %>
 
 <div align="center">
 	<h3>강사조회</h3>
 	<table border="1">
 		<tr>	
 				<td>강사코드</td>
	 			<td>강사명</td>
	 			<td>강의명</td>
	 			<td>수강료</td>
	 			<td>강사자격취득일</td>
 		</tr>
 		<%for(int i = 0; i<teacherList.length; i++){ %>
 			<tr>	
 				<td><%=teacherList[i][0] %></td>
 				<td><%= teacherList[i][1] %></td>
	 			<td><%= teacherList[i][2] %></td>
	 			<td><%= teacherList[i][3] %></td>
	 			<td><%= teacherList[i][4] %></td>
 			</tr>
 		<% }%>
 	</table>
 </div>
</body>
</html>
  • _1102_골프장_문제2.jsp
<%@page import="java.util.Arrays"%>
<%@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>Insert title here</title>
</head>
<body>
	<%--
        [문제]_1102_골프장_정답2.png 이미지와 같은 결과가 나오도록 처리하시오.
	 --%>
	 
<%
	Connection conn = null; 
	PreparedStatement pstmt = null; 
	ResultSet rs = null; 
	
	int memberSize = 0; //맴버 개수
	
	//
	try{
		String jdbcUrl = "jdbc:mysql://localhost:3306/_11_golf?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcUrl, dbId, dbPw);
		
		String sql ="SELECT COUNT(*) FROM tbl_member_202201";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			memberSize = rs.getInt(1);
		}else{
			System.out.println("검색데이터가 없습니다."); 
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		if(conn != null){
			conn.close();			
		}
		if(pstmt != null){
			pstmt.close();	
		}
		if(rs != null){
			rs.close();
		}
	}
	 //---------------------------------------------------------
	String[][] memberList = new String[memberSize][5];
	 //맴버 배열 개수만큼 만듬 
	try{
		String jdbcUrl = "jdbc:mysql://localhost:3306/_11_golf?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcUrl, dbId, dbPw);
		
		String sql = "SELECT * FROM tbl_member_202201";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		
		int i = 0; 
		while(rs.next()){
			memberList[i][0] = rs.getString(1);
			memberList[i][1] = rs.getString(2);
			memberList[i][2] = rs.getString(3);
			memberList[i][3]  = rs.getString(4);
			memberList[i][4] = rs.getString(5);
			
			i += 1; 
		}
		if(i == 0){
			System.out.println("검색데이터가 없습니다. "); 
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		
		if(conn != null){
			conn.close();			
		}
		if(pstmt != null){
			pstmt.close();	
		}
		if(rs != null){
			rs.close();
		}
	}
	 //---------------------------------------------------------
	//선생님 개수 //몇명이 있는지 알아야 배열을 만들 수 있다. 
	//String[][] teacherList = new String[teatcherSize][5];
	 	//teatcherSize 이거때문 
	 int teatcherSize = 0; 
	try{
		String jdbcUrl = "jdbc:mysql://localhost:3306/_11_golf?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcUrl, dbId, dbPw);
		
		String sql = "SELECT COUNT(*) FROM tbl_teacher_202201";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			teatcherSize = rs.getInt(1); 
		}else{
			System.out.println("검색데이터가 없습니다. "); 
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		
		if(conn != null){
			conn.close();			
		}
		if(pstmt != null){
			pstmt.close();	
		}
		if(rs != null){
			rs.close();
		}
	}
	 //---------------------------------------------------------
	//배열 만들기 
	 String[][] teacherList = new String[teatcherSize][5];
	try{
		String jdbcUrl = "jdbc:mysql://localhost:3306/_11_golf?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcUrl, dbId, dbPw);
		
		String sql = "SELECT * FROM tbl_teacher_202201";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		
		int i = 0; 
		while(rs.next()){
			teacherList[i][0] = rs.getString(1);
			teacherList[i][1] = rs.getString(2);
			teacherList[i][2] = rs.getString(3); 
			teacherList[i][3] = rs.getInt(4)+""; 
			teacherList[i][4] = rs.getString(5);
			i += 1; 
		}
		if(i == 0){
			System.out.println("검색데이터가 없습니다.");
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		if(conn != null){
			conn.close();			
		}
		if(pstmt != null){
			pstmt.close();	
		}
		if(rs != null){
			rs.close();
		}
	}
	 //---------------------------------------------------------
	int classSize = 0; //강의 
	try{
		String jdbcUrl = "jdbc:mysql://localhost:3306/_11_golf?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcUrl, dbId, dbPw);
		
		String sql = "SELECT COUNT(*) FROM tbl_class_202201";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			classSize = rs.getInt(1);
		}else{
			System.out.println("검색데이터가 없습니다.");
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		if(conn != null){
			conn.close();			
		}
		if(pstmt != null){
			pstmt.close();	
		}
		if(rs != null){
			rs.close();
		}
	}
	 //---------------------------------------------------------
	String[][] classList = new String[classSize][5];
	try{
		String jdbcUrl = "jdbc:mysql://localhost:3306/_11_golf?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcUrl, dbId, dbPw);
		
		String sql = "SELECT * FROM tbl_class_202201";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		
		int i = 0; 
		while(rs.next()){
			classList[i][0] = rs.getString(1);
			classList[i][1] = rs.getString(2);
			classList[i][2] = rs.getString(3);
			classList[i][3] = rs.getInt(4)+"";
			classList[i][4] = rs.getString(5);
				
			i += 1; 
			
  		}
		if( i == 0){
			System.out.println("검색데이터가 없습니다. ");
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		if(conn != null){
			conn.close();			
		}
		if(pstmt != null){
			pstmt.close();	
		}
		if(rs != null){
			rs.close();
		}
	}
	 //---------------------------------------------------------
	for(int i=0; i<memberList.length; i++) {
		System.out.println(Arrays.toString(memberList[i]));
	}
	System.out.println("-------------------------------------");
	for(int i=0; i<classList.length; i++) {
		System.out.println(Arrays.toString(classList[i]));
	}
	System.out.println("-------------------------------------");
	for(int i=0; i<teacherList.length; i++) {
		System.out.println(Arrays.toString(teacherList[i]));
	}
	System.out.println("-------------------------------------");
	 //---------------------------------------------------------
	 
	 //화면 출력 
	String[][]viewList = new String[memberList.length][7];
	
		for(int i = 0; i<memberList.length; i++){
			for(int j = 0; j<classList.length; j++){
				if(memberList[i][0].compareTo(classList[j][1]) == 0){
					String date = classList[j][0];
					String year = date.substring(0,4);
					String month = date.substring(4,6);
					
					viewList[i][0] = year +"년" +month +"월";
					
					viewList[i][4] = memberList[i][3];
					
					viewList[i][5] = classList[i][3];
					
					for(int k = 0; k<teacherList.length; k++){
						if(classList[j][4].compareTo(teacherList[k][0]) == 0){
							viewList[i][3] = teacherList[k][2];
						}
					}
				}
			}
			
			viewList[i][1] = memberList[i][0];
			
			viewList[i][2] = memberList[i][1];
			
			viewList[i][6] = memberList[i][4];
			
			Arrays.toString(viewList[i]);
		}
%>

	 <div align="center">
	 	<h3>회원정보조회</h3>
	 	<table border="1">
	 		<tr>
	 			<td>수강월</td>
	 			<td>회원번호</td>
	 			<td>회원명</td>
	 			<td>강의명</td>
	 			<td>강의장소</td>
	 			<td>수강료</td>
	 			<td>등급</td>
	 		</tr>
	 	<% for(int i=0; i<viewList.length; i++) { %>
	 		<tr>
	 			<td><%= viewList[i][0] %></td>
	 			<td><%= viewList[i][1] %></td>
	 			<td><%= viewList[i][2] %></td>
	 			<td><%= viewList[i][3] %></td>
	 			<td><%= viewList[i][4] %></td>
	 			<td><%= viewList[i][5] %></td>
	 			<td><%= viewList[i][6] %></td>
	 		</tr>
	 	<% } %>
	 	</table>
	 </div>
</body>
</html>
  • _1103_골프장_문제3.jsp
<%@page import="java.util.Arrays"%>
<%@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>Insert title here</title>
</head>
<body>
	<%--
		[문제]_1103_골프장_정답3.png 이미지와 같은 결과가 나오도록 처리하시오.
	 --%>
<%
	Connection conn = null; 
	PreparedStatement pstmt = null; 
	ResultSet rs = null; 
	
	int teatcherSize = 0; 
	
	try{
		String jdbcUrl = "jdbc:mysql://localhost:3306/_11_golf?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcUrl, dbId, dbPw);
		
		String sql = "SELECT COUNT(*) FROM tbl_teacher_202201";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			teatcherSize = rs.getInt(1);
		}else{
			System.out.println("검색데이터가 없습니다.");
		}
				
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		
		if(conn != null){
			conn.close();			
		}
		if(pstmt != null){
			pstmt.close();	
		}
		if(rs != null){
			rs.close();
		}
	}

	//-----------------------------------------------
	String[][] teacherList = new String[teatcherSize][5];
	try{
		String jdbcUrl = "jdbc:mysql://localhost:3306/_11_golf?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcUrl, dbId, dbPw);
		
		String sql = "SELECT * FROM tbl_teacher_202201";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		
		int i = 0; 
		while(rs.next()){
			teacherList[i][0] = rs.getString(1);
			teacherList[i][1] = rs.getString(2);
			teacherList[i][2] = rs.getString(3);
			teacherList[i][3] = rs.getInt(4) + "";
			teacherList[i][4] = rs.getString(5);
			
			i += 1; 
		}
		if(i == 0){
			System.out.println("검색데이터가 없습니다.");
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		if(conn != null){
			conn.close();			
		}
		if(pstmt != null){
			pstmt.close();	
		}
		if(rs != null){
			rs.close();
		}
	}
	//------------------------------------------
	int classSize = 0; 
	
	
	
	try{
		String jdbcUrl = "jdbc:mysql://localhost:3306/_11_golf?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcUrl, dbId, dbPw);
		
		String sql = "SELECT COUNT(*) FROM tbl_class_202201";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		
		if(rs.next()){
			classSize = rs.getInt(1);
		}else{
			System.out.println("검색데이터가 없습니다."); 
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		if(conn != null){
			conn.close();			
		}
		if(pstmt != null){
			pstmt.close();	
		}
		if(rs != null){
			rs.close();
		}
	}
	
	//-----------------------------------------
	String[][] classList = new String[classSize][5];
	
	try{
		String jdbcUrl = "jdbc:mysql://localhost:3306/_11_golf?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcUrl, dbId, dbPw);
		
		String sql ="SELECT * FROM tbl_class_202201";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		
		int i = 0; 
		while(rs.next()){
			classList[i][0] = rs.getString(1);
			classList[i][1] = rs.getString(2);
			classList[i][2] = rs.getString(3);
			classList[i][3] = rs.getInt(4) + "";
			classList[i][4] = rs.getString(5);
			
			i += 1; 
		}
		if( i == 0){
			System.out.println("검색데이터가 없습니다."); 
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		if(conn != null){
			conn.close();			
		}
		if(pstmt != null){
			pstmt.close();	
		}
		if(rs != null){
			rs.close();
		}
	}
	
	
	for(int i=0; i<teacherList.length; i++) {
		System.out.println(Arrays.toString(teacherList[i]));
	}
	System.out.println("-------------------------------------");
	for(int i=0; i<classList.length; i++) {
		System.out.println(Arrays.toString(classList[i]));
	}
	System.out.println("-------------------------------------");
	
	//--------------------------------------------------
	String[][] viewList = new String[teacherList.length][4];
	
	for(int i = 0; i<teacherList.length; i++){
		
		int total = 0; 
		for(int j = 0; j<classList.length; j++){
			if(teacherList[i][0].compareTo(classList[j][4]) == 0){
				total += Integer.parseInt(classList[j][3]);
			}
		}
		
		viewList[i][3] = String.valueOf(total);
		
		viewList[i][0] = teacherList[i][0];
		
		viewList[i][2] = teacherList[i][2];
		
		viewList[i][1] = teacherList[i][1];
		
		System.out.println(Arrays.toString(viewList[i]));
	}
%>

	 <div align="center">
	 	<h3>강사매출현황</h3>
	 	<table border="1">
	 		<tr>
	 			<td>강사코드</td>
	 			<td>강의명</td>
	 			<td>강사명</td>
	 			<td>총매출</td>
	 		</tr>
	 	<% for(int i=0; i<viewList.length; i++) { %>
	 		<tr>
	 			<td><%= viewList[i][0] %></td>
	 			<td><%= viewList[i][1] %></td>
	 			<td><%= viewList[i][2] %></td>
	 			<td><%= viewList[i][3] %></td>
	 		</tr>
	 	<% } %>
	 	</table>
	 </div>
</body>
</html>
반응형

'코딩 > 4-JSP & SERVLET' 카테고리의 다른 글

D02_session_async  (0) 2025.07.07
C22_dao_board_re  (0) 2025.07.03
C02_session_cart  (0) 2025.06.30
C01_session_basic  (3) 2025.06.30
B23_servlet_board-TEST1  (0) 2025.05.29

'코딩/4-JSP & SERVLET'의 다른글

  • 현재글C12_db_gisatest => 01_골프장

관련글

티스토리툴바