package BTSinhVien;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;


public class Execute {

    public static Connection getConn () {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String dbUserName = "root";
            String dbPassword = "";
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:8306/NhanVienDB?useUnicode=yes&characterEncoding=UTF-8", dbUserName, dbPassword);
            return conn;
        }
        catch (Exception e) {
            System.out.println ("Lỗi! " + e.getMessage() + "\n---");
            return null;
        }
    }
    public static void dsLop (Connection conn) {
        try {
            Statement stmt = conn.createStatement();
            String sql = "SELECT * FROM Lop";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String malop = rs.getString("MaLop");
                String tenlop = rs.getString("TenLop");
                System.out.println ("Mã lop: " + malop + "; Tên Lop: " + tenlop);
            }
        }
        catch (Exception e) {
            System.out.println (e.getMessage());
        }
    }
    public static void dsSinhVien (Connection conn) {
        try {
            Statement stmt = conn.createStatement();
            String sql = "SELECT * FROM SinhVien";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String manv = rs.getString("MaSV");
                String hoten = rs.getString("TenSV");
                String ngaysinh = rs.getString("NgaySinh");
                String gioitinh = rs.getString("GioiTinh");
                System.out.println (manv + "; Tên: " + hoten + "; Ngày sinh: " + ngaysinh + "; Giới tính: " + gioitinh);
            }
        }
        catch (Exception e) {
            System.out.println (e.getMessage());
        }
    }
    public static void nhapLop (Connection conn) {
        Scanner scan = new Scanner (System.in);
        while (true) {
            System.out.println("---\nTên lớp: ");
            String tenlop = scan.nextLine();
            try {
                Statement stmt = conn.createStatement();
                String sql = String.format("INSERT INTO Lop (TenLop) VALUES ('%s')", tenlop);
                int n = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
                if (n > 0) {
                    System.out.println ("Thêm thành công!");
                    ResultSet rs = stmt.getGeneratedKeys();
                    if (rs.next())
                        System.out.println ("Mã lớp mới có STT là: " + rs.getInt(1));
                }
                else {
                    System.out.println ("Lỗi!");
                }
            }
            catch (Exception e) {
                System.out.println (e.getMessage());
            }
            finally {
                System.out.println ("Tiếp tục? (y/n): ");
                String out = scan.nextLine();
                if (!out.equals("y")) {
                    break;
                }
            }
        }

    }
    public static void nhapSinhVien (Connection conn) {
        Scanner scan = new Scanner (System.in);
        while (true) {
            System.out.println("---\nMã sinh viên: ");
            String manv = scan.nextLine();
            System.out.println("Tên sinh viên: ");
            String tennv = scan.nextLine();
            System.out.println("Ngày sinh: ");
            String ngaysinh = scan.nextLine();
            System.out.println("Giới tính: ");
            String gioitinh = scan.nextLine();
            System.out.println("Mã lớp: ");
            String malop = scan.nextLine();
            try {
                Statement stmt = conn.createStatement();
                String sql = String.format("INSERT INTO SinhVien VALUES ('%s', '%s', '%s', '%s', '%s')", manv, tennv, ngaysinh, gioitinh, malop);
                int n = stmt.executeUpdate(sql);
                if (n==1) {
                    System.out.println ("Thêm thành công!");
                }
                else {
                    System.out.println ("Lỗi!");
                }
            }
            catch (Exception e) {
                System.out.println (e.getMessage());
            }
            finally {
                System.out.println ("Tiếp tục? (y/n): ");
                String out = scan.nextLine();
                if (!out.equals("y")) {
                    break;
                }
            }
        }

    }
    public static void capNhatSVLop (Connection conn) {
        Scanner scan = new Scanner (System.in);
        while (true) {
            System.out.println("---\nMã sinh viên cần cập nhật lớp: ");
            String masv = scan.nextLine();
            System.out.println("Mã lớp mới: ");
            int malop = Integer.parseInt(scan.nextLine());
            try {
                Statement stmt = conn.createStatement();
                String sql = String.format("UPDATE SinhVien SET MaLop = %d WHERE MaSV = '%s'", malop, masv);
                int n = stmt.executeUpdate(sql);
                if (n>0) {
                    System.out.println ("Cập nhật thành công!");
                }
                else {
                    System.out.println ("Lỗi!");
                }
            }
            catch (Exception e) {
                System.out.println (e.getMessage());
            }
            finally {
                System.out.println ("Tiếp tục? (y/n): ");
                String out = scan.nextLine();
                if (!out.equals("y")) {
                    break;
                }
            }
        }
    }
    public static void xoaSVtheoMaSV (Connection conn) {
        Scanner scan = new Scanner (System.in);
        while (true) {
            System.out.println("---\nMã sinh viên cần xóa: ");
            String masv = scan.nextLine();
            try {
                Statement stmt = conn.createStatement();
                String sql = String.format("DELETE FROM SinhVien WHERE MaSV = '%s'", masv);
                int n = stmt.executeUpdate(sql);
                if (n>0) {
                    System.out.println ("Xóa thành công!");
                }
                else {
                    System.out.println ("Lỗi!");
                }
            }
            catch (Exception e) {
                System.out.println (e.getMessage());
            }
            finally {
                System.out.println ("Tiếp tục? (y/n): ");
                String out = scan.nextLine();
                if (!out.equals("y")) {
                    break;
                }
            }
        }
    }
    public static void dsSinhVienTheoLop (Connection conn) {
        Scanner scan = new Scanner (System.in);
        while (true) {
            System.out.println("---\nMã lớp: ");
            String malop = scan.nextLine();
            try {
                Statement stmt = conn.createStatement();
                String sql = String.format("SELECT * FROM SinhVien WHERE MaLop = '%s'", malop);
                ResultSet rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    String tensv = rs.getString("TenSV");
                    System.out.println ("Ten SV: " + tensv);
                }
            }
            catch (Exception e) {
                System.out.println (e.getMessage());
            }
            finally {
                System.out.println ("Tiếp tục? (y/n): ");
                String out = scan.nextLine();
                if (!out.equals("y")) {
                    break;
                }
            }
        }
    }
    public static void main(String[] args) {
        // TODO code application logic here
        Scanner scan = new Scanner (System.in);
        Connection conn = getConn();
        dsLop (conn);
        dsSinhVien (conn);
}