JDBC advanced

Use of JDBC connection pool

• The concept of database connection pool

  • Database connection is a key and limited resource. When java program and database get the connection, it consumes resources especially. Therefore, a database connection pool is created to manage the connection between database and program and reuse the connection.

• Third party open source connection pool

  • c3p0 connection pool

    • Profile name must be: c3p0-config.xml Or c3p0-config.properties
    • The configuration file must be below the src file

  • Basic use

  • configuration file

    <c3p0-config>
      <!-- Read connection pool objects using the default configuration -->
      <default-config>
      	<!--  Connection parameters -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost/day05</property>
        <property name="user">root</property>
        <property name="password">root</property>
        
        <!-- Connection pool parameters -->
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">1000</property>
        <property name="checkoutTimeout">3000</property>
      </default-config>
    
      <named-config name="otherc3p0"> 
        <!--  Connection parameters -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/db15</property>
        <property name="user">root</property>
        <property name="password">itheima</property>
        
        <!-- Connection pool parameters -->
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">8</property>
        <property name="checkoutTimeout">1000</property>
      </named-config>
    </c3p0-config>
    

    package com.wx.c3p0;
    import com.mchange.v2.c3p0.ComboPooledDataSource;
    import java.sql.Connection;
    import java.sql.SQLException;
    public class C3P0Test {
        public static void main(String[] args) {
            //Create connection pool object
            ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
            try {
                //Get connected
                Connection connection = comboPooledDataSource.getConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    

  • druid connection pool

    • Is an open source database connection pool software developed by Alibaba

    • Profile needs to be created and loaded manually

    • Profile: properties configuration

    • properties configuration:

      url=jdbc:mysql://localhost:3306/exercise
      username=root
      password=root
      driverClassName=com.mysql.jdbc.Driver
      # Number of initial connections
      initialSize=6
      # maximum connection
      maxActive=10
      #Timeout waiting time
      maxWait=10000
      

    • Use of druid connection pool

      public static void main(String[] args) throws Exception {
              //Load profile
              InputStream config = DruidConnectionPoolTest.class.getResourceAsStream("/druid.properties");
              System.out.println(config);
              Properties properties = new Properties();
              properties.load(config);
              //Create connection object
              DataSource connectionPool = DruidDataSourceFactory.createDataSource(properties);
              System.out.println(connectionPool.getConnection());
          }
      

  • Connection pool tool class

    /**
     * Database connection pool tool class
     */
    public class ConnectionUtils {
        //Privatization construction method
        private ConnectionUtils() {
        }
    
        ;
        //Name data source variable
        private static DataSource dataSource;
        private static Properties properties;
    
        //Provide static code block load profile
        static {
            InputStream resourceAsStream = ConnectionUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            properties = new Properties();
            try {
                properties.load(resourceAsStream);
                dataSource = DruidDataSourceFactory.createDataSource(properties);
            } catch (IOException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        //Provide a method to get database connection object
        public static Connection getConnection() {
            Connection con = null;
            try {
                con = dataSource.getConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return con;
        }
    
        // close resource
        public static void close(Connection con, ResultSet rs, Statement st) {
    
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    

Custom JDBC connection pool

• java officially provides a database connection pool specification (DataSource interface)
• Custom connection pool is to implement the DataSource interface
• Core function getConnection
• Enable connection reuse
• Implementation ideas:
  • Define static code to get a specified number of connections quickly
  • Define collection save connection
  • Implementation of return connection function: (inheritance, agent, decorator design mode, adapter design mode)

Customize JDBC framework

• When using jdbc, most of the codes are the same, but the sql statements are different. You can encapsulate these codes as a tool class, which is a simple jdbc framework
• Source information represents the structural information of data
• Source information of database
  • Get parameter source information: ParameterMetaData
  • Get information about each parameter of the precompiled object
    • Number of parameters to get precompiled object: getParameterCount()
  • Get the source information of the result set
• How to write the update method of the custom framework:
  1. Methods pass sql statements and execute sql parameters
  2. Precompile the passed sql statement
  3. Determine whether the number of parameters required by the sql statement is the same as the number of parameters passed. If not, an error will be reported
  4. If it is the same, replace the placeholder to execute the sql statement and return the impact result
• Encapsulation query method
  • There are many query results for sql statements
    1. Query result is a scalar
    2. The query result is one line
    3. Query result is multiline
  • Define an interface to process the returned result set
  • Strategic model
    • Realize the decoupling of query result and result processing