对象池实战-编写一个数据库连接池

前言

通过对象池编写一个数据库连接池，深入学习，巩固对象池用法。

• 能代替其它数据库连接池产品

  • Hikari、DBCP、Tomcat、Druid…

• 带监控，随时了解了解连接池的情况

ConnectionPooledObjectFactory

public class ConnectionPooledObjectFactory
  implements PooledObjectFactory<MyConnection> {
  private ObjectPool<MyConnection> objectPool;

  public ObjectPool<MyConnection> getObjectPool() {
    return objectPool;
  }

  public void setObjectPool(ObjectPool<MyConnection> objectPool) {
    this.objectPool = objectPool;
  }

  @Override
  public PooledObject<MyConnection> makeObject() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection(
      "jdbc:mysql://localhost:3306/xxxx?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
      "root",
      "root123"
    );
    MyConnection myConnection = new MyConnection();
    myConnection.setConnection(connection);
    myConnection.setObjectPool(objectPool);
    return new DefaultPooledObject<>(myConnection);
  }

  @Override
  public void destroyObject(PooledObject<MyConnection> p) throws Exception {
    p.getObject().close();
  }

  @Override
  public boolean validateObject(PooledObject<MyConnection> p) {
    Connection connection = p.getObject();
    try {
      PreparedStatement statement = connection.prepareStatement("SELECT 1");
      ResultSet resultSet = statement.executeQuery();
      int i = resultSet.getInt(1);
      return i == 1;
    } catch (SQLException e) {
      return false;
    }
  }

  @Override
  public void activateObject(PooledObject<MyConnection> p) throws Exception {
    // 可以把connection额外的配置放到这里
  }

  @Override
  public void passivateObject(PooledObject<MyConnection> p) throws Exception {
    // 钝化
    MyConnection myConnection = p.getObject();
    Statement statement = myConnection.getStatement();
    if (statement != null) {
      statement.close();
    }
  }
}

MyDataSource

public class MyDataSource implements DataSource {
  private GenericObjectPool<MyConnection> pool;

  public GenericObjectPool<MyConnection> getPool() {
    return pool;
  }

  public void setPool(GenericObjectPool<MyConnection> pool) {
    this.pool = pool;
  }

  public MyDataSource() {
    ConnectionPooledObjectFactory factory = new ConnectionPooledObjectFactory();
    this.pool = new GenericObjectPool<>(factory);
    factory.setObjectPool(pool);
  }

  @Override
  public Connection getConnection() throws SQLException {
    try {
      return this.pool.borrowObject();
    } catch (Exception e) {
      throw new SQLException("获取连接失败！", e);
    }
  }

  @Override
  public Connection getConnection(String username, String password) throws SQLException {
    return this.getConnection();
  }

  @Override
  public <T> T unwrap(Class<T> iface) throws SQLException {
    throw new UnsupportedOperationException("不支持的操作！");
  }

  @Override
  public boolean isWrapperFor(Class<?> iface) throws SQLException {
    throw new UnsupportedOperationException("不支持的操作！");
  }

  @Override
  public PrintWriter getLogWriter() throws SQLException {
    throw new UnsupportedOperationException("不支持的操作！");
  }

  @Override
  public void setLogWriter(PrintWriter out) throws SQLException {
    throw new UnsupportedOperationException("不支持的操作！");
  }

  @Override
  public void setLoginTimeout(int seconds) throws SQLException {
    throw new UnsupportedOperationException("不支持的操作！");
  }

  @Override
  public int getLoginTimeout() throws SQLException {
    throw new UnsupportedOperationException("不支持的操作！");
  }

  @Override
  public Logger getParentLogger() throws SQLFeatureNotSupportedException {
    throw new UnsupportedOperationException("不支持的操作！");
  }
}

MyConnection

// 1. 回收statement
// 2. 回收resultSet
// 3. 复用Connection
public class MyConnection implements Connection {
  private Connection connection;
  private Statement statement;
  private ObjectPool<MyConnection> objectPool;
  
  // 篇幅有限，省略其它方法
  
  @Override
  public Statement createStatement() throws SQLException {
    return this.connection.createStatement();
  }

  @Override
  public PreparedStatement prepareStatement(String sql) throws SQLException {
    PreparedStatement prepareStatement = this.connection.prepareStatement(sql);
    this.statement = prepareStatement;
    return prepareStatement;
  }
  
  @Override
  public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
    PreparedStatement statement = this.connection.prepareStatement(sql, columnNames);
    this.statement = statement;
    return statement;
  }
  
  @Override
  public void close() throws SQLException {
    // 如果底层的Connection已经关闭
    if (this.isClosed()) {
      try {
        objectPool.invalidateObject(this);
      } catch (Exception e) {
        throw new SQLException(e);
      }
    }
    // 底层Connection没有关闭，可以继续复用
    else {
      try {
        objectPool.returnObject(this);
      } catch (Exception e) {
        this.connection.close();
        throw new SQLException(e);
      }
    }
  }
  
}

如何使用？

@Bean
@Primary
public DataSource dataSource(){
    return new DMDataSource();
}

如何使用 spring-boot-starter-actuator 添加监控？

// 可以用/actuator/datasource
@Endpoint(id = "datasource")
public class DataSourceEnpoint {
  private MyDataSource dataSource;

  public DataSourceEnpoint(MyDataSource dataSource) {
    this.dataSource = dataSource;
  }

  @ReadOperation
  public Map<String, Object> pool() {
    GenericObjectPool<MyConnection> pool = dataSource.getPool();
    HashMap<String, Object> map = Maps.newHashMap();
    map.put("numActive", pool.getNumActive());
    map.put("numIdle", pool.getNumIdle());
    map.put("createdCount", pool.getCreatedCount());
    return map;
  }
}