JDBC connection pool

summary

The creation and destruction of Connection objects for one-time use takes time.
Connection pool can reuse connections and avoid waste.
When the program initializes, it initializes multiple connections and puts them into the pool (Collection). Each time, it can be obtained directly from the connection pool. After use, it returns the connections to the pool.

principle

1. The program starts by creating a certain number of connections in the connection pool
2. When in use, directly take the connection object and return it after use
3. If the connection in the pool is used up and the program needs to use the connection, wait for a period of time. If a connection is returned within this period of time, take it for use; If the connection has not been returned, a new one will be created, but it will be destroyed when it is used up
4. Set selection LinkedList
   Add and delete faster
   The removeFirst() and addLast() methods in LinkedList are consistent with the principle of connection pool

Third party connection pool

The connection pool we defined is still weak compared with the third-party connection pool. Using third-party tools requires importing jar packages and some configuration files.

• C3P0 connection pool, which realizes the binding of data source and JNDI, and supports the JDBC 3 specification and the standard extension of JDBC 2. C3P0 is asynchronous operation, so some JDBC operations that take too long are completed through other auxiliary threads. At present, its open source projects include Hibernate, Spring and so on. C3P0 has the function of automatically reclaiming idle connections
• druid connection pool: a project of Alibaba open source platform. The whole project consists of database connection pool, plug-in framework and SQL parser.
• DBCP(DataBase Connection Pool) is a Java connection pool project on Apache and a connection pool component used by Tomcat. dbcp does not automatically recycle idle connections.

datasource interface

summary

javax.sql.DataSource, the public interface of database Connection pool, is actually the Connection pool, which can obtain the Connection object. Various database vendors provide implementations.

shortcoming

The DataSource does not provide a method to return the connection. Solution:
1. Inheritance.
Condition: you can control the parent class. At least you know the name of the parent class
2. Decorator mode.

        1. Create a MyConnection realization Connection
        2. stay MyConnection Get enhanced connection object
        3. rewrite MyConnection Inside close()The logic of the method is return
        4. MyConnection Other method calls inside are enhanced connection Logic before object
        5. stay MyDataSource03 of getConnection()Method returns myConnection

3. Dynamic proxy.

Example

public class MyDataSource2 implements DataSource{
    private LinkedList<Connection> connectionPool = new LinkedList<>();

    public MyDataSource2() {
        for (int i = 0; i < 5; i++) {
            try {
               Connection conn = DriverManager.getConnection("jdbc:mysql:///day20?characterEncoding=utf8", "root", "123");
                connectionPool.add(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    @Override
    public Connection getConnection() throws SQLException {
        //If there is a connection in the container, get it from the container. If there is no connection in the container, directly create a new connection
        Connection conn = null;
        if (connectionPool.size() > 0){
            conn = connectionPool.removeFirst();
        }else {
            conn = DriverManager.getConnection("jdbc:mysql:///day20?characterEncoding=utf8", "root", "123");
        }
        return conn;
    }

C3P0 connection pool

summary

Open source JDBC connection pool. Can automatically recycle idle connections.

requirement

1. Import c3p0-0.9.1.2.jar to lib
2. Import configuration file c3p0-config.xml

configuration information

<c3p0-config>
   <default-config>
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql:///10_ Zhou Yuke_ Curriculum design_ Bookstore? characterEncoding=UTF-8& useUnicode=true</property>
      <property name="user">root</property>
      <property name="password">root</property>
      <property name="initialPoolSize">5</property>
   </default-config>
</c3p0-config>

step

1. Copy jar
2. Copy the configuration file (c3p0-config.xml) to the resources in the src directory [do not change the name]
3. Create a C3P0 connection pool object (c3p0-config.xml in the resources directory will be automatically read, so we don't need to parse the configuration file) DataSource ds = new ComboPooledDataSource();
4. Get the connection from the pool

Tool class

Tools, providing DataSource Object to ensure that there is only one object in the whole project DataSource object
public class C3P0Util {
    private static DataSource dataSource;
    static {
        dataSource = new ComboPooledDataSource();
    }
    public static DataSource getDataSource(){
        return dataSource;
    }
}

DRUID connection pool

summary

The database connection pool developed by Alibaba, known as monitoring, is the best database connection pool in China.

requirement

Import druid-1.0.9.jar
Import configuration file druid.properties

step

1. Import DRUID jar package
2. Copy profile to src Catalog resources in
3. Create from profile Druid Connection pool object
4. from Druid Connection pool object get Connection

realization

1. Create druid.properties, put them in the src directory, and edit the contents

	    url=jdbc:mysql:///day20
	    username=root
	    password=123
	    driverClassName=com.mysql.jdbc.Driver

2. Writing Java code

    Properties properties = new Properties();
    // Associate druid.properties file
    InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
    properties.load(is);
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();

Write a tool class

public class DruidUtil {
	    private static DataSource dataSource;
	    static {
	        try {
	            Properties properties = new Properties();
	            InputStream is = DruidUtil.class.getClassLoader().getResourceAsStream("druid.properties");
	            properties.load(is);
	            //The druid bottom layer uses the factory design pattern to load the configuration file and create the DruidDataSource object
	            dataSource = DruidDataSourceFactory.createDataSource(properties);
	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	    }
	    public static DataSource getDataSource(){
	        return dataSource;
	    }
	}

DBUtils database tool jar package

summary

The open source tool class library that simply encapsulates JDBC can simplify development without affecting the performance of the program

step

1. Copy the commons-dbutils-1.4.jar package
2. Create QueryRunner() object and pass in dataSource
3. Call the update() method

method

1. Create QueryRunner object
The new QueryRunner(DataSource ds) provides connection pools, and the underlying DBUtils automatically maintains connections
2.1. Execute SQL statements for addition, deletion and modification
int update(String sql, Object... params) executes the SQL statement of addition, deletion and modification. The params parameter is a variable parameter. The number of parameters depends on the number of question marks in the statement
2.2. Execute SQL statement of query

       query(String sql, ResultSetHandler<T> rsh, Object... params) among ResultSetHandler Is an interface,Represents the result set processor.
    - BeanHandler()   		Query a record encapsulated in JavaBean object
    - BeanListHandler()     Query multiple records encapsulated into List<JavaBean> list
    - MapHandler()            Query a record encapsulated in Map object
    - MapListHandler()     Query multiple records encapsulated into List<Map> list
    - ScalarHandler()        Encapsulating a single record eg:Statistical quantity

Encapsulated into JavaBean conditions, the column names of the queried data must be consistent with the JavaBean properties

Example

Addition, deletion and modification: String sql = "insert into user values (null,?,?,?)";
        //1. Create QueryRunner object
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());

        //2. Use the QueryRunner object to call update(sql,...) to execute SQL statements of addition, deletion and modification
        queryRunner.update(sql,"aobama","666666","Holy Lance Ranger");

Check multiple items: String sql = "select * from user where id>?";
    QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
    List<Map<String, Object>> mapList = queryRunner.query(sql, new MapListHandler(), 1);
    for (Map<String, Object> map : mapList) {
        System.out.println(map);
    }

Check one line: String sql = "select * from user where id=?";
    QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
    //Execute SQL statement
    U user = queryRunner.query(sql, new BeanHandler<>(U.class), 3);
    System.out.println(user);

Check one: String sql = "select count(*) from user";
    QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
    //You need to use long to receive, because you have a larger pattern
    long count = (long) queryRunner.query(sql,new ScalarHandler());
    System.out.println(count);

The above is the author's notes on the connection pool and DBUtils package. The level is slightly limited, but I hope it can help you, meow~
Reprint please indicate the source, thank you!