Java连接池

连接池的步骤

1. 初始化连接池
2. 销毁连接池
3. 获得一个连接
4. 释放一个连接
5. 自我检查

具体代码

MysqlPool.java

package com.feifei.cloud.pool.service;

import java.sql.Connection;

/**
 * @author xupf@wangreat.com
 * @create 2019-06-12 15:50
 */
public interface MysqlPool {

    // 池子初始化
    void init();

    // 池子销毁
    void destroy();

    // 取一个连接
    Connection getConn();

    // 释放一个连接
    void release(Connection conn);
}

MysqlPoolImpl.java

package com.feifei.cloud.pool.service.impl;

import com.feifei.cloud.pool.service.IMysqlPool;
import com.feifei.cloud.pool.util.MysqlUtil;
import org.springframework.scheduling.annotation.Scheduled;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author xupf@wangreat.com
 * @create 2019-06-12 15:50
 */
public class MysqlPoolImpl implements MysqlPool {

    // 空闲
    private LinkedBlockingQueue<Connection> idle;

    // 繁忙
    private LinkedBlockingQueue<Connection> busy;

    // 连接池的最大数
    private int maxSize = 10;

    public int getMaxSize() {
        return maxSize;
    }

    public void setMaxSize(int maxSize) {
        this.maxSize = maxSize;
    }

    // 当前已经有的
    private AtomicInteger activeSize = new AtomicInteger(0);

    @Override
    public void init() {
        idle = new LinkedBlockingQueue<>();
        busy = new LinkedBlockingQueue<>();
    }

    @Override
    public void destroy() {

    }

    @Override
    public Connection getConn() {
        // 1.从idle中取出对象，放入busy中
        Connection conn = idle.poll();

        // 2.idle有值，直接取
        if (null != conn) {
            busy.offer(conn);
            return conn;
        }

        // 3.idle无值，若池子未满，新建
        if (activeSize.get() < maxSize) {
            if (activeSize.incrementAndGet() <= maxSize) {
                // 新创建一个连接
                conn = MysqlUtil.getConn();
                busy.offer(conn);
                return conn;
            }
        }

        // 4.池子全繁忙，等待返回
        try {
            System.out.println("排队等待！");
            conn = idle.poll(10000, TimeUnit.MILLISECONDS);
            if (null == conn) {
                System.out.println("等待超时！");
                throw new RuntimeException("等待超时");
            }
            System.out.println("等到了一个对象");
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        return conn;
    }

    @Override
    public void release(Connection conn) {
        busy.remove(conn);
        idle.offer(conn); // 添加一个
    }

    /**
     * 健康检查
     */
    @Scheduled(fixedRate = 60 * 1000)
    public void check(){
        for (int i=0;i<activeSize.get();i++) {
            Connection conn = idle.poll();
            try {
                boolean valid = conn.isValid(2000);
                if (!valid) {
                    conn = MysqlUtil.getConn();
                }
                idle.offer(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

将其注入到spring的bean中

package com.feifei.cloud.pool.config;

import com.feifei.cloud.pool.service.IMysqlPool;
import com.feifei.cloud.pool.service.impl.MysqlPoolImpl;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @author xupf@wangreat.com
 * @create 2019-06-12 16:21
 */
@Configuration
public class MysqlPoolConfig {

    @Bean
    public IMysqlPool mysqlPool(){
        MysqlPoolImpl mysqlPool = new MysqlPoolImpl();
        mysqlPool.setMaxSize(9);
        mysqlPool.init();
        return mysqlPool;
    }
}

测试

package com.feifei.cloud.pool.controllers;

import com.feifei.cloud.pool.service.IMysqlPool;
import com.feifei.cloud.pool.util.MysqlUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.CountDownLatch;

/**
 * @author xupf@wangreat.com
 * @create 2019-06-12 15:56
 */
@RestController
public class MysqlPoolController {

    private CountDownLatch countDownLatch = new CountDownLatch(15);

    @Autowired
    private IMysqlPool mysqlPool;

    private final static String SQL = "INSERT INTO sys_mysql_connection_pool(name) VALUES('Zhangsan')";

    public static int business(Connection conn, String sql){
        try {
            Statement statement = conn.createStatement();
            return statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 无连接池
     * @param times
     * @return
     */
    @GetMapping("/getConn")
    public String getConn(@RequestParam int times){
        System.out.println(times + "次请求连接，请求线程：" + Thread.currentThread().getName());
        int count = 0;
        for (int i=0;i<times;i++) {
            Connection conn = MysqlUtil.getConn();
            count = business(conn, SQL);
            System.out.println("插入数据成功");
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (count > 0) {
            return "插入数据成功";
        }
        return "插入数据失败";
    }

    /**
     * 用连接池
     * @param times
     * @return
     */
    @GetMapping("/getPoolConn")
    public String getPoolConn(@RequestParam int times){
        System.out.println(times + "次请求连接，请求线程：" + Thread.currentThread().getName());
        int count = 0;
        for (int i=0;i<times;i++) {
            Connection conn = mysqlPool.getConn();
            count = business(conn, SQL);
            System.out.println("插入数据成功");
            mysqlPool.release(conn);
        }
        if (count > 0) {
            return "插入数据成功";
        }
        return "插入数据失败";
    }

    private int bfCount = 0;

    /**
     * 并发使用连接池测试
     * @param times
     * @return
     */
    @GetMapping("/getPoolBfConn")
    public String getPoolBfConn(@RequestParam int times){
        System.out.println(times + "次请求连接，请求线程：" + Thread.currentThread().getName());
        int count = 0;
        new Thread(new Runnable() {
            @Override
            public void run() {
                countDownLatch.countDown();

                try {
                    countDownLatch.await();
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                for (int i=0;i<times;i++) {
                    Connection conn = mysqlPool.getConn();
                    bfCount = business(conn, SQL);
                    System.out.println("插入数据成功");
                    mysqlPool.release(conn);
                }
            }
        }).start();
        if (bfCount > 0) {
            return "插入数据成功";
        }
        return "插入数据失败";
    }
}

工具类

package com.feifei.cloud.pool.util;

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

/**
 * mysql连接工具类
 *
 * @author xupf@wangreat.com
 * @create 2019-06-12 15:49
 */
public class MysqlUtil {
    private static final String URL = "jdbc:mysql://rm-bp126fqh01mxky2l44o.mysql.rds.aliyuncs.com:3306/blog?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC";

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return
     */
    public static Connection getConn(){
        System.out.println("创建新连接，请求线程名：" + Thread.currentThread().getName());
        try {
            return DriverManager.getConnection(URL, "username", "password");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}