DBCP and C3P0 data sources

Posted by parth on Sat, 22 Jan 2022 20:26:20 +0100

Database connection pool

DBCP---------C3P0 connection pool

What is a connection pool

Database connection - execution complete - release

Connect - free up wasted system resources

Pooling Technology: prepare some pre prepared resources and connect the pre prepared resources

For example, the bank counter:

Open the door - serve one person - close the door

Open the door - salesman: wait - Service - close the door

How many salesmen does a bank need? (minimum number of connections)

**Minimum number of connections: * * if the number of common connections is about 100, the minimum number of connections is set to 100;

Maximum number of connections: the maximum carrying capacity of the service;

**Waiting timeout: * * 100ms

Open source data source implementation

To write a connection pool, you need to implement an interface DataSource

DBCP

C3P0

Druid: Ali

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

In the case of concurrency, using data sources can improve the performance of programs

Open source implementation: import JAR package and use it immediately

Using DBCP

Configure Maven environment first

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.otwb</groupId>
    <artifactId>MavenMysqlJDBC</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>

        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>

        <dependency>
            <groupId>commons-pool</groupId>
            <artifactId>commons-pool</artifactId>
            <version>1.5.4</version>
        </dependency>
    </dependencies>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

</project>

create profile

#The name in connection settings is defined in the DBCP data source and cannot be named by yourself
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root

#<!--  Initialize connection -- >
initialSize=10

#Maximum number of connections
maxActive=50

#<!--  Maximum idle connections -- >
maxIdle=20

#<!--  Minimum idle connection -- >
minIdle=5

#<!--  Timeout wait time in milliseconds 6000 milliseconds / 1000 equals 60 seconds -- >
maxWait=60000
#The format of the connection property attached when the JDBC driver establishes a connection must be: [property name = property;]
#Note: the user and password attributes will be explicitly passed, so there is no need to include them here.
connectionProperties=useUnicode=true;characterEncoding=UTF8

#Specifies the auto commit status of connections created by the connection pool.
defaultAutoCommit=true

#driver default specifies the read-only status of connections created by the connection pool.
#If this value is not set, the "setReadOnly" method will not be called. (some drivers do not support read-only mode, such as Informix)
defaultReadOnly=

#driver default specifies the transaction level (TransactionIsolation) of the connection created by the connection pool.
#The available values are one of the following: (see javadoc for details.) NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

Use DBCP to obtain connections and write tool classes

package demo6.utils;

import org.apache.commons.dbcp.BasicDataSourceFactory;

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


public class JdbcUtils_DBCP {

    private static DataSource dataSource = null;

    static {
        try {
            /*Read the configuration file of DBCP below*/
            //Get the configuration file with the Properties class
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);
            //Create data source factory pattern -- > create object
            dataSource = BasicDataSourceFactory.createDataSource(properties);

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

    //Get connection
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection(); //Get connections from data sources -- pooling technology improves performance
    }


    //Release connection resources
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
//        if (in != null) {
//            try {
//                in.close();
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
//        }
    }
}

test

package demo6;

import demo2.utils.JdbcUtils;
import demo6.utils.JdbcUtils_DBCP;

import java.util.Date;// new Data(); If an error is reported, you need to import this package
import java.sql.*;

public class TestDBCP {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re = null;
        try {
            conn = JdbcUtils_DBCP.getConnection();
            //Difference from Statement
            //use? Placeholder instead of parameter
            //prepareStatement prevents the essence of SQL injection and treats the parameters passed in as characters
            //Suppose there are escape characters, such as' will be directly escaped
            //The parameters in MyBatis are written like this
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)";
            st = conn.prepareStatement(sql);//You need a precompiled SQL. Write the SQL first and then do not execute it
            //Assign parameters manually
            st.setInt(1,4);//id
            st.setString(2,"leakli");//NAME
            st.setString(3,"1234567");//password
            st.setString(4,"1234567@qq.com");//password
            //Note: SQL Date database
            //       util.Date   java       new Date().getTime() gets the timestamp
            st.setDate(5,new java.sql.Date(new Date().getTime()));//birthday
            //implement
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("Insert successful");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils_DBCP.release(conn,st,null);
        }
    }
}

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-69ryfwo3-1625391309669) (C: \ users \ hang. Li \ appdata \ roaming \ typora user images \ image-20210704163051943. PNG)]

I can't see the effect temporarily, but in the case of concurrency, the performance of the program is improved by using data sources

Use C3P0

Jar package to use:

c3p0-0.9.55.jar
mchange-commons-java-0.2.19.jar

Configure Maven environment

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.otwb</groupId>
    <artifactId>MavenMysqlJDBC</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>

        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>

        <dependency>
            <groupId>commons-pool</groupId>
            <artifactId>commons-pool</artifactId>
            <version>1.5.4</version>
        </dependency>

        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>

        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>mchange-commons-java</artifactId>
            <version>0.2.11</version>
        </dependency>

    </dependencies>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

</project>

create profile

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- Default configuration. If not specified, this configuration is used -->
    <!--C3P0 Default of(default)to configure
    If in code"ComboPooledDataSource ds = new ComboPooledDataSource();
    This means that C3P0 Default of(default)"-->
    <default-config>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;
            characterEncoding=utf8&amp;useSSL=true</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="checkoutTimeout">30000</property>
        <property name="idleConnectionTestPeriod">30</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">2</property>
        <property name="maxStatements">200</property>
    </default-config>
    <!-- Named configuration,It can be implemented through method calls -->
    <!-- If in code ComboPooledDataSource ds=new ComboPooledDataSource(MySQL);
    This means that mysql Default for (default)-->
    <named-config name="MySQL">
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;
            characterEncoding=utf8&amp;useSSL=true</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!-- If the data connection in the pool is insufficient, how many data connections will be increased at a time -->
        <property name="acquireIncrement">5</property>
        <!-- The number of connections when initializing the database connection pool -->
        <property name="initialPoolSize">20</property>
        <!-- The maximum number of database connections in the database connection pool -->
        <property name="maxPoolSize">25</property>
        <!-- Minimum number of database connections in the database connection pool -->
        <property name="minPoolSize">5</property>
    </named-config>
</c3p0-config>

Write tool class

package demo6.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.*;


public class JdbcUtils_C3P0 {

    private static ComboPooledDataSource dataSource = null;

    static {
        try {
//            //Code implementation
//            dataSource = new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setUser();
//            dataSource.setPassword();
//            dataSource.setJdbcUrl();
//
//            dataSource.setMaxPoolSize();
//            dataSource.setMinPoolSize();
            /*The configuration file of DBCP is read below, and the configuration file type of C3P0 is xml, which will be read automatically*/
            //Create data source factory pattern -- > create object
            dataSource = new ComboPooledDataSource("MySQL");//Configuration file writing

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

    //Get connection
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection(); //Get connections from data sources -- pooling technology improves performance
    }


    //Release connection resources
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
//        if (in != null) {
//            try {
//                in.close();
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
//        }
    }
}

test

package demo6;

import demo6.utils.JdbcUtils_C3P0;
import java.util.Date;// new Data(); If an error is reported, you need to import this package
import java.sql.*;

public class TestC3P0 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re = null;
        try {
            conn = JdbcUtils_C3P0.getConnection(); //It was originally realized by yourself, but now it is realized by others
            //Difference from Statement
            //use? Placeholder instead of parameter
            //prepareStatement prevents the essence of SQL injection from treating the parameters passed in as characters
            //Suppose there are escape characters, such as' will be directly escaped
            //The parameters in MyBatis are written like this
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)";
            st = conn.prepareStatement(sql);//You need a precompiled SQL. Write the SQL first and then do not execute it
            //Assign parameters manually
            st.setInt(1,8);//id
            st.setString(2,"leakli");//NAME
            st.setString(3,"1234567");//password
            st.setString(4,"1234567@qq.com");//password
            //Note: SQL Date database
            //       util.Date   java       new Date().getTime() gets the timestamp
            st.setDate(5,new java.sql.Date(new Date().getTime()));//birthday
            //implement
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("Insert successful");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils_C3P0.release(conn,st,null);
        }
    }
}

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-zhw71a4c-1625391309670) (C: \ users \ hang. Li \ appdata \ roaming \ typora user images \ image-20210704172136304. PNG)]

conclusion

No matter what data source is used, the essence is the same. The DataSource interface will not change and the method will not change; Only a few changes are needed

Druid (data source, primary)

apache Apache Foundation

www.apache.rog

tomcat/maven and other tools, open source

Topics: dbcp c3p0

Programmer Think

DBCP and C3P0 data sources

Database connection pool

Using DBCP

Configure Maven environment first

create profile

Use DBCP to obtain connections and write tool classes

test

Use C3P0

Configure Maven environment

create profile

Write tool class

test

conclusion

Druid (data source, primary)

Hot Topics