JDBC Learning 03-Custom connection pooling

First, connection pooling overview
In actual development, "get Connections" and "Release resources" is a very consuming system resources process, in order to solve this kind of performance problem, the connection pooling technology is usually used to share connection.
using pools to manage connection can reuse connection. Instead of creating the connection object yourself, you can get it through the pool, and calling the close () method after use is not to turn off the connection but to return connection to the pool.
Java provides a common interface for database connection pooling: Javax.sql.DataSource, where each vendor has its own connection pool to implement this interface.
Two, custom connection pooling
steps:
1. Create a connection pool implementation (data source) and implement Interface Javax.sql.DataSource.
2. Provides a collection for storing connections. Because there are more removal additions, it is appropriate to use a linked list structure.
3. Initializes a connection for the connection pool.
4. The getconnection () method that invokes the implementation class gets the connection. To ensure that the current connection is used by a thread, you need to remove the connection from the connection pool first.
5. Add the connection to the connection pool again when you are finished using the current connection.
Three, custom connection pooling code implementation
1. Simple custom connection pool
Temporarily use custom method backconnection return connection

Import JDBC.utils.JDBCUtils_V3;
Import Javax.sql.DataSource;
Import Java.io.PrintWriter;
Import java.sql.Connection;
Import java.sql.SQLException;
Import java.sql.SQLFeatureNotSupportedException;
Import java.util.LinkedList;

Import Java.util.logging.Logger;     public class myDataSource implements DATASOURCE{//1. Implements the DataSource interface private static int init_count = 3; Initialize the number of connections//2. Creates a container for storing Connection objects private static linkedlist<connection> pool = new Linkedlist<connec

    Tion> (); 3. Initialize connection pool static{for (int i=0;i<init_count;i++) {Connection conn = jdbcutils_v3.getconnection (
            );
        POOL.ADD (conn); /** * Get Connection method * @return Connection conn * @throws SQLException * * @Override Publ
        IC Connection getconnection () throws SQLException {Connection = null; Determine if the connection exists in the pool before use if (Pool.size () ==0) {//Jochi is empty, and then create some connection for (int i=0;i<init_count;i++){conn = jdbcutils_v3.getconnection ();
            POOL.ADD (conn);
        //4. Get Connection object Conn=pool.remove (0);
    Return conn;
    }//Return connection method (temporary) public void backconnection (Connection conn) {POOL.ADD (conn); @Override Public Connection getconnection (string Username, string password) throws SQLException {retur
    n null;
    @Override public <T> T Unwrap (class<t> iface) throws SQLException {return null;
    @Override public boolean iswrapperfor (Class<?> iface) throws SQLException {return false;
    @Override public PrintWriter Getlogwriter () throws SQLException {return null;  @Override public void Setlogwriter (PrintWriter out) throws SQLException {} @Override public void setLoginTimeout (int seconds) throws SQLException {} @Override public int getlogintimeout () throws tion {return 0;
    @Override public Logger Getparentlogger () throws sqlfeaturenotsupportedexception {return null;
 }
}

JUnit test Code:

Import JDBC.
Datasource.mydatasource;

Import Org.junit.Test;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;

Import java.sql.SQLException;
        public class Testmydatasource {@Test public void Testselect () {Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        1. Create a custom Connection pool object myDataSource DataSource = new myDataSource ();
            try {//2. Get object from Pool conn = Datasource.getconnection ();
            3. Write SQL statement String sql= "select * from city where id=?";
            4. Get the object that executes the SQL statement pstmt = conn.preparestatement (sql);
            5. Set parameter Pstmt.setint (1,4060);
            6. Implementation rs = Pstmt.executequery ();
            7. Process result set while (Rs.next ()) {System.out.println (rs.getstring ("Name"));
        } catch (SQLException e) {e.printstacktrace (); } finally{datasource.backconnection (conn); }
    }
}

Test results:

2. The enhanced Close method
Custom connection pool currently has a problem: if the Conn.close () method is invoked, the connection is actually freed instead of being returned to the pool, and the Close () method is enhanced. The way the
method is enhanced:
1. Inheritance
subclasses inherit the parent class, overriding the parent class's methods to enhance the method. The requirement must have a parent class and there is an inheritance relationship. The
2. Decorator design pattern
requires that an interface be required.
3. Dynamic proxies
Similar to adorners, dynamic creation of proxy classes at run time completes the enhancement operation. The
4. Bytecode Enhancement
Creates a target subclass to be enhanced at run time, using a third-party framework.
Decorator design pattern
Invariant structure:
Interface A, known implementation Class C, requiring adorners to create a proxy class B
Step:
1. Create Class B and Implement interface a
2. Provides a constructor for Class B, with a parameter type of a, Other implementation class (C)
3, which is used to receive a. Add a member variable of type A to Class B to hold the other implementation class of a (C)
4. Enhance the methods that are required
5. Implement methods that do not require enhancements
Code implementation:
1. Decorator proxy class main code

Import java.sql.*;
Import java.util.LinkedList;
Import Java.util.Map;
Import java.util.Properties;
Import Java.util.concurrent.Executor;

1. Implement the same interface Connection public
class MyConnection implements Connection {

    private linkedlist<connection> Pool;
    3. Add a member variable of type A, which is used to store the other implementation classes of a
    private Connection conn;

    2. Provides a construction method that contains the parameters of type interface A, which is used to receive the other implementation classes of a C public
    myconnection (Connection conn, linkedlist<connection> Pool) {
        ////This.conn=conn the received implementation class object to multiply the variable
        ;
        This.pool=pool;
    }
    4. Override the methods that need to be enhanced
    @Override public
    Void Close () throws SQLException {
        pool.add (conn);
    }
    5. Implement methods that do not require enhancements
    @Override public
    preparedstatement preparestatement (String sql) throws SQLException {
        return conn.preparestatement (SQL);
    

2. Custom connection Pool main code using the adorner class

Import JDBC.utils.JDBCUtils_V3;
Import Javax.sql.DataSource;
Import Java.io.PrintWriter;
Import java.sql.Connection;
Import java.sql.SQLException;
Import java.sql.SQLFeatureNotSupportedException;
Import java.util.LinkedList;

Import Java.util.logging.Logger;     public class MYDATASOURCE02 implements DATASOURCE{//1. Implements the DataSource interface private static int init_count = 3; Initialize the number of connections//2. Creates a container for storing Connection objects private static linkedlist<connection> pool = new Linkedlist<connec

    Tion> (); 3. Initialize connection pool static{for (int i=0;i<init_count;i++) {Connection conn = jdbcutils_v3.getconnection (
            );
            MyConnection myconnection = new MyConnection (Conn,pool);
        The Pool.add (myconnection) is put into the pool by the modified connection object through the Decorator class; /** * Get Connection method * @return Connection conn * @throws SQLException * * @Override Publ
        IC Connection getconnection () throws SQLException {Connection = null; //Before use, determine if a connection exists in the pool if (pool.size () ==0) {//Jochi is empty, and then create some connection for (int i=0;i<init_count;i++) {
                conn = Jdbcutils_v3.getconnection ();
                MyConnection myconnection = new MyConnection (Conn,pool);
            Pool.add (MyConnection);
        //4. Get Connection object Conn=pool.remove (0);
    Return conn;
 }

3. Test code

  @Test public void testSelect02 () {Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        1. Create a custom Connection pool object DataSource DataSource = new MyDataSource02 ();
            try {//2. Get the object from the pool (after transformation) conn = Datasource.getconnection ();
            3. Write SQL statement String sql= "select * from city where id=?";
            4. Get the object that executes the SQL statement pstmt = conn.preparestatement (sql);
            5. Set parameter Pstmt.setint (1,4060);
            6. Implementation rs = Pstmt.executequery ();
            7. Process result set while (Rs.next ()) {System.out.println (rs.getstring ("Name"));
        } catch (SQLException e) {e.printstacktrace ();
                finally {if (rs!=null) {try {rs.close ();
                catch (SQLException e) {e.printstacktrace ();
           }
            } if (pstmt!=null) {try {pstmt.close ();
                catch (SQLException e) {e.printstacktrace ();
                } if (Conn!=null) {try {conn.close ();
                catch (SQLException e) {e.printstacktrace (); }
            }
        }
    }

The result is normal, the close () method after the override of the close () method call.