Introduction to MySQL (VII)

Time:2022-5-27
9. Database connection pool
  • Users need to obtain a link from the database every time they request, and creating a connection to the database usually consumes relatively large resources and takes a long time to create;
  • If the website has 100000 visits a day and the database server needs to create 100000 connections, which will greatly waste database resources and easily cause memory overflow and downtime of the database server.

Basic concepts of database connection pool

  • Database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection instead of re establishing one
  • Database connection pool: during initialization, create a certain number of database connections and put them into the connection pool. The number of these database connections is set by the minimum number of database connections. Whether these database connections are used or not, the connection pool will always ensure the minimum number of connections;
  • The maximum number of database connections in the connection pool limits the maximum number of connections that can be occupied by the connection pool. When the number of connections requested by the application from the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue;
  • The following factors should be taken into account when setting the minimum number of connections and the maximum number of connections:
    • Minimum number of connections: the number of database connections maintained all the time. If the application does not use a lot of database connections, a lot of resources will be wasted;
    • Maximum connections: the maximum number of connections that can be applied for. If the number of database connection requests exceeds, the subsequent database connection requests will be added to the waiting queue, affecting future database operations;
    • If there is a big difference between the minimum number of connections and the maximum number of connections: the first connection request will benefit, and then the connection request exceeding the minimum number of connections is equivalent to establishing a new database connection. However, these database connections greater than the minimum number of connections will not be released immediately after use, but will be placed in the connection pool and released after repeated use or space timeout.
  • Write a connection pool, which needs to be implementedjava.sql.DataSourceInterface

Open source database connection pool

  • Now many web servers (Weblogic, WebSphere, Tomcat) provideDataSoruceThe realization of,That is, the implementation of connection pool
  • UsuallyDataSourceAccording to its English meaning, it is calleddata source, the data source includes the implementation of database connection pool;
  • There are also some open source organizations that provide independent implementations of data sources:
    • DBCP database connection pool;
    • C3p0 database connection pool;
    • Druid
  • After using the database connection pool, in the actual development of the project, there is no need to write the code to connect to the database, and obtain the connection to the database directly from the data source.

DBCP data source

  • For the implementation of open source connection pool under Apache, two jar files need to be added to the system:
    • Commons-dbcp-1.4.jar: implementation of connection pool;Download link
    • Commons-pool-1.6.jar: dependency library implemented by connection pool;Download link
  • Tomcat’s connection pool is realized by using this connection pool. The database connection pool can be integrated with the application server or used independently by the application program;

DBCP test:

  • Import relevant jar packages;
Introduction to MySQL (VII)
  • Class directory, create the DBCP configuration file: dbcpconfig properties
#Connection settings
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

#Initialize connection
#initialSize=10

#Maximum number of connections
#maxActive=50

#Maximum idle connections
#maxIdle=20

#Minimum idle connection
#minIdle=5

#The timeout waiting time is 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
  • Authoring tool class: jdbcutils_ DBCP
package com.xxx.demo05.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 {
    /*
        In Java, writing database connection pool needs to implement Java sql. Datasource interface,
        Each database connection pool is the implementation of datasource interface
        DBCP connection pool is Java sql. A concrete implementation of datasource interface
     */
    private static DataSource ds = null;

    //Create database connection pool in static code block
    static {
        try {
            //Load dbcpconfig Properties configuration file
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            //Create Attribute Collection
            Properties props = new Properties();
            //Read stream
            props.load(in);

            //Create data source factory pattern - > create
            ds = BasicDataSourceFactory.createDataSource(props);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //Get database connection from data source
    public static Connection getConnection() throws SQLException {
        //Get database connection from data source
        return ds.getConnection();
    }

    //Release resources: close from back to front
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                //Close resultset resultset object
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                //Close the statement object that executes the SQL command
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                //Close connection database connection object
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
  • Test class
package com.xxx.demo05;

import com.xxx.demo05.utils.JdbcUtils_DBCP;

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

public class TestDBCP {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            //Get database connection through database connection pool
            conn = JdbcUtils_DBCP.getConnection();

            //Execute SQL command
            String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)" +
                    "VALUES(?,?,?,?,?);";
            //Precompile SQL
            st = conn.prepareStatement(sql);
            st.setInt(1, 5);
            St.setstring (2, "test 3");
            st.setString(3, "123");
            st.setString(4, "[email protected]");
            st.setDate(5, new java.sql.Date(new Date().getTime()));

            //Execute SQL
            int i = st.executeUpdate();
            if (i > 0) {
                System. out. Println ("insert succeeded");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //Release resources
            JdbcUtils_DBCP.release(conn, st, null);
        }
    }
}