DB- 실습03

2021-12-22

Student class

public class Student {
	private String sno;
	private String sname;
	private String sdept;
	
	public String getSno() {
		return sno;
	}
	public void setSno(String sno) {
		this.sno = sno;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public String getSdept() {
		return sdept;
	}
	public void setSdept(String sdept) {
		this.sdept = sdept;
	}
	@Override
	public String toString() {
		return "Student [sno=" + sno + ", sname=" + sname + ", sdept=" + sdept + "]";
	}
}

StudentDAO class

public class StudentDAO implements Studentinterface{

	@Override
	public ArrayList<Student> list() {
		ArrayList<Student> array = new ArrayList<>();
		try {
			String sql = "select * from tbl_student";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {  //rs에 next값을 하나씩 가져와서 true인동안 반복
				Student vo = new Student();
				vo.setSno(rs.getString("sno"));
				vo.setSname(rs.getString("sname"));
				vo.setSdept(rs.getString("sdept"));
				array.add(vo);
			}
		}catch(Exception e) {
			System.out.println("student list 오류 :"+ e.toString());
		}
		return array;
	}

	@Override
	public void insert(Student vo) {
		try {
			//마지막학번구하기
			String sql = "select max(sno) mno from tbl_student";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				String mno = rs.getString("mno");
				int sno =Integer.parseInt(mno)+1;
				vo.setSno(Integer.toString(sno));
			}
			sql= "insert into tbl_student(sno,sname,sdept) values(?,?,?)";
			ps = Database.CON.prepareStatement(sql);
			ps.setString(1, vo.getSno());
			ps.setString(2, vo.getSname());
			ps.setString(3, vo.getSdept());
			ps.execute();
			System.out.println("학생등록완료!");
		}catch(Exception e) {
			System.out.println("student insert 오류 :"+ e.toString());
		}
	}

	@Override
	public Student read(String sno) {
		Student vo = new Student();
		try {
			String sql ="select * from tbl_student where sno=?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, sno);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				vo.setSno(rs.getString("sno"));
				vo.setSname(rs.getString("sname"));
				vo.setSdept(rs.getString("sdept"));
			}
			
		}catch(Exception e) {
			System.out.println("student read 오류 :"+ e.toString());
		}
		return vo;
	}

	@Override
	public void update(Student vo) {
		try {
			String sql ="update tbl_student set sname=?,sdept=? where sno=?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, vo.getSname());
			ps.setString(2, vo.getSdept());
			ps.setString(3, vo.getSno());
			ps.execute();
			System.out.println("학생정보수정완료");
		}catch(Exception e) {
			System.out.println("student read 오류 :"+ e.toString());
		}
	}
}

Score class

public class Score {
	private String sno;
	private String sdate;
	private int kor;
	private int eng;
	private int mat;
	private int tot;
	private double avg;
	
	
	public String getSno() {
		return sno;
	}
	public void setSno(String sno) {
		this.sno = sno;
	}
	public String getSdate() {
		return sdate;
	}
	public void setSdate(String sdate) {
		this.sdate = sdate;
	}
	public int getKor() {
		return kor;
	}
	public void setKor(int kor) {
		this.kor = kor;
	}
	public int getEng() {
		return eng;
	}
	public void setEng(int eng) {
		this.eng = eng;
	}
	public int getTot() {
		return tot;
	}
	public void setTot(int tot) {
		this.tot = tot;
	}
	public double getAvg() {
		return avg;
	}
	public void setAvg(double avg) {
		this.avg = avg;
	}
	public int getMat() {
		return mat;
	}
	public void setMat(int mat) {
		this.mat = mat;
	}
	@Override
	public String toString() {
		return "Score [sno=" + sno + ", sdate=" + sdate + ", kor=" + kor + ", eng=" + eng + ", mat=" + mat + ", tot="
				+ tot + ", avg=" + avg + "]";
	}	
}

ScoreDAO class

public class ScoreDAO {
	
	
	//성적목록
	public ArrayList<Score> list(String sno) {
		ArrayList<Score> array = new ArrayList<>(); 
		try {
			String sql = "select * from view_score where sno = ?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, sno);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				Score vo = new Score();
				vo.setSno(rs.getString("sno"));
				vo.setSdate(rs.getString("sdate"));
				vo.setKor(rs.getInt("kor"));
				vo.setEng(rs.getInt("eng"));
				vo.setMat(rs.getInt("mat"));
				vo.setTot(rs.getInt("tot"));
				vo.setAvg(rs.getInt("av"));
				array.add(vo);
			}
			
		}catch(Exception e) {
			System.out.println("score list 오류:" + e.toString());
		}
		  
		return array;
	}
	
	//성적등록
	public boolean insert(Score vo) {
		boolean success = false;
		try {
			String sql = "insert into tbl_score(sno,sdate,kor,eng,mat) values(?,?,?,?,?)";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, vo.getSno());
			ps.setString(2, vo.getSdate());
			ps.setInt(3, vo.getKor());
			ps.setInt(4, vo.getEng());
			ps.setInt(5, vo.getMat());
			ps.execute();
			success=true;
		}catch(Exception e) {
			System.out.println("score insert 오류:" + e.toString());
		}
		return success;
	}
	
	//성적삭제
	public boolean delete(String sno, String sdate) {
		boolean success= false;
		try {
			//시험 데이타가 있는지 체크
			String sql = "select * from tbl_score where sno=? and sdate=?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, sno);
			ps.setString(2, sdate);
			ResultSet  rs=ps.executeQuery();
			if(rs.next()) {
				sql ="delete from tbl_score where sno=? and sdate=?";
				ps = Database.CON.prepareStatement(sql);
				ps.setString(1, sno);
				ps.setString(2, sdate);
				ps.execute();
				success=true;
			}
		}catch(Exception e) {
			System.out.println("score insert 오류:" + e.toString());
	}
		return success;
	}
}

Main

import java.sql.Connection;
import java.text.*;
import java.util.*;

public class Main {
	public static void main(String[] args) {
//		Connection CON=Database.CON;
		Scanner s = new Scanner(System.in);
		boolean run =true;
		StudentDAO dao = new StudentDAO();
		ScoreDAO sdao = new ScoreDAO();
		Student vo = new Student();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		DecimalFormat df = new DecimalFormat("#,###");
			while(run) {
				System.out.println("");
				System.out.println("┌──────────────────────────────────────────────────────────────────────┐");
				System.out.print("│1.학생등록  ");
				System.out.print("2.학생목록  ");
				System.out.print("3.학생검색  ");
				System.out.print("4.학생수정  ");
				System.out.print("5.성적등록  ");
				System.out.print("6.성적현황  ");
				System.out.print("7.성적삭제  ");
				System.out.print("0.종료    │\n");
				System.out.println("└──────────────────────────────────────────────────────────────────────┘");
				System.out.print("선택>");
				String menu = s.nextLine();
				switch(menu) {
				case "1": 
					System.out.print("성명>"); vo.setSname(s.nextLine());;
					System.out.print("학과>"); vo.setSdept(s.nextLine());;
					dao.insert(vo);
					break;
				case "2":
					ArrayList<Student> array = dao.list();
					for(Student a:array) {
						System.out.printf("%s\t%s\t%s\n",
								a.getSno(), a.getSname(),a.getSdept());
					}
					break;
				case "3":
					System.out.print("검색할 학번 >"); String sno = s.nextLine();
					vo=dao.read(sno);
					if(vo.getSname()==null) {
						System.out.println("등록된 학생이 없습니다!");
					}else {
						System.out.println("성명:" +vo.getSname());
						System.out.println("학과:" + vo.getSdept());
					}
					break;
				case "4":
					System.out.print("검색할 학번 >"); sno = s.nextLine();
					vo=dao.read(sno);
					if(vo.getSname()==null) {
						System.out.println("등록된 학생이 없습니다!");
					}else {
						System.out.print("성명:" +vo.getSname()+">");
						String sname = s.nextLine();
						if(!sname.equals("")) vo.setSname(sname);
						System.out.print("학과:" +vo.getSdept()+">");
						String sdept = s.nextLine();
						if(!sdept.equals("")) vo.setSdept(sdept);
						System.out.print(vo.toString()+"으로 수정하실래요? (y)");
						String sel=s.nextLine();
						if(sel.equals("y")||sel.equals("ㅛ")) {
							dao.update(vo);
						}
					}
					break;
				case "5":
					Score v= new Score();
					System.out.print(" 학번 >");sno = s.nextLine();
					v.setSno(sno);
					vo=dao.read(sno);
					if(vo.getSname()==null) {
						System.out.println("등록된 학생이 없습니다!");
					}else {
						System.out.println("성명:"+vo.getSname());
						System.out.println("학과:"+vo.getSdept());
						String today = sdf.format(new Date());
						System.out.print("시험일:" +today +">");
						String date =s.nextLine();
						
						if(!date.equals("")) {
							v.setSdate(date);
						}else {
							v.setSdate(today);
						}
						System.out.print("국어>"); 
						String kor=s.nextLine();
						v.setKor(Integer.parseInt(kor));
						
						System.out.print("영어>"); 
						String eng=s.nextLine();
						v.setEng(Integer.parseInt(eng));
						
						System.out.print("수학>"); 
						String mat=s.nextLine();
						v.setMat(Integer.parseInt(mat));
						
						boolean result = sdao.insert(v);
						if(result == true) {
							System.out.println("성적등록완료!");
						}						
					}
					break;
				case "6":
					System.out.print("학번>"); sno=s.nextLine();
					vo=dao.read(sno);
					if(vo.getSname()==null) {
						System.out.println("등록된 학생이 없습니다!");
					}else {
						System.out.println("성명:" +vo.getSname());
						System.out.println("학과:" + vo.getSdept());
						ArrayList<Score> sarray = sdao.list(sno);
						if(sarray.size() ==0) {
							System.out.println("성적 데이터가 없습니다.");
						}else {
							for(Score c:sarray) {
								System.out.printf("%s\t%d\t%d\t%d\t%d\t%.2f\n",
										c.getSdate().substring(0,10), c.getKor(), c.getEng()
										, c.getMat(), c.getTot(), c.getAvg());								
							}
						}
					}
					break;
				case "7":
					System.out.print("검색할 학번 >"); sno = s.nextLine();
					vo=dao.read(sno);
					if(vo.getSname()==null) {
						System.out.println("등록된 학생이 없습니다!");
					}else {
						System.out.println("성명:" +vo.getSname());
						System.out.println("학과:" + vo.getSdept());
						vo=dao.read(sno);
						if(vo.getSname()==null) {
							System.out.println("등록된 학생이 없습니다!");
						}else {
							System.out.println("성명:" +vo.getSname());
							System.out.println("학과:" + vo.getSdept());
							ArrayList<Score> sarray = sdao.list(sno);
							if(sarray.size() ==0) {
								System.out.println("성적 데이터가 없습니다.");
							}else {
								for(Score c:sarray) {
									System.out.printf("%s\t%d\t%d\t%d\t%d\t%.2f\n",
											c.getSdate().substring(0,10), c.getKor(), c.getEng()
											, c.getMat(), c.getTot(), c.getAvg());
									
								}
							}
							System.out.println("────────────────────────────────");
						}
						System.out.print("삭제일>");
						String date = s.nextLine();
						boolean result = sdao.delete(sno, date);
						if(result ==true) {
							System.out.println("성적데이터삭제완료!");
						}else {
							System.out.println("성적데이터삭제실패!");
						}
					}
					
					break;
				case "0":
					run = false;
					break;
				default:
					System.out.println("0~7번 메뉴를 선택하세요!");
				}
			}
			System.out.println("프로그램종료!");
	}
}

실습 02번과 비슷한 유형의 문제이다.

Twitter Facebook LinkedIn

KangKkaem

DB- 실습03

Score class

ScoreDAO class

공유하기

댓글남기기

참고

CSS - Grid

CSS - Flex

[오류]Several ports (8005, 8080, 8009) required by Tomcat v7.0 Server at localhost are already in use.

git - github에 파일올리고 내려받기