JDBC进阶操作（Druid连接池 与 DBUtils工具类的使用）

书接上文，使用JDBC需要导入相应的 jar 包，使用drudi连接池和DBUtils工具类也是。

直接将jar包，复制粘贴到 lib 目录下，再从Project Structure 中导入即可。

阿里巴巴 Druid 连接池

Druid对数据库连接进行有效管理与重用,最大化程序执行效率
连接池负责创建管理连接,程序只负责取用与归还

ps：原来每执行一次SQL都要建立和关闭一次链接，非常浪费时间。使用数据库连接池是典型的空间换时间的思想。

使用：

1. 在 src 目录下 创建 druid-config.properties 文件（里面包含配置信息）
2. driverClassName=com.mysql.cj.jdbc.Driver
   url=jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
   username=root
   password=123456
   initialSize=10
   maxActive=20

initalSize 代表我们初始时创建的链接数量
maxActive 代表最大允许的链接数量，这个数值要够大，防止死机。

3.在文件中使用：

public static void main(String[] args) {
        //1.加载属性文件
        Properties properties = new Properties();
        String propertyFile = DruidSample.class.getResource("/druid-config.properties").getPath();
        System.out.println("propertyFile:"+propertyFile); //propertyFile:/D:/Users/dell/Desktop/imooc/JDBC/out/production/JDBC/druid-config.properties
        try { //容错，解决有的文件名中间有空格的问题！
            propertyFile = new URLDecoder().decode(propertyFile,"UTF-8");
            properties.load(new FileInputStream(propertyFile)); //加载配置文件
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e1){
            e1.printStackTrace();
        } catch (IOException e2){
            e2.printStackTrace();
        }
        //2.获取DateSource数据源对象
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            connection =dataSource.getConnection();
            //这一步往下就和之前的JDBC一样了
            String sql = "select * from employee limit 0,10";
            pst = connection.prepareStatement(sql);
            rs = pst.executeQuery();
            while(rs.next()){
                int eno = rs.getInt("eno");
                String ename = rs.getString("ename");
                System.out.println(eno+" "+ename);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(connection);
        }
    }

DBUtils工具类 与 Druid连接池 联合使用

public static void query() {
        Properties properties = new Properties();
        String propertyFile = DbUtilsSample.class.getResource("/druid-config.properties").getPath();
        try {
            propertyFile = new URLDecoder().decode(propertyFile,"UTF-8"); //容错处理，解决文件命名中的空格问题
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);//数据库连接

            Connection connection = dataSource.getConnection();
            ResultSet rs = null;
            String sql = "select * from employee limit ?,10";
            PreparedStatement pst = connection.prepareStatement(sql);
            pst.setInt(1,2);
            rs = pst.executeQuery();
            while (rs.next()){
                int id = rs.getInt(1);
                System.out.println(id);
            }
            QueryRunner qr = new QueryRunner(dataSource);
            List<Employee> employeeList = qr.query("select * from employee limit ?,10 ",
                    new BeanListHandler<>(Employee.class),
                    new Object[] {10}); //一开始无法创建实体类，原因是没有空的构造函数！！！！！
            for(Employee e:employeeList){
                System.out.println(e);
            }
        } catch (Exception e2){
            e2.printStackTrace();
        }
    }
    public static void update(){
        Properties properties = new Properties();
        String propertyFile = DbUtilsSample.class.getResource("/druid-config.properties").getPath();
        Connection connection = null;
        PreparedStatement pst = null;
        try {
            propertyFile = new URLDecoder().decode(propertyFile,"UTf-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            String sql1 = "update employee set salary = salary-100 where eno = ?";
            String sql2 = "update employee set salary = salary+100 where eno = ?";
            QueryRunner qr = new QueryRunner();
            qr.update(connection,sql1,new Object[] {1001});
            qr.update(connection,sql2,new Object[] {1002});
            connection.commit();
        } catch (UnsupportedEncodingException | FileNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e1){
            e1.printStackTrace();
            try {
                if(connection!=null&&!connection.isClosed())
                    connection.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        } finally {
            try {
                if(connection!=null&&!connection.isClosed())
                    connection.close(); //是回收不是关闭
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

在使用DBUtils工具类时遇到的坑：
QueryRunner 是使用这个工具类的核心，查询数据十分方便，但是 封装的实体类需要有空的构造方法才可以

			QueryRunner qr = new QueryRunner(dataSource);
            List<Employee> employeeList = qr.query("select * from employee limit ?,10 ",
                    new BeanListHandler<>(Employee.class),
                    new Object[] {10}); //一开始无法创建实体类，原因是没有空的构造函数！！！！！
            for(Employee e:employeeList){
                System.out.println(e);
            }

更新操作：

connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            String sql1 = "update employee set salary = salary-100 where eno = ?";
            String sql2 = "update employee set salary = salary+100 where eno = ?";
            QueryRunner qr = new QueryRunner();
            qr.update(connection,sql1,new Object[] {1001});
            qr.update(connection,sql2,new Object[] {1002});
            connection.commit();

插入操作：

connection = dataSource.getConnection();
            String sql = "insert into goods(id,name,price,desp,create_time) values(?,?,?,?,?)";
            QueryRunner qr = new QueryRunner();
            qr.update(connection,sql,new Object[] {id,name,price,desp,sdTime});

处理时间类型

有一点遗漏的知识，这里再补充记录一下。
Date 类型有两个
一个是 java.util.Date
另一个是 java.sql.Date
我们从客户端接受的Date是字符串类型的，我们要先转换成 java.util.Date
再转换成 java.sql.Date 才能存入数据库！

System.out.print("请输入日期：");
        String strTime = in.next();
        java.util.Date udTime =null;
        java.sql.Date sdTime = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            udTime = sdf.parse(strTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        sdTime = new java.sql.Date(udTime.getTime());

udTime.getTime() 获取 udTime的时间戳，类型为 long