JDBC database connection pool

1. Concept

• In fact, it is a container (Collection) for storing database connections.

  • After the system is initialized, the container is created, and some database connection objects will be applied in the container. When the user accesses the database, the connection objects will be obtained from the container, and the user will return the connection to the container after accessing the database.

2. Benefits

• save resources
• Efficient user access

2. Realization

• 

2.1 standard interface

• Standard interface: DataSource java.sql package
• method
  • Get connection: getConnection()
  • Return Connection: Connection close(). If the Connection is obtained from the Connection pool, the close () method will not close the Connection. Instead, return the Connection

2.2 C3P0 database connection pool

1. Rely on jar package

• Address connection: Mchange Commons Java,C3P0

2. Define profile

• Name: c3p0 Properties or c3p0 config xml

  <?xml version="1.0" encoding="UTF-8"?>
  
  <c3p0-config>
      <default-config>
          <!-- Connection parameter setting, password and driver -->
          <property name="driverClass">com.mysql.jdbc.Driver</property>
          <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC</property>
          <property name="user">root</property>
          <property name="password">root</property>
  
          <!-- Connection pool parameters -->
          <!-- Initial call application quantity -->
          <property name="initialPoolSize">10</property>
  
  <!--        <property name="maxIdleTime">30</property>-->
          <!-- maximum connection -->
          <property name="maxPoolSize">100</property>
  <!--        <property name="minPoolSize">10</property>-->
          <!--Set timeout-->
          <property name="checkoutTimeout">3000</property>
      </default-config>
  
      <named-config name="mySource">
          <property name="driverClass">com.mysql.jdbc.Driver</property>
          <property name="jdbcUrl">jdbc:mysql://localhost:3306/bookstore</property>
          <property name="user">root</property>
          <property name="password">xxxx</property>
  
          <property name="initialPoolSize">10</property>
          <property name="maxIdleTime">30</property>
          <property name="maxPoolSize">100</property>
          <property name="minPoolSize">10</property>
      </named-config>
  </c3p0-config>
  

• Path: directly under src directory.

3. Create core object and get connection object

• Create object ComboPooledDataSource()
• Get object DS getConnection()

package com.sql.one;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0Demo {
    public static void main(String[] args) throws SQLException {
        //Create database connection pool object
        DataSource ds=new ComboPooledDataSource();
        //Get connection object
        Connection conn=ds.getConnection();
        //3. Print connection
        System.out.println(conn);
    }
}

package com.sql.one;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0Demo {
    public static void main(String[] args) throws SQLException {
        //Create a database connection pool object. The parameter can be passed into the name in the xml configuration file to use other parameters
        DataSource ds=new ComboPooledDataSource();
        //Get connection object
//        Connection conn=ds.getConnection();
        //3. Print connection
        for(int i=1;i<=10;i++){
            Connection conns=ds.getConnection();
            System.out.println(i+":"+conns);
            if(i==5){
                conns.close();//The return connection is not closed
            }
        }
    }
}

2.3 Druid database connection pool

1. Rely on jar package

Address: https://mvnrepository.com/artifact/com.alibaba/druid

2. Define profile

# Drive loading
driverClassName=com.mysql.jdbc.Driver
# Register driver
url=jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC
# user
username=root
# password
password=password123
# The number of physical connections established in the pool during initialization.
initialSize=2
# Maximum number of active connection pools
maxActive=50
# The maximum waiting time unit is milliseconds, 60 * 1000 = 1 minute
maxWait=60000

• You can call any name and put it in any directory

3. Get database connection pool

• Get DruidDataSourceFactory through the factory class

• package com.sql.one;
  
  import com.alibaba.druid.pool.DruidDataSourceFactory;
  
  import javax.sql.DataSource;
  import java.io.InputStream;
  import java.util.Properties;
  import java.sql.Connection;
  
  public class DruidDemo {
      public static void main(String[] args)throws Exception{
          //1. Import jar package
          //2. Load configuration file
          Properties pro =new Properties();
          InputStream is =DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
          pro.load(is);
          //4. Get the connection pool object and use the factory mode
          DataSource ds= DruidDataSourceFactory.createDataSource(pro);
          //5. Get connection
          Connection conn= ds.getConnection();
          System.out.println(conn);
  
      }
  }
  
  

4. Get connection

3. Encapsulation of tools

3.1 general

• Define a class JDBC utils

• Provide static code blocks, load configuration files, and initialize connection pool objects

• Provide method

  • Get connection method: get a connection through the database connection pool
  • Release resources
  • Method to get connection pool

3.2 test use

package com.sql.one;

import com.sql.jdbcutils.JDBCUtils;

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

public class DuridDemo2 {
    public static void main(String[] args) {
        try {
            Connection conn= JDBCUtils.getConnection();
            String sql="insert into t1 values(null,?,?)";
            PreparedStatement pstmt= conn.prepareStatement(sql);
            //To SQL? assignment
            pstmt.setString(1,"logic");
            pstmt.setInt(2,30);
            int count=pstmt.executeUpdate();
            System.out.println(count);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

4.Spring JDBC

4.1 General

Call the method of JdbcTemplate to complete CRUD operation

• update(): execute DML statement. Add, delete and modify sentences.

• queryForMap(): query results, encapsulating the result set as a map set

• queryForList(): query results are encapsulated into a list collection

• query(): the query result is returned as a JavaBean object

• queryForObject: the query result is encapsulated as an object.

4.2 simple use

• Import related jar packages

  

• Simple to use, but only if there is a database connection pool DataSource

• package com.sql.jdbctemplate;
  
  import com.sql.jdbcutils.JDBCUtils;
  import org.springframework.jdbc.core.JdbcTemplate;
  
  public class JDBCTemplate {
      public static void main(String[] args) {
          //1. Import jar package
          //2. Create a JDBC template object
  
          JdbcTemplate template =new JdbcTemplate(JDBCUtils.getDataSource());
          //3. Call method
          String sqlstr="insert into t1 values(null,?,?)";
          int count = template.update(sqlstr, "Shi Qiang", 30);
          System.out.println("insert"+count+"Data entry succeeded");
      }
  }
  
  

  

4.3 simple use of test module

• If @ Test is added to the function name, the modified function can be executed independently without relying on the main method.

  

4.4 introduction to common methods

1. update() method

• Returns the number of entries inserted into the database.

package com.sql.jdbctemplate;

import com.sql.jdbcutils.JDBCUtils;
import org.testng.annotations.Test;
import org.springframework.jdbc.core.JdbcTemplate;

public class JdbcTemplateDemo2 {
    //Instantiate JdbcTemplate
    JdbcTemplate template= new JdbcTemplate(JDBCUtils.getDataSource());
    //Junit unit Test allows methods to be executed independently, using @ Test annotation,
    @Test
    public void test1(){
        String sqlstr="insert into t1 values(null,?,?)";
        int count = template.update(sqlstr, "AI AA", 30);
        System.out.println("insert"+count+"Data entry succeeded");
        //System.out.println("I was executed");
    }
}

2. queryForMap() method

• Returns the map collection, which can only have one result.

• 	@Test
      public void test2(){
          String sqlstr="select * from t1 where id=1";
          Map<String, Object> map = template.queryForMap(sqlstr);
          System.out.println(map);
      }
  
  	//{id=1, username = Dongfang, age=21}
  

• 

• Two returned results will report an error. Because the key of map is unique.

  

• An error will also be reported if there is no returned result.

  

3. Queryforlist() method

• The query results are encapsulated into a list set, in which each record is encapsulated in a map set and in a list.

• 	@Test
      public void test3(){
          String sqlstr="select * from t1";
          List<Map<String, Object>> maps = template.queryForList(sqlstr);
  
          for (Map<String, Object> map : maps) {
              System.out.println(map);
          }
      }
  

  

4. query() method

• This method is the most commonly used.

  • new BeanPropertyRowMapper<class>(class.class)
    

• Returns the object encapsulated by the JavaBean.

• First, create the corresponding class

  package com.sql.jdbctemplate;
  
  public class Role {
      private Integer id;
      private String username;
      private Integer age;//Use the referenced data type to prevent null data in the database
  
      public Role(Integer id, String username, Integer age) {
          this.id = id;
          this.username = username;
          this.age = age;
      }
  
      public Role() {
      }
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      public String getUsername() {
          return username;
      }
  
      public void setUsername(String username) {
          this.username = username;
      }
  
      public Integer getAge() {
          return age;
      }
  
      public void setAge(Integer age) {
          this.age = age;
      }
  
      @Override
      public String toString() {
          return "Role{" +
                  "id=" + id +
                  ", username='" + username + '\'' +
                  ", age=" + age +
                  '}';
      }
  }
  
  

• The first method (cumbersome and not recommended).

  

• The second method

      @Test
      public void test4(){
          String sqlstr="select * from t1";
          //Use generics and reflection to load the corresponding JavaBean into memory for use
          List<Role> list= template.query(sqlstr,new BeanPropertyRowMapper<Role>(Role.class));
          for(Role r : list){
              System.out.println(r);
          }
      }
  

  

• Note: if the field does not match the field in the bean, you can alias it in the SQL statement.

5. queryForObject() method

• It is generally used to execute sql statements of aggregate functions, such as count, sum and AVG

•     @Test
      public void test5(){
          String sqlstr="select count(1) from t1";
          //In addition to SQL statements, you also need to pass in the type of return value, which is received with the specified type
          Long count=template.queryForObject(sqlstr,Long.class);
          System.out.println(count);
      }
  

  

5. Supplement of shortcut keys

• Return value: ctrl+Alt+V in idea quickly generates a return value.
• For loop: iter enhances for loop and fori ordinary for loop.

Continue to work hard and finally become a great thing!