【JavaOOP应用】7、JDBC

14. JDBC

14.1 相关 API 介绍

JDBC： Java Database Connection
API： Application Programming Interface (应用程序编程接口或规范)
SUM公司制定了JDBC规范：DriverManager 类
一套接口规范：Connection 连接、Statement 命令接口、ResultSet 结果集接口

14.2 Statement 命令对象

将相关的 myslq-connector 的 jar 包添加到当前工程中

定义实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private String stuno;
    private String stuname;
    private String classno;
    private String sex;
    private Integer age;

}

编写 JdbcUtil 工具类

public class JdbcUtil {
    
    /**
     * 使用配置文件加载数据库连接信息
     */
    public static  String driver;
    public static  String url;
    public static  String username;
    public static  String password;

    /**
     * 静态代码块加载驱动
     */
    static {
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        driver = bundle.getString("jdbc.driver");
        url = bundle.getString("jdbc.url");
        username = bundle.getString("jdbc.user");
        password = bundle.getString("jdbc.password");
        try {
            // 加载驱动
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 得到连接
     */
    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
}

编写 StudentDao 用于访问 Student 数据库表

/**
 * 查询所有学生
 */
@Override
public List<Student> findAll() throws SQLException {
    List<Student> students = new ArrayList<>();
    Statement statement = null;
    ResultSet resultSet = null;
    // 得到连接对象
    try (Connection conn = JdbcUtil.getConn()) {
        // 得到命令对象
        statement = conn.createStatement();
        // 执行命令，得到结果集对象
        String sql = "select * from student";
        resultSet = statement.executeQuery(sql);
        // 将结果集转换为集合
        while (resultSet != null && resultSet.next()) {
            // 从结果集中根据列名获取对应列的数据
            String stuno = resultSet.getString("stuno");
            String stuname = resultSet.getString("stuname");
            String classno = resultSet.getString("classno");
            String sex = resultSet.getString("sex");
            int age = resultSet.getInt("age");
            // 构造学生对象
            Student student = new Student(stuno, stuname, classno, sex, age);
            // 将学生对象放到集合中
            students.add(student);
        }
    } finally {
        // 关闭资源
        resultSet.close();
        statement.close();
    }
    return students;
}

测试 StudentDao

/**
 * 查询所有学生
 */
@Test
public void test01() throws SQLException {
    List<Student> students = studentDAO.findAll();
    students.forEach(System.out::println);
}

14.3 PreparedStatement 命令对象

编写 StudentDao

/**
 * 根据学号查询学生
 */
@Override
public Student findByStuno(String stuno) throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    Student student = null;
    // 获取连接对象
    try (Connection conn = JdbcUtil.getConn()) {
        // 得到命令对象
        String sql = "select * from student where stuno = ?";
        ps = conn.prepareStatement(sql);
        // 给占位符赋值
        ps.setString(1, stuno);
        // 执行命令，得到结果集
        rs = ps.executeQuery();
        // 将结果集转换为Student对象
        if (rs != null && rs.next()) {
            String stuname = rs.getString("stuname");
            String classno = rs.getString("classno");
            String sex = rs.getString("sex");
            int age = rs.getInt("age");
            // 构造学生对象
            student = new Student(stuno, stuname, classno, sex, age);
        }
    } finally {
        // 关闭资源
        rs.close();
        ps.close();
    }
    return student;
}

/**
 * 添加学生
 */
@Override
public boolean add(Student student) throws SQLException {
    PreparedStatement ps = null;
    int i = 0;
    // 获取连接对象
    try (final Connection conn = JdbcUtil.getConn()) {
        // 得到命令对象
        String sql = "insert into student value (?,?,?,?,?)";
        ps = conn.prepareStatement(sql);
        // 给占位符赋值
        ps.setString(1, student.getStuno());
        ps.setString(2, student.getStuname());
        ps.setString(3, student.getClassno());
        ps.setString(4, student.getSex());
        ps.setInt(5, student.getAge());
        // 执行命令
        i = ps.executeUpdate();
    } finally {
        // 关闭资源
        ps.close();
    }
    return i > 0;
}

/**
 * 修改学生
 */
@Override
public boolean update(Student student) throws SQLException {
    PreparedStatement ps = null;
    int i = 0;
    // 获取连接对象
    try (Connection conn = JdbcUtil.getConn()) {
        // 得到命令对象
        String sql = "update student set stuname = ?,classno = ?,sex = ?,age = ? where stuno = ?";
        ps = conn.prepareStatement(sql);
        // 给占位符赋值
        ps.setString(1, student.getStuname());
        ps.setString(2, student.getClassno());
        ps.setString(3, student.getSex());
        ps.setInt(4, student.getAge());
        ps.setString(5, student.getStuno());
        // 执行命令
        i = ps.executeUpdate();
    } finally {
        // 关闭资源
        ps.close();
    }
    return i > 0;
}

/**
 * 删除学生
 */
@Override
public boolean deleteByStuno(String stuno) throws SQLException {
    PreparedStatement ps = null;
    int i = 0;
    // 获取连接对象
    try (final Connection conn = JdbcUtil.getConn()) {
        // 得到命令对象
        String sql = "delete from student where stuno = ?";
        ps = conn.prepareStatement(sql);
        // 给占位符赋值
        ps.setString(1, stuno);
        // 执行命令
        i = ps.executeUpdate();
    } finally {
        // 关闭资源
        ps.close();
    }
    return i > 0;
}

测试 StudentDao

/**
 * 根据学号查询学生
 */
@Test
public void test02() throws SQLException {
    String stuno = "20080102";
    Student student = studentDAO.findByStuno(stuno);
    System.out.println(student);

}


/**
 * 添加学生
 */
@Test
public void test03() throws SQLException {
    Student student = new Student("20080202","小六子","200802","F",23);
    boolean b = studentDAO.add(student);
    if (b){
        System.out.println("添加成功!");
    }else {
        System.out.println("添加失败!");
    }
}


/**
 * 修改学生
 */
@Test
public void test04() throws SQLException {
    // 根据传入的学号查询出对应学生信息
    String stuno = "20080202";
    Student student = studentDAO.findByStuno(stuno);
    // 修改字段内容
    student.setStuname("王二麻子");
    student.setAge(28);
    // 修改学生
    boolean b = studentDAO.update(student);
    if (b){
        System.out.println("修改成功!");
    }else {
        System.out.println("修改失败!");
    }
}


/**
 * 删除学生
 */
@Test
public void test05() throws SQLException {
    // 根据传入的学号删除对应学生信息
    String stuno = "20080202";
    boolean b = studentDAO.deleteByStuno(stuno);
    if (b){
        System.out.println("删除成功!");
    }else {
        System.out.println("删除失败!");
    }
}

14.4 CallableStatement 命令对象

编写 StudentDao

/**
 * 访问无参的过程
 * 案例：查询所有的学生信息。
 */
@Override
public List<Student> findStudentsByProc() throws SQLException {
    CallableStatement ps = null;
    ResultSet rs = null;
    List<Student> students = new ArrayList<>();
    try(Connection conn = JdbcUtil.getConn()){
        // 6.1 得到命令对象
        ps = conn.prepareCall("call proc_1");
        // 6.2 执行命令返回结果集
        rs = ps.executeQuery();
        // 6.3 将结果集转换为集合
        while(rs != null && rs.next()){
            // 从结果集中根据列名获取对应列的数据
            String stuno = rs.getString("stuno");
            String stuname = rs.getString("stuname");
            String classno = rs.getString("classno");
            String sex = rs.getString("sex");
            int age = rs.getInt("age");
            // 构造学生对象
            Student student = new Student(stuno, stuname, classno, sex, age);
            // 将学生对象放到集合中
            students.add(student);
        }
    }finally {
        ps.close();
        rs.close();
    }
    return students;
}

/**
 * 带有输入参数的过程
 * 案例：查询指定性别的学生信息。
 */
@Override
public List<Student> findStudentsBySex(String sex) throws SQLException {
    CallableStatement ps = null;
    ResultSet rs = null;
    List<Student> students = new ArrayList<>();
    try(Connection conn = JdbcUtil.getConn()){
        // 7.1 得到命令对象
        ps = conn.prepareCall("call proc_2(?)");
        // 7.2 为占位符赋值
        ps.setString(1,sex);
        // 7.3 执行命令
        rs = ps.executeQuery();
        // 7.4 将结果集转换为集合
        while(rs != null && rs.next()){
            // 从结果集中根据列名获取对应列的数据
            String stuno = rs.getString("stuno");
            String stuname = rs.getString("stuname");
            String classno = rs.getString("classno");
            int age = rs.getInt("age");
            // 构造学生对象
            Student student = new Student(stuno, stuname, classno, sex, age);
            // 将学生对象放到集合中
            students.add(student);
        }
    }finally {
        rs.close();
        ps.close();
    }

    return students;
}

/**
 * 带有输入和输出参数的过程
 * 案例：根据性别查询性别的人数。
 */
@Override
public int findCountBySex(String sex) throws SQLException {
    CallableStatement ps = null;
    int count = 0;
    try(Connection conn = JdbcUtil.getConn()){
        // 8.1 得到命令对象
        ps = conn.prepareCall("call proc_3(?,?)");
        // 8.2 为占位符赋值(只为输入参数赋值)
        ps.setString(1,sex);
        // 8.3 为输出参数注册类型
        ps.registerOutParameter(2,Types.INTEGER);
        // 8.4 执行命令
        ps.execute();
        // 8.5 得到返回值
        count = ps.getInt(2);
    }finally {
        ps.close();
    }
    return count;
}

测试 StudentDao

/**
 * 访问无参的过程
 * 案例：查询所有的学生信息。
 */
@Test
public void test06() throws SQLException {
    List<Student> students = studentDAO.findStudentsByProc();
    students.forEach(System.out::println);
}

/**
 * 带有输入参数的过程
 * 案例：查询指定性别的学生信息。
 */
@Test
public void test07() throws SQLException {
    String sex = "女";
    List<Student> students = studentDAO.findStudentsBySex(sex);
    students.forEach(System.out::println);
}

/**
 * 带有输入和输出参数的过程
 * 案例：根据性别查询性别的人数。
 */
@Test
public void test08() throws SQLException {
    String sex = "男";
    int count = studentDAO.findCountBySex(sex);
    System.out.println(sex + "生共有" + count + "人！");
}