In Java engineering projects, we often use the Mybatis framework to add, delete, query and modify the data in the database. Its principle is to encapsulate JDBC and optimize the connection of data sources.

Let's review the process of JDBC operating database.

When JDBC operates the database, you need to specify the connection type, load the driver, establish the connection, and finally execute the SQL statement. The code is as follows:

public static final String url = "jdbc:mysql://127.0.0.1/somedb";  
    public static final String name = "com.mysql.jdbc.Driver";  
    public static final String user = "root";  
    public static final String password = "root";  
  
    public Connection conn = null;  
    public PreparedStatement pst = null;  
  
    public DBHelper(String sql) {  
        try {  
          	//Specify connection type
            Class.forName(name);
          	//Establish connection 
            conn = DriverManager.getConnection(url, user, password); 
          	//Prepare to execute statement  
            pst = conn.prepareStatement(sql);
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  
  
    public void close() {  
        try {  
            this.conn.close();  
            this.pst.close();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  

If you use JDBC to process the execution of an SQL, you need to go through a process of loading drive, establishing connection, and executing SQL again. When the next SQL comes, you need to carry out this process again, which will cause unnecessary performance loss. Moreover, with the gradual increase of user operations, it is also a process of establishing connection with the database every time for the database itself Pressure.

In order to reduce this unnecessary consumption, data operations can be split. In Mybatis, the establishment and management of database connection is called database connection pool.

• UNPOOLED does not use the connection pool's data source
• POOLED uses the data source of the connection pool
• Data implemented by JNDI using JNDI

• UNPOOLED

  UNPOOLED does not use the data source of the connection pool. When the type attribute of dateSource is configured as UNPOOLED, MyBatis first instantiates an UnpooledDataSourceFactory factory instance, and then returns an UnpooledDataSource instance object reference through the. getDataSource() method. We assume it is dataSource.

  Use getconnection () of UnPooledDataSource to generate a new Connection instance object every time it is called. The getConnection() method of UnPooledDataSource is implemented as follows:

public class UnpooledDataSource implements DataSource {
    private ClassLoader driverClassLoader;
    private Properties driverProperties;
    private static Map<String, Driver> registeredDrivers = new ConcurrentHashMap();
    private String driver;
    private String url;
    private String username;
    private String password;
    private Boolean autoCommit;
    private Integer defaultTransactionIsolationLevel;

    public UnpooledDataSource() {
    }

    public UnpooledDataSource(String driver, String url, String username, String password){
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.password = password;
    }
    
    public Connection getConnection() throws SQLException {
        return this.doGetConnection(this.username, this.password);
    }
    
    private Connection doGetConnection(String username, String password) throws SQLException {
        Properties props = new Properties();
        if(this.driverProperties != null) {
            props.putAll(this.driverProperties);
        }

        if(username != null) {
            props.setProperty("user", username);
        }

        if(password != null) {
            props.setProperty("password", password);
        }

        return this.doGetConnection(props);
    }
    
    private Connection doGetConnection(Properties properties) throws SQLException {
        this.initializeDriver();
        Connection connection = DriverManager.getConnection(this.url, properties);
        this.configureConnection(connection);
        return connection;
    }
}

As shown in the above code, UnpooledDataSource does the following:

1. Initialize driver: determine whether the driver driver has been loaded into memory. If it has not been loaded, the driver class will be loaded dynamically, and a driver object will be instantiated. Use the DriverManager.registerDriver() method to register it in memory for subsequent use.

2. Create Connection object: use the DriverManager.getConnection() method to create a Connection.

3. Configure Connection object: sets whether autoCommit and isolation level isolationLevel are automatically submitted.

4. Return Connection object

• ###POOLED data source connection pool

PooledDataSource: wrap the java.sql.Connection object into a pooldconnection object and put it into a container of PoolState type for maintenance. MyBatis divides PooledConnection in connection pool into two states: idle state and active state. PooledConnection objects in these two states are stored in * * idleConnections and active connections * * two List sets in PoolState container respectively:

idleConnections: the PooledConnection object in idle state is placed in this collection, indicating the currently idle PooledConnection collection that is not in use. When the getConnection() method of PooledDataSource is called, the PooledConnection object will take precedence from this collection. When you run out of a java.sql.Connection object, MyBatis wraps it up as a PooledConnection object and places it in this collection.

activeConnections: the PooledConnection object in the active state is placed in the ArrayList named activeConnections, which indicates the PooledConnection collection currently in use. When calling the getConnection() method of PooledDataSource, the PooledConnection object will be taken from the idleConnections collection in priority. If not, it depends on whether the collection is full. If not, Po Oleddatasource creates a PooledConnection, adds it to the collection, and returns

Now let's see what the popConnection() method does:

1. First, check whether there is a PooledConnection object in idle state. If there is one, directly return an available PooledConnection object; otherwise, go to step 2.

2. Check whether the active PooledConnection pool activeConnections is full; if not, create a new PooledConnection object, place it in the active connections pool, and then return the PooledConnection object; otherwise, proceed to step 3;

3. See whether the PooledConnection object that first enters the active connections pool has expired: if it has expired, remove the object from the active connections pool, create a new PooledConnection object, add it to the active connections pool, and return the object; otherwise, step 4.

4. Thread waiting, loop 2 steps

/*
 * Pass a user name and password to return the available PooledConnection from the connection pool
 */
private PooledConnection popConnection(String username, String password) throws SQLException
{
    boolean countedWait = false;
    PooledConnection conn = null;
    long t = System.currentTimeMillis();
    int localBadConnectionCount = 0;

    while (conn == null)
    {
        synchronized (state)
        {
            if (state.idleConnections.size() > 0)
            {
                // There are free connections in the connection pool, take out the first one
                conn = state.idleConnections.remove(0);
                if (log.isDebugEnabled())
                {
                    log.debug("Checked out connection " + conn.getRealHashCode() + " from pool.");
                }
            }
            else
            {
                // If there are no idle connections in the connection pool, the number of connections currently in use is less than the maximum limit,
                if (state.activeConnections.size() < poolMaximumActiveConnections)
                {
                    // Create a new connection object
                    conn = new PooledConnection(dataSource.getConnection(), this);
                    @SuppressWarnings("unused")
                    //used in logging, if enabled
                    Connection realConn = conn.getRealConnection();
                    if (log.isDebugEnabled())
                    {
                        log.debug("Created connection " + conn.getRealHashCode() + ".");
                    }
                }
                else
                {
                    // Cannot create new connection when the active connection pool is full and cannot be created, the first PooledConnection object in the active connection pool is taken, that is, the PooledConnection object that first enters the connection pool
                    // Calculate its verification time. If the verification time is greater than the maximum verification time specified by the connection pool, it is considered to have expired. Use the realConnection inside the PoolConnection to regenerate a PooledConnection
                    //
                    PooledConnection oldestActiveConnection = state.activeConnections.get(0);
                    long longestCheckoutTime = oldestActiveConnection.getCheckoutTime();
                    if (longestCheckoutTime > poolMaximumCheckoutTime)
                    {
                        // Can claim overdue connection
                        state.claimedOverdueConnectionCount++;
                        state.accumulatedCheckoutTimeOfOverdueConnections += longestCheckoutTime;
                        state.accumulatedCheckoutTime += longestCheckoutTime;
                        state.activeConnections.remove(oldestActiveConnection);
                        if (!oldestActiveConnection.getRealConnection().getAutoCommit())
                        {
                            oldestActiveConnection.getRealConnection().rollback();
                        }
                        conn = new PooledConnection(oldestActiveConnection.getRealConnection(), this);
                        oldestActiveConnection.invalidate();
                        if (log.isDebugEnabled())
                        {
                            log.debug("Claimed overdue connection " + conn.getRealHashCode() + ".");
                        }
                    }
                    else
                    {

                        //If it cannot be released, it must wait for
                        // Must wait
                        try
                        {
                            if (!countedWait)
                            {
                                state.hadToWaitCount++;
                                countedWait = true;
                            }
                            if (log.isDebugEnabled())
                            {
                                log.debug("Waiting as long as " + poolTimeToWait + " milliseconds for connection.");
                            }
                            long wt = System.currentTimeMillis();
                            state.wait(poolTimeToWait);
                            state.accumulatedWaitTime += System.currentTimeMillis() - wt;
                        }
                        catch (InterruptedException e)
                        {
                            break;
                        }
                    }
                }
            }

            //If the PooledConnection is obtained successfully, update its information

            if (conn != null)
            {
                if (conn.isValid())
                {
                    if (!conn.getRealConnection().getAutoCommit())
                    {
                        conn.getRealConnection().rollback();
                    }
                    conn.setConnectionTypeCode(assembleConnectionTypeCode(dataSource.getUrl(), username, password));
                    conn.setCheckoutTimestamp(System.currentTimeMillis());
                    conn.setLastUsedTimestamp(System.currentTimeMillis());
                    state.activeConnections.add(conn);
                    state.requestCount++;
                    state.accumulatedRequestTime += System.currentTimeMillis() - t;
                }
                else
                {
                    if (log.isDebugEnabled())
                    {
                        log.debug("A bad connection (" + conn.getRealHashCode() + ") was returned from the pool, getting another connection.");
                    }
                    state.badConnectionCount++;
                    localBadConnectionCount++;
                    conn = null;
                    if (localBadConnectionCount > (poolMaximumIdleConnections + 3))
                    {
                        if (log.isDebugEnabled())
                        {
                            log.debug("PooledDataSource: Could not get a good connection to the database.");
                        }
                        throw new SQLException("PooledDataSource: Could not get a good connection to the database.");
                    }
                }
            }
        }

    }

    if (conn == null)
    {
        if (log.isDebugEnabled())
        {
            log.debug("PooledDataSource: Unknown severe error condition.  The connection pool returned a null connection.");
        }
        throw new SQLException("PooledDataSource: Unknown severe error condition.  The connection pool returned a null connection.");
    }

    return conn;
}

When we use the connection object in our program, if we do not use the database connection pool, we will generally call the connection.close() method to close the connection connection and release resources

All resources held by the Connection object that has called the close() method will be released, and the Connection object can no longer be used. So, if we use the Connection pool, when we run out of Connection objects, we need to put it in the Connection pool. What should we do?

Maybe the first idea flashed in your mind is: when I should call the con.close() method, I will not call the close() method, instead, I will put the Connection object into the Connection pool container!

How to implement the Connection object calls the close() method, but actually adds it to the Connection pool

This is to use the proxy mode to create a proxy object for the real Connection object. All methods of the proxy object call the corresponding method implementation of the real Connection object. When the proxy object executes the close() method, it needs special handling. Instead of calling the close() method of the real Connection object, it adds the Connection object to the Connection pool.

The PoolState of pooledatasource of MyBatis internally maintains the object of pooldconnection type, while pooldconnection is the wrapper for the real database connection java.sql.Connection instance object.

Within the PooledConnection object, a real database connection instance object and a java.sql.Connection agent are held:

Its source code is as follows:

class PooledConnection implements InvocationHandler {
    private static final String CLOSE = "close";
    private static final Class<?>[] IFACES = new Class[]{Connection.class};
    private int hashCode = 0;
    private PooledDataSource dataSource;
    private Connection realConnection;
    private Connection proxyConnection;
    private long checkoutTimestamp;
    private long createdTimestamp;
    private long lastUsedTimestamp;
    private int connectionTypeCode;
    private boolean valid;

    public PooledConnection(Connection connection, PooledDataSource dataSource) {
        this.hashCode = connection.hashCode();
        this.realConnection = connection;
        this.dataSource = dataSource;
        this.createdTimestamp = System.currentTimeMillis();
        this.lastUsedTimestamp = System.currentTimeMillis();
        this.valid = true;
        this.proxyConnection = (Connection)Proxy.newProxyInstance(Connection.class.getClassLoader(), IFACES, this);
    }
    
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        String methodName = method.getName();
      	//When close, the connection will be recycled, not the real close
        if("close".hashCode() == methodName.hashCode() && "close".equals(methodName)) {
            this.dataSource.pushConnection(this);
            return null;
        } else {
            try {
                if(!Object.class.equals(method.getDeclaringClass())) {
                    this.checkConnection();
                }

                return method.invoke(this.realConnection, args);
            } catch (Throwable var6) {
                throw ExceptionUtil.unwrapThrowable(var6);
            }
        }
    }
}