学生成绩管理系统个人日志

学生成绩管理系统个人日志

我的任务本来是一个架构师兼数据库的维护，但是因为前期对于架构经验的匮乏，不理解mvc等架构。导致我花了一个月写的架构根本无法和gui界面那边对接，双方互相拉扯，最后只能选择参考别人的架构搭建了一个mvc，团队的工作才顺利的进行下去。由于这个原因我们之前gitee也放弃了。

学生操作抽象类

public interface StudentService {

    TableDTO retrieveStudents(StudentRequest request);

    boolean add(StudentDO studentDO);

    StudentDO getById(int selectedStudentId);

    boolean update(StudentDO studentDO);

    boolean delete(int[] selectedStudentIds);
}

学号的自动生成

public static Integer ganerateId() {
        String sql = "select max(id) from student";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Integer id=0;
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            rs.next();
            id= rs.getInt(1);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return id+1;
    }

双条件查询支持模糊匹配

public TableDTO retrieveStudents(StudentRequest request) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from student ");
        if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
            sql.append(" where name like '%"+request.getSearchKey().trim()+"%'");
            sql.append("|| id = '"+request.getSearchKey().trim()+"'");
        }
        sql.append(" order by id asc limit ").append(request.getStart()).append(",").append(request.getPageSize());
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            // 查询记录
            returnDTO.setData(fillData(rs));

            sql.setLength(0);
            sql.append("select count(*) from student ");
            if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
                sql.append(" where name like '%"+request.getSearchKey().trim()+"%' ");
                sql.append("|| id = '"+request.getSearchKey().trim()+"';");
//                sql.append("where name like %'我'% ");
            }
            String a=sql.toString();
            ps = conn.prepareStatement(a);
            rs = ps.executeQuery();
            while (rs.next()) {
                int count = rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            return returnDTO;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

新增学生

public boolean add(StudentDO studentDO) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into student(id,name,gender,birth,java_score,pe_score,math_score) ");
        sql.append(" values(?,?,?,?,?,?,?) ");
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            ps.setInt(1,studentDO.getId());
            ps.setString(2,studentDO.getName());
            ps.setString(3,studentDO.getGender());
            ps.setString(4,studentDO.getBirth());
            ps.setDouble(5,studentDO.getJavaScore());
            ps.setDouble(6,studentDO.getPeScore());
            ps.setDouble(7,studentDO.getMathScore());
            return ps.executeUpdate() == 1;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

更新学生

public boolean update(StudentDO studentDO) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update student set name = ?,birth=?,gender=?,java_score=?,pe_score=?,math_score=? ");
        sql.append(" where id =? ");
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            ps.setString(1,studentDO.getName());
            ps.setString(2,studentDO.getGender());
            ps.setString(3,studentDO.getBirth());
            ps.setDouble(4,studentDO.getJavaScore());
            ps.setDouble(5,studentDO.getPeScore());
            ps.setDouble(6,studentDO.getMathScore());
            ps.setInt(7,studentDO.getId());
            return ps.executeUpdate() == 1;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

多条删除学生

public boolean delete(int[] selectedStudentIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from student where id in ( ");
        int length = selectedStudentIds.length;
        for (int i = 0; i < length; i++) {
            if (i == (length - 1)) {
                sql.append(" ? ");
            }else {
                sql.append(" ?, ");
            }
        }
        sql.append(" ) ");
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            for (int i = 0; i < length; i++) {
                // 设置参数，从1开始
                ps.setInt(i + 1, selectedStudentIds[i]);
            }
            return ps.executeUpdate() == length;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

包装搜索结果

private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (rs.next()) {
            // 处理查出的每一条记录
            Vector<Object> oneRecord = new Vector<>();
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String gender = rs.getString("gender");
            String birth = rs.getString("birth");
            Double javaScore = rs.getDouble("java_score");
            Double peScore = rs.getDouble("pe_score");
            Double mathScore = rs.getDouble("math_score");
            Double totalScore = javaScore + peScore + mathScore;
            oneRecord.addElement(id);
            oneRecord.addElement(name);
            oneRecord.addElement(gender);
            oneRecord.addElement(birth);
            oneRecord.addElement(javaScore);
            oneRecord.addElement(peScore);
            oneRecord.addElement(mathScore);
            oneRecord.addElement(totalScore);
            data.addElement(oneRecord);
        }
        return data;
    }

用户操作抽象类

public interface AdminService {
    boolean validateAdmin(AdminDO adminDO);
    boolean addAdmin(AdminDO adminDO);
    boolean validateAdd(AdminDO adminDO);
}

用户添加

public boolean addAdmin(AdminDO adminDO){
        String userName = adminDO.getUserName();
        String pwdParam = adminDO.getPwd();
        String sql = "insert into manager(user_name,pwd) values(?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            conn = DBUtil.getConn();
            if (conn == null) {
                return false;
            }
            ps = conn.prepareStatement(sql);
            ps.setString(1, userName);
            ps.setString(2,pwdParam);
            return ps.execute();
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeRs(resultSet);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

用户判重

public boolean validateAdd(AdminDO adminDO) {
        String userName = adminDO.getUserName();
        String pwdParam = adminDO.getPwd();
        String sql = "select pwd from manager where user_name = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            conn = DBUtil.getConn();
            if (conn == null) {
                return false;
            }
            ps = conn.prepareStatement(sql);
            ps.setString(1, userName);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                String pwd = resultSet.getString(1);
                if (pwdParam.equals(pwd)) {
                    return false;
                }
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeRs(resultSet);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return true;
    }

用户登录

public boolean validateAdmin(AdminDO adminDO) {
        String userName = adminDO.getUserName();
        String pwdParam = adminDO.getPwd();
        String sql = "select pwd from manager where user_name = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            conn = DBUtil.getConn();
            if (conn == null) {
                return false;
            }
            ps = conn.prepareStatement(sql);
            ps.setString(1, userName);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                String pwd = resultSet.getString(1);
                if (pwdParam.equals(pwd)) {
                    return true;
                }
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeRs(resultSet);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

正则算法判断

判断姓名

public boolean regStr(){
        String str = nameTxt.getText();
        return !Pattern.matches("[\u4e00-\u9fa5]*",str);
    }

判断生日格式

public boolean regstrbirth(){
        String str1 =birthTxt1.getText();
        String str2=birthTxt2.getText();
        String str3=birthTxt3.getText();
        if(!Pattern.matches("[1-9][0-9]*", str1))return false;
        if(!Pattern.matches("[1-9]|[1][0-2]", str2))return false;
        if(!Pattern.matches("[1-9]|[1-2][0-9]|[3][0-1]", str3))return false;
        if(Pattern.matches("(1|3|5|7|8||10|12)",str3))return true;
        if(Pattern.matches("[3][1]",str3))return false;
        if(str2.equals("2")){
            Integer day=Integer.parseInt(str3);
            if(day%4==0&&day%100!=0){
                if(!Pattern.matches("[1-9]|[1-2][0-9]",str3))return false;
            }else if(!Pattern.matches("[1-9]|[1][0-9]|[2][0-8]",str3))return false;
        }
        return true;
    }

判断成绩

public boolean regstrscore(){
        String str1 = javaTxt.getText();
        String str2 = peTxt.getText();
        String str3 = mathTxt.getText();
        if(!Pattern.matches("[1][0][0]|[1-9]?[0-9]?",str1))return false;
        if(!Pattern.matches("[1][0][0]|[1-9]?[0-9]?",str2))return false;
        if(!Pattern.matches("[1][0][0]|[1-9]?[0-9]?",str3))return false;
        return true;
    }

判断男女

public boolean regstrgender(){
        String str = genderTxt.getText();
        return !Pattern.matches("[男]|[女]",str);
    }