JDBC和数据库连接池: SQL注入.预处理.批处理.德鲁伊.Apache-DBUtils

最新推荐文章于 2025-01-24 11:31:47 发布

bone_ds

最新推荐文章于 2025-01-24 11:31:47 发布

阅读量814

点赞数 1

CC 4.0 BY-SA版权

分类专栏： jdbc 文章标签：数据库 apache sql

本文链接：https://blog.youkuaiyun.com/jason_bone_/article/details/123039112

jdbc 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了JDBC接口及其使用，包括JDBC的基本概念、JDBC API的关键部分、连接管理、预处理语句、事务、批处理、数据库连接池和常见工具如C3P0和Druid的应用。还涵盖了如何避免SQL注入和提高性能的最佳实践。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

JDBC 概述

基本介绍

模拟JDBC

public class TestJdbc {
    public static void main(String[] args) {
        JdbcInterface jdbcInterface = new MysqlJdbcImpl();
        //完成对mysql的操作
        jdbcInterface.getConnection();
        jdbcInterface.crud();
        jdbcInterface.close();
    }
}
//java 厂商规定的jdbc接口
public interface JdbcInterface {
    //连接
    public Object getConnection();

    //CRUD
    public void crud();

    //关闭连接
    public void close();
}
//mysql数据库实现了jdbc接口(mysql厂商开发)
public class MysqlJdbcImpl implements JdbcInterface {
    @Override
    public Object getConnection() {
        System.out.println("得到mysql的连接");
        return null;
    }

    @Override
    public void crud() {
        System.out.println("完成mysql增删改查");
    }

    @Override
    public void close() {
        System.out.println("关闭mysql的连接");
    }
}

JDBC 带来的好处

JDBC是java提供一套用于数据库操作的接口API,java程序员只需要面向这套接口编程即可.不同的数据库厂商,需要针对这套接口,提供不同实现.
如果java直接访问数据库:可行,但不建议,因为这意味着java应用程序没有更好的可移植性.

JDBC API

JDBC 快速入门

JDBC程序编写步骤:(先执行前置工作步骤)

注册驱动–加载Driver类(该类在驱动包下的com.mysql.jdbc.Driver路径)
获取连接–得到Connection
执行增删改查–发送SQL给mysql执行
释放资源-- 关闭相关连接

前置工作:

在项目下创建一个文件夹比如 libs
将mysql-connector-java-5.1.37-bin.jar拷贝到该目录下,点击 add to project…加入到项目中
该jar包中即为mysql数据库针对java接口做的实现

//jdbc程序,完成简单操作
public class Jdbc01 {
    public static void main(String[] args) throws SQLException {
        //前置工作： 在项目下创建一个文件夹比如 libs
        // 将 mysql.jar 拷贝到该目录下，点击 add to project ..加入到项目中
        //1. 注册驱动

        Driver driver = new Driver();//com.mysql.jdbc.Driver

        //2.得到连接
        //(1) jdbc:mysql:// 规定好表示协议，通过jdbc的方式连接mysql
        //(2) localhost 主机，可以是ip地址
        //(3) 3306 表示mysql监听的端口
        //(4) hsp_db02 连接到mysql dbms 的哪个数据库
        //(5) mysql的连接本质就是前面学过的socket连接
        String url = "jdbc:mysql://localhost:3306/db02";
        //将 用户名和密码放入到Properties 对象
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "root");
        Connection connect = driver.connect(url, properties);
        //3.执行sql
        String sql = "insert into actor values(null,'刘德华','男','1970-11-11','110')";
        sql = "update actor set name='周星驰' where id=1";
        sql = "delete from actor where id=1";
        //statement 用于执行静态SQL语句并返回其生成的结果的对象
        Statement statement = connect.createStatement();
        int rows = statement.executeUpdate(sql);//执行sql语句, 如果是 dml语句，返回的就是影响行数

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

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

获取数据库连接(5种方式)

方式1: 注册驱动(创建Driver对象)–>将user,password存入配置文件(properties)–>使用方法driver.connect(url,properties)得到连接
方式2: 注册驱动(通过反射动态加载Driver类–>newInstance()-创建Driver对象)–>将user,password存入配置文件(properties)–>使用方法driver.connect(url,properties)得到连接
方式3: 通过反射创建对象–>通过DriverManager静态方法注册驱动–>通过DriverManager静态方法得到连接
方式4: 使用反射加载Driver–>类加载执行静态代码块完成注册–>通过DriverManager静态方法得到连接
方式5: 增加配置文件–>从配置文件读取到url,user,password,driver参数–>执行方式4步骤

总结:

无论属于5种方式的哪一种,最终都是通过DriverManager静态方法注册驱动,具体表现为两种途径:

间接方式:直接创建或通过反射创建或加载Driver类的对象,从而加载Driver类,执行其类中的静态代码块[如下图],调用 DriverManager 静态方法注册驱动
直接方式:不创建Driver对象,自动调用驱动包下的类名通过class.forName()去注册

提示:

mysql驱动5.1.6可以无需Class.forName(“com.mysql.jdbc.Driver”);
从jdk1.5以后使用了jdbc4,不再需要显示调用class.forName()注册驱动而是自动调用驱动jar包下META-INF\services\java.sql.Driver文本中的类名称去注册
建议还是写上Class.forName(“com.mysql.jdbc.Driver”),更加明确

//java连接mysql的5种方式
public class JdbcConn {
    //方式1
    @Test
    public void connect01() throws SQLException {
        Driver driver = new Driver();//创建driver对象
        //连接到ip+数据库
        String url = "jdbc:mysql://localhost:3306/db02";
        //存入mysql用户名密码
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "root");
        //得到连接
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
    }

    //方式2
    @Test
    public void connect02() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        //通过反射动态加载Driver类,更灵活
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();

        String url = "jdbc:mysql://localhost:3306/db02";

        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "root");
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
    }
    //方式3 使用DriverManager 替代 driver 进行统一管理
    @Test
    public void connect03() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();

        String url = "jdbc:mysql://localhost:3306/db02";
        String password = "root";
        String user = "root";

        //通过DriverManager静态方法注册驱动
        DriverManager.registerDriver(driver);
        //通过DriverManager静态方法得到连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }
    //方式4 Driver类加载时自动注册驱动,简化,推荐使用
    @Test
    public void connect04() throws ClassNotFoundException, SQLException {
        //使用反射加载Driver,提示:jdk1.5开始使用jdbc4以后,无需加载,自动调用
        Class.forName("com.mysql.jdbc.Driver");
        //类加载执行静态代码块完成注册
        /*static {
            try {
                DriverManager.registerDriver(new Driver());
            } catch (SQLException var1) {
                throw new RuntimeException("Can't register driver!");
            }
        }*/
        String url = "jdbc:mysql://localhost:3306/db02";
        String password = "root";
        String user = "root";

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("connection = " + connection);
    }
    //方式5 增加配置文件,更加灵活
    @Test
    public void connect05() throws IOException, ClassNotFoundException, SQLException {
        //通过Properties对象获取配置文件信息
        Properties properties = new Properties();
        properties.load(new FileReader("src\\mysql.properties"));
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");

        Class<?> aClass = Class.forName(driver);//建议写上
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("connection = " + connection);
    }
}

ResultSet结果集

基本介绍

debug查看resultSet内部结构

由图看出:

resultSet底层维护了ArrayList集合存入了多行记录
每行记录由byte类型二维数组维护
该行的每列(标量)存放在byte[]中

代码实现:

//演示select语句返回结果集,并取出结果
public class ResultSet_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //通过Properties对象获取配置文件的信息
        Properties properties = new Properties();
        properties.load(new FileReader("chapter25\\src\\mysql.properties"));
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        //1. 注册驱动
        Class.forName(driver);
        //2. 得到连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //3. 得到Statement
        Statement statement = connection.createStatement();
        //4. 组织SqL
        String sql = "select id,name,sex,borndate,phone from actor";

        //debug得到resultSet类型:com.mysql.jdbc.JDBC42ResultSet@6e0e048a
        //该对象里有rowData-->rows-->elementDate存放每行记录里的数据
        //执行给定的SQL语句，该语句返回单个 ResultSet对象
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {// 让光标向后移动，如果没有更多行，则返回false
            int id = resultSet.getInt(1);//获取该行的第1列
//            int id = resultSet.getInt("id");通过列名来获取值, 推荐
            String name = resultSet.getString(2);
            String sex = resultSet.getString(3);
            Date date = resultSet.getDate(4);
            System.out.println(id+"\t"+name+"\t"+sex+"\t"+date);
        }
        //关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

Statement

基本介绍

mysql中演示sql注入

create table admin
		(`name` varchar(32)not null unique,
		`pwd` varchar(32) not null default '')character set utf8;

insert into admin values('tom','123');
-- 查找某个管理是否存在
select * from admin where `name`='tom' AND `pwd`='123';
-- sql注入:tom as 1' or , 123 as or '1'='1

select * from admin where `name`='1' or' AND `pwd`='or '1'='1';

说明:

当查询时可在输入过滤条件的参数时使用万能用户名(1’ or)和密码( or ‘1’='1)即可

jdbc中演示SQL注入

//演示statement的注入问题
public class Statement_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);

        System.out.println("请输入用户名:");//1' or
        String name = sc.nextLine();//next(): 当接收到 空格或者 '就是表示结束,只能用nextLine
        System.out.println("请输入密码:");// or '1' = '1
        String pwd = sc.nextLine();

        Properties properties = new Properties();
        properties.load(new FileReader("chapter25\\src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
        String sql = "select name,pwd from admin where name='"+name+"' and pwd='"+pwd+"'";
        ResultSet resultSet = statement.executeQuery(sql);

        if (resultSet.next()) { //如果查询到一条记录，则说明该管理存在
            System.out.println("恭喜,登录成功");
        } else {
            System.out.println("抱歉,登录失败");
        }

        resultSet.close();
        statement.close();
        connection.close();

    }
}

PreparedStatement

基本介绍

预处理好处

不再使用+拼接sql语句,减少语法错误
有效的解决了sql注入问题
大大减少了编译次数,效率较高

查询应用案例:

//演示PreparedStatement使用:有效防止statement注入
public class PreparedStatement_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);

        System.out.println("请输入用户名:");//1' or
        String name = sc.nextLine();//next(): 当接收到 空格或者 '就是表示结束,只能用nextLine
        System.out.println("请输入密码:");// or '1' = '1
        String pwd = sc.nextLine();

        Properties properties = new Properties();
        properties.load(new FileReader("chapter25\\src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);
        //先组织字段值为?的sql语句-->构造PreparedStatement对象时使用
        String sql = "select name,pwd from admin where name=? and pwd=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //调用方法给?赋值
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, pwd);
        //执行select语句时再不可填入sql作为参数
        ResultSet resultSet = preparedStatement.executeQuery();


        if (resultSet.next()) { //如果查询到一条记录，则说明该管理存在
            System.out.println("恭喜,登录成功");
        } else {
            System.out.println("抱歉,登录失败");
        }

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

DML案例:

//演示预处理方式使用dml语句
    @SuppressWarnings({"all"})
public class PreparedStatementDML_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);

        System.out.println("请输入用户名:");//1' or
        String name = sc.nextLine();//next(): 当接收到 空格或者 '就是表示结束,只能用nextLine
        System.out.println("请输入密码:");// or '1' = '1
        String pwd = sc.nextLine();

        Properties properties = new Properties();
        properties.load(new FileReader("chapter25\\src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);
        //先组织字段值为?的sql语句-->构造PreparedStatement对象时使用
        String sql = "select name,pwd from admin where name=? and pwd=?";
        sql = "insert into admin values(?,?)";
        sql = "update admin set pwd=? where name=?";//注意两个字段赋值的顺序变化
        sql = "delete from admin where name =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //调用方法给?赋值
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, pwd);
        //执行DML语句
        int update = preparedStatement.executeUpdate();

        System.out.println(update>0?"执行成功":"受影响的行数为0");

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

    }
}

JDBC相关API小结

总结:以上接口都是在导入的mysql-connector-java-5.1.37-bin.jar中提供实现,即mysql提供.

封装 JDBCUtils

在jdbc操作中,获得连接和释放资源是经常用到的,可以将其封装到JDBC连接的工具类:JDBCUtils

作用:完成 mysql的连接和关闭资源

代码实现:

//工具类，完成 mysql的连接和关闭资源(因为连接和关闭只执行一次,使用静态成员)
public class JDBCUtils {
    private static String url;
    private static String user;
    private static String pwd;
    private static String driver;

    //初始化读取属性值
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileReader("src\\mysql.properties"));
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            pwd = properties.getProperty("password");
            driver = properties.getProperty("driver");
        } catch (IOException e) {
            //在实际开发中，我们可以这样处理
            //1. 将编译异常转成 运行异常
            //2. 调用者，可以选择捕获该异常，也可以选择默认处理该异常，比较方便.
            throw new RuntimeException(e);
        }
    }

    //得到连接
    public static Connection getConnect() {
        try {
            return DriverManager.getConnection(url, user, pwd);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    //关闭相关资源(如果没有该对象传入null)
    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);
        }
    }

}

使用JDBCUtils工具类，完成CRUD

//演示使用JDBCUtils工具类，完成dml 和 select
public class JDBCUtils_Use {
    @Test
    public void testSelect() throws SQLException {//使用trycatch捕捉的话注意待关闭的资源的引用(变量)的作用域
        //使用工具类静态方法得到连接
        Connection connect = JDBCUtils.getConnect();
        System.out.println(connect.getClass());//(Collection在mysql中的实现类)class com.mysql.jdbc.JDBC4Connection
        //sql
        String sql = "select id,name,sex,borndate,phone from actor where id=?";
        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        //?赋值
        preparedStatement.setInt(1,2);
        //查询(1条),获得结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        System.out.println(resultSet.getClass());
        //指针下移
        resultSet.next();

        //处理结果集
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String sex = resultSet.getString("sex");
        Date borndate = resultSet.getDate("borndate");
        String phone = resultSet.getString("phone");
        System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
        //关闭资源
        JDBCUtils.close(resultSet, preparedStatement, connect);
    }

    @Test
    public void testDML() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnect();
            String sql = "update actor set name='周星驰' where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 2);
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
}

事务

基本介绍

应用实例

模拟经典的转账业务

不使用事务可能出现的问题:默认情况下,connection是默认自动提交,导致执行1条sql语句后如果出现异常,由于try catch的特性,后面的sql语句没有被执行,导致转账出错
使用事务保持一致性,全部执行或全部回退,不会发生上述情况

代码实现:

//jdbc 中使用事务
public class Transaction_ {
    @Test
    public void noTransaction() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnect();
            String sql = "update account set balance=balance-100 where id=1";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();//该方法无参默认执行由连接获得preparedstatement的方法中的sql
            int i=1/0;//转账失败,化腾血亏100块
            String sql2 = "update account set balance=balance+100 where id=2";
            preparedStatement.executeUpdate(sql2);//该方法优先执行自己的sql2
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }

    @Test
    public void transaction() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //在默认情况下，connection是默认自动提交
            connection = JDBCUtils.getConnect();
            //开启事务
            connection.setAutoCommit(false);

            String sql = "update account set balance=balance-100 where id=1";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();//该方法无参默认执行由连接获得preparedstatement的方法中的sql
            int i=1/0;//转账失败,终止交易,化腾不亏即血赚
            String sql2 = "update account set balance=balance+100 where id=2";
            preparedStatement.executeUpdate(sql2);//该方法优先执行自己的sql2

            //提交事务
            connection.commit();
        } catch (Exception e) {
            //这里我们可以进行回滚，即撤销执行的SQL
            //默认回滚到事务开始的状态.
            System.out.println("执行发生了异常，撤销执行的sql");
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
}

批处理

基本介绍

应用实例

准备工作:创建mysql中表格

create table admin2(
		id int primary key auto_increment,
		username varchar(32) not null,
		`password` varchar(32) not null);

jdbc代码实现:

//jdbc 的批处理
//总结:执行多条SQL语句:
//方式1:每次执行(executeUpdate)完sql,
//-->只需更新sql语句内容(sql赋新值或者setString等方法改sql参数)
//-->再执行(executeUpdate)改变的sql
//方式2:向集合中加入(addBatch(Str/no paramters))改后的sql,批量执行

public class Batch_ {
    @SuppressWarnings({"all"})
    @Test
    public void noBatch() throws SQLException {
        Connection connect = JDBCUtils.getConnect();
        String sql = "insert into admin2 values(null ,?,?)";
        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack[" + i + "]");
            preparedStatement.setString(2, "123");
            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("不使用批处理耗时:" + (end - start));//6358ms
        JDBCUtils.close(null, preparedStatement, connect);
    }

    @Test//别忘了改配置文件的url参数?rewriteBatchedStatements=true
    public void batch() throws SQLException {
        Connection connect = JDBCUtils.getConnect();
        String sql = "insert into admin2 values(null ,?,?)";
        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack[" + i + "]");
            preparedStatement.setString(2, "123");
            //将sql 语句加入到批处理包中 -> 看源码
            /*
            //1. //第一就创建 ArrayList - elementData => Object[]
            //2. elementData => Object[] 就会存放我们预处理的sql语句
            //3. 当elementData满后,就按照1.5扩容
            //4. 当添加到指定的值后，就executeBatch
            //5. 批量处理会减少我们发送sql语句的网络开销，而且减少编译次数，因此效率提高
            public void addBatch() throws SQLException {
                synchronized(this.checkClosed().getConnectionMutex()) {
                    if (this.batchedArgs == null) {

                        this.batchedArgs = new ArrayList();
                    }

                    for(int i = 0; i < this.parameterValues.length; ++i) {
                        this.checkAllParametersSet(this.parameterValues[i], this.parameterStreams[i], i);
                    }

                    this.batchedArgs.add(new PreparedStatement.BatchParams(this.parameterValues, this.parameterStreams, this.isStream, this.streamLengths, this.isNull));
                }
            }

             */

            preparedStatement.addBatch();//preparedStatement中的SQL语句放入 集合
            if ((i + 1) % 1000 == 0) {
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("使用批处理耗时:" + (end - start));//142ms
        JDBCUtils.close(null, preparedStatement, connect);
    }
}

数据库连接池

问题引出

5k次连接数据库问题:

编写程序完成连接MySQL5000次的操作
看看有什么问题,耗时又是多久.==>数据库连接池

//测试传统方式连接的速度
public class ConQuestion {
    @Test
    public void testCon() {
        System.out.println("开始连接...");
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connect = JDBCUtils.getConnect();//如果不关闭连接:Too many connections

            JDBCUtils.close(null,null,connect);
        }
        long end = System.currentTimeMillis();
        System.out.println("传统方式连5000次耗时:"+(end-start));//13197ms
    }
}

问题分析

tomanyconn.drawio

数据库连接池种类

datasource.drawio

说明:使用了第三方数据库连接池后,连接池,连接等接口以及接口对应的方法都改为由第三方提供实现(jar驱动包中)

C3P0应用实例

前置工作:

将c3p0-0.9.1.2.jar拷贝到项目下指定的目录(libs)下,点击 add to project…加入到项目中,包中提供数据库连接池接口的实现–>数据库连接池接口的实现类型变成com.mchange.v2.c3p0.ComboPooledDataSource–>Connection接口的实现类型变成com.mchange.v2.c3p0.impl.NewProxyConnection
将c3p0-config.xml拷贝到项目目录(src)下,该文件指定了连接数据库和连接池的相关参数(url,user,password,driver驱动路径)

//演示使用c3p0
public class C3P0_ {
    //方式1： 相关参数，在程序中指定user, url , password等
    @Test
    public void testC3P0_01() throws Exception {
        //1. 创建一个数据源对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();//com.mchange.v2.c3p0.ComboPooledDataSource
        //2. 通过配置文件mysql.properties 获取相关连接的信息
        Properties properties = new Properties();
        properties.load(new FileReader("src\\mysql.properties"));

        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        //给数据源 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();

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

    }

    //第二种方式 使用配置文件模板来完成
    //1. 将c3p0 提供的 c3p0.config.xml 拷贝到 src目录下
    //2. 该文件指定了连接数据库和连接池的相关参数
    @Test
    public void testc3p0_02() throws SQLException {
        //提示:该方式的构造器参数不能为空,且与配置文件的named-config name保持一致
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hello");
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connection = comboPooledDataSource.getConnection();

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

Druid(德鲁伊)应用实例

前置工作:

将druid-1.1.10.jar拷贝到项目下指定的目录(libs)下,点击 add to project…加入到项目中,包中提供数据库连接池接口的实现–>数据库连接池接口的实现类型变成com.alibaba.druid.pool.DruidDataSource–>Connection接口的实现类型变成com.alibaba.druid.pool.DruidPooledConnection
将druid.properties拷贝到项目目录(src)下,该文件指定了连接数据库和连接池的相关参数(url,user,password,driver驱动路径)

使用:

//Druid数据库连接管理的使用
public class Druid_ {
    @Test
    public void testDruid() throws Exception {
        Properties properties = new Properties();
        properties.load(new FileReader("src\\druid.properties"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connection = dataSource.getConnection();

            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("druid连接5000次耗时:"+(end-start));//741
    }
}

Druid工具类

说明:将 JDBCUtils 工具类改成Druid (德鲁伊)实现

功能:创建一个数据源对象–>通过配置文件druid.properties 获取相关连接的信息–>得到连接Connection(引用)–>关闭连接(将连接引用的对象放回连接池)

//基于druid数据库连接池的工具类
public class JDBCUtilsByDruid {
    private static DataSource dataSource;

    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileReader("src\\druid.properties"));

            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection connection=null;
        try {
            connection=dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 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是接口,因第三方不同,Collection实现类不同
                //这里关闭机制由原来的mysql里Collection实现类提供
                //改为由Druid里的实现类提供关闭机制
                //两者区别:后者把引用断掉,collection对象放回连接池
                //前者:直接断开JDBC数据库连接
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

使用Druid工具类

public class JDBCUtilsByDruid_USE {
    @Test
    public void testSelect() throws SQLException {//使用trycatch捕捉的话注意待关闭的资源的引用(变量)的作用域
        //使用工具类静态方法得到连接
        Connection connect = JDBCUtilsByDruid.getConnection();
        System.out.println(connect.getClass());//(Collection在Druid中的实现类)class com.alibaba.druid.pool.DruidPooledConnection
        //sql
        String sql = "select id,name,sex,borndate,phone from actor where id=?";
        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        //?赋值
        preparedStatement.setInt(1,2);
        //查询(1条),获得结果集
        ResultSet resultSet = preparedStatement.executeQuery();

        //如果在操作结果集之前就关闭连接即关闭结果集 ,会异常
        //-->Operation not allowed after ResultSet closed
        //-->因此在关闭之前就要将读取结果封装到集合中

        //指针下移
        resultSet.next();
        //处理结果集
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String sex = resultSet.getString("sex");
        Date borndate = resultSet.getDate("borndate");
        String phone = resultSet.getString("phone");
        System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
        //关闭资源
        JDBCUtilsByDruid.close(resultSet, preparedStatement, connect);
    }
}

Apache-DBUtils

分析问题

关闭connection后,resultSet结果集无法使用
resultSet不利于数据的管理

自行尝试解决

手动封装记录:创建集合–>将循环读取resultSet中的记录并保存在事先创建的集合中

@Test
public void testSelectToArrayList() throws SQLException {
    ArrayList<Actor> list = new ArrayList<>();
    Connection connection = JDBCUtilsByDruid.getConnection();
    String sql = "select * from actor where id>?";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setInt(1,0);
    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String sex = resultSet.getString("sex");
        Date borndate = resultSet.getDate("borndate");
        String phone = resultSet.getString("phone");
        list.add(new Actor(id, name, sex, borndate, phone));
    }
    JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
    System.out.println(list);
}

工具介绍

功能:隐式编译预处理sql语句–>执行SQL语句后,隐式处理CRUD结果(封装查询记录),直接返回存放记录的集合/对象–>隐式关闭结果集–>隐式关闭preparedstatement

提示:使用DBUtils同时使用德鲁伊工具类时,不需显示关闭结果集和preparedstatement

前置工作:

将commons-dbutils-1.3.jar拷贝到项目下指定的目录(libs)下,点击 add to project…加入到项目中

应用实例:

//使用 apache-DBUtils 工具类 + druid 完成对表的 crud 操作
public class DBUtils_USE {
    @Test
    //返回结果是多行的情况
    public void selectManyTest() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. 使用 DBUtils 类和接口 , 先引入DBUtils 相关的jar , 加入到本Project
        //3. 创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();//org.apache.commons.dbutils
        String sql = "select * 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 ,会在query 关闭, 关闭PreparedStatment
        /**
         * 分析 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);//创建PreparedStatement
         *             this.fillStatement(stmt, params);//对sql 进行 ? 赋值
         *             rs = this.wrap(stmt.executeQuery());//执行sql,返回resultset
         *             result = rsh.handle(rs);//返回的resultset --> arrayList[result] [使用到反射，对传入class对象处理]
         *         } catch (SQLException var33) {
         *             this.rethrow(var33, sql, params);
         *         } finally {
         *             try {
         *                 this.close(rs);//关闭resultset
         *             } finally {
         *                 this.close((Statement)stmt);//关闭preparedstatement对象
         *             }
         *         }
         *
         *         return result;
         *     }
         */
        List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 0);
        System.out.println(list);
        JDBCUtilsByDruid.close(null,null,connection);
    }
    @Test
    //返回结果是单行的情况
    public void selectSingle() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from actor where id=?";
        // 因为我们返回的单行记录<--->单个对象 , 使用的Hander 是 BeanHandler
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 3);
        System.out.println(actor);
        JDBCUtilsByDruid.close(null,null,connection);
    }
    @Test
    //返回结果是标量(单行单列)的情况
    public void selectsaclar() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select name from actor where id=?";
        //返回单行单列 , 返回的就是Object,使用的handler 就是 ScalarHandler
        Object o = queryRunner.query(connection, sql, new ScalarHandler(),3);
        System.out.println(o);
        JDBCUtilsByDruid.close(null,null,connection);
    }
    //演示 apache-dbutils + druid 完成 dml (update, insert ,delete)
    @Test
    public void dml() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        String sql = "update actor set name=? where id=?";
        sql = "insert into actor values(null,?,?,?,?)";
        sql = "delete from actor where id=?";
        QueryRunner queryRunner = new QueryRunner();
        //(1) 执行dml 操作是 queryRunner.update()
        //(2) 返回的值是受影响的行数 (affected: 受影响)
        //        queryRunner.update(connection, sql, "刘德华", 2);
//        queryRunner.update(connection, sql, "黎明", "男","1971-11-11","114");
        int affectedRow = queryRunner.update(connection, sql, 2);
        System.out.println(affectedRow>0?"执行成功":"执行没有影响到表");

        JDBCUtilsByDruid.close(null,null,connection);
    }
}

补充:表和 JavaBean 的类型映射关系

DAO

问题

说明

BasicDAO 应用实例

代码实现:

domain层:

package com.hspedu.dao_.domain;

import java.util.Date;
//javaBean/domain
public class Actor {
    private Integer id;
    private String name;
    private String sex;
    private Date borndate;
    private String phone;

    public Actor() {
    }

    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 + '\'' +
                '}';
    }
}

utils层

package com.hspedu.dao_.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @ClassName
 * @Description
 * @Author zxk
 * @DateTime 2022-02-19-21:24
 * @Version
 *///基于druid数据库连接池的工具类
public class JDBCUtilsByDruid {
    private static DataSource dataSource;

    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileReader("src\\druid.properties"));

            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection connection=null;
        try {
            connection=dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 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是接口,因第三方不同,Collection实现类不同
                //这里关闭机制由原来的mysql里Collection实现类提供
                //改为由Druid里的实现类提供关闭机制
                //两者区别:后者把引用断掉,collection对象放回连接池
                //前者:直接断开JDBC数据库连接
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

dao层

package com.hspedu.dao_.dao;

import com.hspedu.dao_.utils.JDBCUtilsByDruid;
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 java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
//开发BasicDAO , 是其他DAO的父类
public class BasicDAO<T>{
    private QueryRunner qr = new QueryRunner();//apache-DB工具类
    private Connection connection ;

    //开发通用的dml方法, 针对任意的表
    public int update(String sql, Object... parameters) {
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.update(connection, sql, parameters);
        } catch (SQLException e) {
            //将编译异常->运行异常 ,抛出,解决了:该方法接收不到返回语句的编译问题(作用域)
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }

    //返回多个对象(即查询的结果是多行), 针对任意表
    /**
     *
     * @param sql sql 语句，可以有 ?
     * @param clazz clazz 传入一个类的Class对象 比如 Actor.class
     * @param parameters parameters 传入 ? 的具体的值，可以是多个
     * @return 根据Actor.class 返回对应的 ArrayList 集合
     */
    public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new BeanListHandler<>(clazz), parameters);

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }

    //查询单行结果 的通用方法
    public T querySingle(String sql, Class<T> clazz, Object... parameters) {
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection,sql, new BeanHandler<>(clazz), parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }

    //查询单行单列的方法,即返回单值的方法
    public Object queryScalar(String sql, Object... parameters) {
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new ScalarHandler(), parameters);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }

    }
}

package com.hspedu.dao_.dao;

import com.hspedu.dao_.domain.Actor;

public class ActorDAO extends BasicDAO<Actor> {
    //1. 就有 BasicDAO 的方法
    //2. 根据业务需求，可以编写特有的方法.
}

view层(测试)

package com.hspedu.dao_.test;

import com.hspedu.dao_.dao.ActorDAO;
import com.hspedu.dao_.domain.Actor;
import org.junit.jupiter.api.Test;

import java.util.List;

public class TestDAO {
    @Test
    public void testActorDAO() {
        ActorDAO actorDAO = new ActorDAO();
        //4. dml操作  insert ,update, delete
        int update = actorDAO.update("update actor set name='周星驰' where id=?", 3);
        System.out.println(update>0?"执行成功":"没有受影响的行数");
        //1. 查询多行记录
        List<Actor> actors = actorDAO.queryMulti("select * from actor where id>?", Actor.class, 0);
        for (Actor actor : actors) {
            System.out.println(actor);
        }
        //2. 查询单行记录
        Actor actor = actorDAO.querySingle("select * from actor where id=?", Actor.class, 4);
        System.out.println(actor);
        //3. 查询单行单列
        Object o = actorDAO.queryScalar("select * from actor where id=?", 4);
        System.out.println(o);

    }
}