/////////////////////////////////Admin列出試題方法/////////////////////////////////////////////////
    public ArrayList getTestList(int count, int page) {
        ArrayList list = new ArrayList();
        try {
            proc = conn.prepareCall("{call proc_test_page (?,?,?)}");
            proc.setInt(1, page);
            proc.setInt(2, count);
            proc.registerOutParameter(3, Types.INTEGER);
            res = proc.executeQuery();
            while (res.next()) {
                TestBean tb = new TestBean();
                tb.setTest_ID(res.getInt(1));
                tb.setTest_Theme(res.getString(2));
                tb.setTest_Result(res.getString(3));
                tb.setTest_Score(res.getInt(4));
                tb.setTest_Type(res.getString(5));
                tb.setTest_A(res.getString(6));
                tb.setTest_B(res.getString(7));
                tb.setTest_C(res.getString(8));
                tb.setTest_D(res.getString(9));
                list.add(tb);
            }
            pagecount = proc.getInt(3);
        } catch (SQLException ex) {
            System.out.println("服務器異常發生在 getTestList()");
            ex.printStackTrace();
        }
        return list;
    }

/////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Admin編輯試題方法/////////////////////////////////////////////////
    public boolean updateTest(TestBean tb) {
        try {
            prepar = conn.prepareStatement(
                    "update Test set Test_Theme=?,Test_Result=?,Test_Score=?,Test_Type=?,Test_A=?,Test_B=?,Test_C=?,Test_D=? where Test_ID=?");
            prepar.setString(1, tb.getTest_Theme());
            prepar.setString(2, tb.getTest_Result());
            prepar.setInt(3, tb.getTest_Score());
            prepar.setString(4, tb.getTest_Type());
            prepar.setString(5, tb.getTest_A());
            prepar.setString(6, tb.getTest_B());
            prepar.setString(7, tb.getTest_C());
            prepar.setString(8, tb.getTest_D());
            prepar.setInt(9, tb.getTest_ID());
            int flag = prepar.executeUpdate();
            if (flag == 0) {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public TestBean getTestBean(int test_ID) {
        TestBean tb = new TestBean();
        String sql =
                "select * from Test where Test_ID=?";

        try {
            prepar = conn.prepareStatement(sql);
            prepar.setInt(1, test_ID);
            res = prepar.executeQuery();
            if (res.next()) {
                tb.setTest_ID(res.getInt(1));
                tb.setTest_Theme(res.getString(2));
                tb.setTest_Result(res.getString(3));
                tb.setTest_Score(res.getInt(4));
                tb.setTest_Type(res.getString(5));
                tb.setTest_A(res.getString(6));
                tb.setTest_B(res.getString(7));
                tb.setTest_C(res.getString(8));
                tb.setTest_D(res.getString(9));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return tb;
    }

    public boolean deleteTest(int test_ID) {
        try {
            prepar = conn.prepareStatement(
                    "delete from Test where Test_ID=?");
            prepar.setInt(1, test_ID);
            int flag = prepar.executeUpdate();
            if (flag == 0) {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Admin獲取用戶列表方法/////////////////////////////////////////////////
    public ArrayList getUsersList(int page,int count) {
        ArrayList list = new ArrayList();
        try {
            proc = conn.prepareCall("{call proc_users_page (?,?,?)}");
            proc.setInt(1, page);
            proc.setInt(2, count);
            proc.registerOutParameter(3, Types.INTEGER);
            res = proc.executeQuery();
            while (res.next()) {
                UserBean ub = new  UserBean();
                ub.setUsers_ID(res.getInt("Users_ID"));
                ub.setUsers_Name(res.getString("Users_Name"));
                ub.setUsers_Pwd(res.getString("Users_Pwd"));
                list.add(ub);
            }
            pagecount = proc.getInt(3);
        } catch (SQLException ex) {
            System.out.println("服務器異常發生在 getUsersList()");
            ex.printStackTrace();
        }
        return list;
    }
    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Admin刪除用戶方法/////////////////////////////////////////////////
    public boolean deleteUseers(int users_ID) {
        try {
            prepar = conn.prepareStatement(
                    "delete from Users where Users_ID=?");
            prepar.setInt(1, users_ID);
            int flag = prepar.executeUpdate();
            if (flag == 0) {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }



    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////User登陸方法/////////////////////////////////////////////////
    public boolean checkUserLogin(UserBean ub) { // 驗證登陸

        boolean result = false;
        if (!MyString.checkParameter(ub.getUsers_Name()
                                     + ub.getUsers_Pwd())) {
            return result;
        }
        try {
            String sql =
                    "select count(*) from Users where Users_Name=? and Users_Pwd=?";
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, ub.getUsers_Name());
            prepar.setString(2, ub.getUsers_Pwd());
            res = prepar.executeQuery();
            if (res.next()) {
                if (res.getInt(1) > 0) {
                    result = true;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public UserBean getUserBean(UserBean ub) { // 獲取UserBean持久化
        UserBean user = null;
        try {
            String sql =
                    "select * from Users where Users_Name=? and Users_Pwd=?";
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, ub.getUsers_Name());
            prepar.setString(2, ub.getUsers_Pwd());
            res = prepar.executeQuery();
            if (res.next()) {
                user = new UserBean();
                user.setUsers_ID(res.getInt("Users_ID"));
                user.setUsers_Name(res.getString("Users_Name"));
                user.setUsers_Pwd(res.getString("Users_Pwd"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return user;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////User注冊方法/////////////////////////////////////////////////
    public boolean regUser(UserBean ub) {
        try {
            prepar = conn.prepareStatement(
                    "insert into Users (Users_Name,Users_Pwd) values(?,?)");
            prepar.setString(1, ub.getUsers_Name());
            prepar.setString(2, ub.getUsers_Pwd());
            int flag = prepar.executeUpdate();
            if (flag == 0) {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;

    }

    public boolean isHasName(String userName) {
        try {
            prepar = conn.prepareStatement(
                    "select count(*) from Users where Users_Name=?");
            prepar.setString(1, userName);
            res = prepar.executeQuery();
            res.next();
            if (res.getInt(1) != 0) {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;

    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////User取考題方法/////////////////////////////////////////////////
    public String selectTests() {
        try {
            prepar = conn.prepareStatement(
                    "select top 1 * from Tests order by NEWID()");
            res = prepar.executeQuery();
            res.next();
            return res.getString("Tests_Time");
        } catch (SQLException ex) {
            ex.printStackTrace();
            return "";
        }
    }

}