简易数据库（JDBC等）连接池的实现

连接池基本功能：
1. 初始化n个指定的数据库链接，供后续并发调用。
2. 用完某个链接后，归还这个链接给pool，后续重用
3. 若池子当前没有可用的空闲链接，就往池子里扩容固定数量的新链接
4. 线程安全（同一时刻只能由一个线程来完成 获取链接并更新pool状态的操作 ）

我的应用场景是在spark分布式mapPartition写ES的时候产生的，因为在每个mapPartition中临时new出链接，or 用类似单例（静态化一组固定数量的链接）的方式并不优雅和高效，改用连接池的方式实现，在mapPartition中调用连接池的get接口获取空闲的链接，代码如下（用通用的Connection代替ES来说明吧）

package sdk;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Random;
import java.sql.Connection;

public class ConnectorPool implements Runnable{
    public String tableName = "";
    public String fieldMetaStr = "";
    private static int es_num = 12;//池中链接的数量
    //
    private static Map<String,ArrayList<Connection>> esw_free_map = new HashMap<>();//空闲的链接List
    private static Map<String,ArrayList<Connection>> esw_using_map = new HashMap<>();//使用中的链接List
    // 初始化 or 用完pool时新增
    private static void iniESList(String tableName, String fieldMetaStr){
        ArrayList<Connection> esmap = new ArrayList<>();
        //
        if(!esw_free_map.containsKey(tableName + fieldMetaStr)){
            esw_free_map.put(tableName+fieldMetaStr, new ArrayList<Connection>());
        }
        if(!esw_using_map.containsKey(tableName + fieldMetaStr)){
            esw_using_map.put(tableName+fieldMetaStr, new ArrayList<Connection>());
        }
        esmap = esw_free_map.get(tableName+fieldMetaStr);
        //
        if(esmap == null || esmap.size()==0){
            int n = es_num;
            for(int i=0;i<n;){
                Connection indexUtilNew = null; 
                try {
                    indexUtilNew = TestESmysql.getNewConnect();// 初始化一个你的Connection
                    Thread.sleep(100);
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                if(indexUtilNew!=null){
                    esmap.add(indexUtilNew);
                    i++;
                }
            }
            System.out.println("get new  ES writer pool.");
        }
    }
    // 标记连接为使用中
    private synchronized static void setESstatus2using(String tableName_in, String fieldMetaStr_in,
            Connection indexUtil,int es_index){
        esw_using_map.get(tableName_in + fieldMetaStr_in).add(indexUtil);
        esw_free_map.get(tableName_in + fieldMetaStr_in).remove(es_index);
    }
    // 标记连接为空闲链接
    public synchronized static void setESstatus2free(String tableName_in, String fieldMetaStr_in,
            Connection indexUtil){
        esw_free_map.get(tableName_in + fieldMetaStr_in).add(indexUtil);
        esw_using_map.get(tableName_in + fieldMetaStr_in).remove(indexUtil);
    }
    // 从pool中得到1个（tableName_in + fieldMetaStr_in）类的链接，ES中tableName_in + fieldMetaStr_in共同确定一个链接。
    public synchronized static Connection getVacantOrNewESwriter(String tableName_in, String fieldMetaStr_in){
        Connection indexUtil = null;
        if(esw_free_map.containsKey(tableName_in + fieldMetaStr_in) 
                && esw_free_map.get(tableName_in + fieldMetaStr_in).size()>0){
            Random random = new Random(); 
            Integer free_es_num = esw_free_map.get(tableName_in + fieldMetaStr_in).size();
            Integer es_index = Math.abs(random.nextInt()%free_es_num);
            indexUtil = esw_free_map.get(tableName_in + fieldMetaStr_in).get(es_index);//getESwriter();
            // 切换es connect的状态
            setESstatus2using(tableName_in, fieldMetaStr_in, indexUtil, es_index);
            System.out.println("get es_index:" + es_index+": "+indexUtil.toString()); 
        }
        else{
            if(!esw_using_map.containsKey(tableName_in + fieldMetaStr_in) || esw_using_map.get(tableName_in + fieldMetaStr_in).size()==0){
                System.out.println("===================>>>>> iniESList: "+tableName_in+"-"+fieldMetaStr_in);}
            if(esw_using_map.containsKey(tableName_in + fieldMetaStr_in) && esw_using_map.get(tableName_in + fieldMetaStr_in).size()>0){
                System.out.println("===================>>>>> iniESList again: "+tableName_in+"-"+fieldMetaStr_in);
            }

            //
            iniESList(tableName_in, fieldMetaStr_in);
            indexUtil = esw_free_map.get(tableName_in + fieldMetaStr_in).get(0);
            setESstatus2using(tableName_in, fieldMetaStr_in, indexUtil, 0);
            System.out.println("get es_index:" + "00" +": "+indexUtil.toString()); 
        }
        return indexUtil;
    }
    @Override
    public void run() {
        // TODO Auto-generated method stub
        Random random = new Random(); 
        int flag  = random.nextInt()%1000;
        System.err.println("thread flag: "+flag+"   (获取4个链接....模拟集群上1个4核的executor)");
        for(int i=0;i<4;i++){
            System.out.print("thread flag: "+flag+"\t------ ");
            Connection cc = getVacantOrNewESwriter("table_name","cols");//在这里 "table_name"和"cols" 只是这类Connection的名字
            //
            if(random.nextInt()%2==0){
                try {
                    //process data using Connection cc here
                    Thread.sleep(Math.abs(random.nextInt()%5*200));//
                } catch (InterruptedException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                System.out.println("把用好的链接还给Pool: "+cc);
                setESstatus2free("table_name","cols", cc);
            }
        }
    }
    public static void main(String[] args) {
        // 测试：可观察到不同线程 并发地 获取和归还链接
        new Thread(new ConnectorPool()).start();
        new Thread(new ConnectorPool()).start();
        new Thread(new ConnectorPool()).start();
        new Thread(new ConnectorPool()).start();
        new Thread(new ConnectorPool()).start();
    }
}

可观察到程序输出如下：

thread flag: -965   (获取4个链接....模拟集群上1个4核的executor)
thread flag: -965   ------ thread flag: -779    ------ ===================>>>>> iniESList: table_name-cols
thread flag: 667    ------ thread flag: -861    ------ thread flag: -779   (获取4个链接....模拟集群上1个4核的executor)
thread flag: 667   (获取4个链接....模拟集群上1个4核的executor)
thread flag: -861   (获取4个链接....模拟集群上1个4核的executor)
thread flag: -607   (获取4个链接....模拟集群上1个4核的executor)
thread flag: -607   ------ get new  ES writer pool.
get es_index:00: com.mysql.jdbc.JDBC4Connection@171a41d3
thread flag: -965   ------ get es_index:8: com.mysql.jdbc.JDBC4Connection@51ab0bbe
get es_index:1: com.mysql.jdbc.JDBC4Connection@27280786
get es_index:6: com.mysql.jdbc.JDBC4Connection@e53189e
thread flag: 667    ------ get es_index:1: com.mysql.jdbc.JDBC4Connection@28e6abad
get es_index:4: com.mysql.jdbc.JDBC4Connection@4b519730
get es_index:1: com.mysql.jdbc.JDBC4Connection@94167bd
thread flag: -965   ------ get es_index:1: com.mysql.jdbc.JDBC4Connection@1ac2d6ac
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@4b519730
thread flag: 667    ------ get es_index:4: com.mysql.jdbc.JDBC4Connection@4b519730
thread flag: 667    ------ get es_index:1: com.mysql.jdbc.JDBC4Connection@14baa9b7
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@51ab0bbe
thread flag: -607   ------ get es_index:0: com.mysql.jdbc.JDBC4Connection@5f6cfffb
thread flag: -607   ------ get es_index:1: com.mysql.jdbc.JDBC4Connection@5b5360ce
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@5b5360ce
thread flag: -607   ------ get es_index:1: com.mysql.jdbc.JDBC4Connection@51ab0bbe
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@28e6abad
thread flag: -779   ------ 把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@27280786
get es_index:0: com.mysql.jdbc.JDBC4Connection@262140c1
thread flag: -861   ------ get es_index:2: com.mysql.jdbc.JDBC4Connection@27280786
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@14baa9b7
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@27280786
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@1ac2d6ac
thread flag: -861   ------ thread flag: -965    ------ get es_index:3: com.mysql.jdbc.JDBC4Connection@27280786
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@27280786
get es_index:0: com.mysql.jdbc.JDBC4Connection@5b5360ce
thread flag: -861   ------ get es_index:2: com.mysql.jdbc.JDBC4Connection@1ac2d6ac
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@1ac2d6ac
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@262140c1
thread flag: -779   ------ get es_index:3: com.mysql.jdbc.JDBC4Connection@1ac2d6ac
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@1ac2d6ac
thread flag: -779   ------ get es_index:0: com.mysql.jdbc.JDBC4Connection@28e6abad
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@5b5360ce
把用好的链接还给Pool: com.mysql.jdbc.JDBC4Connection@28e6abad