JDBC连接池

小咪咪 2023-10-11 11:08 30阅读 0赞

**一、常用连接池：**

（1）C3P0（2）Druid(德鲁伊)（3）DBCP（4）JNDI

**二、Druid(德鲁伊)：**

**　　1、****配置说明****：**

**　　**　（1）导入jar包：druid.jar；

**　　　**（2）配置properties格式的配置文件：文件位置随意；命名一般db\_server.properties；

**　　2、****properties****配置****：**

driverClassName=com.mysql.jdbc.Driver
    url=jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8
    username=root
    password=123456
    filters=stat
    initialSize=2
    maxActive=300
    maxWait=60000
    timeBetweenEvictionRunsMillis=60000
    minEvictableIdleTimeMillis=300000
    validationQuery=SELECT 1
    testWhileIdle=true
    testOnBorrow=false
    testOnReturn=false
    poolPreparedStatements=false
    maxPoolPreparedStatementPerConnectionSize=200

**　　3、工具类封装：**

import java.io.File;
        import java.io.FileInputStream;
        import java.io.InputStream;
        import java.sql.SQLException;
        import java.util.Properties;
        import com.alibaba.druid.pool.DruidDataSource;
        import com.alibaba.druid.pool.DruidDataSourceFactory;
        import com.alibaba.druid.pool.DruidPooledConnection;
        
        /**
         * 要实现单例模式，保证全局只有一个数据库连接池
         */
        public class DBPoolConnection {
            private static DBPoolConnection dbPoolConnection = null;
            private static DruidDataSource druidDataSource = null;
        
            static {
                Properties properties = loadPropertiesFile("src/druid.properties");
                try {
                    druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties); // DruidDataSrouce工厂模式
                } catch (Exception e) {
                    e.printStackTrace();
                    System.out.println("配置失败");
                }
            }
        
            /**
             * 数据库连接池单例
             * 
             * @return
             */
            public static synchronized DBPoolConnection getInstance() {
                if (null == dbPoolConnection) {
                    dbPoolConnection = new DBPoolConnection();
                }
                return dbPoolConnection;
            }
        
            /**
             * 返回druid数据库连接
             * 
             * @return
             * @throws SQLException
             */
            public DruidPooledConnection getConnection() throws SQLException {
                return druidDataSource.getConnection();
            }
        
            /**
             * @param string 配置文件名
             * @return Properties对象
             */
            private static Properties loadPropertiesFile(String fullFile) {
                String webRootPath = null;
                if (null == fullFile || fullFile.equals("")) {
                    throw new IllegalArgumentException("Properties file path can not be null" + fullFile);
                }
                webRootPath = DBPoolConnection.class.getClassLoader().getResource("").getPath();
                webRootPath = new File(webRootPath).getParent();
                InputStream inputStream = null;
                Properties p = null;
                try {
                    inputStream = new FileInputStream(new File(webRootPath + File.separator + fullFile));
                    p = new Properties();
                    p.load(inputStream);
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    try {
                        if (null != inputStream) {
                            inputStream.close();
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                return p;
            }
            
            /**
             * @param Object 需要关闭的资源
             * @return 
             */
            public static void closeAll(Object... objects) {
                for (int i = 0; i < objects.length; i++) {
                    if(objects[i] instanceof Statement) {
                        Statement statement = (Statement)objects[i];
                        if(statement!=null) {
                            try {
                                statement.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                    if(objects[i] instanceof ResultSet) {
                        ResultSet resultSet = (ResultSet)objects[i];
                        if(resultSet!=null) {
                            try {
                                resultSet.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                    if(objects[i] instanceof Connection) {
                        Connection connection = (Connection)objects[i];
                        if(connection!=null) {
                            try {
                                connection.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
    }

**　　4、配置****sql****监控：**在web.xml中配置；

<servlet>
    <servlet-name>DruidStatView</servlet-name>
    <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
    </servlet>
    <servlet-mapping>
    <servlet-name>DruidStatView</servlet-name>
    <url-pattern>/druid/*</url-pattern>
    </servlet-mapping>

**三、DBCP：**

**　　1、配置说明：**

（1）导入jar包；

（2）配置properties文件；

**　　2、创建简单工具类：**

![1651677-20190804233752011-148517206.png][]

**　　3、配置文件：**

driverClassName=com.mysql.jdbc.Driver
    url=jdbc:mysql://127.0.0.1:3306/servlettest
    username=root
    password=000000
    maxTotal=30
    maxIdle=10
    minIdle=5
    maxWaitMillis=1000
    removeAbandonedOnMaintenance=true
    removeAbandonedOnBorrow=true
    removeAbandonedTimeout=1

**四、C3P0：**

**　　1、配置说明：**

**　　　（1）****导入****jar****包；**

**　　　（2）****配置c3p0-config.xml：**必须再src目录下；名字不能变；

**　　2、****xml****配置：**

<?xml version="1.0" encoding="UTF-8"?>
        <c3p0-config>
           
            <default-config>  
                <property name="initialPoolSize">10</property>  
                <property name="maxIdleTime">30</property>  
                <property name="maxPoolSize">100</property>  
                <property name="minPoolSize">10</property>  
                <property name="maxStatements">200</property>  
            </default-config>  
          
           
            <named-config name="mysql">  
                <property name="driverClass">com.mysql.jdbc.Driver</property>  
                <property name="jdbcUrl">jdbc:mysql://localhost:3306/CoupleSpace</property>  
                <property name="user">root</property>  
                <property name="password">root</property>  
                <property name="initialPoolSize">10</property>  
                <property name="maxIdleTime">30</property>  
                <property name="maxPoolSize">100</property>  
                <property name="minPoolSize">10</property>  
                <property name="maxStatements">200</property>  
            </named-config>  
            
            
            ......
            
            ......
            
            ......
        </c3p0-config>

**　　3、使用说明：**

**　　（****1****）获取****c3q0****对象：**new ComboPooledDataSource(\["mySource"\])；

//参数mySource：为<named-config name="标识"> </name-config>标签中的name属性；

//参数可以不写为默认；

**　　（****2****）获取****Connection****：**new ComboPooledDataSource().getConnection()；

**　　（****3****）归还****Connection**：.close()；

转载于:https://www.cnblogs.com/Tractors/p/11300584.html

[1651677-20190804233752011-148517206.png]: https://img2018.cnblogs.com/blog/1651677/201908/1651677-20190804233752011-148517206.png