Database connection pool (data source)

1. Database connection pool

Database open connection-execute code-release connection
Open connection and release connection is a waste of system resource
pooling technology: prepare some pre-resources, come and connect to the prepared ones

Minimum number of connections: 10
Maximum number of connections: 15 Maximum service load limit
. Waiting in line with more than the maximum number of connections
Waiting timeout: 100ms

Connection pool, which implements an interface DataSource

Open source data source realizes
dbcp
c3p0
Durid: Alibaba

After using these database connection pools, we don’t need to write database connection code in project development

1.1, DBCP

Need to use jar package

Insert picture description here


Create DBCP configuration file

#连接设置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone-GMT%2B8
username=root
password=root

#!-- 初始化连接 --
initialSize=10

#最大连接数量
maxActive=50

#!-- 最大空闲连接 --
maxIdle=20

#!-- 最小空闲连接 --
minIdle=5

#!-- 超时等待时间以毫秒为单位 6000毫秒/100等于60秒 --
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:【属性名=property;】
#注意:user 与 password 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读(read-only)状态。
#如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

Create a DBCP toolkit

package com.jialidun.datasource.utils;



import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils_DBCP{

        private static DataSource dataSource = null;



        static {

            try {

                InputStream in =
                        JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
                Properties properties = new Properties();
                properties.load(in);

                //创建数据源 工厂模式--->创建
                dataSource = BasicDataSourceFactory.createDataSource(properties);


            } catch (IOException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


        //获取连接
    public static Connection getConnection() throws SQLException {
            return dataSource.getConnection();//从数据源获取连接
    }



        //释放连接资源
        public static void close(Connection connection, Statement statement, ResultSet resultSet){
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

}



Test adding users

package com.jialidun.datasource.test;

import com.jialidun.datasource.utils.JdbcUtils_DBCP;
import com.jialidun.utils.JdbcUtils;

import java.sql.*;

public class TestDbcp {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement statement = null;
        String sql = null;

        try {
            //获取连接
            connection = JdbcUtils_DBCP.getConnection();



            sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";

            //获取statement传输器
            statement = connection.prepareStatement(sql);
            statement.setInt(1,4);
            statement.setString(2,"ragar");
            statement.setString(3,"rygar123");
            statement.setString(4,"[email protected]");
            statement.setDate(5,new Date(new java.util.Date().getTime()));

            int num = statement.executeUpdate();

            if(num > 0){
                System.out.println("添加用户成功!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //释放连接
            JdbcUtils_DBCP.close(connection,statement,null);


        }



    }
}

1.2, C3P0

The required jar packages are as follows:

Insert picture description here


Insert picture description here


c3p0 configuration file c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
        <c3p0-config>
        <!--
        c3p0的缺省(默认)配置
        如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省(默认)
        -->
    <default-config>
        <property> name="driverClass"> com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&amp;characterEncoding=utf8&amp;uesSSL=true&amp;serverTimezone=UTC</property>
        <property name="user"> root </property>
        <property name="password">root</property>

        <property name="acquireIncrement"> 5 </property>
        <property name="initialPoolSize"> 10 </property>
        <property name="minPoolSize"> 5 </property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <!--
       c3p0的缺省(默认)配置
       如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");这样写就表示使用的是c3p0的缺省(默认)
       -->
    <named-config name="MySQL">
        <property> name="driverClass"> com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&amp;characterEncoding=utf8&amp;uesSSL=true&amp;serverTimezone=UTC</property>
        <property name="user"> root </property>
        <property name="password">root</property>

        <property name="acquireIncrement"> 5 </property>
        <property name="initialPoolSize"> 10 </property>
        <property name="minPoolSize"> 5 </property>
        <property name="maxPoolSize">20</property>
    </named-config>
        </c3p0-config>

Some special symbols in the xml file
Create c3p0 toolkit

package com.jialidun.datasource.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

public class JdbcUtils_C3P0 {
    private static ComboPooledDataSource dataSource = null;



    static {

        try {

            //会自动读取xml文件里匹配的内容

            //创建数据源 工厂模式--->创建
            //dataSource = new ComboPooledDataSource("MySQL");//配置name = MySQL

            dataSource = new ComboPooledDataSource();

            //代码实现配置
//            dataSource.setJdbcUrl();
//            dataSource.setUser();
//            dataSource.setPassword();
//            dataSource.setMinPoolSize();
//            dataSource.setMaxPoolSize();
//            dataSource.setMaxIdleTime();
//            ...



        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源获取连接
    }



    //释放连接资源
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

Test adding users

package com.jialidun.datasource.test;

import com.jialidun.datasource.utils.JdbcUtils_C3P0;
import com.jialidun.datasource.utils.JdbcUtils_DBCP;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestC3p0 {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement statement = null;
        String sql = null;

        try {
            //获取连接
            connection = JdbcUtils_C3P0.getConnection();



            sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";

            //获取statement传输器
            statement = connection.prepareStatement(sql);
            statement.setInt(1,5);
            statement.setString(2,"黑旋风李逵");
            statement.setString(3,"likui666");
            statement.setString(4,"[email protected]");
            statement.setDate(5,new Date(new java.util.Date().getTime()));

            int num = statement.executeUpdate();

            if(num > 0){
                System.out.println("添加用户成功!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //释放连接
            JdbcUtils_C3P0.close(connection,statement,null);


        }




    }
}

Conclusion
No matter what data source is used, the essence remains the same. The DataSource interface will not change, and the method will not change.
Druid

Apache

Insert picture description here