（二）JDBC学习笔记——案例：对表进行CRUD操作

一、数据准备

• 进行准备工作，创建一个新数据库，建立一张student表，包含sid、name、age、birthaday四列，然后向表中插入几条数据。

-- 创建db14数据库
CREATE DATABASE db14;
-- 使用db14数据库
USE db14;

-- 创建student表
CREATE TABLE student(
	sid INT PRIMARY KEY AUTO_INCREMENT,	-- 学生id
	name VARCHAR(20),					-- 学生姓名
	age INT,							-- 学生年龄
	birthday DATE						-- 学生生日
);

-- 添加数据
INSERT INTO student VALUES (NULL,'小甲',23,'1999-09-23'),
                           (NULL,'小乙',24,'1998-08-10'),
                           (NULL,'小丙',25,'1996-06-06'),
                           (NULL,'小丁',26,'1994-10-20');

此时表如下：

• 导入JDBC的jar包，另外，还使用到了jutil这个jar包，用于对工程中的某个方法执行进行测试。使用前需要先下载两个jar包，下载地址：官方网站 ，然后将jar包导入工程即可。使用时只需要在方法名上使用注解@Test即可。
  

二、创建项目基础架构

1.创建三层架构

目前使用较多的还是经典的三层架构模式，分别是控制层（controller）、业务层（service）、数据访问层（dao）。

• 项目中新建controller、service、dao三个目录，分别存放控制层、业务层、数据访问层的代码
• 新建domain目录存放实体类
  

1.创建Student类

• 首先在domain目录下创建Student类，其中定义了sid、name、age、birthday四个变量，并实现四个变量的set和get方法。
• 然后实现构造函数，四个变量作为形参。
• 最后重写toString()方法，方便打印数据查看。重写toString方法后，将Student实体传入System.out.println()方法即可自动调用此方法输出信息。

public class Student {
    private Integer sid;
    private String name;
    private Integer age;
    private Date birthday;

    public Student() {
    }

    public Student(Integer sid, String name, Integer age, Date birthday) {
        this.sid = sid;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
    }

    public Integer getSid() {
        return sid;
    }

    public String getName() {
        return name;
    }

    public Integer getAge() {
        return age;
    }

    public Date getBirthday() {
        return birthday;
    }


    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public void setName(String name) {
        this.name = name;
    }

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

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", birthday=" + birthday +
                '}';
    }
}

2.创建dao层的接口和实现类

• 1.dao层接口

public interface StudentDao {
    //查询所有的学生信息
    public abstract ArrayList<Student> findAll();

    //根据id获取学生信息
    public abstract Student findById(Integer id);

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

    //修改学生信息
    public abstract int update(Student stu);

    //删除学生信息
    public abstract int delete(Integer id);
}

• dao实现类：

public class StudentDaoImpl implements StudentDao{

    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // 1.注册驱动

            //2.获取数据库连接
            connection = DriverManager.getConnection
                    ("jdbc:mysql://localhost:3306/db14", "root", "12345678");
            //3.获取执行者对象
            statement = connection.createStatement();
            //4.执行sql语句，并且接受返回的结果集
            String querySql = "SELECT * FROM student";
            resultSet = statement.executeQuery(querySql);
            //5.处理结果集
            while (resultSet.next()){
                Integer sid = resultSet.getInt("sid");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");

                //封装Student对象
                Student student = new Student(sid, name, age, birthday);
                //将Student对象保存到list中
                list.add(student);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //6.释放资源
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return list;
    }

    @Override
    public Student findById(Integer id) {
        Student student = new Student();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // 1.注册驱动

            //2.获取数据库连接
            connection = DriverManager.getConnection
                    ("jdbc:mysql://localhost:3306/db14", "root", "12345678");
            //3.获取执行者对象
            statement = connection.createStatement();
            //4.执行sql语句，并且接受返回的结果集
            String querySql = "SELECT * FROM student WHERE sid=" + id;
            resultSet = statement.executeQuery(querySql);
            //5.处理结果集
            while (resultSet.next()){
                Integer sid = resultSet.getInt("sid");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");

                //封装Student对象
                student.setSid(sid);
                student.setName(name);
                student.setAge(age);
                student.setBirthday(birthday);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //6.释放资源
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return student;
    }

    @Override
    public int insert(Student stu) {
        Connection connection = null;
        Statement statement = null;
        int res = 0; //影响行数
        try {
            // 1.注册驱动

            //2.获取数据库连接
            connection = DriverManager.getConnection
                    ("jdbc:mysql://localhost:3306/db14", "root", "12345678");
            //3.获取执行者对象
            statement = connection.createStatement();
            //4.执行sql语句，并且接受返回的结果集
            // "INSERT INTO student VALUES (" + NULL + ",'" + 小甲 +"'," + 23 + ",'" + 1999-09-23 + "')";
            Date date = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(date);
            String InsertSql =
                    "INSERT INTO student VALUES (" + stu.getSid() + ",'" + stu.getName() +
                            "'," + stu.getAge() + ",'" + birthday + "')";
            res = statement.executeUpdate(InsertSql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //6.释放资源
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return res;
    }

    @Override
    public int update(Student stu) {
        Connection connection = null;
        Statement statement = null;
        int res = 0; //影响行数
        try {
            // 1.注册驱动

            //2.获取数据库连接
            connection = DriverManager.getConnection
                    ("jdbc:mysql://localhost:3306/db14", "root", "12345678");
            //3.获取执行者对象
            statement = connection.createStatement();
            //4.执行sql语句，并且接受返回的结果集
            Date date = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(date);
            String updateSql =
                    "UPDATE student SET sid='"+ stu.getSid() +"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+ birthday + "' WHERE sid='"+ stu.getSid() +"'";
            res = statement.executeUpdate(updateSql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //6.释放资源
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return res;
    }

    @Override
    public int delete(Integer id) {
        Connection connection = null;
        Statement statement = null;
        int res = 0; //影响行数
        try {
            // 1.注册驱动

            //2.获取数据库连接
            connection = DriverManager.getConnection
                    ("jdbc:mysql://localhost:3306/db14", "root", "12345678");
            //3.获取执行者对象
            statement = connection.createStatement();
            //4.执行sql语句，并且接受返回的结果集
            String updateSql ="DELETE FROM student WHERE sid='"+ id +"'";
            res = statement.executeUpdate(updateSql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //6.释放资源
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return res;
    }
}

3.创建service层的接口和实现类

• 创建service层的接口

public interface StudentService {
    //查询所有的学生信息
    public abstract ArrayList<Student> findAll();

    //根据id获取学生信息
    public abstract Student findById(Integer id);

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

    //修改学生信息
    public abstract int update(Student stu);

    //删除学生信息
    public abstract int delete(Integer id);
}

• 创建service层的实现类

public class StudentServiceImpl implements StudentService{
    private StudentDao dao = new StudentDaoImpl();
    @Override
    public ArrayList<Student> findAll() {
        return dao.findAll();
    }

    @Override
    public Student findById(Integer id) {
        return dao.findById(id);
    }

    @Override
    public int insert(Student stu) {
        return dao.insert(stu);
    }

    @Override
    public int update(Student stu) {
        return dao.update(stu);
    }

    @Override
    public int delete(Integer id) {
        return dao.delete(id);
    }
}

4.创建controller层

public class StudentController {
    private StudentService service = new StudentServiceImpl();

    @Test
    public void findAll() {
        ArrayList<Student> list = service.findAll();
        for (Student stu : list){
            System.out.println(stu);
        }
    }
    @Test
    public void findById() {
        Student student = service.findById(3);
        System.out.println(student);
    }
    @Test
    public void insert() {
        Student student  = new Student(5, "大王", 33, new Date());
        int res = service.insert(student);
        if (res != 0){
            System.out.println("插入成功！");
        }else {
            System.out.println("插入失败！");
        }
    }
    @Test
    public void update() {
        Student student  = service.findById(5);
        student.setName("王王");
        int res = service.update(student);
        if (res != 0){
            System.out.println("修改成功！");
        }else {
            System.out.println("修改失败！");
        }
    }
    @Test
    public void delete() {
        int res = service.delete(5);
        if (res != 0){
            System.out.println("删除成功！");
        }else {
            System.out.println("删除失败！");
        }
    }
}

项目架构如下：