13_连接池的使用

心已赠人 2023-09-30 19:54 12阅读 0赞

建立数据库连接的两种方式:

传统连接方式：

首先调用Class.forName()方法加载数据库驱动，然后调用DriverManager.getConnection()方法建立连接.

连接池方式:

连接池解决方案是在应用程序启动时就预先建立多个数据库连接对象,然后将连接对象保存到连接池中。当客户请求到来时,从池中取出一个连接对象为客户服务。当请求完成时,客户程序调用close()方法,将连接对象放回池中.对于多于连接池中连接数的请求，排队等待。应用程序还可根据连接池中连接的使用率，动态增加或减少池中的连接数。

传统方式存在问题

Connection对象在每次执行DML和DQL的过程中都要创建一次,DML和DQL执行完毕后,connection对象都会被销毁. connection对象是可以反复使用的,没有必要每次都创建新的.该对象的创建和销毁都是比较消耗系统资源的,如何实现connection对象的反复使用呢?使用连接池技术实现.

连接池的优势

1预先准备一些链接对象,放入连接池中,当多个线程并发执行时,可以避免短时间内一次性大量创建链接对象,减少计算机单位时间内的运算压力,提高程序的响应速度

2实现链接对象的反复使用,可以大大减少链接对象的创建次数,减少资源的消耗

![3fca079280733b53a4677beb37c942d0.png][]

![7a5270ecf481458d9fbc87c8b1f59069.png][]

具体实现如下

![19b1d95dc23e2abaaaed93bb1ec006e8.png][]

1定义连接池

package com.msb.dao;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.LinkedList;
    /**
     * @Author: Ma HaiYang
     * @Description: MircoMessage:Mark_7001
     */
    public class MyConnectionPool {
        private static String driver ="com.mysql.cj.jdbc.Driver";
        private static String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        private static String user="root";
        private static String password="root";
        private static int initSize=1;
        private static int maxSize=1;
        private static LinkedList<Connection> pool;
        static{
            // 加载驱动
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            // 初始化pool
            pool=new LinkedList<Connection>();
            // 创建5个链接对象
            for (int i = 0; i <initSize ; i++) {
                Connection connection = initConnection();
                if(null != connection){
                    pool.add(connection);
                    System.out.println("初始化连接"+connection.hashCode()+"放入连接池");
                }
            }
        }
        // 私有的初始化一个链接对象的方法
        private static Connection initConnection(){
            try {
                return DriverManager.getConnection(url,user,password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
        // 共有的向外界提供链接对象的
        public static Connection getConnection(){
            Connection connection =null;
            if(pool.size()>0){
                connection= pool.removeFirst();// 移除集合中的第一个元素
                System.out.println("连接池中还有连接:"+connection.hashCode());
            }else{
                connection = initConnection();
                System.out.println("连接池空,创建新连接:"+connection.hashCode());
            }
            return connection;
        }
        // 共有的向连接池归还连接对象的方法
        public static void returnConnection(Connection connection){
            if(null != connection){
                try {
                    if(!connection.isClosed()){
                        if(pool.size()<maxSize){
                            try {
                                connection.setAutoCommit(true);// 调整事务状态
                                System.out.println("设置连接:"+connection.hashCode()+"自动提交为true");
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                            pool.addLast(connection);
                            System.out.println("连接池未满,归还连接:"+connection.hashCode());
                        }else{
                            try {
                                connection.close();
                                System.out.println("连接池满了,关闭连接:"+connection.hashCode());
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }else{
                        System.out.println("连接:"+connection.hashCode()+"已经关闭,无需归还");
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }else{
                System.out.println("传入的连接为null,不可归还");
            }
        }
    }

![47536fcd335127c42bfbb86b98bbdd55.gif][]

![c3434cd186bca613be68e7bca1a055a5.gif][]

2修改BaseDao

package com.msb.dao;
    import java.lang.reflect.Field;
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    /**
     * @Author: Ma HaiYang
     * @Description: MircoMessage:Mark_7001
     */
    public abstract class BaseDao {
        public int baseUpdate(String sql,Object ... args){
            // 向 Emp表中增加一条数据
            Connection connection = null;
            PreparedStatement preparedStatement=null;
            int rows=0;
            try{
                connection = MyConnectionPool.getConnection();
                preparedStatement = connection.prepareStatement(sql);
                //设置参数
                for (int i = 0; i <args.length ; i++) {
                    preparedStatement.setObject(i+1, args[i]);
                }
                //执行CURD
                rows =preparedStatement.executeUpdate();// 这里不需要再传入SQL语句
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                if(null != preparedStatement){
                    try {
                        preparedStatement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                MyConnectionPool.returnConnection(connection);
            }
            return rows;
        }
        public List baseQuery(Class clazz,String sql,Object ... args) {
            // 查询名字中包含字母A的员工信息
            Connection connection = null;
            PreparedStatement preparedStatement=null;
            ResultSet resultSet=null;
            List list =null;
            try{
                connection = MyConnectionPool.getConnection();
                preparedStatement = connection.prepareStatement(sql);//这里已经传入SQL语句
                //设置参数
                for (int i = 0; i <args.length ; i++) {
                    preparedStatement.setObject(i+1, args[i]);
                }
                //执行CURD
                resultSet = preparedStatement.executeQuery();// 这里不需要再传入SQL语句
                list=new ArrayList() ;
                // 根据字节码获取所有 的属性
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);// 设置属性可以 访问
                }
                while(resultSet.next()){
                    // 通过反射创建对象
                    Object obj = clazz.newInstance();//默认在通过反射调用对象的空参构造方法
                    for (Field field : fields) {// 临时用Field设置属性
                        String fieldName = field.getName();// empno  ename job .... ...
                        Object data = resultSet.getObject(fieldName);
                        field.set(obj,data);
                    }
                    list.add(obj);
                }
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                if(null != resultSet){
                    try {
                        resultSet.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if(null != preparedStatement){
                    try {
                        preparedStatement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                MyConnectionPool.returnConnection(connection);
            }
            return list;
        }
    }

![97d6d44039e7781c02abd42ffd3591a7.gif][]

![f70a3b1d98ab9a907549b04bf317c6a5.gif][]

配置文件优化参数存储

准备jdbc.properties配置文件,放在src下

## key=value
    driver=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
    user=root
    password=root
    initSize=1
    maxSize=1

![d9123881140a74d771541ea39f736807.gif][]

![6b137f7ac0d12e41a534d534918e1b19.gif][]

![35b09bbcf3996bdda76710be81c79157.png][]

准备PropertiesUtil工具类

package com.msb.util;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.Properties;
    /**
     * @Author: Ma HaiYang
     * @Description: MircoMessage:Mark_7001
     */
    public class PropertiesUtil {
        private Properties properties;
        public PropertiesUtil(String path){
            properties=new Properties();
            InputStream inputStream = this.getClass().getResourceAsStream(path);
            try {
                properties.load(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        public String getProperties(String key){
            return properties.getProperty(key);
        }
    }

![963a736036f1bbf5d59180ab083f4b88.gif][]

![f92a27c609a4a02e6476bf6e0d314b92.gif][]

连接池中代码修改

package com.msb.dao;
    import com.msb.util.PropertiesUtil;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.LinkedList;
    /**
     * @Author: Ma HaiYang
     * @Description: MircoMessage:Mark_7001
     */
    public class MyConnectionPool {
        private static String driver;
        private static String url;
        private static String user;
        private static String password;
        private static int initSize;
        private static int maxSize;
        private static LinkedList<Connection> pool;
        static{
            // 初始化参数
            PropertiesUtil propertiesUtil=new PropertiesUtil("/jdbc.properties");
            driver=propertiesUtil.getProperties("driver");
            url=propertiesUtil.getProperties("url");
            user=propertiesUtil.getProperties("user");
            password=propertiesUtil.getProperties("password");
            initSize=Integer.parseInt(propertiesUtil.getProperties("initSize"));
            maxSize=Integer.parseInt(propertiesUtil.getProperties("maxSize"));
            // 加载驱动
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            // 初始化pool
            pool=new LinkedList<Connection>();
            // 创建5个链接对象
            for (int i = 0; i <initSize ; i++) {
                Connection connection = initConnection();
                if(null != connection){
                    pool.add(connection);
                    System.out.println("初始化连接"+connection.hashCode()+"放入连接池");
                }
            }
        }
        // 私有的初始化一个链接对象的方法
        private static Connection initConnection(){
            try {
                return DriverManager.getConnection(url,user,password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
        // 共有的向外界提供链接对象的
        public static Connection getConnection(){
            Connection connection =null;
            if(pool.size()>0){
                connection= pool.removeFirst();// 移除集合中的第一个元素
                System.out.println("连接池中还有连接:"+connection.hashCode());
            }else{
                connection = initConnection();
                System.out.println("连接池空,创建新连接:"+connection.hashCode());
            }
            return connection;
        }
        // 共有的向连接池归还连接对象的方法
        public static void returnConnection(Connection connection){
            if(null != connection){
                try {
                    if(!connection.isClosed()){
                        if(pool.size()<maxSize){
                            try {
                                connection.setAutoCommit(true);// 调整事务状态
                                System.out.println("设置连接:"+connection.hashCode()+"自动提交为true");
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                            pool.addLast(connection);
                            System.out.println("连接池未满,归还连接:"+connection.hashCode());
                        }else{
                            try {
                                connection.close();
                                System.out.println("连接池满了,关闭连接:"+connection.hashCode());
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }else{
                        System.out.println("连接:"+connection.hashCode()+"已经关闭,无需归还");
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }else{
                System.out.println("传入的连接为null,不可归还");
            }
        }
    }

![6b101b5f08786b117aa3f5e3efb488c8.gif][]

![9e09da12ea1fcd26ddee07f5b698099b.gif][]

[3fca079280733b53a4677beb37c942d0.png]: https://img-blog.csdnimg.cn/img_convert/3fca079280733b53a4677beb37c942d0.png
[7a5270ecf481458d9fbc87c8b1f59069.png]: https://img-blog.csdnimg.cn/img_convert/7a5270ecf481458d9fbc87c8b1f59069.png
[19b1d95dc23e2abaaaed93bb1ec006e8.png]: https://img-blog.csdnimg.cn/img_convert/19b1d95dc23e2abaaaed93bb1ec006e8.png
[47536fcd335127c42bfbb86b98bbdd55.gif]: https://img-blog.csdnimg.cn/img_convert/47536fcd335127c42bfbb86b98bbdd55.gif
[c3434cd186bca613be68e7bca1a055a5.gif]: https://img-blog.csdnimg.cn/img_convert/c3434cd186bca613be68e7bca1a055a5.gif
[97d6d44039e7781c02abd42ffd3591a7.gif]: https://img-blog.csdnimg.cn/img_convert/97d6d44039e7781c02abd42ffd3591a7.gif
[f70a3b1d98ab9a907549b04bf317c6a5.gif]: https://img-blog.csdnimg.cn/img_convert/f70a3b1d98ab9a907549b04bf317c6a5.gif
[d9123881140a74d771541ea39f736807.gif]: https://img-blog.csdnimg.cn/img_convert/d9123881140a74d771541ea39f736807.gif
[6b137f7ac0d12e41a534d534918e1b19.gif]: https://img-blog.csdnimg.cn/img_convert/6b137f7ac0d12e41a534d534918e1b19.gif
[35b09bbcf3996bdda76710be81c79157.png]: https://img-blog.csdnimg.cn/img_convert/35b09bbcf3996bdda76710be81c79157.png
[963a736036f1bbf5d59180ab083f4b88.gif]: https://img-blog.csdnimg.cn/img_convert/963a736036f1bbf5d59180ab083f4b88.gif
[f92a27c609a4a02e6476bf6e0d314b92.gif]: https://img-blog.csdnimg.cn/img_convert/f92a27c609a4a02e6476bf6e0d314b92.gif
[6b101b5f08786b117aa3f5e3efb488c8.gif]: https://img-blog.csdnimg.cn/img_convert/6b101b5f08786b117aa3f5e3efb488c8.gif
[9e09da12ea1fcd26ddee07f5b698099b.gif]: https://img-blog.csdnimg.cn/img_convert/9e09da12ea1fcd26ddee07f5b698099b.gif