package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.jupiter.api.Test;

public class JDBC {
    @Test
    public void fun1() throws Exception {
        // 注册驱动
        String driverClassName = "com.mysql.jdbc.Driver";
        Class.forName(driverClassName);
        // 获取连接,jdbc协议的格式
        String url = "jdbc:mysql://localhost:3306/db1";
        String username = "root";
        String password = "747699";
        Connection con = DriverManager.getConnection(url, username, password);

        // 定义sql语句
        String sql1 = "update account set money = 2000 where id = 1";
        String sql2 = "INSERT INTO account VALUES('3','王五',3000)";
        // 获取SQL对象
        Statement stmt = con.createStatement();

        // 执行sql语句,返回影响的行数
        int count = stmt.executeUpdate(sql1); // executeUpdate是向mysql发送sql语句，不是只能发送update语句

        // 打印行数
        System.out.println("影响的行数：" + count);
    }

    @Test
    public void fun2() throws Exception {
        // 驱动名
        String driverClassName = "com.mysql.jdbc.Driver";
        // 注册驱动
        Class.forName(driverClassName);
        // 连接sql参数
        String url = "jdbc:mysql://localhost:3306/db1"; // mysql格式
        String username = "root";
        String password = "747699";

        // 连接sql
        Connection con = DriverManager.getConnection(url, username, password);

        // sql对象
        Statement stmt = con.createStatement();

        // 定义sql语句
        String sql1 = "SELECT * FROM account";

        // 发送sql语句,返回一个表格对象
        ResultSet re = stmt.executeQuery(sql1);

        // ResultSet中有一个行光标
        while (re.next()) { // 光标下移(下一行存在)
            int id = re.getInt(1); // 通过列编号获取
            String name = re.getString("name"); // 通过列名称获取
            double salary = re.getDouble(3); // 通过列编号获取
            System.out.println("id = " + id + " 、 name = " + name + " 、 salary = " + salary);
        }
        re.close();
        stmt.close();
        con.close();
    }

    /**
     * 进行SQL连接的规范(应该尽可能使用try...catch...finally...语句块以保证资源的关闭)
     * 
     * @throws SQLException
     */
    @Test
    public void fun3() throws Exception {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            // 四个参数
            String driverClassName = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/db1";
            String username = "root";
            String password = "747699";

            // 注册驱动
            con = DriverManager.getConnection(url, username, password);

            // 获取SQL对象
            st = con.createStatement();

            // 定义SQL语句
            String sql1 = "SELECT * FROM account";

            // 发送SQL语句
            rs = st.executeQuery(sql1);

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                double salary = rs.getDouble("money");
                System.out.println("id = " + id + " 、 name = " + name + " 、 salary = " + salary);
            }
        } finally {
            if (rs != null)
                rs.close();
            if (st != null)
                st.close();
            if (con != null)
                con.close();
        }
    }
}