jdbc-简单学生管理系统-增删改查

jdbc-简单学生管理系统-增删改查

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/stumanager
user=root
password=123456

JDBCUtils

public class JDBCUtils {

    private static String driver = "";
    private static String url = "";
    private static String user = "";
    private static String password = "";

    static {
        Properties p = new Properties();
        try {
            p.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver = p.getProperty("driver");
        url = p.getProperty("url");
        user = p.getProperty("user");
        password = p.getProperty("password");
        /*try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }*/
    }

    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    //释放的时候要从小到大释放
    //Connection -> Statement --> Resultset


    public static void release(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Student类

public class Student {
    private int id;
    private String name;
    private int score;
    public Student(int id, String name,int score) {

        this.id = id;
        this.name = name;
        this.score = score;
    }

    public Student() {

    }
    public String toString() {
        return "Student{" +
                "name='" + id + '\'' +
                ", age=" + name +
                ", sex='" + score + '\'' +
                '}';
    }
    public int getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getScore() {
        return score;
    }
    public void setScore(Integer score) {
        this.score = score;
    }
}

StudentManager

    public static int updateStudent(Student student) {
        conn = JDBCUtils.getConnection();
        int result = 0;
        try {
            ps = conn.prepareStatement("UPDATE student SET id = ?, `name` = ?, score = ? WHERE id = ?");
            ps.setInt(1, student.getId()); //设置第一个参数
            ps.setString(2, student.getName()); //设置第二个参数
            ps.setInt(3, student.getScore()); //设置第三个参数
            ps.setInt(4, student.getId());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(null, ps, conn);  //关闭连接
        }
        return result;
    }

    public void update() {
        Scanner scan = new Scanner(System.in);
        System.out.println("请输入学生id");
        int id = scan.nextInt();
        System.out.println("请输入学生姓名");
        scan.nextLine();
        String name = scan.nextLine();

        System.out.println("请输入学生成绩");
        int score = scan.nextInt();

        Student s = new Student(id, name, score );
        int flag = updateStudent(s);
        if (flag > 0) {
            System.out.println("更新成功");
        } else {
            System.out.println("更新失败");
        }
    }

    /**
     * 删除
     *
     * @param
     * @return
     * @throws SQLException
     */

    public static void select() throws SQLException  {
        Scanner scan = new Scanner(System.in);
        conn = JDBCUtils.getConnection();
        int n;
        String sql = "select * from student where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        System.out.println("请输入要查询的学号");
        n = scan.nextInt();
        ps.setInt(1, n);
        ResultSet rs  = ps.executeQuery();
        //将sql语句传至数据库，返回的值为一个字符集用一个变量接收
        while(rs.next()){    //next（）获取里面的内容
            System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getInt(3));
            //getString（n）获取第n列的内容
            //数据库中的列数是从1开始的
        }
    }
    public static int deleteStudent(int id) {
        conn = JDBCUtils.getConnection();
        int result = 0;
        try {
            ps = conn.prepareStatement("DELETE FROM student WHERE id = ?");
            ps.setInt(1, id); //设置第一个参数
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(null, ps, conn);  //关闭连接
        }
        return result;
    }

    public void delete() {
        Scanner scan = new Scanner(System.in);
        System.out.println("请输入学生id");
        int id = scan.nextInt();
        int flag = deleteStudent(id);
        if (flag > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
    }

    public static void main(String[] args) throws SQLException {
        System.out.println("************ 欢迎进入学生管理系统 *************");
        StudentManager ms = new StudentManager();
        boolean b = true;
        while (b) {
            System.out.println("你想进行以下哪项操作");
            System.out.println("1、添加学生   2、更新学生数据   3、学生信息查询   4、删除学生  0、退出");
            Scanner scan = new Scanner(System.in);
            int i = scan.nextInt();
            switch (i) {
                case 1:
                    ms.add();
                    break;
                case 2:
                    ms.update();
                    break;
                case 3:
                    ms.select();
                    break;
                case 4:
                    ms.delete();
                    break;
                default:
                    System.out.println("没有该操作选项,请重新来过！");
                    main(args);
                    break;
            }
        }
    }
}

参考文章：https://www.cnblogs.com/wjingbo/p/15455941.html