五、数据库编程

1、JDBC的设计

1.1 JDBC的典型用法

在三层应用模型中，客户端不直接调用数据库，而是调用服务器上的中间件层，由中间件层完成数据库查询操作，这种三层模型有以下优点：它将可视化表示（位于客户端）从业务逻辑（位于中间层）和原始数据（位于数据库）中分离处理。

2、JDBC配置

jdbc.driver = com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/aaa?serverTimezone=CTT&characterEncoding=UTF-8
jdbc.username = root
jdbc.password = root

public class JDBCDemo01 {
    public static void main(String[] args) throws IOException, SQLException, ClassNotFoundException {
        //1、读取配置
        Properties properties = new Properties();
        try (InputStream in = Files.newInputStream(Paths.get("database.properties"))) {
            properties.load(in);
        }
        //2、注册驱动类
        String driver = properties.getProperty("jdbc.driver");
        Class.forName(driver);
        //3、连接到数据库
        String url = properties.getProperty("jdbc.url");
        String username = properties.getProperty("jdbc.username");
        String password = properties.getProperty("jdbc.password");
        Connection connection = DriverManager.getConnection(url, username, password);
        //4、执行sql
        Statement statement = connection.createStatement();
        statement.executeUpdate("insert into t_student (name, no) values ('张三', 13)");
        ResultSet resultSet = statement.executeQuery("select * from t_student");
        if (resultSet.next()){
            System.out.println(resultSet.getString(2));
        }
    }
}

3、执行SQL语句

        //返回修改的行数
        int a = statement.executeUpdate("insert into t_student (name, no) values ('王五', '20201515')");
        int b = statement.executeUpdate("update t_student set name = '张三update' where no = '20201313'");
        System.out.println(a + " --------- " + b);
        ResultSet resultSet = statement.executeQuery("select * from t_student");
        if (resultSet.next()){
            //输出查询结果第二列
            System.out.println(resultSet.getString(2));
            //输出查询结果字段名为“no”列
            System.out.println(resultSet.getString("no"));
        }

        String sql1 = "select * from t_student where name = ?";
        //设置预备语句
        PreparedStatement statement2 = connection.prepareStatement(sql1);
        //执行预备语句之前设置 ？ 参数（1代表第一个？， 后面的是参数值）
        statement2.setString(1, "张三update");
        ResultSet resultSet1 = statement2.executeQuery();
        if (resultSet1.next()){
            //输出查询结果第二列
            System.out.println(resultSet1.getString(2));
            //输出查询结果字段名为“no”列
            System.out.println(resultSet1.getString("no"));

4、事务

我们可以将一组语句构建成一个事务。当所有语句都顺利执行之后，事务可以被提交。否则，如果其中某个语句遇到错误，那么事务将被回滚。
将多个语句组合成事务的主要原因是为了确保数据库完整性。

public class JDBCDemo02 {
    public static void main(String[] args) throws IOException, SQLException, ClassNotFoundException {
        //1、读取配置
        Properties properties = new Properties();
        try (InputStream in = Files.newInputStream(Paths.get("D:\\codes\\study\\src\\main\\java\\com\\java02\\day05\\database.properties"))) {
            properties.load(in);
        }
        //2、注册驱动类
        String driver = properties.getProperty("jdbc.driver");
        Class.forName(driver);
        //3、连接到数据库
        String url = properties.getProperty("jdbc.url");
        String username = properties.getProperty("jdbc.username");
        String password = properties.getProperty("jdbc.password");
        Connection connection = DriverManager.getConnection(url, username, password);

        //关闭自动提交
        connection.setAutoCommit(false);

        //4、执行sql
        Statement statement = connection.createStatement();
        statement.executeUpdate("insert into t_student (name, no) values ('赵六', '20201616')");
        //还可以设置保存点，用来精确回滚操作
        //创建一个保存点，意味着回滚的时候只需回滚到这个位置，不需要回滚到事务最初的位置
        Savepoint savepoint = connection.setSavepoint();
        try {
            statement.executeUpdate("update t_student set name = '张三update111' where no = '20201313'");
            statement.executeUpdate("update t_student set name = '王五333' where no = '20201515'");
            //执行提交
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            //遇到错误执行回滚
            connection.rollback();
        }
        //如果设置了保存点，当不需要保存点时，必须要释放它。
        connection.releaseSavepoint(savepoint);

    }
}