Explain

See the article of the head of the official account, and refer to a simple database connection pool.

text

ConnPool connection pool class must implement javax.sql.DataSource interface

package com.datasource.connpool;

import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.Properties;
import java.util.logging.Logger;

import javax.sql.DataSource;

/**
 * Simple realization of database connection pool
 * Adopt agent mode
 * @author wds
 *
 */
public class ConnPool implements DataSource {

    //Use LinkedList collection to store database connection
    private static LinkedList<Connection> connPool = new LinkedList<Connection>();

    //Load configuration file in static code block
    static{
        InputStream in = ConnPool.class.getClassLoader().getResourceAsStream("db.properties");
        Properties prop = new Properties();
        try {
            prop.load(in);
            String driver = prop.getProperty("driver");
            String url = prop.getProperty("url");
            String user = prop.getProperty("user");
            String password = prop.getProperty("password");
            //Size of the number of initial connections for the database connection pool
            int  InitSize = Integer.parseInt(prop.getProperty("InitSize"));
            //Load driver
            Class.forName(driver);
            for(int i = 0; i < InitSize; i++){
                Connection conn = DriverManager.getConnection(url, user, password);
                //Add the created connection to the list
                System.out.println("Initialize the database connection pool, and create the " + (i + 1) +" Connections, adding to pool");
                connPool.add(conn);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * Get database connection
     */
    public Connection getConnection() throws SQLException {
        if(connPool.size() > 0){
            //Get a connection from the collection
            final Connection conn = connPool.removeFirst();
            //Returns the proxy object for the Connection
            return (Connection) Proxy.newProxyInstance(ConnPool.class.getClassLoader(), conn.getClass().getInterfaces(), new InvocationHandler() {
                public Object invoke(Object proxy, Method method, Object[] args)
                        throws Throwable {
                    if(!"close".equals(method.getName())){
                        return method.invoke(conn, args);
                    }else{
                        connPool.add(conn);
                        System.out.println("Close the connection, and actually return the connection pool");
                        System.out.println("The number of connections in the pool is " + connPool.size());
                        return null;
                    }
                }
            });
        }else{
            throw new RuntimeException("Database busy, try again later");
        }
    }

    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    public void setLogWriter(PrintWriter out) throws SQLException {

    }

    public void setLoginTimeout(int seconds) throws SQLException {

    }

    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }

    public Object unwrap(Class iface) throws SQLException {
        return null;
    }

    public boolean isWrapperFor(Class iface) throws SQLException {
        return false;
    }

    public Connection getConnection(String username, String password)
            throws SQLException {
        return null;
    }


}

The JdbcUtil utility class obtains the connection from the connection pool through this class

package com.datasource.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.datasource.connpool.ConnPool;

/**
 * Get connected tool class
 * @author wds
 *
 */
public class JdbcUtil {

    //Database connection pool
    private static ConnPool  connPool = new ConnPool();

    /**
     * Get a connection from the pool
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException{
        return connPool.getConnection();
    }

    /**
     * Close connection
     * @param conn
     * @param st
     * @param rs
     * @throws SQLException 
     */
    public static void CloseConnection(Connection conn, Statement st, ResultSet rs) throws SQLException{

        // Close the ResultSet object that stores the query results
        if(rs != null){
                rs.close();
        }

        //Close Statement object
        if(st != null){
                st.close();
        }

        //Close connection
        if(conn != null){
                conn.close();
        }
    }

}

PoolTest test test class

package com.datasource.test;

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

import com.datasource.util.JdbcUtil;

public class PoolTest {

    /**
     * Test database connection pool
     * @param args
     */
    @SuppressWarnings("all")
    public static void main(String[] args) {
        JdbcUtil util = new JdbcUtil();
        try {
            Connection conn = util.getConnection();
            if(conn != null){
                System.out.println("I got a connection");
            }
            util.CloseConnection(conn, null, null);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

test result

During the test, a problem was found. When the database connection driver version is too low, an exception will occur

By looking for solutions, it is found that the problem is due to the driver version. The online solution is to change conn.getClass().getInterfaces() to new Class[] {Connection.class}. Here, I change the driver version and use the higher version to succeed