JAVA
JAVA(골프연습장회원관리프로그램)_2024-06-28
앵보몬
2024. 6. 30. 20:59
728x90
반응형
DB생성
--drop table TBL_MEMBER_202201;
--drop table TBL_CLASS_202201;
--drop table TBL_TEACHER_202201;
--1.강사정보 테이블 생성
CREATE TABLE TBL_TEACHER_202201 (
TEACHER_CODE CHAR(3) PRIMARY KEY,
TEACHER_NAME VARCHAR2(15),
CLASS_NAME VARCHAR2(20),
CLASS_PRICE NUMBER(8),
TEACHER_REGIST_DATE VARCHAR2(8)
);
--2.회원정보 테이블 생성
CREATE TABLE TBL_MEMBER_202201 (
C_NO CHAR(5) PRIMARY KEY,
C_NAME VARCHAR2(15),
PHONE VARCHAR2(11),
ADDRESS VARCHAR2(50),
GRADE VARCHAR2(6)
);
--3.수강정보 테이블 생성
CREATE TABLE TBL_CLASS_202201 (
REGIST_MONTH VARCHAR2(6),
C_NO CHAR(5),
CLASS_AREA VARCHAR2(15),
TUITION NUMBER(8),
TEACHER_CODE CHAR(3),
CONSTRAINT TBL_CLASS_202201_PK PRIMARY KEY(REGIST_MONTH, C_NO)
);
--1.강사정보 테이블(TBL_TEACHER_202201)
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');
commit;
--2.회원정보 테이블(TBL_MEMBER_202201)
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');
commit;
--3.수강정보 테이블(TBL_CLASS_202201)
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);
commit;
package abc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class OracleDBExample3 {
public static void main(String[] args) {
String username = "system";
String password = "1234";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Scanner scanner = new Scanner(System.in);
try {
conn = DriverManager.getConnection(url, username, password);
while (true) {
System.out.println("============= 골프연습장 회원관리 프로그램 =============");
System.out.println("1. 강사조회");
System.out.println("2. 회원정보조회");
System.out.println("3. 강사매출현황");
System.out.println("4. 프로그램 종료");
System.out.println("=================================================");
System.out.print("선택 : ");
int choice = scanner.nextInt();
switch (choice) {
case 1:
String sqlQuery1 =
"SELECT M.TEACHER_CODE AS 강사코드, " +
" M.TEACHER_NAME AS 강사명, " +
" M.CLASS_NAME AS 강의명, " +
" TO_CHAR(M.CLASS_PRICE,'L9,999,999') AS 수강료, " +
" TO_CHAR(TO_DATE(M.TEACHER_REGIST_DATE, 'YYYYMMDD'),'YYYY\"년\"MM\"월\"DD\"일') AS 강사자격취득일 " +
"FROM TBL_TEACHER_202201 M";
pstmt = conn.prepareStatement(sqlQuery1);
rs = pstmt.executeQuery();
System.out.println("");
System.out.println("-----------------------------------------------------------");
System.out.println("강사코드 강사명 강의명 수강료 강사자격취득일");
System.out.println("-----------------------------------------------------------");
while (rs.next()) {
String 강사코드 = rs.getString("강사코드");
String 강사명 = rs.getString("강사명");
String 강의명 = rs.getString("강의명");
String 수강료 = rs.getString("수강료");
String 강사취득일 = rs.getString("강사자격취득일");
System.out.printf("%-5s %-5s %-1s %-20s %-10s%n",
강사코드, 강사명, 강의명, 수강료, 강사취득일);
}
break;
case 2:
String sqlQuery2 =
"SELECT TO_CHAR(TO_DATE(B.REGIST_MONTH, 'YYYYMM'),'YYYY\"년\"MM\"월\"') AS 수강월, " +
" A.C_NO AS 회원번호, " +
" A.C_NAME AS 회원명, " +
" D.CLASS_NAME AS 강의명, " +
" B.CLASS_AREA AS 강의장소, " +
" TO_CHAR(D.class_price,'L9,999,999') AS 수강료, " +
" A.GRADE AS 등급 " +
"FROM TBL_MEMBER_202201 A " +
" JOIN TBL_CLASS_202201 B ON A.C_NO = B.C_NO " +
" JOIN TBL_TEACHER_202201 D ON B.TEACHER_CODE = D.TEACHER_CODE";
pstmt = conn.prepareStatement(sqlQuery2);
rs = pstmt.executeQuery();
System.out.println("");
System.out.println("---------------------------------------------------------------------");
System.out.println(" 수강월 회원번호 회원명 강의명 강의장소 수강료 등급");
System.out.println("---------------------------------------------------------------------");
while (rs.next()) {
String 수강월 = rs.getString("수강월");
String 회원번호 = rs.getString("회원번호");
String 회원명 = rs.getString("회원명");
String 강의명 = rs.getString("강의명");
String 강의장소 = rs.getString("강의장소");
String 수강료 = rs.getString("수강료");
String 등급 = rs.getString("등급");
System.out.printf("%-10s %-10s %-5s %-5s %-1s %-20s %-1s%n",
수강월, 회원번호, 회원명, 강의명, 강의장소, 수강료, 등급);
}
break;
case 3:
String sqlQuery3 =
"SELECT M.TEACHER_CODE AS 강사코드, " +
" M.CLASS_NAME AS 강의명, " +
" M.TEACHER_NAME AS 강사명, " +
" TO_CHAR(SUM(D.TUITION),'L9,999,999') AS 총매출액 " +
"FROM TBL_TEACHER_202201 M " +
" JOIN TBL_CLASS_202201 D ON M.TEACHER_CODE = D.TEACHER_CODE " +
"GROUP BY M.TEACHER_CODE, M.CLASS_NAME, M.TEACHER_NAME " +
"ORDER BY M.TEACHER_CODE ASC";
pstmt = conn.prepareStatement(sqlQuery3);
rs = pstmt.executeQuery();
System.out.println("");
System.out.println("-----------------------------------------");
System.out.println("강사코드 강의명 강사명 총매출액");
System.out.println("-----------------------------------------");
while (rs.next()) {
String 강사코드 = rs.getString("강사코드");
String 강의명 = rs.getString("강의명");
String 강사명 = rs.getString("강사명");
String 총매출액 = rs.getString("총매출액");
System.out.printf("%-5s %-5s %-5s %-5s%n",
강사코드, 강의명, 강사명, 총매출액);
}
break;
case 4:
System.out.println("프로그램을 종료합니다.");
return;
default:
System.out.println("잘못된 선택입니다. 다시 선택해주세요.");
break;
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
if (conn != null) conn.close();
scanner.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}728x90
반응형