JDBC第四篇【数据库连接池、DbUtils框架、分页】(修订版)

青旅半醒 2021-09-30 22:16 353阅读 0赞

## 前言 ##

> 只有光头才能变强。
> 
> 文本已收录至我的GitHub仓库，欢迎Star：https://github.com/ZhongFuCheng3y/3y

# 1.数据库连接池 #

## 什么是数据库连接池 ##

简单来说：**数据库连接池就是提供连接的。。。**

## 为什么我们要使用数据库连接池 ##

## 编写连接池 ##

1.  编写连接池需**实现java.sql.DataSource接口**
2.  **创建批量的Connection用LinkedList保存**【既然是个池，当然用集合保存、、LinkedList底层是链表，对增删性能较好】
3.  **实现getConnetion()**，让getConnection()每次调用，都是**在LinkedList中取一个Connection返回给用户**
4.  **调用Connection.close()方法，Connction返回给LinkedList**

private static LinkedList<Connection> list = new LinkedList<>();//获取连接只需要一次就够了，所以用static代码块static {    //读取文件配置    InputStream inputStream = Demo1.class.getClassLoader().getResourceAsStream("db.properties");    Properties properties = new Properties();    try {        properties.load(inputStream);        String url = properties.getProperty("url");        String username = properties.getProperty("username");        String driver = properties.getProperty("driver");        String password = properties.getProperty("password");        //加载驱动        Class.forName(driver);        //获取多个连接，保存在LinkedList集合中        for (int i = 0; i < 10; i++) {            Connection connection = DriverManager.getConnection(url, username, password);            list.add(connection);        }    } catch (IOException e) {        e.printStackTrace();    } catch (ClassNotFoundException e) {        e.printStackTrace();    } catch (SQLException e) {        e.printStackTrace();    }}//重写Connection方法，用户获取连接应该从LinkedList中给他@Overridepublic Connection getConnection() throws SQLException {    System.out.println(list.size());    System.out.println(list);   //先判断LinkedList是否存在连接   return list.size() > 0 ? list.removeFirst() : null; }static LinkedList<Connection> list = new LinkedList<>();
    
    //获取连接只需要一次就够了，所以用static代码块
    static {
        
        //读取文件配置
        InputStream inputStream = Demo1.class.getClassLoader().getResourceAsStream("db.properties");
    
        Properties properties = new Properties();
        try {
        
            properties.load(inputStream);
            String url = properties.getProperty("url");
            String username = properties.getProperty("username");
            String driver = properties.getProperty("driver");
            String password = properties.getProperty("password");
    
            //加载驱动
            Class.forName(driver);
    
            //获取多个连接，保存在LinkedList集合中
            for (int i = 0; i < 10; i++) {
        
                Connection connection = DriverManager.getConnection(url, username, password);
                list.add(connection);
            }
    
        } catch (IOException e) {
        
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
        
            e.printStackTrace();
        } catch (SQLException e) {
        
            e.printStackTrace();
        }
    
    }
    
    //重写Connection方法，用户获取连接应该从LinkedList中给他
    @Override
    public Connection getConnection() throws SQLException {
        
        System.out.println(list.size());
        System.out.println(list);
    
       //先判断LinkedList是否存在连接
       return list.size() > 0 ? list.removeFirst() : null; 
    }

我们已经完成前三步了，现在问题来了**。我们调用Conncetion.close()方法，是把数据库的物理连接关掉，而不是返回给LinkedList的**

解决思路：

1.  写一个Connection子类，覆盖close()方法
2.  写一个Connection包装类，增强close()方法
3.  用动态代理，返回一个代理对象出去，拦截close()方法的调用，对close()增强

分析第一个思路：

分析第二个思路：

1.  写一个类，实现与被增强对象的相同接口【Connection接口】
2.  定义一个变量，指向被增强的对象
3.  定义构造方法，接收被增强对象
4.  覆盖想增强的方法
5.  对于不想增强的方法，直接调用被增强对象的方法

这个思路本身是没什么毛病的，就是**实现接口时，方法太多了！**，所以我们也不使用此方法

分析第三个思路代码实现：

@Overridepublic Connection getConnection() throws SQLException {    if (list.size() > 0) {        final Connection connection = list.removeFirst();        //看看池的大小        System.out.println(list.size());        //返回一个动态代理对象        return (Connection) Proxy.newProxyInstance(Demo1.class.getClassLoader(), connection.getClass().getInterfaces(), new InvocationHandler() {            @Override            public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {                //如果不是调用close方法，就按照正常的来调用                if (!method.getName().equals("close")) {                    method.invoke(connection, args);                } else {                    //进到这里来，说明调用的是close方法                    list.add(connection);                    //再看看池的大小                    System.out.println(list.size());                }                return null;            }        });    }    return null;}
    public Connection getConnection() throws SQLException {
        
    
        if (list.size() > 0) {
        
            final Connection connection = list.removeFirst();
    
            //看看池的大小
            System.out.println(list.size());
    
            //返回一个动态代理对象
            return (Connection) Proxy.newProxyInstance(Demo1.class.getClassLoader(), connection.getClass().getInterfaces(), new InvocationHandler() {
        
    
                @Override
                public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        
    
                    //如果不是调用close方法，就按照正常的来调用
                    if (!method.getName().equals("close")) {
        
                        method.invoke(connection, args);
                    } else {
        
    
                        //进到这里来，说明调用的是close方法
                        list.add(connection);
    
                        //再看看池的大小
                        System.out.println(list.size());
    
                    }
                    return null;
                }
    
            });
        }
        return null;
    }

--------------------

我们上面已经能够简单编写一个线程池了。下面我们来使用一下开源数据库连接池

## DBCP ##

使用DBCP数据源的步骤：

1.  导入两个jar包【Commons-dbcp.jar和Commons-pool.jar】
2.  读取配置文件
3.  获取BasicDataSourceFactory对象
4.  创建DataSource对象

private static DataSource dataSource = null;static {    try {        //读取配置文件        InputStream inputStream = Demo3.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");        Properties properties = new Properties();        properties.load(inputStream);        //获取工厂对象        BasicDataSourceFactory basicDataSourceFactory = new BasicDataSourceFactory();        dataSource = basicDataSourceFactory.createDataSource(properties);    } catch (IOException e) {        e.printStackTrace();    } catch (Exception e) {        e.printStackTrace();    }}public static Connection getConnection() throws SQLException {    return dataSource.getConnection();}//这里释放资源不是把数据库的物理连接释放了，是把连接归还给连接池【连接池的Connection内部自己做好了】public static void release(Connection conn, Statement st, ResultSet rs) {    if (rs != null) {        try {            rs.close();        } catch (Exception e) {            e.printStackTrace();        }        rs = null;    }    if (st != null) {        try {            st.close();        } catch (Exception e) {            e.printStackTrace();        }    }    if (conn != null) {        try {            conn.close();        } catch (Exception e) {            e.printStackTrace();        }    }}static DataSource dataSource = null;
    
    static {
        
        try {
        
            //读取配置文件
            InputStream inputStream = Demo3.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
    
            //获取工厂对象
            BasicDataSourceFactory basicDataSourceFactory = new BasicDataSourceFactory();
            dataSource = basicDataSourceFactory.createDataSource(properties);
    
        } catch (IOException e) {
        
            e.printStackTrace();
        } catch (Exception e) {
        
            e.printStackTrace();
        }
    }
    
    public static Connection getConnection() throws SQLException {
        
        return dataSource.getConnection();
    
    }
    
    //这里释放资源不是把数据库的物理连接释放了，是把连接归还给连接池【连接池的Connection内部自己做好了】
    public static void release(Connection conn, Statement st, ResultSet rs) {
        
    
        if (rs != null) {
        
            try {
        
                rs.close();
            } catch (Exception e) {
        
                e.printStackTrace();
            }
            rs = null;
        }
        if (st != null) {
        
            try {
        
                st.close();
            } catch (Exception e) {
        
                e.printStackTrace();
            }
    
        }
        if (conn != null) {
        
            try {
        
                conn.close();
            } catch (Exception e) {
        
                e.printStackTrace();
            }
    
        }
    }

## C3P0 ##

C3P0数据源的性能更胜一筹，并且它可以**使用XML配置文件配置信息！**

步骤：

1.  导入开发包【c3p0-0.9.2-pre1.jar】和【mchange-commons-0.2.jar】
2.  导入XML配置文件【可以在程序中自己一个一个配，C3P0的doc中的Configuration有XML文件的事例】
3.  new出ComboPooledDataSource对象

private static ComboPooledDataSource comboPooledDataSource = null;static {    //如果我什么都不指定，就是使用XML默认的配置，这里我指定的是oracle的    comboPooledDataSource = new ComboPooledDataSource("oracle");}public static Connection getConnection() throws SQLException {    return comboPooledDataSource.getConnection();}static ComboPooledDataSource comboPooledDataSource = null;
    
    static {
        
        //如果我什么都不指定，就是使用XML默认的配置，这里我指定的是oracle的
        comboPooledDataSource = new ComboPooledDataSource("oracle");
    }
    
    public static Connection getConnection() throws SQLException {
        
        return comboPooledDataSource.getConnection();
    }

--------------------

## Tomcat数据源 ##

Tomcat服务器也给我们提供了连接池，内部其实就是DBCP

步骤：

1.  在META-INF目录下配置context.xml文件【文件内容可以在tomcat默认页面的 JNDI Resources下Configure Tomcat's Resource Factory找到】
2.  导入Mysql或oracle开发包到tomcat的lib目录下
3.  初始化JNDI->获取JNDI容器->检索以XXX为名字在JNDI容器存放的连接池

context.xml文件的配置：

try {    //初始化JNDI容器    Context initCtx = new InitialContext();    //获取到JNDI容器    Context envCtx = (Context) initCtx.lookup("java:comp/env");    //扫描以jdbc/EmployeeDB名字绑定在JNDI容器下的连接池    DataSource ds = (DataSource)            envCtx.lookup("jdbc/EmployeeDB");    Connection conn = ds.getConnection();    System.out.println(conn);} 
    
        //初始化JNDI容器
        Context initCtx = new InitialContext();
    
        //获取到JNDI容器
        Context envCtx = (Context) initCtx.lookup("java:comp/env");
    
        //扫描以jdbc/EmployeeDB名字绑定在JNDI容器下的连接池
        DataSource ds = (DataSource)
                envCtx.lookup("jdbc/EmployeeDB");
    
        Connection conn = ds.getConnection();
        System.out.println(conn);
    
    }

--------------------

# 使用dbutils框架 #

**dbutils它是对JDBC的简单封装，极大简化jdbc编码的工作量**

## DbUtils类 ##

提供了**关闭连接，装载JDBC驱动，回滚提交事务等方法**的工具类【比较少使用，因为我们学了连接池，就应该使用连接池连接数据库】

## QueryRunner类 ##

该类**简化了SQL查询，配合ResultSetHandler使用，可以完成大部分的数据库操作**，重载了许多的查询，更新，批处理方法。大大减少了代码量

## ResultSetHandler接口 ##

该接口**规范了对ResultSet的操作**，要对结果集进行什么操作，传入ResultSetHandler接口的实现类即可。

--------------------

使用DbUtils框架对数据库的CRUD

/** 使用DbUtils框架对数据库的CRUD* 批处理** */public class Test {    @org.junit.Test    public void add() throws SQLException {        //创建出QueryRunner对象        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "INSERT INTO student (id,name) VALUES(?,?)";        //我们发现query()方法有的需要传入Connection对象，有的不需要传入        //区别：你传入Connection对象是需要你来销毁该Connection，你不传入，由程序帮你把Connection放回到连接池中        queryRunner.update(sql, new Object[]{"100", "zhongfucheng"});    }    @org.junit.Test    public void query()throws SQLException {        //创建出QueryRunner对象        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "SELECT * FROM student";        List list = (List) queryRunner.query(sql, new BeanListHandler(Student.class));        System.out.println(list.size());    }    @org.junit.Test    public void delete() throws SQLException {        //创建出QueryRunner对象        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "DELETE FROM student WHERE id='100'";        queryRunner.update(sql);    }    @org.junit.Test    public void update() throws SQLException {        //创建出QueryRunner对象        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "UPDATE student SET name=? WHERE id=?";        queryRunner.update(sql, new Object[]{"zhongfuchengaaa", 1});    }    @org.junit.Test    public void batch() throws SQLException {        //创建出QueryRunner对象        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "INSERT INTO student (name,id) VALUES(?,?)";        Object[][] objects = new Object[10][];        for (int i = 0; i < 10; i++) {            objects[i] = new Object[]{"aaa", i + 300};        }        queryRunner.batch(sql, objects);    }}
    public class Test {
        
    
        @org.junit.Test     public void add() throws SQLException {
        
    
            //创建出QueryRunner对象
            QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "INSERT INTO student (id,name) VALUES(?,?)";
    
            //我们发现query()方法有的需要传入Connection对象，有的不需要传入
            //区别：你传入Connection对象是需要你来销毁该Connection，你不传入，由程序帮你把Connection放回到连接池中
            queryRunner.update(sql, new Object[]{
        "100", "zhongfucheng"});
    
        }
    
        @org.junit.Test     public void query()throws SQLException {
        
    
            //创建出QueryRunner对象
            QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "SELECT * FROM student";
    
            List list = (List) queryRunner.query(sql, new BeanListHandler(Student.class));
            System.out.println(list.size());
    
        }
    
        @org.junit.Test     public void delete() throws SQLException {
        
            //创建出QueryRunner对象
            QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "DELETE FROM student WHERE id='100'";
    
            queryRunner.update(sql);
        }
    
        @org.junit.Test     public void update() throws SQLException {
        
            //创建出QueryRunner对象
            QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "UPDATE student SET name=? WHERE id=?";
    
            queryRunner.update(sql, new Object[]{
        "zhongfuchengaaa", 1});
        }
    
        @org.junit.Test     public void batch() throws SQLException {
        
            //创建出QueryRunner对象
            QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "INSERT INTO student (name,id) VALUES(?,?)";
    
            Object[][] objects = new Object[10][];
            for (int i = 0; i < 10; i++) {
        
                objects[i] = new Object[]{
        "aaa", i + 300};
            }
            queryRunner.batch(sql, objects);
        }
    
    }

# 分页 #

分页技术是非常常见的，**在搜索引擎下搜索页面，不可能把全部数据都显示在一个页面里边**。所以我们用到了分页技术。

## Oracle实现分页 ##

/*  Oracle分页语法：    @lineSize---每页显示数据行数    @currentPage----当前所在页*/SELECT *FROM (    SELECT 列名,列名,ROWNUM rn    FROM 表名    WHERE ROWNUM<=(currentPage*lineSize)) tempWHERE temp.rn>(currentPage-1)*lineSize;
    SELECT *FROM (
        SELECT 列名,列名,ROWNUM rn
        FROM 表名
        WHERE ROWNUM<=(currentPage*lineSize)) temp
    
    WHERE temp.rn>(currentPage-1)*lineSize;

**Oracle分页原理简单解释**：

/*  Oracle分页：    Oracle的分页依赖于ROWNUM这个伪列，ROWNUM主要作用就是产生行号。  分页原理：    1：子查询查出前n行数据，ROWNUM产生前N行的行号    2：使用子查询产生ROWNUM的行号，通过外部的筛选出想要的数据  例子：    我现在规定每页显示5行数据【lineSize=5】，我要查询第2页的数据【currentPage=2】    注：【对照着语法来看】  实现：    1：子查询查出前10条数据【ROWNUM<=10】    2：外部筛选出后面5条数据【ROWNUM>5】    3：这样我们就取到了后面5条的数据*/

--------------------

## Mysql实现分页 ##

/*  Mysql分页语法：  @start---偏移量，不设置就是从0开始【也就是(currentPage-1)*lineSize】  @length---长度，取多少行数据*/SELECT *FROM 表名LIMIT [START], length;/*  例子：    我现在规定每页显示5行数据，我要查询第2页的数据  分析：    1：第2页的数据其实就是从第6条数据开始，取5条  实现：    1：start为5【偏移量从0开始】    2：length为5*/
    SELECT *
    FROM 表名
    LIMIT [START], length;
    
    /*   例子：     我现在规定每页显示5行数据，我要查询第2页的数据   分析：     1：第2页的数据其实就是从第6条数据开始，取5条   实现：     1：start为5【偏移量从0开始】     2：length为5 */

**总结：**

--------------------

## 使用JDBC连接数据库实现分页 ##

下面是常见的分页图片

![640?wx\_fmt=png][640_wx_fmt_png]

--------------------

配合图片，看下我们的需求是什么：

1.  **算出有多少页的数据，显示在页面上**
2.  **根据页码，从数据库显示相对应的数据。**

分析：

1.  算出有多少页数据这是非常简单的【在数据库中查询有多少条记录，你每页显示多少条记录，就可以算出有多少页数据了】
2.  使用Mysql或Oracle的分页语法即可

通过上面分析，我们会发现需要用到4个变量

//每页显示3条数据    int lineSize = 3;    //总记录数    int totalRecord = getTotalRecord();    //假设用户指定的是第2页    int currentPage = 2;    //一共有多少页    int pageCount = getPageCount(totalRecord, lineSize);    //使用什么数据库进行分页，记得要在JdbcUtils中改配置    List<Person> list = getPageData2(currentPage, lineSize);    for (Person person : list) {        System.out.println(person);    }//使用JDBC连接Mysql数据库实现分页public static List<Person> getPageData(int currentPage, int lineSize) throws SQLException {    //从哪个位置开始取数据    int start = (currentPage - 1) * lineSize;    QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());    String sql = "SELECT name,address  FROM person LIMIT ?,?";    List<Person> persons = (List<Person>) queryRunner.query(sql, new BeanListHandler(Person.class), new Object[]{start, lineSize});    return persons;}//使用JDBC连接Oracle数据库实现分页public static List<Person> getPageData2(int currentPage, int lineSize) throws SQLException {    //从哪个位置开始取数据    int start = (currentPage - 1) * lineSize;    //读取前N条数据    int end = currentPage * lineSize;    QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());    String sql = "SELECT " +            "  name, " +            "  address " +            "FROM ( " +            "  SELECT " +            "    name, " +            "    address , " +            "    ROWNUM rn " +            "  FROM person " +            "  WHERE ROWNUM <= ? " +            ")temp WHERE temp.rn>?";    List<Person> persons = (List<Person>) queryRunner.query(sql, new BeanListHandler(Person.class), new Object[]{end, start});    return persons;}public static int getPageCount(int totalRecord, int lineSize) {    //简单算法    //return (totalRecord - 1) / lineSize + 1;    //此算法比较好理解，把数据代代进去就知道了。    return totalRecord % lineSize == 0 ? (totalRecord / lineSize) : (totalRecord / lineSize) + 1;}public static int  getTotalRecord() throws SQLException {    //使用DbUtils框架查询数据库表中有多少条数据    QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());    String sql = "SELECT COUNT(*) FROM person";    Object o = queryRunner.query(sql, new ScalarHandler());    String ss = o.toString();    int  s = Integer.parseInt(ss);    return s;}
        int lineSize = 3;
    
        //总记录数
        int totalRecord = getTotalRecord();
    
        //假设用户指定的是第2页
        int currentPage = 2;
    
        //一共有多少页
        int pageCount = getPageCount(totalRecord, lineSize);
    
        //使用什么数据库进行分页，记得要在JdbcUtils中改配置
        List<Person> list = getPageData2(currentPage, lineSize);
        for (Person person : list) {
        
            System.out.println(person);
        }
    
    
    //使用JDBC连接Mysql数据库实现分页
    public static List<Person> getPageData(int currentPage, int lineSize) throws SQLException {
        
    
        //从哪个位置开始取数据
        int start = (currentPage - 1) * lineSize;
    
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT name,address  FROM person LIMIT ?,?";
    
        List<Person> persons = (List<Person>) queryRunner.query(sql, new BeanListHandler(Person.class), new Object[]{start, lineSize});
        return persons;
    
    }
    
    //使用JDBC连接Oracle数据库实现分页
    public static List<Person> getPageData2(int currentPage, int lineSize) throws SQLException {
        
    
        //从哪个位置开始取数据
        int start = (currentPage - 1) * lineSize;
    
        //读取前N条数据
        int end = currentPage * lineSize;
    
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT " +
                "  name, " +
                "  address " +
                "FROM ( " +
                "  SELECT " +
                "    name, " +
                "    address , " +
                "    ROWNUM rn " +
                "  FROM person " +
                "  WHERE ROWNUM <= ? " +
                ")temp WHERE temp.rn>?";
    
        List<Person> persons = (List<Person>) queryRunner.query(sql, new BeanListHandler(Person.class), new Object[]{end, start});
        return persons;
    
    }
    
    public static int getPageCount(int totalRecord, int lineSize) {
        
    
        //简单算法
        //return (totalRecord - 1) / lineSize + 1;
    
        //此算法比较好理解，把数据代代进去就知道了。
        return totalRecord % lineSize == 0 ? (totalRecord / lineSize) : (totalRecord / lineSize) + 1;
    
    }
    
    
    public static int  getTotalRecord() throws SQLException {
        
    
        //使用DbUtils框架查询数据库表中有多少条数据
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT COUNT(*) FROM person";
    
        Object o = queryRunner.query(sql, new ScalarHandler());
    
        String ss = o.toString();
        int  s = Integer.parseInt(ss);
        return s;
    }

## 最后 ##

##  ##

> 乐于输出**干货**的Java技术公众号：Java3y。公众号内有200多篇**原创**技术文章、海量视频资源、精美脑图，不妨来**关注**一下！

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg]

有帮助？好看！转发！ ![640][]

[640_wx_fmt_png]: /images/20210827/4dae08b05b0947928a3577ffe97a8cd6.png?wx_fmt=png
[640_wx_fmt_jpeg]: /images/20210827/7d05e6df5d9f469985df7710a0b3a6ef.png?wx_fmt=jpeg
[640]: /images/20210827/7a4305ef8b9d434f856eb1689e3b575d.png