/*
	准备工作：   在项目下创建一个文件夹libs
	将mysql.jar拷贝到目录下 点击add to project 加入到项目
*/
//1. 注册驱动
Driver driver = new Driver();//创建Driver对象 驱动

//2. 得到连接
//(1) jdbc:mysql://  规定好的表示协议，通过jdbc的方式连接mysql
//(2) localhost 主机  表示ip地址
//(3) 3306  表示端口
//(4) study 表示连接到 mysql study的那个数据库
//(5) mysql的连接本质就是网络连接 socket
String url = "jdbc:mysql://localhost:3306/study";
//将 用户名和密码放入到Properties 对象
Properties properties = new Properties();

// user 和 password 是规定好的 不可以更改 值根据实际情况填写
properties.setProperty("user","root");//用户
properties.setProperty("password","123456");//密码

Connection connect = driver.connect(url, properties);//获取连接

//3. 执行sql
//String sql = "insert into actor values(1, '刘德华', '男', '1970-11-11', '110')";
//String sql = "update actor set name = '周星驰' where id = 1";
String sql = "delete from actor where id = 1";
// 执行前先获得statement对象
// statement 用于返回静态SQL语句并返回其生成的结果对象
// 使用statement 会存在sql注入问题 推荐使用preparedStatement 可防止此问题
Statement statement = connect.createStatement();
// 如果是dml 语句 返回的就是影响的行数
int rows = statement.executeUpdate(sql);

System.out.println(rows > 0 ? "成功" : "失败");

//4. 关闭连接资源
statement.close();
connect.close();

// 方式1获取Driver实现类对象
@Test
    public void connect01() throws SQLException {
        Driver driver = new Driver();
        String url = "jdbc:mysql://localhost:3306/study";
        Properties properties = new Properties();
        properties.setProperty("user","root");//用户
        properties.setProperty("password","123456");//密码
        Connection connect = driver.connect(url, properties);

        System.out.println("方式1：" + connect);
    }

// 方式2 -- 通过反射
@Test
public void connect02() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
    Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
    Driver driver = (Driver) aClass.newInstance();
    String url = "jdbc:mysql://localhost:3306/study";
    Properties properties = new Properties();
    properties.setProperty("user","root");//用户
    properties.setProperty("password","123456");//密码

    Connection connect = driver.connect(url, properties);
    System.out.println("方式2：" + connect);

//方式3 使用DriverManager 替代Driver 进行统一管理
@Test
public void connect03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
    
    Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
    Driver driver = (Driver) aClass.newInstance();

    //创建url  和 user 和 password
    String url = "jdbc:mysql://localhost:3306/study";
    String user = "root";
    String password = "123456";

    DriverManager.registerDriver(driver);//注册Driver驱动

    Connection connection = DriverManager.getConnection(url, user, password);
    System.out.println("第三种方式：" + connection);
}

//方式4 使用Class.forName 自动完成注册驱动 简化代码
//这种方式获取连接使用最多 推荐使用
@Test
public void connect04() throws ClassNotFoundException, SQLException {
    //使用反射加载Driver类
    Class.forName("com.mysql.jdbc.Driver");

    //创建url  和 user 和 password
    String url = "jdbc:mysql://localhost:3306/study";
    String user = "root";
    String password = "123456";

    Connection connection = DriverManager.getConnection(url, user, password);
    System.out.println("第4种方式：" + connection);
}

//方式5  ,在方式4的基础改进  增加配置文件，让mysql更加灵活
@Test
public void connect05() throws IOException, ClassNotFoundException, SQLException {

    //通过Properties 对象获取配置文件信息
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\\mysql.properties"));

    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    String url = properties.getProperty("url");
    String driver = properties.getProperty("driver");

    Class.forName(driver);

    Connection connection = DriverManager.getConnection(url, user, password);
    System.out.println("第5种方式：" + connection);
}

public class ResultSet_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {

        //获取配置文件中的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        //1. 加载Driver驱动
        Class.forName(driver);

        //2. 获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

        //3. 得到Statement
        Statement statement = connection.createStatement();

        //4. sql语句
        String sql = "select * from actor";
        ResultSet resultSet = statement.executeQuery(sql);

        //5. while 取出数据
        while(resultSet.next()) { //光标向后移动，没有更多行就返回 false
            int id = resultSet.getInt(1);//获取该列第1行
            String name = resultSet.getString(2);//获取该列第2行
            String sex = resultSet.getString(3);
            Date date = resultSet.getDate(4);
            String phone = resultSet.getString(5);
            System.out.println(id + "\t" + name + "\t" + sex + "\t" + date + "\t" + phone );
        }

        //6. 关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

public class Statement_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {

        //statement 存在sql注入问题 即利用字符串拼接问题 获取数据库的数据
        Scanner scanner = new Scanner(System.in);

        System.out.print("请输入用户名：");//输入名字  1' or
        String admin_name = scanner.nextLine();// 这里不能使用next() 因为遇到空格会报错
        System.out.print("请输入密码：");//输入万能密码 or '1' = '1
        String admin_pwd = scanner.nextLine();

        //获取配置文件中的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        //加载Driver驱动
        Class.forName(driver);

        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

        Statement statement = connection.createStatement();
        String sql = "select * from admin where name = '" + admin_name + "' and pwd = '" + admin_pwd + "'";

        ResultSet resultSet = statement.executeQuery(sql);

        if (resultSet.next()) {
            System.out.println("登入成功...");
        } else {
            System.out.println("登入失败...");
        }

        //关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

public class PreparedStatement_ {
    public static void main(String[] args) throws Exception {
        Scanner scanner = new Scanner(System.in);

        System.out.print("请输入用户名：");
        String admin_name = scanner.nextLine();// 这里不能使用next() 因为遇到空格会报错
        System.out.print("请输入密码：");
        String admin_pwd = scanner.nextLine();

        //获取配置文件中的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        //加载Driver驱动
        Class.forName(driver);

        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

        //preparedStatement 是实现了PreparedStatement接口的实现类的对象
        String sql = "select * from admin where name = ? and pwd = ?";//问号是占位符
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //给？ 赋值
        preparedStatement.setString(1,admin_name);//第一个参数指第几个问号，第二个参数指给这个问号赋的值
        preparedStatement.setString(2,admin_pwd);

        ResultSet resultSet = preparedStatement.executeQuery();//查询并返回ResultSet结果集

        if (resultSet.next()) {
            System.out.println("登入成功...");
        } else {
            System.out.println("登入失败...");
        }

        //关闭资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

public class PreparedStatementDML {
    public static void main(String[] args) throws Exception {

        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String admin_name = scanner.nextLine();// 这里不能使用next() 因为遇到空格会报错
//        System.out.print("请输入密码：");
//        String admin_pwd = scanner.nextLine();

        //获取配置文件中的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        //加载Driver驱动
        Class.forName(driver);

        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

        //preparedStatement 是实现了PreparedStatement接口的实现类的对象

        //添加记录
        //String sql = "insert into admin values(?,?)";//问号是占位符

        //修改记录
        //String sql = "update admin set pwd = ? where name = ?";

        //删除记录
        String sql = "delete from admin where name = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //给？ 赋值
        preparedStatement.setString(1,admin_name);
        //preparedStatement.setString(1,admin_pwd);

        int i = preparedStatement.executeUpdate();
        System.out.println(i > 0 ? "成功.." : "失败...");

        //关闭资源
        preparedStatement.close();
        connection.close();
    }
}

public class Transaction_ {

    //没有使用事务
    @Test
    public void noTransaction() {
        //1. 得到数据库连接
        Connection connection = null;
        //2. 执行sql语句
        String sql = "update account set balance = balance - 100 where id = 1";
        String sql2 = "update account set balance = balance + 100 where id = 2";
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();//在默认情况下 connection 是默认自动提交的
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();//执行第一条sql

            int i = 1 / 0;//抛出异常

            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();//执行第二条sql
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.close(null, connection, preparedStatement);
        }
    }

    //使用事务
    @Test
    public void useTransaction() {
        //1. 得到数据库连接
        Connection connection = null;
        //2. 执行sql语句
        String sql = "update account set balance = balance - 100 where id = 1";
        String sql2 = "update account set balance = balance + 100 where id = 2";
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();//在默认情况下 connection 是默认自动提交的
            //将connection 设置为不自动提交
            //这里会自动开启事务
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();//执行第一条sql

            //int i = 1 / 0;//抛出异常

            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();//执行第二条sql

            //提交事务
            connection.commit();

        } catch (SQLException e) {
            //进行回滚, 撤销执行的sql  默认回滚到事务开始的状态
            System.out.println("执行发生了异常，撤销执行的sql...");
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.close(null, connection, preparedStatement);
        }
    }
}

url=jdbc:mysql://localhost:3306/study?rewriteBatchedStatements=true

//批处理方法
addBatch();//添加需要批处理的SQL语句或参数
executeBatch();//执行批处理的语句
clearnBatch();//清空批处理语句

public class Batch_ {

    @Test
    //传统方法 添加5000条数据到admin2
    public void noBatch() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values(null, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.println("开始执行");
        long start = System.currentTimeMillis();//开始时间
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "666");
            preparedStatement.executeUpdate();
        }

        long end = System.currentTimeMillis();//结束时间
        System.out.println("传统耗时 = " + (end - start));//传统耗时 = 10208
        //关闭连接
        JDBCUtils.close(null, connection, preparedStatement);
    }

    @Test
    //使用批量方式 添加5000条数据到admin2
    //配置文件中需添加  ?rewriteBatchedStatements=true 开启批处理
    //url=jdbc:mysql://localhost:3306/study?rewriteBatchedStatements=true
    public void batch() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values(null, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.println("开始执行");
        long start = System.currentTimeMillis();//开始时间
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "666");
            //将sql语句加入到 批处理包中
            preparedStatement.addBatch();
            //当有1000条记录时 在批量执行
            if ((i + 1) % 1000 == 0) {
                preparedStatement.executeBatch();
                //清空
                preparedStatement.clearBatch();
            }
        }

        long end = System.currentTimeMillis();//结束时间
        System.out.println("批量耗时 = " + (end - start));//传统耗时 = 10208
        //关闭连接
        JDBCUtils.close(null, connection, preparedStatement);
    }

}

public class C3P0_ {

    //方式1 ： 相关参数 在程序中指定 user, url, password driver等

    @Test
    public void testC3P0_01() throws Exception {

        //1. 创建数据源对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        //2. 通过配置文件获取相关信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //读取相关信息
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        //3. 给数据源 comboPooledDataSource 设置参数
        //   连接管理由comboPooledDataSource 管理
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);

        //设置初始连接数
        comboPooledDataSource.setInitialPoolSize(10);
        //设置最大连接数
        comboPooledDataSource.setMaxPoolSize(50);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connection = comboPooledDataSource.getConnection();//这个方法就是从DataSource接口实现

            //关闭连接
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("c3p0 耗时 = " + (end - start));
    }


    @Test
    // 第二种方式 使用配置文件模板
    public void testC3P0_02() throws SQLException {

        ComboPooledDataSource comboPooledDataSource  = new ComboPooledDataSource("zb_study");//zb_study表示数据源名称

        long start = System.currentTimeMillis();
        //测试5000 次连接
        for (int i = 0; i < 500000; i++) {
            Connection connection = comboPooledDataSource.getConnection();
            //System.out.println("连接OK");
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("c3p0 第二种方式 耗时= " + (end-start));

    }
}

<c3p0-config>
    <!-- c3p0连接池配置 -->
    <!-- 数据源名称代表连接池 -->
    <named-config name= "zb_study">

        <!--默认是mysql数据库-->
        <!--驱动类-->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!--url-->
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/study</property>
        <!-- 数据库的用户名  默认值：null -->
        <property name="user">root</property>
        <!-- 数据库的密码  默认值：null -->
        <property name="password">123456</property>


        <!--初始化连接数  取值要在minPoolSize和maxPoolSize之间（可包含，闭区间） 默认值：3 -->
        <property name="initialPoolSize">5</property>
        <!-- 最大连接数 （连接池中的连接数不能超过maxPoolSize最大连接数） 默认值：15-->
        <property name="maxPoolSize">50</property>
        <!--最小连接数  默认值：3  -->
        <property name="minPoolSize">10</property>

        <!-- c3p0连接池中数据连接不够时（无空闲连接可用）,一次增长的个数（增长不能超过maxPoolSize最大连接个数） 默认值：3 -->
        <property name="acquireIncrement">5</property>
        <!-- 连接的最大空闲时间，如果超过这个时间还没有被使用，就断开这个连接（设置为0或负数，就永远都不会被断开） 单位：秒  默认值 ：0 -->
        <property name="maxIdleTime">600</property>

        <!-- 从数据库获取新连接失败后重复尝试的次数。小于等于0表示无限次  默认值: 30-->
        <property name="acquireRetryAttempts" value="30"/>
        <!-- 两次连接的中间间隔时间（重新尝试的时间间隔）  单位：毫秒  默认值：1000 -->
        <property name="acquireRetryDelay">1000</property>
        <!-- 连接关闭时，是否将所有未提交的操作进行事务回滚  默认值：false -->
        <property name="autoCommitOnClose">false</property>
        <!-- 当连接池用完时,客户端调用getConnection()后等待获取新连接的时间  单位：毫秒  默认值：0
                如果值设为 0，将无限期等待，直到有空闲连接。  否则按照设置的值，超时将抛出SQLException异常
                时间设置过小时会出现连接超时，这样会抛出SQLException异常，设置时间时需要小心，按照实际情况设置适当的值-->
        <property name="checkoutTimeout">0</property>
        <!-- 每隔多少秒检查所有连接池中的空闲连接  单位：秒   默认值：0 -->
        <property name="idleConnectionTestPeriod">60</property>

        <!-- 配置PreparedStatement缓存，设置连接池为数据源缓存的PreparedStatement的总数
                为0的时候不缓存，同时maxStatementsPerConnection的配置无效。
               由于PreparedStatement属于单个Connection,所以这个数量应该根据应用中平均连接数乘以每个连接的平均PreparedStatement来计算-->
        <property name="maxStatements">1000</property>
        <!-- 每个对象可连接的最多命令对象数 -->
        <property name="maxStatementsPerConnection">2</property>
    </named-config>

</c3p0-config>

public class Druid_ {

    @Test
    public void testDruid() throws Exception {

        //1. 导入Druid jar包
        //2. 加入配置文件 将该文件拷贝到项目的src目录
        //3. 读取配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));

        //4. 创建一个指定的数据库连接池 Druid连接
        DataSource dataSource =
                DruidDataSourceFactory.createDataSource(properties);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 500000; i++) {
            Connection connection = dataSource.getConnection();
            //System.out.println("连接成功");
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("druid 连接池 耗时 = " + (end - start));//druid 连接池 耗时 = 692
    }
}

public class JDBCUtilsByDruid {

    private static DataSource ds;
    //在静态代码块完成 ds初始化
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            //转换为运行异常
            throw new RuntimeException(e);
        }
    }

    //getConnection 方法
    public static Connection getConnection()  {
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //关闭连接
    //close 不是关闭连接 将使用的Connection 对象放回到连接池中
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if(resultSet != null) {
                resultSet.close();
            }
            if(statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

ArrayHandler//把结果集中的第一行数据转成对象数组。
ArrayListHandler//把结果集中的每一行数据都转成一个数组，再存放到List中。BeanHandler:将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler//将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
ColumnListHandler//将结果集中某一列的数据存放到List中
KeyedHandler(name)//将结果集中的每行数据都封装到Map里，再把这些map再存到一个map里，其key为指定的key.
MapHandler//将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
MapListHandler//将结果集中的每一行数据都封装到一个Map里，然后再存放到List

public class DBUtils_USE {

    @Test
    //使用 apache - DBUtils 工具类 + druid 完成对表的crud操作
    public void testQueryMany() throws SQLException {//返回结果是多行情况

        //1. 得到连接(druid)
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. 使用 DBUtils 类和接口 先导入 DBUtils包
        //3. 创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4. 执行相关方法 返回 ArrayList 结果集
        //String sql = "select * from actor where id >= ?";
        // 也可以查询部分
        String sql = "select id,name from actor where id >= ?";
        //(1) query 方法执行sql语句 得到 resultSet --封装到--> ArrayList 集合中
        //(2) 返回集合
        //(3) connection 连接
        //(4) sql : 执行的sql语句
        //(5) new BeanListHandler<>(Actor.class) 在底层将resultSet -> Actor 对象 —-> 封装到 ArrayList
        //    底层使用反射机制 获取Actor类的属性 然后封装
        //(6) 1 就是给 sql 语句中的? 赋值， 可以有多个值 因为是 Object... params
        //(7) 底层得到 resultSet  PreparedStatement 会在query 关闭
        List<Actor> list =
                queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);

        System.out.println("集合的信息");
        for (Actor actor : list) {
            System.out.println(actor);
        }

        //释放连接
        JDBCUtilsByDruid.close(null,null,connection);

    }


    @Test

    //返回结果是单行情况
    // Apache + dbutils + druid
    public void testQuerySingle() throws SQLException {

        //1. 先得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. 使用dbutils类 和接口
        //3. 创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4. 执行相关方法 返回单个对象
        String sql = "select * from actor where id = ?";
        //  因为返回的单行记录<----> 单个对象 使用的Handler 是BeanHandler
        Actor actor =
                queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 4);

        System.out.println(actor);
        //释放资源
        JDBCUtilsByDruid.close(null, null, connection);

    }



    @Test
    //返回单行单列的情况

    // apache + dbutils + druid
    public void testScalar() throws SQLException {

        //1. 得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. 创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //3. 执行sql
        String sql = "select name from actor where id = ?";
        //4. 执行相关方法
        Object obj =
                queryRunner.query(connection, sql, new ScalarHandler<>(), 4);
        //当查询的内容没有是 返回null
        System.out.println(obj);

    }


    @Test
    // apache + dbutils + druid 完成dml (update , insert, delete)

    public void testDml() throws SQLException {

        //1. 得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. 创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //3. 执行sql
        //String sql = "update actor set name = ? where id = ?";
        //String sql = "insert into actor values(null, ?, ?,?,?)";
        String sql = "delete from actor where id = ?";

        //4. 执行 dml 操作是 queryRunner.updated()
        //   返回的值是受影响的行数
        //int affectRows = queryRunner.update(connection, sql, "执笔", "男","2000-01-01","123456");
        int affectRows = queryRunner.update(connection, sql, 2);
        System.out.println(affectRows > 0 ? "执行成功" : "执行没有受影响到表");
        //释放资源
        JDBCUtilsByDruid.close(null,null,connection);
    }

}

/**
 * @author 执笔
 * @version 1.0
 * 该类对应ResultSet 中的数据
 */
public class Actor {

    private Integer id;
    private String name;
    private String sex;//mysql中字符也是字符串
    //这里使用 Date会报错 原因java实体类中的java.util.Date 和 mysql8中的datetime类型无法相互转换
    private String borndate;
    private String phone;

    public Actor() {//给予无参构造器便于反射使用
    }

    public Actor(Integer id, String name, String sex, String borndate, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.borndate = borndate;
        this.phone = phone;
    }

    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 String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getBorndate() {
        return borndate;
    }

    public void setBorndate(String borndate) {
        this.borndate = borndate;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "\nActor{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", borndate=" + borndate +
                ", phone='" + phone + '\'' +
                '}';
    }
}

/**
 * @author 执笔
 * @version 1.0
 * 开发basicDAO 是其他DAO的父类
 */
public class BasicDAO<T> {//泛型指定具体的类

    private QueryRunner qr = new QueryRunner();

    //通用的 执行dml的方法 针对任意的表
    public int update(String sql, Object... parameters) {

        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.update(connection, sql, parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }

    }

    //返回值是多行的情况
    public List<T> queryMultiply(String sql, Class<T> clazz, Object... parameters) {

        Connection connection = null;

        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }

    //返回值是单行的情况
    public T querySingle(String sql, Class<T> clazz, Object... parameters) {

        Connection connection = null;

        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }

    //返回是单行单列的情况
    public Object queryScalar(String sql, Object... parameters) {
        Connection connection = null;

        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new ScalarHandler<T>(), parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }
}

/**
 * @author 执笔
 * @version 1.0
 */
public class ActorDAO extends BasicDAO<Actor>{
    //1. 这里有BasicDAO 的所有方法
    //2. 添加独有的操作

}

public class TestDAO {

    @Test
    //查询获得多行
    public void tesActorDAO() {
        ActorDAO actorDAO = new ActorDAO();

        //1. 查询多行记录
        List<Actor> actors = actorDAO.queryMultiply("select * from actor where id > ?", Actor.class, 1);
        System.out.println("==== 多行查询结果 ====");
        for (Actor actor : actors) {
            System.out.println(actor);
        }

        //2. 查询单行记录
        Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 4);
        System.out.println("==== 单行查询结果 ====");
        System.out.println(actor);

        //3. 查询单行单列记录
        Object o = actorDAO.queryScalar("select name from actor where id = ?", 4);
        System.out.println("==== 单行单列查询结果 ====");
        System.out.println(o);

        //4. dml 操作 update insert delete
        int rows = actorDAO.update("update actor set name = ? where id = ?", "李白", 4);
        System.out.println("==== dml操作结果 ====");
        System.out.println(rows > 0 ? "执行成功" : "执行没有影响");
    }

    @Test
    public void testGoodsDAO() {

        //执行dml 操作
        GoodsDAO goodsDAO = new GoodsDAO();
        String sql = "insert into goods values(?, ?, ?)";
        //int rows = goodsDAO.update(sql, 10, "华为手机", 2000);
        int rows = goodsDAO.update(sql, 20, "苹果手机", 8000);
        System.out.println("==== dml操作结果 ====");
        System.out.println(rows > 0 ? "执行成功" : "执行没有影响");

        //查询多行记录
        List<Goods> goods =
                goodsDAO.queryMultiply("select * from goods where id > ?", Goods.class, 1);
        System.out.println("==== 多行查询结果 ====");
        for (Goods good :goods) {
            System.out.println(good);
        }

        //查询单行记录
        Object o = goodsDAO.querySingle("select * from goods where id = ?", Goods.class, 10);
        System.out.println("==== 单行查询结果 ====");
        System.out.println(o);

        //单行单列查询
        Object o1 = goodsDAO.queryScalar("select goods_name from goods where id = ?", 20);
        System.out.println(o1);
    }
}