JDBC增删改查

//查询所有学生信息
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/kd34", "root", "root");
            //3.获取执行者对象
            stat = con.createStatement();
            //4.执行sql语句，并且接收返回的结果集
            String sql = "SELECT * FROM student;";
            rs = stat.executeQuery(sql);
            //5.处理结果集
            while (rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");

                //封装成Student对象
                Student stu = new Student(sid, name, age, birthday);

                //将Student对象保存到集合中
                list.add(stu);
            }
            //6.释放资源

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            try {
                if (con != null) {
                    con.close();
                }
                if (stat != null) {
                    stat.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();

            }
        }
        //将集合对象返回
        return list;
    }

    //条件查询，根据id查询学生信息
    @Override
    public Student findById(Integer id) {
        Student stu = new Student();
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/kd34", "root", "root");
            //3.获取执行者对象
            stat = con.createStatement();
            //4.执行sql语句，并且接收返回的结果集
            String sql = "SELECT * FROM student WHERE sid = '" + id + "'";
            rs = stat.executeQuery(sql);
            //5.处理结果集
            while (rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");

                //封装成Student对象
                stu.setSid(sid);
                stu.setName(name);
                stu.setAge(age);
                stu.setBirthday(birthday);
            }
            //6.释放资源

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            try {
                if (con != null) {
                    con.close();
                }
                if (stat != null) {
                    stat.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();

            }
        }
        return stu;
    }

    //新增学生信息
    public int insert(Student stu) {

        Connection con = null;
        Statement stat = null;
        int result = 0;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/kd34", "root", "root");
            //3.获取执行者对象
            stat = con.createStatement();
            //4.执行sql语句，并且接收返回的结果集
            java.util.Date current = stu.getBirthday(); //new java.util.Date();
            java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(current);
//            java.util.Date d =  stu.getBirthday();
//            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
//            String birthday = sdf.format(d);
            String sql = "INSERT INTO student VALUE ('" + stu.getSid() + "','" + stu.getName() + "','" + stu.getAge() + "','" + birthday + "')";
            result = stat.executeUpdate(sql);


            //6.释放资源

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            try {
                if (con != null) {
                    con.close();
                }
                if (stat != null) {
                    stat.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();

            }
        }
        return result;
    }


    //修改学生信息
    @Override
    public int update(Student stu) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/kd34", "root", "root");
            //3.获取执行者对象
            stat = con.createStatement();
            //4.执行sql语句，并且接收返回的结果集
            java.util.Date current = stu.getBirthday(); //new java.util.Date();
            java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(current);
            String sql = "UPDATE student SET sid = '" + stu.getSid() + "',NAME = '" + stu.getName() + "',age='" + stu.getAge() + "',birthday='" + birthday + "'WHERE sid='" + stu.getSid() + "'";
            result = stat.executeUpdate(sql);


            //6.释放资源
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            try {
                if (con != null) {
                    con.close();
                }
                if (stat != null) {
                    stat.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return result;
    }

    //删除学生信息
    @Override
    public int delete(Integer id) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/kd34", "root", "root");
            //3.获取执行者对象
            stat = con.createStatement();
            //4.执行sql语句，并且接收返回的结果集
            String sql = "DELETE FROM student WHERE sid = '"+id+"'";
            result = stat.executeUpdate(sql);


            //6.释放资源
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            try {
                if (con != null) {
                    con.close();
                }
                if (stat != null) {
                    stat.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return result;

DriverManager

获取数据库的连接

获取数据库连接对象：

static Connection getConnection(String url,String users,String password);

返回值：Connection 数据库连接对象

参数：url：指定连接的路径。语法：jdbc:mysql://ip地址(域名)：端口号/数据库名称

user:用户名 password：密码

Connection

Connection数据库连接对象

1. 获取执行者对象

• 获取普通执行者对象：Statement createStatement();
• 获取预编译执行者对象：PreparedStatement prepareStatement(String sql);

2. 管理事务

• 开启事务：setAutoCommit(boolean autoCommit);参数为false，则开启事务
• 提交事务：commit();
• 回滚事务：rollback();

3. 释放资源

• 立即将数据库连接对象释放：void close();

ResultSet

1.ResultSet结果集对象

判断结果集中是否还有数据：boolean next();

• 有数据返回true，并将索引向下移动一行
• 没有数据返回false

获取结果集中的数据：XXX getXxx("列名")；

• XXX代表数据类型(要获取某列数据，这一列的数据类型)。
• 例如：String getString("name"); int getInt("age");