This is the 8th day of my participation in the First Challenge 2022. For details: First Challenge 2022.

preface

Hello everyone, I'm GW_gw. I'm glad to learn and progress with you.Copy the code

The following content is from the network, if there is any infringement, please contact me to delete, this article is only used for learning exchange, not for any commercial purposes.

Abstract

This article mainly introduces database connection pool technology and the use of two database connection pools (C3P0 database connection pool and DruID database connection pool).Copy the code

1. Basic concepts

For simple database applications, the access to the database is not so frequent, so we can directly close the database after using the database, but for complex database applications, frequent establishment and closing of connections will cause a large additional performance overhead.

Database connection pooling came into being.

A database connection pool is a container for database connection objects.

The database connection pool initially stores a certain number of database connections (the minimum number of database connections). If more than the initial number of database connections are required, new database connections will be established, but the number of connections will not exceed the maximum number. These new database connections will not be released immediately after they are used, but will be retained in the database connection pool for reuse or automatically released after idle timeout.

Advantages:

  1. Save resources.
  2. Improve access efficiency.

2. Implementation

For the implementation of database connection pooling, we need to understand the javax. SQL package interface: DataSource.

The DataSource object is the preferred method for retrieving connections. The DataSource interface is primarily implemented by the driver vendor.

DataSource has only two overloaded methods:

Connection getConnection() attempts to establish a Connection to the DataSource represented by this DataSource object. Connection getConnection(String username, String password) attempts to establish a Connection to the DataSource represented by the DataSource object.Copy the code

Instead of implementing the DataSource interface, vendors implement it, such as the following two database connection pooling implementations.

2.1 C3P0 Connection Pool Configuration

Configuration steps:

  1. Download the driver JAR package and import it.

    https://sourceforge.net/projects/c3p0/
Copy the code

  2. Define the configuration file.

    Create the c3P0.properties file or c3P0-config. XML file in the SRC path (used frequently).

    C3p0. The properties file

    c3p0.url=jdbc:mysql://localhost:3306/db_test? serverTimezone=Asia/Shanghai&useSSL=false c3p0.username=root c3p0.password=123456 c3p0.driver=com.mysql.cj.jdbc.Driver c3p0.maxPoolSize = 20 c3p0.minPoolSize = 3 c3p0.maxStatements = 30 c3p0.maxIdleTime = 150 c3p0.initialPoolSize=10Copy the code

    c3p0-config.xml

    <c3p0-config> <! -- Read database connection pool object with default configuration --> <default-config> <! <property name="driverClass"> com.mysql.cj.jdbc.driver </property> <! - database connection url and to escape the & symbol - > < property name = "jdbcUrl" > JDBC: mysql: / / localhost: 3306 / db_test? serverTimezone=Asia/Shanghai&amp; useSSL=false</property> <! <property name="user">root</property> <! <property name="password">123456</property> <! -- Connection pool parameters --> <! <property name="initialPoolSize">5</property> <! <property name="maxPoolSize">10</property> <! -- Timeouts in milliseconds --> <property name="checkoutTimeout">3000</property> </default-config> <named-config name=" otherc3P0 "> <! <property name="driverClass"> com.mysql.cj.jdbc.driver </property> <! - database connection url and to escape the & symbol - > < property name = "jdbcUrl" > JDBC: mysql: / / localhost: 3306 / db_test1? serverTimezone=Asia/Shanghai&amp; useSSL=false</property> <! <property name="user">root</property> <! <property name="password">123456</property> <! -- Connection pool parameters --> <! <property name="initialPoolSize">5</property> <! <property name="maxPoolSize">20</property> <! <property name="checkoutTimeout">2000</property> </named-config> </ c3P0-config >Copy the code

  3. Create a database connection pool object.

  4. C3p0 XML can have multiple configurations in the same configuration file. If you do not specify a name when creating the file, the default configuration is used. If a name is specified, the configuration with the specified name is used.

    Generally, only the default configuration is used.

    DataSource = new ComboPooledDataSource(); // Use the configuration with the specified name. DataSource dataSource = new ComboPooledDataSource("otherc3p0");Copy the code

  5. Gets the database connection object.

    Connection connection = comboPooledDataSource.getConnection();
Copy the code

  6. Configuration testing

    public class C3p0Demo01 { public static void main(String[] args) throws SQLException { DataSource dataSource = new ComboPooledDataSource(); Connection connection = dataSource.getConnection(); System.out.println(connection); connection.close(); }}Copy the code

2.2 Druid Connection Pool Configuration

Druid database connection pooling is the most popular and high-performance database connection pooling technology developed by Alibaba.

The configuration procedure is as follows:

  1. Download the JAR package and import it.

    https://repo1.maven.org/maven2/com/alibaba/druid
Copy the code

    Just download the latest one.

  2. Create the Properties configuration file

    url=jdbc:mysql://localhost:3306/db_test? serverTimezone=Asia/Shanghai&useSSL=false username=root password=123456 driverClassName=com.mysql.cj.jdbc.Driver # InitialSize =10 # maxActive=20 # maxWait=3000Copy the code

  3. Loading a Configuration File

    Properties properties = new Properties();
properties.load(DruidDemo01.class.getClassLoader().getResourceAsStream("druid.properties"));
Copy the code

  4. Create a database connection pool object

    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Copy the code

  5. Create a database connection object

    Connection connection = dataSource.getConnection();
Copy the code

2.2.1 Druid Tools

package com.poison.utils; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; /** * @description: * @author: Poison * @date: */ public class JdbcUtils { private static DataSource dataSource; Static {try {// load configuration file Properties = new Properties(); properties.load(JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties")); / / create a database connection pool object dataSource = DruidDataSourceFactory createDataSource (properties); } catch (Exception e) { e.printStackTrace(); } /** * Obtain a database Connection from the database Connection pool * @return * @throws SQLException */ public static Connection getConnection() throws SQLException { return dataSource.getConnection(); } /** * Close the connection and release the resource. * @param connection Database connection */ public static void close(Statement statement, connection connection){ close(null,statement,connection); } /** * Close the connection and release the resource. * @param resultSet SQL resultSet object * @param statement SQL statement execution object * @param connection database connection object */ public static void close(ResultSet resultSet,Statement statement, Connection connection){ if(resultSet! =null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if(statement! =null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection! =null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); Public static DataSource getDataSource(){return DataSource; }}Copy the code

summary

So that’s the basic concept of database connection pooling and the basic use of two types of database connection pooling. Druid is more popular because of its higher performance. Hope the above content can be helpful to the reader, if there is a wrong place, welcome to dig by the comments pointed out.