JDBC(영어 단어장, 학생 성적 관리 프로그램 )

영어 단어장

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

public class VocaMain {
    public static void main(String[] args) {

        Scanner sc = new Scanner(System.in);
        VocaDAO vocaDAO = new VocaDAO();

        while(true){
            System.out.println("        📚📚📚📚📚 단어장 📚📚📚📚📚");
            System.out.println("원하는 메뉴를 선택하세요");
            System.out.println("1. 등록 2. 리스트 3. 검색 4. 수정 5 삭제 6. 종류");
            int input  = sc.nextInt();
            if(input == 6){
                System.out.println("프로그램을 종료합니다.");
                break;
            }
            try {
                switch (input) {
                    case 1:
                        vocaDAO.insert();
                        break;
                    case 2:
                        vocaDAO.list();
                        break;
                    case 3:
                        vocaDAO.search();
                        break;
                    case 4:
                        vocaDAO.edit();
                        break;
                    case 5:
                        vocaDAO.delete();
                        break;
                }
            }catch (ClassNotFoundException e){
                e.printStackTrace();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class VocaDAO {
    public void insert() throws ClassNotFoundException, SQLException {// 메소드를 호출한 곳에서 책임전가 -> 메인 트라이 캐치
        Scanner sc = new Scanner(System.in);
        System.out.println("🧶단어를 입력하세요 : ");
        String eng = sc.next();
        System.out.println("🧶뜻 입력하세요 : ");
        String kor = sc.next();
        System.out.println("🧶레벨을 입력하세요 : ");
        int level = sc.nextInt();

        Connection conn = Dbconn.getConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("insert into tb_word(eng,kor,lev)")
                .append("values(?,?,?)");
        PreparedStatement pstmt = conn.prepareStatement(sql.toString());
        pstmt.setString(1, eng);
        pstmt.setString(2, kor);
        pstmt.setInt(3, level);
        int result = pstmt.executeUpdate();
        if (result >= 1) System.out.println("단어등록 성공! ");
        else System.out.println("단어등록 실패! ");

    }

    public void list() throws ClassNotFoundException, SQLException {
        Connection conn = Dbconn.getConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("select eng, kor, lev, rdate from tb_word order by eng");
        PreparedStatement pstmt = conn.prepareStatement(sql.toString());
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            String eng = rs.getString("eng");
            String kor = rs.getString("kor");
            int lev = rs.getInt("lev");
            String rdate = rs.getString("rdate");
            System.out.println(eng + ": " + kor + "(" + lev + ", " + rdate + ")");
        }
    }

    public void search()throws  ClassNotFoundException, SQLException{
        Scanner sc = new Scanner(System.in);
        System.out.println("🧶검색할 단어를 입력하세요 : ");
        String word = sc.next();

        boolean isFind = false;
        Connection conn = Dbconn.getConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("select eng, kor, lev, rdate from tb_word where eng like ?");
        PreparedStatement pstmt = conn.prepareStatement(sql.toString());
        pstmt.setString(1,"%"+word+"%");
        ResultSet rs = pstmt.executeQuery();

        while (rs.next()){ // 있어야 돈다 rs.next()
            isFind = true;
            String eng = rs.getString("eng");
            String kor = rs.getString("kor");
            int lev = rs.getInt("lev");
            String rdate = rs.getString("rdate");
            System.out.println(eng + ": " + kor + "(" + lev + ", " + rdate + ")");
        }
        if(!isFind) System.out.println("찾는 단어가 없습니다.😢😢😢");
    }
    public void edit() throws ClassNotFoundException, SQLException {
        // 수정할 단어를 입력하세요 : apple
        // 단어의 뜻을 입력하세요 : 사과
        // 레벨을 입력하세요 : 2
        Scanner sc = new Scanner(System.in);
        System.out.println("수정할 단어를 입력하세요");
        String eng = sc.next();
        System.out.println("단어의 뜻을 입력하세요");
        String kor = sc.next();
        System.out.println("레벨을 입력하세요");
        int lev = sc.nextInt();

        Connection conn = Dbconn.getConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("update tb_word set kor =?, lev =? where eng =?");

        PreparedStatement pstmt = conn.prepareStatement(sql.toString());

        pstmt.setString(1, kor);
        pstmt.setInt(2, lev);
        pstmt.setString(3, eng);
        int result = pstmt.executeUpdate();
        if (result >= 1) System.out.println("단어장 성공");
        else System.out.println("찾는 단어가 없습니다.");
    }
    public void delete() throws ClassNotFoundException, SQLException {
        // 삭제할 단어를 입력하세요
        Scanner sc = new Scanner(System.in);
        System.out.println("삭제할 단어를 입력하세요");
        String eng = sc.next();
        boolean isFind = false;
        Connection conn = Dbconn.getConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("delete  from tb_word where eng = ?");
        PreparedStatement pstmt = conn.prepareStatement(sql.toString());
        pstmt.setString(1,eng);
        int result = pstmt.executeUpdate();
        if (result >= 1) System.out.println("단어삭제 성공");
        else System.out.println("단어삭제 실패");

    }

}

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

public class Dbconn {
    private static Connection conn; // 주요하게 생각하는것을 컨넥션 객체를 사용할 수 있게
    //스테틱이 있으면 클래스만 불러도 된다 메소드 호출시
    public static Connection getConnection() throws SQLException, ClassNotFoundException {// 리턴타입은 Connection
        String url = "jdbc:mysql://127.0.0.1/aidev?useSSL=false";
        String uid = "root";
        String upw = "1234";

        Class.forName("com.mysql.cj.jdbc.Driver");
         conn = DriverManager.getConnection(url, uid, upw);
        return conn;
    }
}

학생 성적 관리 프로그램

과제.
테이블 생성
학생 테이블
학번, 이름, 연락처, 성별, 주소, 등록날짜
- 학번은 primary key 등록
성적 테이블
학번, 국어점수, 수학점수, 등록날짜
- 학번은 외래키로 등록

*********** 메뉴 **********
1. 학생 등록
2. 학생 리스트
3. 학생 수정
4. 학생 삭제
5. 성적 등록
    성적을 등록할 학번을 입력하세요. 00000001
    국어점수를 입력하세요
    수학점수를 입력하세요
    영어점수를 입력하세요
6. 성적 보기 - 조인이 되어야 볼 수 있음
    학번 이름 국어점수 수학점수 영어점수 총점 평균
7. 프로그램 종료

 

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

public class ScoreMain {
    public static void main(String[] args) {

        Scanner sc = new Scanner(System.in);
        ScoreDAO scoreDAO = new ScoreDAO();

        while(true) {
            System.out.println("        📚📚📚📚📚 메뉴 📚📚📚📚📚");
            System.out.println("원하는 메뉴를 선택하세요");
            System.out.println("1. 등록 2. 리스트 3. 수정 4. 삭제 5 성적등록 6. 성적보기 7. 종료");
            int input = sc.nextInt();
            if (input == 7) {
                System.out.println("프로그램을 종료합니다.");
                break;
            }
            try {
                switch (input) {
                    case 1:
                        scoreDAO.insert();
                        break;
                    case 2:
                        scoreDAO.list();
                        break;
                    case 3:
                        scoreDAO.edit();
                        break;
                    case 4:
                        scoreDAO.delete();
                        break;
                    case 5:
                        scoreDAO.score();
                        break;
                    case 6:
                        scoreDAO.scoreList();
                        break;

                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

 

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class ScoreDAO {
    public void insert() throws ClassNotFoundException, SQLException {// 메소드를 호출한 곳에서 책임전가 -> 메인 트라이 캐치
        Scanner sc = new Scanner(System.in);
        System.out.println("🧶학번을 입력하세요 : ");
        String st_id = sc.next();
        System.out.println("🧶이름을 입력하세요 : ");
        String st_name = sc.next();
        System.out.println("🧶연락처를 입력하세요 : ");
        String st_hp = sc.next();
        System.out.println("🧶성별을 입력하세요 : ");
        String st_gender = sc.next();
        System.out.println("🧶주소를 입력하세요 : ");
        String st_address = sc.next();


        Connection conn = Dbconn.getConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("insert into tb_student(st_id,st_name,st_hp,st_gender,st_address)")
                .append("values(?,?,?,?,?)");
        PreparedStatement pstmt = conn.prepareStatement(sql.toString());
        pstmt.setString(1, st_id);
        pstmt.setString(2, st_name);
        pstmt.setString(3, st_hp);
        pstmt.setString(4, st_gender);
        pstmt.setString(5, st_address);
        int result = pstmt.executeUpdate();
        if (result >= 1) System.out.println("단어등록 성공! ");
        else System.out.println("단어등록 실패! ");

    }


    public void list() throws ClassNotFoundException, SQLException {
        Connection conn = Dbconn.getConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("select st_id, st_name, st_hp, st_gender, st_address,st_rdate from tb_student order by st_id");
        PreparedStatement pstmt = conn.prepareStatement(sql.toString());
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            String st_id = rs.getString("st_id");
            String st_name = rs.getString("st_name");
            String st_hp = rs.getString("st_hp");
            String st_gender = rs.getString("st_gender");
            String st_address = rs.getString("st_address");
            String st_rdate = rs.getString("st_rdate");
            System.out.println(st_id + ": " + st_name +  ": " + st_hp +
                    ": " + st_gender +  ": "+st_address+": "+st_rdate);
        }
    }


    public void edit() throws ClassNotFoundException, SQLException {

        Scanner sc = new Scanner(System.in);
        System.out.println("수정할 학번을 입력하세요");
        String st_id = sc.next();
        System.out.println("변경 연락처 입력하세요");
        String st_hp = sc.next();
        System.out.println("변경 주소 입력하세요");
        String st_address = sc.next();

        Connection conn = Dbconn.getConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("update tb_student set st_hp =?, st_address =? where st_id =?");

        PreparedStatement pstmt = conn.prepareStatement(sql.toString());

        pstmt.setString(1, st_hp);
        pstmt.setString(2, st_address);
        pstmt.setString(3, st_id);
        int result = pstmt.executeUpdate();
        if (result >= 1) System.out.println("학생 변경 성공");
        else System.out.println("해당 학생이 없습니다.");
    }

    public void delete() throws ClassNotFoundException, SQLException {
        // 삭제할 단어를 입력하세요
        Scanner sc = new Scanner(System.in);
        System.out.println("삭제할 학번을 입력하세요");
        String st_id = sc.next();
        boolean isFind = false;
        Connection conn = Dbconn.getConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("delete  from tb_student where st_id = ?");
        PreparedStatement pstmt = conn.prepareStatement(sql.toString());
        pstmt.setString(1,st_id);
        int result = pstmt.executeUpdate();
        if (result >= 1) System.out.println("단어삭제 성공");
        else System.out.println("단어삭제 실패");

    }
    public void score() throws ClassNotFoundException, SQLException{
        Scanner sc = new Scanner(System.in);
        System.out.println("성적을 등록할 학번을 입력하세요 : ");
        String sc_id = sc.next();
        System.out.println("국어점수를 입력하세요");
        int sc_kor = sc.nextInt();
        System.out.println("수학점수를 입력하세요");
        int sc_math = sc.nextInt();
        System.out.println("영어점수를 입력하세요");
        int sc_eng = sc.nextInt();

        int sc_sum = sc_kor +sc_eng+sc_math;
        int sc_avg = (sc_kor +sc_eng+sc_math)/3;

        Connection conn = Dbconn.getConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("insert into tb_score(sc_id,sc_kor,sc_math,sc_eng,sc_sum,sc_avg)")
                .append("values(?,?,?,?,?,?)");
        PreparedStatement pstmt = conn.prepareStatement(sql.toString());

        pstmt.setString(1, sc_id);
        pstmt.setInt(2, sc_kor);
        pstmt.setInt(3, sc_math);
        pstmt.setInt(4, sc_eng);
        pstmt.setInt(5, sc_sum);
        pstmt.setInt(6, sc_avg);
        int result = pstmt.executeUpdate();
        if (result >= 1) System.out.println("점수등록 성공! ");
        else System.out.println("점수등록 실패! ");

    }

    public void scoreList() throws ClassNotFoundException, SQLException{
        Connection conn = Dbconn.getConnection();
        StringBuilder sql = new StringBuilder();
        System.out.println("성적보기");
        sql.append("select st_id, st_name, sc_kor,sc_math,sc_eng,sc_avg,sc_sum  \n" +
                " from tb_student left join tb_score on tb_student.st_id = tb_score.sc_id;");
        PreparedStatement pstmt = conn.prepareStatement(sql.toString());
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            String st_id = rs.getString("st_id");
            String st_name = rs.getString("st_name");
            int sc_kor = rs.getInt("sc_kor");
            int sc_math = rs.getInt("sc_math");
            int sc_eng = rs.getInt("sc_eng");
            int sc_avg = rs.getInt("sc_avg");
            int sc_sum = rs.getInt("sc_sum");
            System.out.println(" 아이디 : "+st_id + " 이름 : " + st_name +  " 국어 : " + sc_kor +
                    "수학 : " + sc_math +  "영어 : "+sc_eng+" 평균 : "+sc_avg+" 합계: "+sc_sum);
        }
    }

}

학생 테이블

#mysql
create table tb_student(
   st_id varchar(50) primary key,
    st_name varchar(50) unique not null,
    st_hp varchar(50) not null,
    st_gender varchar(50),
    st_address varchar(50),
    st_rdate datetime default now()
);
create table tb_score(
    sc_id varchar(50) not null,
   sc_kor int ,
    sc_math int ,
    sc_eng int,
    sc_rdate datetime default now(),
    sc_sum int,
    sc_avg int,
    foreign key(sc_id) references tb_student(st_id)
);