What is a connection pool

• No connection pool: each time a user creates a database connection object, then destroys the object

• Database connection pool:

  • Pools: objects that hold data

  • Connection pool: a container that holds database connection objects

  • effect:

    • With multiple database objects, you can get them when you use them
    • After use, the object is not released directly, but put into the pool for next use

    Advantages of pool technology: eliminate the delay caused by the creation of library objects, so as to improve the performance of the system

• Attributes in data connection pool:

  • 1. Four elements required for data connection: driver name, database address, user name and password
  • 2. Number of initialized connections
  • 3. Maximum number of connections
  • 4. Minimum number of connections
  • 5. Maximum idle time: if the connection object is obtained and there is no operation within the specified time, the connection will be automatically released
  • 6. Maximum waiting time: try to obtain the connection within the specified time. If it exceeds the specified time, you will be prompted that the acquisition fails

Use of connection pool

• Introduction:

  The connection pool uses Java sql. Datasource interface to represent the connection pool

  Like jdbc, DataSource only provides an interface, which is provided by a third-party organization

• Common connection pool;

  • DBCP: recommended by Spring
  • Druid: provided by Alibaba (recommended)

• Difference between using connection pool and not using connection pool

  • Different acquisition methods
    • Traditional: connection conn = drivermanner getConnection(url, user,password);
    • Connection pool; Connection conn = DataSource object getConnection();
  • Different resources released
    • Traditional: disconnect from data conn.close();
    • Connection pool: return the database object to the connection pool for the next person to use

• Connection pool operation:

  • Mainly learn to create a DataSource object, and then obtain the Connection object from the DataSource object
  • They are all provided by a third party and can be called directly
  • After connection, the operation is the same as before
  • Different database connection pools are created in different DataSource

Create DataSource

• Import jar package

  • For DBCP, you need to download commons-dbcp-1.4 Jar and commons-pool-1.6 Jar, how to download the jar package: https://blog.csdn.net/be_your1/article/details/88110883
  • For Druid, go to GitHub's official website to download: https://github.com/alibaba/druid/

  After downloading, import it into the lib folder of the project, and import mysql-connector-java-8.0.25 Jar operation is the same

• Get connection object

  Direct code:

  package com.zhj.jdbc.test;
  
  import com.alibaba.druid.pool.DruidDataSource;
  import com.alibaba.druid.pool.DruidPooledConnection;
  import org.apache.commons.dbcp.BasicDataSource;
  
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  
  public class DBCPTest {
      public static void main(String[] args) {
          String driverName = "com.mysql.jdbc.Driver";
          String url = "jdbc:mysql://localhost:3306/stus?rewriteBatchedStatements=true";
          String user = "root";
          String password = "034312";
          DruidDataSource ds = new DruidDataSource();
  //        BasicDataSource ds = new BasicDataSource();
          ds.setDriverClassName(driverName);
          ds.setUrl(url);
          ds.setUsername(user);
          ds.setPassword(password);
          try {
              // To establish a connection, the following is a general operation, except that it is different to obtain the conn object
             Connection conn = ds.getConnection();
              // Create statement
              String sql = "select * from student";
              // Execute statement
              PreparedStatement ps = conn.prepareStatement(sql);
              ResultSet res = ps.executeQuery();
              if(res.next()){
                  System.out.println(res.getString(2));
                  System.out.println(res.getInt(3));
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }
        // Release resources
      }
  }
  

configuration file

• What is a profile?

  Resource file to properties file with extension

• How to use it?

  Create properties

  Stored as key = value

• A source folder is usually created in the project directory properties file

Read the configuration file and get the conn object

• Create profile dB properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/stus?rewriteBatchedStatements=true
username=root
password=034312

Note: the attribute name (key) here is written in a standard way. Using another name may report an error

• read file

@Test
    public void getConnectionTest(){
        Properties p = new Properties();
        FileInputStream fis = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet res = null;
        try {
            fis = new FileInputStream("source/db.properties");
            p.load(fis);
            //System.out.println(p.get("driver"));
            //System.out.println(p.get("url"));
            //System.out.println(p.get("username"));
            //System.out.println(p.get("password"));
            DataSource ds = DruidDataSourceFactory.createDataSource(p);
//            //Establish connection
            conn = ds.getConnection();
            // Create statement
            String sql = "select * from student";
            // Execute statement
            ps = conn.prepareStatement(sql);
            res = ps.executeQuery();
            if (res.next()) {
                System.out.println(res.getString(2));
                System.out.println(res.getInt(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            // Release resources
            try {
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }