大家好，我是邵奈一，一个不务正业的程序猿、正儿八经的斜杠青年。
1、世人称我为：被代码耽误的诗人、没天赋的书法家、五音不全的歌手、专业跑龙套演员、不合格的运动员…
2、这几年，我整理了很多IT技术相关的教程给大家，爱生活、爱分享。
3、如果您觉得文章有用，请收藏，转发，评论，并关注我，谢谢！
博客导航跳转（请收藏）：邵奈一的技术博客导航
| 公众号 | 微信 | 微博 | CSDN | 简书 |

教程目录

0x00 教程内容
0x01 环境准备
- - - - 表数据准备
      - 项目结构准备
0x02 编写代码
- - - - Student实体类代码
      - StudentDAO访问接口
      - StudentDAOImpl访问接口实现类
0x03 测试
- - - - 增删改查测试类编写
      - 测试结果
0xFF 总结

0x00 教程内容

环境准备
编写代码
测试

如果没有基础，或者环境没有准备，请参考此两篇教程：
JDBC连接MySQL数据库（一）
JDBC连接MySQL数据库（二）

0x01 环境准备

1. 表数据准备

给数据库添加几条数据：

insert into student(name,age) values ("邵奈一",30);
insert into student(name,age) values ("邵奈一",28);

2. 项目结构准备

新建相应的包以及类（注意新建的文件类型）
在这里插入图片描述

0x02 编写代码

1. Student实体类代码

package com.shaonaiyi.domain;
/** * @Auther: shaonaiyi@163.com * @Date: 2021/1/13 15:17 * @Description: 学生实体类 */
public class Student { 
    private Integer id;
    private String name;
    private Integer age;
    public Integer 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 Integer getAge() { 
        return age;
    }
    public void setAge(Integer age) { 
        this.age = age;
    }
}

2. StudentDAO访问接口

package com.shaonaiyi.dao;
import com.shaonaiyi.domain.Student;
import java.util.List;
/** * @Auther: shaonaiyi@163.com * @Date: 2021/1/13 15:19 * @Description: Student访问接口 */
public interface StudentDAO { 
    //1、查询所有的学生
    public List<Student> query();
    //2、新增学生
    public Integer save(Student student);
    //3、删除学生
    public Integer delete(Integer id);
    //4、修改学生
    public Integer update(Student student);
}

3. StudentDAOImpl访问接口实现类

package com.shaonaiyi.dao;
import com.shaonaiyi.domain.Student;
import com.shaonaiyi.utils.JDBCUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
/** * @Auther: shaonaiyi@163.com * @Date: 2021/1/13 15:22 * @Description: Student访问接口实现类 */
public class StudentDAOImpl implements StudentDAO { 
    @Override
    public List<Student> query() { 
        List<Student> studentList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try { 
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement("select * from student");
            resultSet = preparedStatement.executeQuery();
            Student student = null;
            while (resultSet.next()) { 
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                student = new Student();
                student.setId(id);
                student.setName(name);
                student.setAge(age);
                studentList.add(student);
            }
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            JDBCUtil.release(resultSet, preparedStatement, connection);
        }
        return studentList;
    }
    @Override
    public Integer save(Student student) { 
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Integer result = 0;
        try { 
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement("insert into student(name,age) values (?,?)");
            preparedStatement.setString(1, student.getName());
            preparedStatement.setInt(2,student.getAge());
            result = preparedStatement.executeUpdate();
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            JDBCUtil.release(resultSet, preparedStatement, connection);
        }
        return result;
    }
    @Override
    public Integer delete(Integer id) { 
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Integer result = 0;
        try { 
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement("delete from student where id = " + id);
            result = preparedStatement.executeUpdate();
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            JDBCUtil.release(resultSet, preparedStatement, connection);
        }
        return result;
    }
    @Override
    public Integer update(Student student) { 
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Integer result = 0;
        try { 
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement("update student set age = " + student.getAge() + " where name = '" + student.getName() + "'");
            result = preparedStatement.executeUpdate();
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            JDBCUtil.release(resultSet, preparedStatement, connection);
        }
        return result;
    }
}

0x03 测试

1. 增删改查测试类编写

package com.shaonaiyi.dao;
import com.shaonaiyi.domain.Student;
import org.junit.Test;
import java.util.List;
/** * @Auther: shaonaiyi@163.com * @Date: 2021/1/13 15:34 * @Description: Student访问接口实现测试类 */
public class StudentDAOImplTest { 
    @Test
    public void testQuery() { 
        StudentDAO studentDAO = new StudentDAOImpl();
        List<Student> studentList =  studentDAO.query();
        for (Student student : studentList) { 
            System.out.println("---------------------------");
            System.out.println("学生id：" + student.getId());
            System.out.println("学生name：" + student.getName());
            System.out.println("学生age：" + student.getAge());
        }
    }
    @Test
    public void testInsert() { 
        StudentDAO studentDAO = new StudentDAOImpl();
        Student student = new Student();
        student.setName("邵奈二");
        student.setAge(18);
        Integer result = studentDAO.save(student);
        System.out.println("结果：" + result);
    }
    @Test
    public void testDelete() { 
        StudentDAO studentDAO = new StudentDAOImpl();
        Integer result = studentDAO.delete(2);
        System.out.println("结果：" + result);
    }
    @Test
    public void testUpdate() { 
        StudentDAO studentDAO = new StudentDAOImpl();
        Student student = new Student();
        student.setName("邵奈一");
        student.setAge(32);
        Integer result = studentDAO.update(student);
        System.out.println("结果：" + result);
    }
// @Test
// public void testSQL() { 
// Student student = new Student();
// student.setName("邵奈一");
// student.setAge(32);
// String sql = "update student set age = " + student.getAge() + " where name = '" + student.getName() + "'";
// System.out.println(sql);
//
// }
}

温馨提示：为了方便，这里把SQL语句直接写死在了参数里，其实可以赋值给一个字符串，增加代码的可读性，并且方便打印出来看看，免得自己在拼接SQL语句的时候拼接错误。

当然，如果担心拼接错误，完全可以以下代码，通过设置坐标传值进去：

preparedStatement = connection.prepareStatement("insert into student(name,age) values (?,?)");
preparedStatement.setString(1, student.getName());
preparedStatement.setInt(2,student.getAge());