본문 바로가기
Project/성적 관리 프로그램

[JAVA] 성적 관리 프로그램 - 통합 (파일 첨부)

잉지렁 2023. 8. 14. 16:14
728x90
반응형

Professor.java

package project_score;

import lombok.Data;

@Data
public class Professor {
	private String proId;
	private String proPw;
	private String proName;
	private String proEmail;
	private int proAge;
	private String proClass;
}

 

Score.java

package project_score;

import lombok.Data;

@Data

public class Score {
	private String stuName;
	private String stuNo;
	private String stuBirth;
	private int kor;
	private int mat;
	private int eng;
	private int sum;
	private String grade;
	private int rank;
}

 

loginMain.java

package project_score;

public class loginMain {
	public static void main(String[] args) {
		LoginMenu loginMenu = new LoginMenu();
		loginMenu.LoginMainMenu();
	}
}

 

LoginMenu.java

package project_score;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Scanner;

public class LoginMenu {
	// Field
	private Connection conn;
	private Scanner scanner = new Scanner(System.in);
		
		
	// Constructor
	public LoginMenu() {
		try {
			// JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
				
			// 연결하기
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl", "java", "oracle");
		} catch(Exception e) {
			e.printStackTrace();
			exit();
		}
	}
		
	// Method
	public void LoginMainMenu() {
		System.out.println();
		System.out.println("1.교수 | 2.학생 | 3.종료");
		System.out.println("--------------------------------------------");
		System.out.print("메뉴 선택 : ");
		String menuNo = scanner.nextLine();
					
		if(menuNo.equals("1")) {
			proLoginMethod proLoginMethod = new proLoginMethod();
			System.out.println();
			System.out.println("1.로그인 | 2.회원가입 | 3.이전으로");
			System.out.println("--------------------------------------------");
			System.out.print("메뉴 선택 : ");
			menuNo = scanner.nextLine();
			switch(menuNo) {
			case "1" -> proLoginMethod.proLogin();
			case "2" -> proLoginMethod.proJoin();
			case "3" -> LoginMainMenu();
			}
		} else if(menuNo.equals("2")) {
			stuLoginMethod stuLoginMethod = new stuLoginMethod();
			System.out.println();
			stuLoginMethod.stuLogin();
		} else if(menuNo.equals("3")) {
			exit();
		}
	}
	
	public void exit() {
		if(conn != null) {
			try {
				conn.close();
			} catch(SQLException e) {}
		}
		System.out.println("종료합니다.");
		System.exit(0);
	}
}

 

proLoginMethod.java

package project_score;

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 proLoginMethod {
	// Field
	private Connection conn;
	private Scanner scanner = new Scanner(System.in);
	private Professor pro = new Professor();
		
		
	// Constructor
	public proLoginMethod() {
		try {
			// JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
			
			// 연결하기
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl", "java", "oracle");
		} catch(Exception e) {
			e.printStackTrace();
			exit();
		}
	}
		
	// Method
	public void proLogin() {			
		int chance = 0;
			
		while (chance < 3) { // 3번 틀리면 프로그램 종료
			System.out.print("ID : ");
			String scannerId = scanner.nextLine(); // 아이디 입력
				
			System.out.print("PW : ");
			String scannerPw = scanner.nextLine(); // 비밀번호 입력
			
			try {
				String sql = "" + "SELECT proId, proPw "
						+ "FROM professors "
						+ "WHERE proId=?";
				
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, scannerId);
				ResultSet rs = pstmt.executeQuery();
				
				if(rs.next()) {
					pro.setProId(rs.getString("proId"));
					pro.setProPw(rs.getString("proPw"));
					
					if(scannerId.equals(pro.getProId())) { // 아이디가 일치하는지 확인
						if(scannerPw.equals(pro.getProPw())) { // 비밀번호가 일치하는지 확인
							System.out.println("로그인 성공");
							proMethod pro = new proMethod();
							pro.mainMenu(); // 아이디와 비밀번호가 일치하면 교수 메인메뉴로 이동
						}
					}
					else {
						System.out.println("일치하지 않습니다."); // 로그인 실패 메시지 출력
					}
				}
				chance ++;
			} catch(Exception e) {
				e.printStackTrace();
				exit();
			}
		}
		LoginMenu loginMenu = new LoginMenu();
		loginMenu.LoginMainMenu();
	}
		
	
	public void proJoin() {
		System.out.println("[교수 회원가입]");
		System.out.print("ID : ");
		pro.setProId(scanner.nextLine());
		System.out.print("PW : ");
		pro.setProPw(scanner.nextLine());
		System.out.print("이름 : ");
		pro.setProName(scanner.nextLine());
		System.out.print("이메일 : ");
		pro.setProEmail(scanner.nextLine());
		System.out.print("반 : ");
		pro.setProClass(scanner.nextLine());
		System.out.print("나이 : ");
		pro.setProAge(scanner.nextInt());
		scanner.nextLine();
		
		try {
			// 매개변수화된 SQL문 작성
			String sql = "" + "INSERT INTO professors (proId, proPw, proName, proEmail, proClass, proAge) "
					+ "VALUES(?, ?, ?, ?, ?, ?)";
			
			// PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pro.getProId());
			pstmt.setString(2, pro.getProPw());
			pstmt.setString(3, pro.getProName());
			pstmt.setString(4, pro.getProEmail());
			pstmt.setString(5, pro.getProClass());
			pstmt.setInt(6, pro.getProAge());
			
			// SQL문 실행
			pstmt.executeUpdate();
			
			// PreparedStatement 닫기
			pstmt.close();
		} catch(Exception e) {
			e.printStackTrace();
			exit();
		}
		LoginMenu loginMenu = new LoginMenu();
		loginMenu.LoginMainMenu();
	}	
	
	
	public void exit() {
		if(conn != null) {
			try {
				conn.close();
			} catch(SQLException e) {}
		}
		System.out.println("종료합니다.");
		System.exit(0);
	}
}

 

stuLoginMethod.java

package project_score;

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 stuLoginMethod {
	// Field
	private Connection conn;
	private Scanner scanner = new Scanner(System.in);
	private Score stu = new Score();
			
			
	// Constructor
	public stuLoginMethod() {
		try {
			// JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
			
			// 연결하기
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl", "java", "oracle");
		} catch(Exception e) {
			e.printStackTrace();
			exit();
		}
	}
			
	// Method
	public void stuLogin() {
		int chance = 0;
		
		while (chance < 3) { // 3번 틀리면 프로그램 종료
			System.out.print("학번 : ");
			String scannerNo = scanner.nextLine(); // 아이디 입력
				
			System.out.print("생년월일 : ");
			String scannerBirth = scanner.nextLine(); // 비밀번호 입력
			
			try {
				String sql = "" + "SELECT stuNo, stuBirth "
						+ "FROM students "
						+ "WHERE stuNo=?";
				
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, scannerNo);
				ResultSet rs = pstmt.executeQuery();
				
				if(rs.next()) {
					stu.setStuNo(rs.getString("stuNo"));
					stu.setStuBirth(rs.getString("stuBirth"));
					
					if(scannerNo.equals(stu.getStuNo())) { // 아이디가 일치하는지 확인
						if(scannerBirth.equals(stu.getStuBirth())) { // 비밀번호가 일치하는지 확인
							System.out.println("로그인 성공");
							stuMethod stu = new stuMethod();
							stu.stuSearch(rs.getString("stuNo")); // 아이디와 비밀번호가 일치하면 교수 메인메뉴로 이동
						}
					}
					else {
						System.out.println("일치하지 않습니다."); // 로그인 실패 메시지 출력
					}
				}
				chance ++;
			} catch(Exception e) {
				e.printStackTrace();
				exit();
			}
		}
		LoginMenu loginMenu = new LoginMenu();
		loginMenu.LoginMainMenu();
	}
	
	
	public void exit() {
		if(conn != null) {
			try {
				conn.close();
			} catch(SQLException e) {}
		}
		System.out.println("종료합니다.");
		System.exit(0);
	}
}

 

stuMethod.java

package project_score;

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 stuMethod {
	// Field
	private Connection conn;
	private Score stu = new Score();
			
			
	// Constructor
	public stuMethod() {
		try {
			// JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
				
			// 연결하기
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl", "java", "oracle");
		} catch(Exception e) {
			e.printStackTrace();
			exit();
		}
	}
			
	// Method
	public void stuSearch(String stuNo) {
		try {
			String sql = "" + "SELECT stuName, stuNo, stuBirth, kor, mat, eng, sum, sum/3, grade "
					+ "FROM students "
					+ "WHERE stuNo=?";
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, stuNo);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()) {
				stu.setStuName(rs.getString("stuName"));
				stu.setStuNo(rs.getString("stuNo"));
				stu.setStuBirth(rs.getString("stuBirth"));
				stu.setKor(rs.getInt("kor"));
				stu.setMat(rs.getInt("mat"));
				stu.setEng(rs.getInt("eng"));
				stu.setSum(rs.getInt("sum"));
				stu.setGrade(rs.getString("grade"));
				
				System.out.println();
				System.out.println("[ " + stuNo + " 학생 ]");
				System.out.println("이름 : " + stu.getStuName());
				System.out.println("학번 : " + stu.getStuNo());
				System.out.println("생년월일 : " + stu.getStuBirth());
				System.out.println("국어 : " + stu.getKor());
				System.out.println("수학 : " + stu.getMat());
				System.out.println("영어 : " + stu.getEng());
				System.out.println("합 : " + stu.getSum());
				System.out.println("평균 : " + stu.getSum() / 3);
				System.out.println("등급 : " + stu.getGrade());
				System.out.println();
			}
		} catch(Exception e) {
			e.printStackTrace();
			exit();
		}
		LoginMenu loginMenu = new LoginMenu();
		loginMenu.LoginMainMenu();
	}
	
	public void exit() {
		if(conn != null) {
			try {
				conn.close();
			} catch(SQLException e) {}
		}
		System.out.println("종료합니다.");
		System.exit(0);
	}
}

 

proMethod.java

package project_score;

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 proMethod {
	
	// Field
	private Scanner scanner = new Scanner(System.in);
	private Connection conn;
	private Score score = new Score();
	
	
	// Constructor
	public proMethod() {
		try {
			// JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
			
			// 연결하기
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl", "java", "oracle");
		} catch(Exception e) {
			e.printStackTrace();
			exit();
		}
	}
	
	
	// Method
	public void mainMenu() {
		System.out.println();
		System.out.println("1.입력 | 2.조회 | 3.전체 삭제 | 4.집계표 | 5.종료");
		System.out.println("--------------------------------------------");
		System.out.print("메뉴 선택 : ");
		String menuNo = scanner.nextLine();
		System.out.println();
		
		switch(menuNo) {
			case "1" -> insert();
			case "2" -> search();
			case "3" -> clear();
			case "4" -> read();
			case "5" -> exit();
			default -> mainMenu();
		}
	}
	
	
	public void insert() {
		// 학생 정보 (이름 / 학번 / 생년월일 / 국어 / 수학 / 영어 성적) 입력
		System.out.println("[학생 정보 입력]");
		System.out.print("이름 : ");
		score.setStuName(scanner.nextLine());
		System.out.print("학번 : ");
		score.setStuNo(scanner.nextLine());
		System.out.print("생년월일 : ");
		score.setStuBirth(scanner.nextLine());
		System.out.print("국어 성적 : ");
		score.setKor(scanner.nextInt());
		System.out.print("수학 성적 : ");
		score.setMat(scanner.nextInt());
		System.out.print("영어 성적 : ");
		score.setEng(scanner.nextInt());
		
		// DB에 합 추가
		score.setSum(score.getKor() + score.getMat() + score.getEng());
		
		// DB에 등급 추가
		if((score.getSum() / 3) >= 90) {
			score.setGrade("A");
		} else if((score.getSum() / 3) >= 80) {
			score.setGrade("B");
		} else if((score.getSum() / 3) >= 70) {
			score.setGrade("C");
		} else if((score.getSum() / 3) >= 60) {
			score.setGrade("D");
		} else {
			score.setGrade("F");
		}
		
		scanner.nextLine();
		/* Scanner.nextInt() 메소드가 사용자가 입력한 enter(개행문자) 를 제거하지 않음.
		 * 남겨진 개행문자가 다음 scan.nextLine()의 입력으로 처리되어 곧바로 개행됨
		 * 이를 방지하기 위해서 scanner.nextLine()을 작성하여 개행문자 제거 */
		
		try {
			// 매개변수화된 SQL문 작성
			String sql = "" + "INSERT INTO students (stuName, stuNo, stuBirth, kor, mat, eng, sum, grade) "
					+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
			
			// PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, score.getStuName());
			pstmt.setString(2, score.getStuNo());
			pstmt.setString(3, score.getStuBirth());
			pstmt.setInt(4, score.getKor());
			pstmt.setInt(5, score.getMat());
			pstmt.setInt(6, score.getEng());
			pstmt.setInt(7, score.getSum());
			pstmt.setString(8, score.getGrade());
				
			// SQL문 실행
			pstmt.executeUpdate();
				
			// PreparedStatement 닫기
			pstmt.close();
		} catch(Exception e) {
			e.printStackTrace();
			exit();
		}
		mainMenu();
	}
	
	
	public void search() {
		System.out.println("[조회할 학생의 학번을 입력하세요.]");
		System.out.print("학번 : ");
		String stuNo = scanner.nextLine();
		
		/* 석차를 추가하고 싶었는데 석차가 1로만 나와서 추후에 해결 필요 */
		
		try {
			String sql = "" + "SELECT stuName, stuNo, stuBirth, kor, mat, eng, sum, sum/3, grade "
					+ "FROM students "
					+ "WHERE stuNo=?";
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, stuNo);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()) {
				score.setStuName(rs.getString("stuName"));
				score.setStuNo(rs.getString("stuNo"));
				score.setStuBirth(rs.getString("stuBirth"));
				score.setKor(rs.getInt("kor"));
				score.setMat(rs.getInt("mat"));
				score.setEng(rs.getInt("eng"));
				score.setSum(rs.getInt("sum"));
				score.setGrade(rs.getString("grade"));
				
				System.out.println();
				System.out.println(stuNo + " 학생을 조회했습니다.");
				System.out.println("이름 : " + score.getStuName());
				System.out.println("학번 : " + score.getStuNo());
				System.out.println("생년월일 : " + score.getStuBirth());
				System.out.println("국어 : " + score.getKor());
				System.out.println("수학 : " + score.getMat());
				System.out.println("영어 : " + score.getEng());
				System.out.println("합 : " + score.getSum());
				System.out.println("평균 : " + score.getSum() / 3);
				System.out.println("등급 : " + score.getGrade());
				System.out.println();

				// 보조 메뉴 출력
				System.out.println("--------------------------------------------");
				System.out.println("1.수정 | 2.삭제 | 3.메뉴로 돌아가기");
				System.out.print("메뉴 선택 : ");
				String menuNo = scanner.nextLine();
				System.out.println();
					
				if(menuNo.equals("1")) {
					update(score, stuNo);	
				} else if(menuNo.equals("2")) {
					delete(score);
				} else if(menuNo.equals("3")) {
					mainMenu();
				} else {
					System.out.println("1-3 중에서 입력해주세요.");
				}
			} else {
				System.out.println("존재하지 않습니다.");
			}
			rs.close();
			pstmt.close();
		} catch(Exception e) {
			e.printStackTrace();
			exit();
		}
		mainMenu();
	}
	
	
	public void update(Score score, String stuNo) {
		System.out.println("[수정]");
		System.out.print("이름 : ");
		score.setStuName(scanner.nextLine());
		System.out.print("학번 : ");
		score.setStuNo(scanner.nextLine());
		System.out.print("생년월일 : ");
		score.setStuBirth(scanner.nextLine());
		System.out.print("국어 : ");
		score.setKor(scanner.nextInt());
		System.out.print("수학 : ");
		score.setMat(scanner.nextInt());
		System.out.print("영어 : ");
		score.setEng(scanner.nextInt());
		
		// 합
		score.setSum(score.getKor() + score.getMat() + score.getEng());
		
		// 등급 산출
		if((score.getSum() / 3) >= 90) {
			score.setGrade("A");
		} else if((score.getSum() / 3) >= 80) {
			score.setGrade("B");
		} else if((score.getSum() / 3) >= 70) {
			score.setGrade("C");
		} else if((score.getSum() / 3) >= 60) {
			score.setGrade("D");
		} else {
			score.setGrade("F");
		}
		
		scanner.nextLine();
		/* Scanner.nextInt() 메소드가 사용자가 입력한 enter(개행문자) 를 제거하지 않음.
		 * 남겨진 개행문자가 다음 scan.nextLine()의 입력으로 처리되어 곧바로 개행됨
		 * 이를 방지하기 위해서 scanner.nextLine()을 작성하여 개행문자 제거 */
		
		try {
			String sql = "" + "UPDATE students SET stuName=?, stuNo=?, stuBirth=?, kor=?, mat=?, eng=?, sum=?, grade=? "
					+ "WHERE stuNo=?";
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, score.getStuName());
			pstmt.setString(2, score.getStuNo());
			pstmt.setString(3, score.getStuBirth());
			pstmt.setInt(4, score.getKor());
			pstmt.setInt(5, score.getMat());
			pstmt.setInt(6, score.getEng());
			pstmt.setInt(7, score.getSum());
			pstmt.setString(8, score.getGrade());
			pstmt.setString(9, stuNo);
			
			pstmt.executeUpdate();
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
			exit();
		}
		mainMenu();
	}
	
	
	public void delete(Score score) {
		try {
			String sql = "DELETE FROM students WHERE stuNo=?";
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, score.getStuNo());
			
			pstmt.executeUpdate();
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
			exit();
		}
		mainMenu();
	}
	
	
	public void clear() {
		System.out.println("입력된 모든 데이터를 삭제하시겠습니까?");
		System.out.println("--------------------------------------------");
		System.out.println("1.Ok | 2.Cancel");
		System.out.print("메뉴 선택 : ");
		String menuNo=scanner.nextLine();
		
		if(menuNo.equals("1")) {
			// 전체 삭제
			try {
				String sql = "TRUNCATE TABLE students";
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.executeUpdate();
				pstmt.close();
			} catch(Exception e) {
				e.printStackTrace();
				exit();
			}
		}
		// 메인 메뉴 메소드로 이동
		mainMenu();
	}
	
	
	public void read() {
		System.out.println();
		System.out.println("1.이름순 | 2.성적순 | 3.학번순 | 4.취소");
		System.out.print("메뉴 선택 : ");
		String menuNo = scanner.nextLine();
		
		switch(menuNo) {
			case "1" -> read_name();
			case "2" -> read_score();
			case "3" -> read_No();
			case "4" -> mainMenu();
		}
	}
	
	
	public void read_name() {
		// 이름순
		
		System.out.println();
		System.out.println("[이름순 조회]");
		System.out.println(" 이름 | 학번 | 생년월일 | 국어 | 수학 | 영어 | 합 | 평균 | 등급 | 석차");
		System.out.println("-----------------------------------------------------------");
		
		try {
			String sql = "" + "SELECT stuName, stuNo, stuBirth, kor, mat, eng, sum, (sum/3), grade, "
					+ "ROW_NUMBER() OVER (ORDER BY sum DESC) AS RANK "
					+ "FROM students "
					+ "ORDER BY stuName";
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()) {
				score.setStuName(rs.getString("stuName"));
				score.setStuNo(rs.getString("stuNo"));
				score.setStuBirth(rs.getString("stuBirth"));
				score.setKor(rs.getInt("kor"));
				score.setMat(rs.getInt("mat"));
				score.setEng(rs.getInt("eng"));
				score.setSum(rs.getInt("sum"));
				score.setGrade(rs.getString("grade"));
				score.setRank(rs.getInt("RANK"));
				System.out.printf(" %-4s %-4s  %-6s   %-3d   %-3d   %-3d  %-3d  %-3.2f   %s   %d \n", score.getStuName(), score.getStuNo(), score.getStuBirth(), score.getKor(), score.getMat(), score.getEng(), score.getSum(), (double)(score.getSum()/3), score.getGrade(), score.getRank());
			}
			rs.close();
			pstmt.close();
		} catch(SQLException e) {
			e.printStackTrace();
			exit();
		}
		mainMenu();
	}
	
	
	public void read_score() {
		// 성적순
		
		System.out.println();
		System.out.println("[성적순 조회]");
		System.out.println(" 이름 | 학번 | 생년월일 | 국어 | 수학 | 영어 | 합 | 평균 | 등급 | 석차");
		System.out.println("-----------------------------------------------------------");
		
		try {
			String sql = "" + "SELECT stuName, stuNo, stuBirth, kor, mat, eng, sum, (sum/3), grade, "
					+ "ROW_NUMBER() OVER (ORDER BY sum DESC) AS RANK "
					+ "FROM students";
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()) {
				score.setStuName(rs.getString("stuName"));
				score.setStuNo(rs.getString("stuNo"));
				score.setStuBirth(rs.getString("stuBirth"));
				score.setKor(rs.getInt("kor"));
				score.setMat(rs.getInt("mat"));
				score.setEng(rs.getInt("eng"));
				score.setSum(rs.getInt("sum"));
				score.setGrade(rs.getString("grade"));
				score.setRank(rs.getInt("RANK"));
				System.out.printf(" %-4s %-4s  %-6s   %-3d   %-3d   %-3d  %-3d  %-3.2f   %s   %d \n", score.getStuName(), score.getStuNo(), score.getStuBirth(), score.getKor(), score.getMat(), score.getEng(), score.getSum(), (double)(score.getSum()/3), score.getGrade(), score.getRank());
			}
			rs.close();
			pstmt.close();
		} catch(SQLException e) {
			e.printStackTrace();
			exit();
		}
		mainMenu();
	}
	
	
	public void read_No() {
		// 학번순
		
		System.out.println();
		System.out.println("[성적순 조회]");
		System.out.println(" 이름 | 학번 | 생년월일 | 국어 | 수학 | 영어 | 합 | 평균 | 등급 | 석차");
		System.out.println("-----------------------------------------------------------");
		
		try {
			String sql = "" + "SELECT stuName, stuNo, stuBirth, kor, mat, eng, sum, (sum/3), grade, "
					+ "ROW_NUMBER() OVER (ORDER BY sum DESC) AS RANK "
					+ "FROM students "
					+ "ORDER BY stuNo";
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()) {
				score.setStuName(rs.getString("stuName"));
				score.setStuNo(rs.getString("stuNo"));
				score.setStuBirth(rs.getString("stuBirth"));
				score.setKor(rs.getInt("kor"));
				score.setMat(rs.getInt("mat"));
				score.setEng(rs.getInt("eng"));
				score.setSum(rs.getInt("sum"));
				score.setGrade(rs.getString("grade"));
				score.setRank(rs.getInt("RANK"));
				System.out.printf(" %-4s %-4s  %-6s   %-3d   %-3d   %-3d  %-3d  %-3.2f   %s   %d \n", score.getStuName(), score.getStuNo(), score.getStuBirth(), score.getKor(), score.getMat(), score.getEng(), score.getSum(), (double)(score.getSum()/3), score.getGrade(), score.getRank());
			}
			rs.close();
			pstmt.close();
		} catch(SQLException e) {
			e.printStackTrace();
			exit();
		}
		mainMenu();
	}
	
	
	public void exit() {
		if(conn != null) {
			try {
				conn.close();
			} catch(SQLException e) {}
		}
		System.out.println("종료합니다.");
		System.exit(0);
	}
}

 

 

project_score.zip
0.03MB

 

https://github.com/wlgus011221/java_score

 

GitHub - wlgus011221/java_score

Contribute to wlgus011221/java_score development by creating an account on GitHub.

github.com

 

728x90
반응형
저작자표시 (새창열림)

'Project > 성적 관리 프로그램' 카테고리의 다른 글

[JAVA] 성적 관리 프로그램 - 5 학생 로그인 / 조회  (2) 2023.08.13
[JAVA] 성적 관리 프로그램 - 4 입력 / 조회 / 수정 / 삭제 / 전체 삭제 / (이름순, 성적순, 학번순) 집계표  (1) 2023.08.12
[JAVA] 성적 관리 프로그램 - 3 교수 로그인 / 회원가입  (0) 2023.08.11
[JAVA] 성적 관리 프로그램 - 2 DB 테이블 구성  (1) 2023.08.10
[JAVA] 자바 성적 관리 프로그램 만들기 - 1 개요  (0) 2023.07.27

'Project/성적 관리 프로그램' Related Articles

티스토리툴바