学生管理系统

JdbcStudent.java

/**
 * jdbc学生类
 * */

public class JdbcStudent {
    private String sno;
    private String sname;
    private int age;
    private double score;

    public JdbcStudent(){}

    public JdbcStudent(String sno, String sname, int age, double score){
        this.sno = sno;
        this.sname = sname;
        this.age = age;
        this.score = score;
    }

    public String getSno() {
        return sno;
    }

    public void setSno(String sno) {
        this.sno = sno;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public double getScore() {
        return score;
    }

    public void setScore(double score) {
        this.score = score;
    }
}

StudentsManager.java

/**
 * 学生管理系统的管理方法类
 * */

import java.sql.*;

public class StudentsManager {
    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;

    public StudentsManager(){
        // 注册驱动，连接数据库
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://192.168.13.22:3306/mydb",
                    "root","root");
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    // 1、添加学生
    public void addStudent(JdbcStudent stu){
        try {
            // 处理sql语句
            String sql = "insert into students values (?,?,?,?)";
            // 获得预处理对象
            pst = conn.prepareStatement(sql);
            // 设置实际参数
            pst.setString(1, stu.getSno());
            pst.setString(2, stu.getSname());
            pst.setInt(3, stu.getAge());
            pst.setDouble(4, stu.getScore());
            // 接受返回值
            int i = pst.executeUpdate();
            System.out.println("影响了"+i+"行数据");
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            // 关闭pst
            if (pst != null){
                try {
                    pst.close();
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }

    // 2、显示所有学生
    public void showStudents(){
        try {
            // 处理sql语句
            String sql = "select * from students";
            // 获得预处理对象
            pst = conn.prepareStatement(sql);
            // 发送并接收返回值
            rs = pst.executeQuery();
            // 迭代取值
            while (rs.next()){
                String sno = rs.getString("sno");
                String sname = rs.getString("sname");
                int age = rs.getInt("age");
                double score = rs.getDouble("score");
                System.out.println(sno+", "+sname+", "+age+", "+score);
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            // 释放资源
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if (pst != null){
                try {
                    pst.close();
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }

    ///3、根据学号查询学生信息
    public void snoShowStu(String sno){
        try {
            // 处理sql语句
            String sql = "select * from students where sno = ?";
            // 获得预处理对象
            pst = conn.prepareStatement(sql);
            // 设置实际参数
            pst.setString(1, sno);
            // 发送并接收返回值
            rs = pst.executeQuery();
            
            // 获得结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // 获得第一列的列名 // sno
            System.out.println("getColumnName(1)："+rsmd.getColumnName(1));
            // 获得列总数 // 4
            System.out.println("getColumnCount()："+rsmd.getColumnCount());
            // 获取第一列类型号 // 12
            System.out.println("getColumnType(1)："+rsmd.getColumnType(1));
            // 获取第一列类型名 // VARCHAR
            System.out.println("getColumnTypeName(1)："+rsmd.getColumnTypeName(1));
            
            int len = 0;
            // 迭代取值
            while (rs.next()){
                len += 1;
                String sname = rs.getString("sname");
                int age = rs.getInt("age");
                double score = rs.getDouble("score");
                System.out.println(sno+", "+sname+", "+age+", "+score);
            }
            if (len == 0){
                System.out.println("并没有该学号的学生");
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            // 释放资源
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if (pst != null){
                try {
                    pst.close();
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }

    // 4、修改学生分数
    public void setScore(String sno, double score){
        try {
            // 处理sql语句
            String sql = "update students set score = ? where sno = ?";
            // 获得预处理对象
            pst = conn.prepareStatement(sql);
            // 设置实际参数
            pst.setDouble(1, score);
            pst.setString(2, sno);
            // 发送并接收返回值
            int i  = pst.executeUpdate();
            System.out.println("影响了"+i+"行数据");
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            // 释放资源
            if (pst != null){
                try {
                    pst.close();
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }

    // 5、根据学号删除学生
    public void delStu(String sno){
        try {
            // 处理sql语句
            String sql = "delete from students where sno = ?";
            // 获得预处理对象
            pst = conn.prepareStatement(sql);
            // 设置实际参数
            pst.setString(1, sno);
            // 发送并接收返回值
            int i  = pst.executeUpdate();
            System.out.println("影响了"+i+"行数据");
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            // 释放资源
            if (pst != null){
                try {
                    pst.close();
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }

    // 6、根据学号升序排序，分页显示学员信息，每页三条
    public void showPageStu(int pageSize, int pageNum){
        try {
            // 处理sql语句
            String sql = "select * from students order by sno asc limit ?,?";
            // 获得预处理对象
            pst = conn.prepareStatement(sql);
            // 设置实际参数
            pst.setInt(1, (pageNum-1)*pageSize);
            pst.setInt(2, pageSize);
            // 发送并接收返回值
            rs = pst.executeQuery();
            // 迭代取值
            while (rs.next()){
                System.out.println("当前是第"+pageNum+"页");
                String sno = rs.getString("sno");
                String sname = rs.getString("sname");
                int age = rs.getInt("age");
                double score = rs.getDouble("score");
                System.out.println(sno+", "+sname+", "+age+", "+score);
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            // 释放资源
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if (pst != null){
                try {
                    pst.close();
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }

    // 关闭资源
    public void close(){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
        }
        if (pst != null){
            try {
                pst.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}

JdbcStudentsManagerSystem.java

/**
 * jdbc实现的学生管理系统
 * */

public class JdbcStudentsManagerSystem {
    public static void main(String[] args){
        Scanner sc = new Scanner(System.in);
        StudentsManager sm = new StudentsManager();
        while (true) {
            System.out.println();
            System.out.println();
            System.out.println("-----JDBC学生信息管理系统-----");
            System.out.println("1、添加学生");
            System.out.println("2、显示所有学生信息");
            System.out.println("3、根据学号显示学员信息");
            System.out.println("4、给学生打分数");
            System.out.println("5、根据学号删除学生");
            System.out.println("6、按学号升序排序，分页显示学生");
            System.out.println("7、退出系统");
            System.out.print("请输入选择：");
            int choose = sc.nextInt();
            switch (choose) {
                case 1:
                    System.out.print("请输入要添加几个学生：");
                    int num = sc.nextInt();
                    for (int i = 0;i < num;i++){
                        System.out.print("请输入学生学号：");
                        String sno = sc.next();
                        System.out.print("请输入学生姓名：");
                        String sname = sc.next();
                        System.out.print("请输入学生年龄：");
                        int age = sc.nextInt();
                        JdbcStudent stu = new JdbcStudent(sno, sname, age, 0);
                        sm.addStudent(stu);
                    }
                    break;
                case 2:
                    sm.showStudents();
                    break;
                case 3:
                    System.out.print("请输入学生学号：");
                    String sno = sc.next();
                    sm.snoShowStu(sno);
                    break;
                case 4:
                    System.out.print("请输入要修改几个学生的成绩：");
                    num = sc.nextInt();
                    for (int i = 0;i < num;i++) {
                        System.out.print("请输入学生学号：");
                        sno = sc.next();
                        System.out.print("请输入学生成绩：");
                        double score = sc.nextDouble();
                        sm.setScore(sno, score);
                    }
                    break;
                case 5:
                    System.out.print("请输入要删除的学生学号：");
                    sno = sc.next();
                    sm.delStu(sno);
                    break;
                case 6:
                    System.out.print("请输入每页显示多少名学生：");
                    int pageSize = sc.nextInt();
                    System.out.print("请输入显示第多少页的学生：");
                    int pageNum = sc.nextInt();
                    sm.showPageStu(pageSize, pageNum);
                    break;
                case 7:
                    sm.close();
                    return;
                default:
                    System.out.println("请输入正确的选项");
                    break;
            }
        }
    }
}