11.2 基于JDBC实现的学生CRUD案例

1. 案例需求

MySQL数据库创建学生表，有主键、姓名、年龄信息。

使用JDBC实现学生信息的新增、修改、删除、查询所有功能，并实现MySQL数据库的操作。

2. 数据初始化

（1）创建student数据库

create database student default charset utf8 collate utf8_general_ci;

（2）创建t_student表

create table t_student  (
    id varchar(32) not null,
    name varchar(255),
    age int
);

（3）初始化数据

insert into t_student values("1","lucy",19); 
insert into t_student values("2","lili",20);

3. 导入相关jar包

导入mysql-connector-java-8.0.27.jar包。

导入junit-4.6.jar包。

4. 创建Student实体类

public class Student {

    private String id;      //学生ID
    private String name;    //学生姓名
    private int age;        //学生年龄

    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }

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

5. 创建StudentController类

public class StudentController {

    private StudentService studentService;

    /**
     *  新增学生信息
     * @param student
     */
    public void addStudent(Student student)throws Exception {
        studentService = new StudentServiceImpl();
        studentService.addStudent(student);
    }
    /**
     *  修改学生信息
     * @param student
     */
    public void updateStudent(Student student)throws Exception {
        studentService = new StudentServiceImpl();
        studentService.updateStudent(student);
    }

    /**
     *  删除学生信息
     * @param id
     */
    public void delStudent(String id)throws Exception {
        studentService = new StudentServiceImpl();
        studentService.delStudent(id);
    }

    /**
     * 查询学生信息
     * @return
     * @throws Exception
     */
    public List<Student> queryStudent()throws Exception {
        studentService = new StudentServiceImpl();
        List<Student> studentList = studentService.queryStudent();
        return studentList;
    }
}

6. 创建StudentService接口

public interface StudentService {
    public void addStudent(Student student)throws Exception;
    public void updateStudent(Student student)throws Exception;
    public void delStudent(String id)throws Exception;
    public List<Student> queryStudent()throws Exception;
}

7. 创建StudentServiceImpl类

public class StudentServiceImpl implements StudentService {

    private StudentDao studentDao;

    @Override
    public void addStudent(Student student) throws Exception {
        studentDao = new StudentDaoImpl();
        studentDao.addStudent(student);
        System.out.println("新增成功！");
    }

    @Override
    public void updateStudent(Student student) throws Exception {
        studentDao = new StudentDaoImpl();
        studentDao.updateStudent(student);
        System.out.println("修改成功！");
    }

    @Override
    public void delStudent(String id) throws Exception {
        studentDao = new StudentDaoImpl();
        studentDao.delStudent(id);
        System.out.println("删除成功！");
    }

    @Override
    public List<Student> queryStudent() throws Exception {
        studentDao = new StudentDaoImpl();
        return studentDao.queryStudent();
    }
}

8. 创建Dao类

public class Dao {
    public Connection getConnection() throws Exception {
        //1、加载驱动程序
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2、创建连接对象
        String dburl = "jdbc:mysql://127.0.0.1:3306/student?useUnicode=true&characterEncoding=utf-8";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(dburl,username,password);
        return conn;
    }
}

9. 创建StudentDao接口

public interface StudentDao {

    public void addStudent(Student student)throws Exception;
    public void updateStudent(Student student)throws Exception;
    public void delStudent(String id)throws Exception;
    public List<Student> queryStudent()throws Exception;
}

10. 创建StudentDaoImpl实现类

public class StudentDaoImpl extends Dao implements StudentDao {

    /**
     * 新增学生信息
     * @param student
     * @throws Exception
     */
    @Override
    public void addStudent(Student student) throws Exception{
        Connection conn = getConnection();
        String sql = "insert into t_student values(?,?,?)";
// 3.通过Connection对象获取Statement对象
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, student.getId());
        ps.setString(2, student.getName());
        ps.setInt(3, student.getAge());
// 4. 使用Statement执行SQL语句
        ps.executeUpdate();
// 6.关闭连接，释放资源
        ps.close();
        conn.close();
    }

    /**
     * 修改学生信息
     * @param student
     * @throws Exception
     */
    @Override
    public void updateStudent(Student student)throws Exception {
        Connection conn = getConnection();
        String sql = "update t_student t set t.name = ?,t.age = ? where t.id = ? ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, student.getName());
        ps.setInt(2, student.getAge());
        ps.setString(3, student.getId());
        ps.executeUpdate();
        ps.close();
        conn.close();
    }

    /**
     * 删除学生信息
     * @param id
     * @throws Exception
     */
    @Override
    public void delStudent(String id) throws Exception{
        Connection conn = getConnection();
        String sql = "delete from t_student t where t.id = ? ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, id);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }

    /**
     * 查询学生信息
     * @return
     * @throws Exception
     */
    @Override
    public List<Student> queryStudent() throws Exception {
        Connection conn = getConnection();
        String sql = "select * from t_student ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        List<Student> list = new ArrayList<Student>();
        while(rs.next()) {
            Student student = new Student();
            student.setId(rs.getString("id"));
            student.setName(rs.getString("name"));
            student.setAge(rs.getInt("age"));
            list.add(student);
        }
        rs.close();
        ps.close();
        conn.close();
        return list;
    }
}

11. 创建测试类

public class StudentTest {

    private StudentController studentController;

    @Before
    public void init()throws Exception{
        studentController = new StudentController();
    }

    /**
     * 测试学生新增
     */
    @Test
    public void testAdd()throws Exception{
        Student student = new Student();
        student.setId("3");
        student.setName("zhangsan");
        student.setAge(20);
        studentController.addStudent(student);
    }
    /**
     * 测试学生修改
     */
    @Test
    public void testUpdate()throws Exception{
        Student student = new Student();
        student.setId("3");
        student.setName("lisi");
        student.setAge(20);
        studentController.updateStudent(student);
    }

    /**
     * 测试学生删除
     */
    @Test
    public void testDel()throws Exception{
        studentController.delStudent("3");
    }

    /**
     *  测试查询学生信息
     * @throws Exception
     */
    @Test
    public void testQuery()throws Exception{
        List<Student> studentList = studentController.queryStudent();
        studentList.forEach(student -> System.out.println(student));
    }
}