package studentmanagement;
//部分數據庫的連接
import java.sql.*;
import java.io.*;
import sun.jdbc.odbc.JdbcOdbcDriver;


public interface useData {
    public int checknum(String num);
    public int addNew(String name, String num, String sex, String addr,
                      String tel);
    public int delete(String num);
}


class Data implements useData {
    static String madguy =
            "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=";
    Connection con;
    String filename;

    public Data(File file) throws Error {
        filename = file.getAbsolutePath();
        prepare_connect();
    }

    public void prepare_connect() throws Error {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            DriverManager.registerDriver(new JdbcOdbcDriver());
            con = DriverManager.getConnection(madguy + filename);
            con.setAutoCommit(true);
        } catch (ClassNotFoundException e) {
            errhappen(1);
        } catch (SQLException e) {
            System.out.println(e.getMessage() + "\t" + filename);
            errhappen(2);
        }
    }

//檢查該學號是否已經存在
    public int checknum(String num) throws Error {
        try {
            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM students WHERE num=?");
            pstmt.setString(1, num);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return 0;
            } else {
                rs.close();
                pstmt.close();
            }
            return 1;
        } catch (SQLException e) {
            return 1;
        }
    }

    public int addNew(String name, String num, String sex, String addr,
                      String tel) throws Error {
        try {
            Statement stmt = con.createStatement();
            PreparedStatement pstmt = con.prepareStatement(
                    "INSERT INTO students(name,num,sex,addr,tel) VALUES (?,?,?,?,?)");
            pstmt.setString(1, name);
            pstmt.setString(2, num);
            pstmt.setString(3, sex);
            pstmt.setString(4, addr);
            pstmt.setString(5, tel);
            pstmt.execute();
            stmt.close();
            con.commit();

            return 1;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return 0;
        }
    }

    public int delete(String num) throws Error {
        try {
            PreparedStatement pstmt = con.prepareStatement("DELETE FROM students WHERE num=?");
            pstmt.setString(1, num);

            pstmt.execute();
            pstmt.close();
            return 1;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return 0;
        }
    }

    public void errhappen(int errnum) {
        System.err.println("程序出現異常，請與管理員聯系" + "內部錯誤" + errnum);
    }

}