package jdbc;

import com.mysql.jdbc.Driver;  //這是我們驅(qū)動(dòng)的路徑

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class Jdbc01 {
    public static void main(String[] args) throws SQLException {

        //1.注冊(cè)驅(qū)動(dòng)
        Driver driver = new Driver();

        //2.得到連接
        //jdbc:mysql:// 規(guī)定好的協(xié)議  localhost 連接的地址  3306 監(jiān)聽(tīng)的端口 test_table 連接的數(shù)據(jù)庫(kù)
        String url = "jdbc:mysql://localhost:3306/test_table";
        Properties properties = new Properties();
        //user和password 規(guī)定好的不能隨意改
        properties.setProperty("user", "root");//
        properties.setProperty("password", "161142");
        Connection connect = driver.connect(url, properties); //相當(dāng)于網(wǎng)絡(luò)連接

        //3.執(zhí)行sql語(yǔ)句
        //String sql = "insert into actor values(null,'syj','女','2000-05-26','110')";
        String sql = "update actor set name = 'xhj' where id =  2";
        Statement statement = connect.createStatement();
        int rows = statement.executeUpdate(sql);    //返回影響的行數(shù)
        if (rows > 0) System.out.println("添加成功");
        else System.out.println("添加失敗");

        //4.關(guān)閉連接資源
        statement.close();
        connect.close();
    }
}

public class JdbcConn {
    @Test    /* 第一種 */
    public void testConn01() throws SQLException {
        //獲取Driver實(shí)現(xiàn)類(lèi)對(duì)象
        Driver driver = new Driver();

        String url = "jdbc:mysql://localhost:3306/test_table";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "161142");
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
    }
    
    @Test    /* 第二種 */
    public void testConn02() throws Exception{
        //使用反射加載Driver類(lèi),動(dòng)態(tài)加載,可以通過(guò)配置文件靈活使用各種數(shù)據(jù)庫(kù)
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.getDeclaredConstructor().newInstance();

        String url = "jdbc:mysql://localhost:3306/test_table";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "161142");
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
    }
    
    @Test    /* 第三種 */
    //DriverManager統(tǒng)一來(lái)管理Driver
    public void testConn03() throws Exception{
        //使用反射加載Driver類(lèi)
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.getDeclaredConstructor().newInstance();

        //創(chuàng)建url和user和password
        String url = "jdbc:mysql://localhost:3306/test_table";
        String user = "root";
        String password = "161142";

        DriverManager.registerDriver(driver);   //注冊(cè)Driver驅(qū)動(dòng)

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }
    
    @Test    /* 第四種 */
    public void testConn04() throws Exception{
        //使用反射加載Driver類(lèi)
        Class.forName("com.mysql.jdbc.Driver");
        /* Class.forName("com.mysql.jdbc.Driver")在底層加載Driver時(shí)自動(dòng)完成注冊(cè)驅(qū)動(dòng),簡(jiǎn)化代碼
            //在底層加載Driver時(shí)會(huì)自動(dòng)加載靜態(tài)代碼塊
            static {
                try {
                    DriverManager.registerDriver(new Driver());
                } catch (SQLException var1) {
                    throw new RuntimeException("Can't register driver!");
                }
            }
        */
        //Class.forName("com.mysql.jdbc.Driver");
        /*  Class.forName("com.mysql.jdbc.Driver");這句話(huà)也可以去掉
            mysql驅(qū)動(dòng)5.1.6可以無(wú)需CLass.forName(“com.mysql.jdbc.Driver");
            從jdk1.5以后使用了jdbc4,不再需要顯示調(diào)用class.forName()注冊(cè)驅(qū)動(dòng)而是自動(dòng)調(diào)用驅(qū)動(dòng)
            jar包下META-INF\services\java.sqI.Driver文本中的類(lèi)名稱(chēng)去注冊(cè)
            建議還是寫(xiě)上,更加明確
        */
        //創(chuàng)建url和user和password
        String url = "jdbc:mysql://localhost:3306/test_table";
        String user = "root";
        String password = "161142";

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }
    
    @Test    /* 第五種(推薦使用) */
    public void testConn05() throws Exception{
        //在方式4的情況下,將信息放到配置文件里,利于后續(xù)可持續(xù)操作
        //獲取配置文件信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //獲取相關(guān)信息
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        Class.forName(driver);  //加載Driver類(lèi),建議加上

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }
}

public class jdbc03 {
    public static void main(String[] args) throws Exception {
        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);
        Statement statement = connection.createStatement();

        ResultSet resultSet = statement.executeQuery("select id,`name`,sex,borndate from actor;");

        while (resultSet.next()){ //resultSet.previous();//向上移動(dòng)一行
            int id = resultSet.getInt(1);
            //int id = resultSet.getInt("id"); //也可以按照列明來(lái)獲取
            String name = resultSet.getString(2);
            String sex = resultSet.getString(3);
            Date date = resultSet.getDate(4);
            //Object object = resultSet.getObject(索引|列明); //對(duì)象形式操作(分情況考慮)
            System.out.println(id + "\t" + name + "\t" + sex + "\t" + date);
        }

        statement.close();
        connection.close();
    }
}

create table admit(name varchar(32),password varchar(32));
insert into admit values('tom','1234');
select * from admit where name = 'tom' and password = '1234'; # 輸出 tom 1234
# 如果有人輸入 name 為 1' or   password 為  or '1' = '1
# 那么select 就變成
select * from admit where name = '1' or ' and password = ' or '1' = '1'; # 其中'1' = '1'永遠(yuǎn)成立

public class jdbc04 {
    public static void main(String[] args) throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.print("請(qǐng)輸入用戶(hù)名:");
        String name = scanner.nextLine();
        System.out.print("請(qǐng)輸入密碼:");
        String 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");
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);

        //Statement statement = connection.createStatement();
        //preparedStatement是PreparedStatement實(shí)現(xiàn)類(lèi)的對(duì)象
        PreparedStatement preparedStatement = connection.prepareStatement("select `name` ,`password` " +
                "from admit where name = ? and password = ?");
        preparedStatement.setString(1,name);    //?號(hào)下標(biāo)從1開(kāi)始
        preparedStatement.setString(2,pwd);

        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) System.out.println("登錄成功");
        else System.out.println("登陸失敗");

        preparedStatement.close();
        connection.close();
    }
}

public class jdbc05 {
    public static void main(String[] args) throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.print("請(qǐng)輸入用戶(hù)名:");
        String name = scanner.nextLine();
        System.out.print("請(qǐng)輸入密碼:");
        String 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");
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);

        //添加
        String sql1 = "insert into admit values(?,?)";
        //修改
        String sql2 = "update admit set name = ? where name = ? and password = ?";
        //刪除
        String sql3 = "delete from admit where name = ? and password = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql3);
        //preparedStatement.setString(1,name+"plas");    //?號(hào)下標(biāo)從1開(kāi)始
        //preparedStatement.setString(2,name);
        //preparedStatement.setString(3,pwd);

        preparedStatement.setString(1,name);
        preparedStatement.setString(2,pwd);

        int rows = preparedStatement.executeUpdate();
        if (rows > 0) System.out.println("操作成功");
        else System.out.println("操作失敗");

        preparedStatement.close();
        connection.close();
    }
}

package utils;
import java.sql.*;
import java.io.FileInputStream;
import java.util.Properties;
public class JDBCUtils {
    private static String user;     //用戶(hù)名
    private static String password; //密碼
    private static String url;      //連接數(shù)據(jù)庫(kù)的url
    private static String driver;   //驅(qū)動(dòng)
    
    //靜態(tài)代碼塊進(jìn)行行初始化
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\mysql.properties"));
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");
        } catch (Exception e) {
            //實(shí)際開(kāi)發(fā)過(guò)程中(將編譯異常,改成運(yùn)行異常,用戶(hù)可以捕獲異常,也可以默認(rèn)處理該異常)
            throw new RuntimeException(e);
        }
    }
    //連接
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //關(guān)閉資源
    public static void close(ResultSet set, Statement statement,Connection connection){
        try {
            if (set != null) set.close();
            if (statement != null)statement.close();
            if (connection != null)connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

public class JdbcUtilsTest {
    @Test  //測(cè)試select操作
    public void testSelect() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //得到連接
            connection = JDBCUtils.getConnection();

            //設(shè)置sql
            String sql = "select * from actor where id = ?";

            //創(chuàng)建PreparedStatement
            preparedStatement = connection.prepareStatement(sql);

            //占位賦值
            preparedStatement.setInt(1,2);

            //執(zhí)行
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                /* 也可以這樣寫(xiě)
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                Date date = resultSet.getDate("borndate");
                String phone = resultSet.getString("phone");
                 */
                int id = resultSet.getInt(1);
                String name = resultSet.getString(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);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
    }

    @Test  //測(cè)試DML操作
    public void testDML() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            //得到連接
            connection = JDBCUtils.getConnection();

            //設(shè)置sql
            String sql = "update actor set name = ?,sex = ? where id = ?";

            //創(chuàng)建PreparedStatement
            preparedStatement = connection.prepareStatement(sql);

            //占位符賦值
            preparedStatement.setString(1, "sxy");
            preparedStatement.setString(2, "男");
            preparedStatement.setInt(3, 2);

            //執(zhí)行
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }
}

public class Jdbc06 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            connection.setAutoCommit(false);//關(guān)閉自動(dòng)提交(開(kāi)啟事務(wù))

            //第一個(gè)動(dòng)作
            String sql = "update actor set phone = phone - 10 where id = 2";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            //int i = 1/0;  異常

            //第二個(gè)動(dòng)作
            sql = "update actor set phone = phone + 10 where id = 1";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            //提交事務(wù)
            connection.commit();

        } catch (Exception e) {
            System.out.println("有異常存在,撤銷(xiāo)sql服務(wù)");
            try {
                connection.rollback();  //回滾到事務(wù)開(kāi)始的地方
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }

    }
}

public class Jdbc07 {
    @Test   //普通處理5000條插入數(shù)據(jù)   執(zhí)行時(shí)間169839
    public void test01() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "insert into actor(id,`name`,sex) values (?,?,'男')";
            preparedStatement = connection.prepareStatement(sql);
            long begin = System.currentTimeMillis();
            for (int i = 0; i < 5000; i++) {
                preparedStatement.setString(1, 3 + i + "");
                preparedStatement.setString(2, "sxy" + (i + 1));
                preparedStatement.executeUpdate();
            }
            long end = System.currentTimeMillis();
            System.out.println(end - begin);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }

    @Test   //批處理   執(zhí)行時(shí)間429
    public void test02() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "insert into actor(id,`name`,sex) values (?,?,'男')";
            preparedStatement = connection.prepareStatement(sql);
            long begin = System.currentTimeMillis();
            for (int i = 0; i < 5000; i++) {
                preparedStatement.setString(1, 3 + i + "");
                preparedStatement.setString(2, "sxy" + (i + 1));
                
                //將sql語(yǔ)句加入批處理包中
                preparedStatement.addBatch();
                /*
                    preparedStatement.addBatch()在底層把每一條數(shù)據(jù)加入到ArrayList
                    執(zhí)行過(guò)程:檢查本條sql中的語(yǔ)法問(wèn)題 -> 把本條sql語(yǔ)句加入到ArrayList -> 每1000條執(zhí)行一次
                    批處理優(yōu)點(diǎn):減少了編譯次數(shù),又減少了運(yùn)行次數(shù),效率大大提高
                    還需要在properties配置文件中將url加上?rewriteBatchedStatements=true
                    url=jdbc:mysql://localhost:3306/test_table?rewriteBatchedStatements=true
                */
                
                //當(dāng)有1000條時(shí),在進(jìn)行處理
                if ((i + 1) % 1000 == 0) {
                    preparedStatement.executeBatch();
                    //清空批處理包
                    preparedStatement.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println(end - begin);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }
}

public class ConQuestion {
    public static void main(String[] args) {
        //看看連接-關(guān)閉 connection 會(huì)耗用多久
        long start = System.currentTimeMillis();
        System.out.println("開(kāi)始連接.....");
        for (int i = 0; i < 5000; i++) {
            //使用傳統(tǒng)的jdbc方式，得到連接
            Connection connection = JDBCUtils.getConnection();
            //做一些工作，比如得到PreparedStatement ，發(fā)送sql
            //..........
            //關(guān)閉
            JDBCUtils.close(null, null, connection);

        }
        long end = System.currentTimeMillis();
        System.out.println("傳統(tǒng)方式5000次 耗時(shí)=" + (end - start));//傳統(tǒng)方式5000次 耗時(shí)=6249
    }
}

public class C3P0_ {
    @Test   //方式一: 相關(guān)參數(shù),在程序中指定user,url,password等
    public void testC3P0_1() throws Exception {
        //創(chuàng)建一個(gè)數(shù)據(jù)源對(duì)象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

        //通過(guò)配合文件獲取相關(guān)連接信息
        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");

        //給數(shù)據(jù)源(comboPooledDataSource)設(shè)置相關(guān)參數(shù)
        //連接管理是由comboPooledDataSource(連接池)來(lái)管理的
        comboPooledDataSource.setDriverClass(driver);   //設(shè)置驅(qū)動(dòng)
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);

        //初始化數(shù)據(jù)源的連接數(shù)
        comboPooledDataSource.setInitialPoolSize(10);
        //數(shù)據(jù)庫(kù)連接池最大容量,如果還有連接請(qǐng)求,那么就會(huì)將該請(qǐng)求放入等待隊(duì)列中
        comboPooledDataSource.setMaxPoolSize(50);

        //測(cè)試連接池的效率, 測(cè)試對(duì)mysql 5000次操作
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            //getConnection()這個(gè)方法就是重寫(xiě)了DataSource接口的方法
            Connection connection = comboPooledDataSource.getConnection();
            connection.close();
        }
        long end = System.currentTimeMillis();
        //c3p0 5000連接mysql 耗時(shí)=456
        System.out.println("c3p0 5000連接mysql 耗時(shí)=" + (end - start));
        comboPooledDataSource.close();
    }
    
    
    //第二種方式 使用配置文件模板來(lái)完成
    //將C3P0 提供的 c3p0.config.xml 拷貝到 src目錄下
    //該文件指定了連接數(shù)據(jù)庫(kù)和連接池的相關(guān)參數(shù)
    @Test
    public void testC3P0_02() throws SQLException {
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("sxy");
        //測(cè)試5000次連接mysql
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connection = comboPooledDataSource.getConnection();
            connection.close();
        }
        long end = System.currentTimeMillis();
        //c3p0的第二種方式(5000) 耗時(shí)=419
        System.out.println("c3p0的第二種方式(5000) 耗時(shí)=" + (end - start));

    }
}

public class Druid_ {
    @Test
    public void testDruid() throws Exception {
        //1.加入Druid jar包
        //2.加入 配置文件 druid.properties 放到src目錄下
        //3.創(chuàng)建Properties對(duì)象
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));

        //4.創(chuàng)建一個(gè)指定參數(shù)的數(shù)據(jù)庫(kù)連接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 500000; i++) {
            Connection connection = dataSource.getConnection();
            connection.close();
        }
        long end = System.currentTimeMillis();
        //Druid的500000次創(chuàng)建 耗時(shí)=643
        System.out.println("Druid的500000次創(chuàng)建 耗時(shí)=" + (end - start));
    }
}

public class JDBCUtilsByDruid {
    private static DataSource ds;

    //在靜態(tài)代碼塊完成 ds初始化
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //編寫(xiě)getConnection方法
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    //關(guān)閉連接:在數(shù)據(jù)庫(kù)連接池技術(shù)中,close不是真的斷掉連接,而是把使用的Connection對(duì)象放回連接池
    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);
        }
    }
}

public class TestUtilsByDruid {
    @Test
    public void testSelect() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //得到連接
            connection = JDBCUtilsByDruid.getConnection();
            System.out.println(connection.getClass());
            //connection 的運(yùn)行類(lèi)型 class com.alibaba.druid.pool.DruidPooledConnection
            //設(shè)置sql
            String sql = "select * from actor where id = ?";
            //創(chuàng)建PreparedStatement
            preparedStatement = connection.prepareStatement(sql);
            //占位賦值
            preparedStatement.setInt(1, 2);
            //執(zhí)行
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String name = resultSet.getString(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);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
        }
    }
}

public class Actor { //Javabean, POJO, Domain對(duì)象
    private Integer id;
    private String name;
    private String sex;
    private Date borndate;
    private String phone;
    public Actor() { //一定要給一個(gè)無(wú)參構(gòu)造器[反射需要]
    }

    public Actor(Integer id, String name, String sex, Date 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 Date getBorndate() { return borndate; }
    public void setBorndate(Date 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 + '\'' + '}';
    }
}

public class LikeApDB {

    @Test
    public /*也可以返回ArrayList<Actor>*/void testSelectToArrayList() {
        Connection connection = null;
        String sql = "select * from actor where id >= ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Actor> list = new ArrayList<>();
        try {
            connection = JDBCUtilsByDruid.getConnection();
            System.out.println(connection.getClass());
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");//getName()
                String sex = resultSet.getString("sex");//getSex()
                Date borndate = resultSet.getDate("borndate");
                String phone = resultSet.getString("phone");
                //把得到的 resultSet 的記錄，封裝到 Actor對(duì)象，放入到list集合
                list.add(new Actor(id, name, sex, borndate, phone));
            }

            System.out.println("list集合數(shù)據(jù)=" + list);
            for(Actor actor : list) {
                System.out.println("id=" + actor.getId() + "\t" + actor.getName());
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //關(guān)閉資源
            JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
        }
        //因?yàn)锳rrayList 和 connection 沒(méi)有任何關(guān)聯(lián)，所以該集合可以復(fù)用.
        //return  list;
    }
}

package datasourse;

import ApDB.Actor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class DBUtils_Use {
    @Test       //查詢(xún)多條數(shù)據(jù)
    public void testQueryMany() throws Exception {
        //1.得到連接(Druid)
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.使用 DBUtils 類(lèi)和接口,先引入 DBUtils jar文件 ,放到src目錄下
        //3.創(chuàng)建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4.執(zhí)行相應(yīng)的方法,返回ArrayList結(jié)果集
        String sql = "select * from actor where id >= ?";
        //String sql = "select id,`name` from actor where id >= ?";
        /*
        (1) query 方法就是執(zhí)行sql 語(yǔ)句，得到resultSet ---封裝到 --> ArrayList 集合中
        (2) 返回集合
        (3) connection: 連接
        (4) sql : 執(zhí)行的sql語(yǔ)句
        (5) new BeanListHandler<>(Actor.class): 在將resultSet -> Actor 對(duì)象 -> 封裝到 ArrayList
            底層使用反射機(jī)制 去獲取Actor 類(lèi)的屬性，然后進(jìn)行封裝
        (6) 1 就是給 sql 語(yǔ)句中的? 賦值，可以有多個(gè)值，因?yàn)槭强勺儏?shù)Object... params
        (7) 底層得到的resultSet ,會(huì)在query 關(guān)閉, 關(guān)閉PreparedStatement
         */
        List<Actor> query =
                queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
        /**
         * 分析 queryRunner.query方法源碼分析
         * public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
         *         PreparedStatement stmt = null;//定義PreparedStatement
         *         ResultSet rs = null;//接收返回的 ResultSet
         *         Object result = null;//返回ArrayList
         *
         *         try {
         *             stmt = this.prepareStatement(conn, sql);//創(chuàng)建PreparedStatement
         *             this.fillStatement(stmt, params);//對(duì)sql 進(jìn)行 ? 賦值
         *             rs = this.wrap(stmt.executeQuery());//執(zhí)行sql,返回resultset
         *             result = rsh.handle(rs);//返回的resultset --> arrayList[result] [使用到反射，對(duì)傳入class對(duì)象處理]
         *         } catch (SQLException var33) {
         *             this.rethrow(var33, sql, params);
         *         } finally {
         *             try {
         *                 this.close(rs);//關(guān)閉resultset
         *             } finally {
         *                 this.close((Statement)stmt);//關(guān)閉preparedstatement對(duì)象
         *             }
         *         }
         *
         *         return result;
         *     }
         */
        for (Actor actor : query) {
            System.out.print(actor);
        }
        JDBCUtilsByDruid.close(null,null,connection);
    }

    @Test               //查詢(xún)單條記錄
    public void testQuerySingle() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from actor where id = ?";
        //已知查詢(xún)的是單行,所以就用BeanHandler,返回一個(gè)對(duì)應(yīng)的對(duì)象
        Actor query = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 2);
        System.out.print(query);
        JDBCUtilsByDruid.close(null,null,connection);
    }

    @Test               //查詢(xún)單行單列(某個(gè)信息)  返回一個(gè)Object對(duì)象
    public void testQuerySingleObject() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select `name` from actor where id = ?";
        //已知查詢(xún)的是單行單列,所以就用BeanHandler,返回一個(gè)Object
        Object query = queryRunner.query(connection, sql, new ScalarHandler(), 1);
        System.out.println(query);
        JDBCUtilsByDruid.close(null,null,connection);
    }

    @Test       //演示DML操作(insert,update,delete)
    public void testDML() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();

        //String sql = "update actor set phone = ? where id = ?";
        //int affectedRow = queryRunner.update(connection, sql, "110", 2);

        String sql = "insert into actor values(?,?,?,?,?)";
        int affectedRow = queryRunner.update(connection, sql, 3, "xhj", "女", "2000-05-26", "110");

        //String sql = "delete from actor where id = ?";
        //int affectedRow = queryRunner.update(connection, sql, 5004);

        System.out.println(affectedRow > 0 ? "OK" : "NO");
        JDBCUtilsByDruid.close(null,null,connection);
    }
}

public class BasicDAO<T> {  //泛型指定具體的類(lèi)型
    private QueryRunner queryRunner = new QueryRunner();

    //開(kāi)發(fā)通用的DML,針對(duì)任意表
    public int update(String sql,Object... parameter){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.update(connection, sql, parameter);
        } catch (SQLException e) {
            throw new RuntimeException(e);//將編譯異常轉(zhuǎn)化成運(yùn)行異常,可以被捕獲,也可以被拋出
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }

    /**                     返回多個(gè)對(duì)象(多行結(jié)果)
     *
     * @param sql       sql語(yǔ)句,可以存在？
     * @param clazz     傳入一個(gè)類(lèi)的class對(duì)象   例如Actor.class
     * @param parameter 傳入?號(hào)具體的值,可以有多個(gè)
     * @return          根據(jù)類(lèi)似Actor.class類(lèi)型,返回對(duì)應(yīng)的ArrayList集合
     */
    public List<T> QueryMultiply(String sql,Class<T> clazz, Object... parameter){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.query(connection,sql,new BeanListHandler<T>(clazz),parameter);
        } catch (SQLException e) {
            throw new RuntimeException(e);//將編譯異常轉(zhuǎn)化成運(yùn)行異常,可以被捕獲,也可以被拋出
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
    //返回單個(gè)對(duì)象(單行數(shù)據(jù))
    public T querySingle(String sql,Class<T> clazz,Object... parameter){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.query(connection,sql,new BeanHandler<T>(clazz),parameter);
        } catch (SQLException e) {
            throw new RuntimeException(e);//將編譯異常轉(zhuǎn)化成運(yùn)行異常,可以被捕獲,也可以被拋出
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
    //返回單個(gè)對(duì)象的單個(gè)屬性(單行中的單列)
    public Object queryScalar(String sql,Object... parameter){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.query(connection,sql,new ScalarHandler(),parameter);
        } catch (SQLException e) {
            throw new RuntimeException(e);//將編譯異常轉(zhuǎn)化成運(yùn)行異常,可以被捕獲,也可以被拋出
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
}

public class Actor { //Javabean, POJO, Domain對(duì)象
    private Integer id;
    private String name;
    private String sex;
    private Date borndate;
    private String phone;
    public Actor() { //一定要給一個(gè)無(wú)參構(gòu)造器[反射需要]
    }

    public Actor(Integer id, String name, String sex, Date 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 Date getBorndate() { return borndate; }
    public void setBorndate(Date 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 + '\'' + '}';
    }
}

public class ActorDAO extends BasicDAO<Actor> {
}

public class TestDAO {
    @Test//測(cè)試ActorDAO對(duì)actor表的操作
    public void testActorDAO() {
        ActorDAO actorDAO = new ActorDAO();
        //1.查詢(xún)多行
        List<Actor> actors = actorDAO.QueryMultiply("select * from actor where id >= ?", Actor.class, 1);
        System.out.println(actors);

        //2.查詢(xún)單行
        Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 1);
        System.out.println(actor);

        //3.查詢(xún)單行單個(gè)數(shù)據(jù)
        Object o = actorDAO.queryScalar("select name from actor where id = ?", 1);
        System.out.println(o);

        //4.DML操作   當(dāng)前演示update
        int affectedRow = actorDAO.update("update actor set phone = ? where id = ?", "120", 3);
        System.out.println(affectedRow > 0 ? "OK" : "NO");
    }
}