Database connection pool

1. The concept

  • Essentially, it holds database connection objectsConnectionThe container
  • When the application starts, the container is created and applies for connection objectsConnectionWhen the user accesses the database, the connection object is obtained from the container. After user access, the connection object is returned to the container to reuse the connection object and improve efficiency.

2. Implement

  • The database vendor implements the interfaceDataSource
    1. C3P0: Database connection pooling technology
    2. Druid: Database connection pooling technology

3. Get/return the connection

  1. Get a connection:dataSource.getConnection()
  2. Return connection:connection.close()

4. Use of C3P0

  1. Import the jar package
    • C3p0-0.9.5.2. Jar.McHange - Commons - Java - 0.2.12. JarAnd database driver JAR packageMysql connector - Java - 5.1.37 - bin. The jar
  2. Define configuration files related to database connection pools (details omitted)
    • Properties or c3P0-config.xml
    • Path: In the SRC directory
  3. Create a database connection pool object
    • DataSource ds = new ComboPooledDataSource();
  4. Get the connection object
    • Connection conn = ds.getConnection();

5. Druid

  1. steps
    1. Import the jar package
      • Druid - 1.0.9. Jar
    2. Defines a configuration file of type properties that can be placed in any directory
    3. Loading a Configuration File
    4. Get the database connection pool object from the factory
    5. Get the connection object
  2. code 
    try {
    Properties pro = new Properties();
    InputStream is = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
    if(is ! =null) {
        pro.load(is);
    }
    ds = DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
    e.printStackTrace();
}
Copy the code

2: JDBCTemplate

  1. Import five related JAR packages
  2. Create the JdbcTemplate object. Depends on the DataSource
    • JdbcTemplate template = new JdbcTemplate(ds);
  3. Call the JdbcTemplate method to complete CRUD operations
    • update(): Executes the DML statement
    • queryForMap(): Encapsulates the query result set into a map set, with column names as keys and values as values
      • Note: The result set length of this method query can only be 1
    • queryForList(): Encapsulates the query result set as a list
      • Note:List<Map<String, Object>>: Encapsulates each record into a Map collection and loads the Map collection into the List collection
    • query(): Encapsulates query results as JavaBean objects
      • Parameter: SQL statement,New BeanPropertyRowMapper< type >(type.class)
      • Using the BeanPropertyRowMapper implementation class, you can complete automatic encapsulation of data into A JavaBean
      • Query all records and encapsulate them as a List collection of Emp objects. Example code: 
        String sql = "select * from emp";
List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
for (Emp emp : list) {
    System.out.println(emp);
}
Copy the code
    • queryForObject(): Encapsulates the query result as an object
      • Parameter: SQL statement,Object type. Class
      • Generally used for aggregate function queries